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

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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.

.

Quantcast