Search Form and Checkboxes...

C

chelsea

I'm trying to adapt a form from one of the Microsoft Templates (Issues
Database). There is an unbound form with various text boxes, which filters a
subform when the "search" command button is pressed. I've added several
text boxes to the form to correspond with fields in my table, and they work
fine. For example, the code for the Location text box in the On_Click
command of the Search
button is

' If Location
If Nz(Me.txtbLocation) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "tblDiscrepancies.LOCATION Like '*"
& Me.txtbLocation & "*'"
End If

This works fine, and all the other text boxes seem to work fine as well.
The problem i've run into, however, is that 3 fields in my table are
Yes/No. I've added check boxes to the form for these fields, but i'm not
entirely sure about the code i should use. If the user selects the check
box, I want the search to return only those records for which the field =
Yes. If the user does not select the check box, I want the seach to return
ALL records, both Yes and No.

Right now, I have

' If Investigated
If Nz(ckbInvestigated, 0) = Yes Then
strWhere = strWhere & " AND " & "tblDiscrepancies.INVESTIGATED? = Yes"
End If

for each checkbox. But when I try to run it, i get the error

Runtime Error: 2448
You can't assign a value to this object.

When I debug, " Me.sbfrmDiscrepancies.Form.Filter = strWhere" is highlighted
in the code.

I've included the full code below:

Private Sub Search_Click()
strWhere = "1=1"

' If Survey Date From
If IsDate(Me.txtbSurveyDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "tblDiscrepancies.[DATE] >= " &
GetDateFilter(Me.txtbSurveyDateFrom)
ElseIf Nz(Me.txtbSurveyDateFrom) <> "" Then
strError = cInvalidDateError
End If

' If Survey Date To
If IsDate(Me.txtbSurveyDateTo) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "tblDiscrepancies.[DATE] <= " &
GetDateFilter(Me.txtbSurveyDateTo)
ElseIf Nz(Me.txtbSurveyDateTo) <> "" Then
strError = cInvalidDateError
End If

' If Resolved Date From
If IsDate(Me.txtbResolvedDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "tblDiscrepancies.[DATE RESOLVED] >=
" & GetDateFilter(Me.txtbResolvedDateFrom)
ElseIf Nz(Me.txtbResolvedDateFrom) <> "" Then
strError = cInvalidDateError
End If

' If Resolved Date To
If IsDate(Me.txtbResolvedDateTo) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "tblDiscrepancies.[DATE RESOLVED] <=
" & GetDateFilter(Me.txtbResolvedDateTo)
ElseIf Nz(Me.txtbResolvedDateTo) <> "" Then
strError = cInvalidDateError
End If

' If Equipment
If Nz(Me.txtbEquipment) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "tblDiscrepancies.EQUIPMENT Like '*"
& Me.txtbEquipment & "*'"
End If

' If Building
If Nz(Me.txtbBuilding) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "tblDiscrepancies.BUILDING Like '*"
& Me.txtbBuilding & "*'"
End If

' If Floor
If Nz(Me.txtbFloor) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "tblDiscrepancies.FL Like '*" &
Me.txtbFloor & "*'"
End If

' If Location
If Nz(Me.txtbLocation) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "tblDiscrepancies.LOCATION Like '*"
& Me.txtbLocation & "*'"
End If

' If IR Survey No
If Nz(Me.txtbIRSurveyNo) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "tblDiscrepancies.IR_SURVEY_NO Like
'*" & Me.txtbIRSurveyNo & "*'"
End If

' If Item No
If Nz(Me.txtbItemNo) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "tblDiscrepancies.ITEM_No Like '*" &
Me.txtbItemNo & "*'"
End If

' If Investigated
If Nz(ckbInvestigated, 0) = True Then
strWhere = strWhere & " AND " & "tblDiscrepancies.INVESTIGATED? =
True"
End If



If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "sbfrmDiscrepancies", 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.sbfrmDiscrepancies.Form.Filter = strWhere
Me.sbfrmDiscrepancies.Form.FilterOn = True
End If
End Sub


Any help would be appreciated!
 
M

[MVP] S.Clark

Try:

If ckbInvestigated Then
strWhere = strWhere & " AND " & "tblDiscrepancies.[INVESTIGATED?]"
End If


--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html


chelsea said:
I'm trying to adapt a form from one of the Microsoft Templates (Issues
Database). There is an unbound form with various text boxes, which
filters a
subform when the "search" command button is pressed. I've added several
text boxes to the form to correspond with fields in my table, and they
work
fine. For example, the code for the Location text box in the On_Click
command of the Search
button is

' If Location
If Nz(Me.txtbLocation) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "tblDiscrepancies.LOCATION Like '*"
& Me.txtbLocation & "*'"
End If

This works fine, and all the other text boxes seem to work fine as well.
The problem i've run into, however, is that 3 fields in my table are
Yes/No. I've added check boxes to the form for these fields, but i'm not
entirely sure about the code i should use. If the user selects the check
box, I want the search to return only those records for which the field =
Yes. If the user does not select the check box, I want the seach to
return
ALL records, both Yes and No.

Right now, I have

' If Investigated
If Nz(ckbInvestigated, 0) = Yes Then
strWhere = strWhere & " AND " & "tblDiscrepancies.INVESTIGATED? =
Yes"
End If

for each checkbox. But when I try to run it, i get the error

Runtime Error: 2448
You can't assign a value to this object.

When I debug, " Me.sbfrmDiscrepancies.Form.Filter = strWhere" is
highlighted
in the code.

I've included the full code below:

Private Sub Search_Click()
strWhere = "1=1"

' If Survey Date From
If IsDate(Me.txtbSurveyDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "tblDiscrepancies.[DATE] >= " &
GetDateFilter(Me.txtbSurveyDateFrom)
ElseIf Nz(Me.txtbSurveyDateFrom) <> "" Then
strError = cInvalidDateError
End If

' If Survey Date To
If IsDate(Me.txtbSurveyDateTo) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "tblDiscrepancies.[DATE] <= " &
GetDateFilter(Me.txtbSurveyDateTo)
ElseIf Nz(Me.txtbSurveyDateTo) <> "" Then
strError = cInvalidDateError
End If

' If Resolved Date From
If IsDate(Me.txtbResolvedDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "tblDiscrepancies.[DATE RESOLVED]
=
Click to expand...
" & GetDateFilter(Me.txtbResolvedDateFrom)
ElseIf Nz(Me.txtbResolvedDateFrom) <> "" Then
strError = cInvalidDateError
End If

' If Resolved Date To
If IsDate(Me.txtbResolvedDateTo) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "tblDiscrepancies.[DATE RESOLVED]
<=
" & GetDateFilter(Me.txtbResolvedDateTo)
ElseIf Nz(Me.txtbResolvedDateTo) <> "" Then
strError = cInvalidDateError
End If

' If Equipment
If Nz(Me.txtbEquipment) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "tblDiscrepancies.EQUIPMENT Like
'*"
& Me.txtbEquipment & "*'"
End If

' If Building
If Nz(Me.txtbBuilding) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "tblDiscrepancies.BUILDING Like '*"
& Me.txtbBuilding & "*'"
End If

' If Floor
If Nz(Me.txtbFloor) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "tblDiscrepancies.FL Like '*" &
Me.txtbFloor & "*'"
End If

' If Location
If Nz(Me.txtbLocation) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "tblDiscrepancies.LOCATION Like '*"
& Me.txtbLocation & "*'"
End If

' If IR Survey No
If Nz(Me.txtbIRSurveyNo) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "tblDiscrepancies.IR_SURVEY_NO Like
'*" & Me.txtbIRSurveyNo & "*'"
End If

' If Item No
If Nz(Me.txtbItemNo) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "tblDiscrepancies.ITEM_No Like '*"
&
Me.txtbItemNo & "*'"
End If

' If Investigated
If Nz(ckbInvestigated, 0) = True Then
strWhere = strWhere & " AND " & "tblDiscrepancies.INVESTIGATED? =
True"
End If



If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "sbfrmDiscrepancies", 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.sbfrmDiscrepancies.Form.Filter = strWhere
Me.sbfrmDiscrepancies.Form.FilterOn = True
End If
End Sub


Any help would be appreciated!
Click to expand...
 

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

Similar Threads

StrWhere error runtime 2448 5
Search Form runtime error 2448 1
Date Filter in VBA 1
runtime error 3075 2
Subform Parameters Not working 5
Capture filtered records only on report 4
Error when Filtering on Unbound Form with Subform 7
Problem with Runtime Error 2448 4

Top