Form with two list boxes how to pass selections to report

[peciam]

Hi to all,

I have a form with two list boxes(miltiselect).

First box I want to allow user to select one or many offices, second box I want user to be able to select one or many job titles. My code below works ok when one selection of office and job title, but not working correctly when selection one office and many job titles, I get all offices for the job titles.

The report source is a query.

Query points to form, report points to query.

In my form I have the following: Under office I ahve in the criteria as:Like [forms]![frm_selection]![office]&"*"
and under job title criteriaI have: Like [forms]![FRM_selection]![job_title]

Here's my code:


Private Sub OK_Click()
On Error GoTo err_preview_report_click
Dim title, wclause, RO_name, ro
Dim frm As Form, ctl As Control, ctl1 As Control
Dim varItm As Variant
Dim strlist, strWhere, strTitle, strGroup As String
Dim strlsit2 As String

strWhere = "[reg office] = "
strTitle = "[Type Contract]= "
Set ctl = Me.[Ro_list]
Set ctl1 = Me![report_list]


'Build my list of offices to pass to query
For Each varItm In ctl.ItemsSelected
If strWhere = "[reg office] = " Then
strWhere = strWhere & "'" & ctl.ItemData(varItm) & "'"
Else
strWhere = strWhere & " or [reg office] = '" & ctl.ItemData(varItm) & "'"
End If
Next varItm

'Build my list of type contracts to pass to query
For Each varItm In ctl1.ItemsSelected
If strTitle = "[Type Contract]= " Then
strTitle = strTitle & "'" & ctl1.ItemData(varItm) & "'"
Else
strTitle = strTitle & " or [Type contract] = '" & ctl1.ItemData(varItm) & "'"
End If
Next varItm

'strGroup = strWhere &" and " strTitle
'strWhere = strWhere & " or [reg office] = '" & ctl.ItemData(varItm) & "'"
strGroup = strWhere & " and " & strTitle
wclause = "rpt_current_diskette_paper_report"

' DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip
DoCmd.OpenReport wclause, acViewNormal, , strGroup
'DoCmd.OpenReport wclause, acViewPreview, WhereCondition:=strGroup
If wclause = "" Then

Resume EXIT_OK_CLICK
'GoTo err_preview_report_click
'MsgBox "You must Select a report!"
'Resume EXIT_OK_CLICK
End If

Me.[report_list] = ""
Me.[Ro_list] = ""
Call Clear_MultiSelect
EXIT_OK_CLICK:
Exit Sub

err_preview_report_click:
MsgBox "Select a report!"
Resume EXIT_OK_CLICK

End Sub

 

[KirbyWallace]

Just a suggestion:

On the line just before you open the report, add a line:

Debug.Print wclause

And then look at the string that you've actually constructed. SQL Errors are usually much more obvious if you look at them first. Things that make you say "That ought to work" when you are building it, sometimes make you say "what the..." when you actually look at them yourself.

 

[PHV]

Replace this:
strGroup = strWhere & " and " & strTitle
with this:
strGroup = "(" & strWhere & ") and (" & strTitle & ")"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[peciam]

Hey PHV,

Thanks for your help, it worked fine.

Now, why did it work?

Thanks,

Pecia

 

[PHV]

You mixed AND & OR operators without proper parenthesis ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886