strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
I am not sure what to do with this code? I have applied the code you gave me, and changed a few of my field names to make it clearer. My code is now as follows:
Option Compare Database
Private Sub cmdFilter_Click()
On Error GoTo Err_cmdFilter_Click
Dim strSQL As String
Dim stDocName As String
stDocName = "rptInstitutions"
If Nz(Me.cboCountry, "") <> "" Then
strSQL = "strCountry='" & Me.cboCountry & "' AND "
End If
If Nz(Me.cboInstitute, "") <> "" Then
strSQL = strSQL & "strInstitute='" & Me.cboInstitute & "' AND "
End If
If Nz(Me.cboLastName, "") <> "" Then
strSQL = strSQL & "strLastName='" & Me.cboLastName & "' AND "
End If
'Strip Last " And "
If strSQL <> "" Then
strSQL = Left(strSQL, (Len(strSQL) - 5))
Debug.Print strSQL
'Set the Filter property
DoCmd.OpenReport stDocName, acPreview
Reports![rptInstitutions].Filter = strSQL
Reports![rptInstitutions].FilterOn = True
Else
MsgBox "No criteria specified, opening the report unfiltered"
DoCmd.OpenReport stDocName, acPreview
End If
Exit_cmdFilter_Click:
Exit Sub
Err_cmdFilter_Click:
MsgBox Err.Description
Resume Exit_cmdFilter_Click
End Sub
But I have been having problems populating the combo boxes and getting it to work. In frmReportFilter I now have;
cboCountry
cboInstitute
cboLastName
'which I would like as my filter options.
and:
cmdClear
cmdFilter
cmdClose
The coding is in On Click of cmdfilter.
Rather than having to choose from a list of every country in the world (I want only those countries to which I have a record relating to), I queried tblStaffInstitutions, used the totals function and selected Group By txtCountryName, then selected this query as the Row Source for cboCountry.
My plan was to then make a similar queries to populate cboInstitute and cboLastName, but when I try applying the filter it will not work, returning 'Enter Parameter Value'. If I clear this then the report will open as normal. cmdClear and cmdClose are not currently working but I am focusing on the bigger problems at the moment.