Report Based on a List Box

[margot]

Hello, I created a listbox with companies names and when the user selects a company a report is generated based on the company name. I created a Function below and then I created a button that will call the function and the report will be generated. If I dont select any field the report is generated showing me all fileds otherwise it gives me an error that it cannot find the company name. Can someone help me?

Thank you in advanced

[code=vb]
Private Function GetCriteria() As String
Dim stDocCriteria As String
Dim VarItm As Variant
For Each VarItm In LstSenderCompan y.ItemsSelected
stDocCriteria = stDocCriteria & "[Sender_CompanyN ame] = " & LstSenderCompan y.Column(0, VarItm) & " OR "
Next
If stDocCriteria <> "" Then
stDocCriteria = Left(stDocCrite ria, Len(stDocCriter ia) - 4)
Else
stDocCriteria = "True"
End If
GetCriteria = stDocCriteria
End Function



Private Sub cmdSenderCompan y_Click()

DoCmd.OpenRepor t "rptSenderCompa ny", acViewPreview, , GetCriteria()
[/code]

[JKing]

Hello, I created a listbox with companies names and when the user selects a company a report is generated based on the company name. I created a Function below and then I created a button that will call the function and the report will be generated. If I dont select any field the report is generated showing me all fileds otherwise it gives me an error that it cannot find the company name. Can someone help me?

Thank you in advanced

[code=vb]
Private Function GetCriteria() As String
Dim stDocCriteria As String
Dim VarItm As Variant
For Each VarItm In LstSenderCompan y.ItemsSelected
stDocCriteria = stDocCriteria & "[Sender_CompanyN ame] = " & LstSenderCompan y.Column(0, VarItm) & " OR "
Next
If stDocCriteria <> "" Then
stDocCriteria = Left(stDocCrite ria, Len(stDocCriter ia) - 4)
Else
stDocCriteria = "True"
End If
GetCriteria = stDocCriteria
End Function



Private Sub cmdSenderCompan y_Click()

DoCmd.OpenRepor t "rptSenderCompa ny", acViewPreview, , GetCriteria()
[/code]

Hi there, try this...
[code=vb]
Private Function GetCriteria() As String
Dim stDocCriteria As String
Dim VarItm As Variant
For Each VarItm In Me.LstSenderCom pany.ItemsSelec ted
stDocCriteria = stDocCriteria & "[Sender_CompanyN ame] = " & Me.LstSenderCom pany.ItemData(V arItm) & " OR "
Next
If stDocCriteria <> "" Then
stDocCriteria = Left(stDocCrite ria, Len(stDocCriter ia) - 4)
Else
stDocCriteria = "True"
End If
GetCriteria = stDocCriteria
End Function



Private Sub cmdSenderCompan y_Click()
DoCmd.OpenRepor t "rptSenderCompa ny", acViewPreview, , GetCriteria()
End Sub
[/code]

If you dont want the report to show when there is no data you can code the No Data event of the report to cancel= true and prompt the user with a message box. Another alternative would be to put your DoCmd.OpenRepor t command in an if statement that checks to see if getcriteria returns "true" if so prompt the user to select an item else open the report.

Hope this helps

Jking

[margot]

Hi there, try this...
[code=vb]
Private Function GetCriteria() As String
Dim stDocCriteria As String
Dim VarItm As Variant
For Each VarItm In Me.LstSenderCom pany.ItemsSelec ted
stDocCriteria = stDocCriteria & "[Sender_CompanyN ame] = " & Me.LstSenderCom pany.ItemData(V arItm) & " OR "
Next
If stDocCriteria <> "" Then
stDocCriteria = Left(stDocCrite ria, Len(stDocCriter ia) - 4)
Else
stDocCriteria = "True"
End If
GetCriteria = stDocCriteria
End Function



Private Sub cmdSenderCompan y_Click()
DoCmd.OpenRepor t "rptSenderCompa ny", acViewPreview, , GetCriteria()
End Sub
[/code]

If you dont want the report to show when there is no data you can code the No Data event of the report to cancel= true and prompt the user with a message box. Another alternative would be to put your DoCmd.OpenRepor t command in an if statement that checks to see if getcriteria returns "true" if so prompt the user to select an item else open the report.

Hope this helps

Jking

Thank you JKing I tried the code that you suggested; however I get the same error. For some reason I get the same error. Hope you can help.

[Error]
Sintax error (Missing Operator) in a query expression.
'([Sender_CompanyN ame]=Dell)'
[/End]

[margot]

Thank you JKing I tried the code that you suggested; however I get the same error. For some reason I get the same error. Hope you can help.

[Error]
Sintax error (Missing Operator) in a query expression.
'([Sender_CompanyN ame]=Dell)'
[/End]

I understand why I got that error and I do not get it anymore; however, it still does not work property. Now when I run it a window pop ups and says "Enter Parameter Value" and If I type the company it will run the report. However I do not need the pop up window. Plesase help.

Best,
Margot

[JKing]

If you're getting the enter parameter value box showing check to ensure the following:
1) The field you are using in your criteria string is spelt correctly
2) The query behind your report has all fields spelt correctly

[margot]

If you're getting the enter parameter value box showing check to ensure the following:
1) The field you are using in your criteria string is spelt correctly
2) The query behind your report has all fields spelt correctly

Thank you is all fixed now.