Generate Reports from both Combo and List Boxes

[Tango23]

First off, sorry if this gets too verbose or wordy.

I have a form that contains several combo boxes for the user to select info from, that then goes out as the criteria for a query and the report associated with it. All of this was working fine, until one of the users decided that there is one combo box that they wish to select more then one field from.

I have already switched that combo box to a list box and after reading other forum threads on this topic, I did a test form, dropped in the following VBA code and had limited success.

Here is the code I used.


Private Sub cmdTest_Click()
Dim DB As Database
Dim QDF As QueryDef
Dim varRow As Variant
Dim strCriteria As String
Dim strSQL As String

Set DB = CurrentDb()
Set QDF = DB.QueryDefs("qryExpense&quot

strCriteria = ""
For Each varRow In cboService.ItemsSelected
If strCriteria = "" Then
strCriteria = "frmreport.cboservice In ('" & Me!cboService.Column(0, varRow) & "'"
Else
strCriteria = strCriteria & ", '" & Me!cboService.Column(0, varRow) & "'"
End If
Next varRow

strCriteria = strCriteria & &quot"

strSQL = "SELECT * FROM [Invoice Table] WHERE [Invoice Table].[Service Code] IN(" & strCriteria & ""
QDF.SQL = strSQL
DoCmd.OpenQuery "qryExpense"

Set DB = Nothing
Set QDF = Nothing
End Sub


The string (strCriteria) gets populated correctly, but when the query fires up, it is still wanting a parameter value for frmReport.cboService

Once I figure that part out, can I still combine a list box with multiple combo boxes on the same form??? Currently I am using SQL for the logic, but am not against going to VBA. using SQL I already have a WHERE statement that is several lines long. Recreating that in VBA is not the first thing I would want to do if I did not have to.

Many thanks in advance for helping me work through this problem

T

 

[mehrnosh]

Hi

The problem is with the strCriteria = "frmreport.cboservice In ('" & Me!cboService.Column(0, varRow) & "'" start your condition with "(" as in your
strSQL you have already mentioned [Invoice Table].[Service Code] IN(" hence it is searching for frmReport.cboService.


All The Best

 

[Tango23]

Thank you!!! That was of help. The query now runs nicely as long as the user selects something from that particular field (cboService).
I am now stuck on the possibility that the user will not select anything from that control, and will end up picking from anothr control on the page.
I am assuming that it is some form of SQL that would be able to capture the possibility that no choices are selected from (cboService) and that the query could still run on the other choises that are made.
please remember that in the end, this form will have 5 combo boxes, a list box and a yes/no field that the user will have available to select data from. That selected data will then be used as the criteria for the query/report

Thanks again

T