Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Re: Search Form and Checkboxes...

Discussion in 'Information Technology' started by [MVP] S.Clark, Jul 28, 2006.

  1. [MVP] S.Clark

    [MVP] S.Clark
    Expand Collapse
    Guest

    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" <chelsea@discussions.microsoft.com> wrote in message
    news:06E29700-58D7-4661-B74D-333BBF9BAF7C@microsoft.com...
    > 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!
    >
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Indian American Couple Donates For Sikh, Punjabi Culture Research In US Breaking News Aug 19, 2016
    Soul Search By Swarn Singh Bains Book Reviews & Editorials May 25, 2016
    Harpal Singh Kumar Cancer Researcher Knighted By Queen Elizabeth II Sikh Personalities Jan 5, 2016
    Movies Ashdoc's Movie Review---dozakh In Search Of Heaven ( 2015 ) Theatre, Movies & Cinema Apr 6, 2015
    Research Shows NFL Quarterbacks Play Better With Beards Business, Lifestyle & Leisure Nov 23, 2014

Share This Page