Search Form, If/Then/Else, Checkboxes...

G

Guest

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.
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
TRUE/FALSE. 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 =
TRUE. If the user does not select the check box, I want the seach to return
ALL records, both TRUE and FALSE.

Any help would be appreciated!
 
M

Marshall Barton

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.
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
TRUE/FALSE. 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 =
TRUE. If the user does not select the check box, I want the seach to return
ALL records, both TRUE and FALSE.
Click to expand...

If Nz(checkboxa, 0) = True Then
strWhere = strWhere & " AND fielda = True"
End If
 
G

Guest

Thanks for your help. Unfortunately, it's still not working quite right.
When the checkbox is not selected, it works fine. When the checkbox is
selected, 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 copied 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 further suggestions?
 
M

Marshall Barton

If your investigated field name really has a ? in it, then
you must enclose the name in [ ]. This is required for
names that contain any non-alphabetic character (except
underscore) so it is best to avoid all funky characters.

strWhere = strWhere & " AND " & _
"tblDiscrepancies.[INVESTIGATED?] = True"

Whenever you have trouble putting together a complicated
string, it can be a big help if right after you build the
string, add either
MsgBox strWhere
or
Debug.Print strWhere
so you can see the string as it will be used.
--
Marsh
MVP [MS Access]

Thanks for your help. Unfortunately, it's still not working quite right.
When the checkbox is not selected, it works fine. When the checkbox is
selected, 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 copied 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 further suggestions?
Click to expand...
 
G

Guest

Thanks so much. That was a real "duh" moment on my part.

Marshall Barton said:
If your investigated field name really has a ? in it, then
you must enclose the name in [ ]. This is required for
names that contain any non-alphabetic character (except
underscore) so it is best to avoid all funky characters.

strWhere = strWhere & " AND " & _
"tblDiscrepancies.[INVESTIGATED?] = True"

Whenever you have trouble putting together a complicated
string, it can be a big help if right after you build the
string, add either
MsgBox strWhere
or
Debug.Print strWhere
so you can see the string as it will be used.
--
Marsh
MVP [MS Access]

Thanks for your help. Unfortunately, it's still not working quite right.
When the checkbox is not selected, it works fine. When the checkbox is
selected, 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 copied 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 further suggestions?
Click to expand...
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

Search Form and Checkboxes... 1
Checkboxes in a search form and VBA code 3
Access 2007 Search Form 2
search for multiple records in a form 4
runtime error 3075 2
Refreshing filter on Subform in Access 2010 0
Help with Coding Search Form 3
Search Form Parameter Not Working 4

Top