search button to report based on multiple combo boxs ( combo criteria based upon queries )

[kupppppz]

So I have this form that wants to read four combo boxes and then
utilize a search button depending on what criteria you choose from the
combo boxes.

Example:


Name [ ] (combo box) - Agent Name (Combo Label)
Issue [ ] (combo box) - Combo4 (Combo Label)
Resource [ ] (combo box) - Combo17 (Combo Label)
Manager [ ] (combo box) - Combo19 (Combo Label)

[ SEARCH ] (search button)

Now depending what you choose, I would like the program to search based
on that. For example, if you choose the name only, it should open the
"report" based on name only. If you choose Issue only, it will return
based on issue, so on and so forth.

Right now I am using the following code, but I can't seem to get it to
work. Any assistance would be stellar.

Private Sub SEARCH_ALL_Click()

Dim stDocName As String
Dim strSQL As String
On Error Resume Next

Me!Combo11.Value = "" 'Clear the Search Day
Me!lstMonth.Value = "" 'Clear the Search Month

stDocName = "Report1"
strSQL = ""

If IsNull(Me![Agent Name]) = False Then
If Len(strSQL) = 0 Then
strSQL = strSQL & "Agent Name = '" & Me.Agent_Name & "'"
Else
strSQL = strSQL & " AND Agent Name = '" & Me.Agent_Name & "'"
End If
End If

If IsNull(Me![Combo4]) = False Then
If Len(strSQL) = 0 Then
strSQL = strSQL & "Combo4 = '" & Me.Combo4 & "'"
Else
strSQL = strSQL & " AND Combo4 = '" & Me.Combo4 & "'"
End If
End If

If IsNull(Me![Combo17]) = False Then
If Len(strSQL) = 0 Then
strSQL = strSQL & "Combo17 = '" & Me.Combo17 & "'"
Else
strSQL = strSQL & " AND Combo17 = '" & Me.Combo17 & "'"
End If
End If

If IsNull(Me![Combo19]) = False Then
If Len(strSQL) = 0 Then
strSQL = strSQL & "Combo19 = '" & Me.Combo19 & "'"
Else
strSQL = strSQL & " AND Combo19 = '" & Me.Combo19 & "'"
End If
End If

DoCmd.OpenReport stDocName, acPreview, , strSQL

End Sub

Again I would like to thank anyone for their assistance ahead of time.

 

[kupppppz]

I should add...I removed the first "On error resume next"

 

[Andy UKAug]

This is a classic Access technique so you should be able to get it
working. You don't specify exactly how the code is failing to work
(errors? does the report open blank?). I can't see any immediate issue
with the code, but I can tell you how to solve this type of problem.

Firstly you want to set a breakpoint on the docmd line of code. Then
when the code breaks look at the value in the strSQL variable. This
will probably solve the problem. If it does not proceed as follows :-

If you then use the immediate window in the debugging environment to
print out the strSQL and then paste the strSQL with WHERE clause in a
new query, using the same SQL as you use in the report you will be able
to validate that the strSQL is working correctly.

Hope this helps

Andy Couch
UK Access User Group

 

[kupppppz]

It comes up with Run-time error '3075'

Syntax error ( missing operator ) in query expression '(Agent Name =
'Persons name')'.

 

[kupppppz]

This is what I am working with right now and here are the errors below:

Form looks as follows:

[Agent Name Combo Box] -> Uses a query for Combo results. Object
= Agent Name
[Issue Combo Box] -> Uses a query for Combo results. Object =
Combo4
[Resource Combo Box] -> Uses a query for Combo results. Object =
Combo17
[Team Manager Combo Box] -> Uses a query for Combo results.
Object = Combo19

[ REPORT SEARCH BUTTON ] -----> Based on the four Combo Box choices,
this opens a generic report. * This needs to be open ended so that it
will not crash when multiple entries are inputted into the above.

Private Sub SEARCH_ALL_Click()

Dim stDocName As String
Dim strSQL As String

Me!Combo11.Value = "" 'Clear the Search Day
Me!lstMonth.Value = "" 'Clear the Search Month
stDocName = "Report1"

If IsNull(Me![Agent Name]) = False Then
If Len(strSQL) = 0 Then
strSQL = strSQL & "Agent Name = '" & Me.Agent_Name & "'"
Else
strSQL = strSQL & " AND Agent Name = '" & Me.Agent_Name & "'"
End If
End If

If IsNull(Me![Combo4]) = False Then
If Len(strSQL) = 0 Then
strSQL = strSQL & "Combo4 = '" & Me.Combo4 & "'"
Else
strSQL = strSQL & " AND Combo4 = '" & Me.Combo4 & "'"
End If
End If

If IsNull(Me![Combo17]) = False Then
If Len(strSQL) = 0 Then
strSQL = strSQL & "Combo17 = '" & Me.Combo17 & "'"
Else
strSQL = strSQL & " AND Combo17 = '" & Me.Combo17 & "'"
End If
End If

If IsNull(Me![Combo19]) = False Then
If Len(strSQL) = 0 Then
strSQL = strSQL & "Combo19 = '" & Me.Combo19 & "'"
Else
strSQL = strSQL & " AND Combo19 = '" & Me.Combo19 & "'"
End If
End If

DoCmd.OpenReport stDocName, acPreview, , strSQL

End Sub

Currently it displays when choosing only agent name:
Run-time error '3075'
Syntax error ( missing operator ) in query expression '(Agent Name =
'Persons name')'.
When choosing multiple selections, the following is displayed:

Run-time error '3075'
Syntax error ( missing operator ) in query expression '(Agent Name =
'Persons name' AND Combo4 =
'Issue' AND Combo17 = 'Resource Name' AND Combo19 = ' Team manager
name')'.

So from what I can read, it is taking the info perfectly, but it will
not open the report.

Any ideas would be warm welcomed.

Sincerely,

 

[dlesandrini]

I created a sample of how I do it and you can download the source at
this ftp site ...

ftp://ftp.amazecreations.com/ftp/ReportCriteria.zip

It employs the system I use every time I pass criteria around. There
is a module with GET and SET procdeures for the criteria expressions.
The form sets them and the query gets them. I can add error handling
so that if a value is missing, a default may be added. If the form
isn't open, it still runs.

Just use the query as the source of a report and you're done.

Danny Lesandrini

 

[kupppppz]

Thanks for the assistance, however I can't seem to download from that
link. EEk!