Multiple criteria selected using checkboxes determine query result

G

GrimesRL

I have a form with unbound checkboxes. Two groups of checkboxes are within a
frame that only allows one checkbox to be selected within that group and then
a list of checkboxes that allows the user to select the location from which
the data will represent. Based on the checkboxes, the filter retrieves the
records using a query and populates a subform in the footer of the form.

Since the checkbox is considered a value, but the field it matches against
in the query is text, the code bombs out.

I'm using Access 2003 and appreciate any help you can offer. I haven't done
much programming in years and I have spent a lot of time on this and getting
no where fast.

Thanks,

My code looks like this:

Private Sub Search_Click()

Dim strWhere As String
Dim strError As String

strWhere = "1=1"


' If Business Line Acquisition
If Nz(Me.CkAcq.Value) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "qryBudgetUpdateInput.[business
line] = " & Me.CkAcq.Value & ""

End If

' If Location is Jacksonville
If Nz(Me.ckJax.Value) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "qryBudgetUpdateInput.location = '"
& Me.ckJax.Value & "'"
End If

' If Category is Travel
If Nz(Me.CkTravel) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "qryBudgetUpdateInput.travel = '" &
Me.CkTravel & "'"
End If


If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "subfrmBudgetUpdate", acFormDS, , strWhere,
acFormEdit, acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.SubfrmBudgetUpdate.Form.Filter = strWhere
Me.SubfrmBudgetUpdate.Form.FilterOn = True
End If


End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top