Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    May 2003
    Location
    Washington, District Of Columbia, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filtering by check boxes (2000)

    I am creating a form to search a report database. Right now, I can search by auditor, auditee and keyword in the title field. There are approximately 25 check boxes for each report listing various characteristics which I would like to be able to search on. I would like users to be able to type in the auditor and then select as many characteristics as they like and have all reports matching their criteria returned. Filtering by form would work, but I'd also like to be able to do a key word search as well. Is there a way to do both? Right now I'm looking for ideas, so I'd welcome other approaches as well. Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtering by check boxes (2000)

    What is it that you can't do in Filter by Form?

    You can put a lot of unbound controls (text boxes, check boxes, ...) in the form header plus a "Filter" command button that assembles the filter to be set in code, then applies it. The filter is the "WHERE" part of an SQL statement (without 'WHERE' itself). If this seems a good idea, let us know how familiar you are with SQL and VBA.

  3. #3
    New Lounger
    Join Date
    May 2003
    Location
    Washington, District Of Columbia, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering by check boxes (2000)

    I'm somewhat familiar with SQL and VBA. Could you give me an example of what you had in mind? Thanks.

    Brandon

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtering by check boxes (2000)

    Here is some example code, to be adapted to your needs. It assumes that you have 25 unbound check boxes and 3 unbound text boxes on your form. The check boxes and text boxes are labeled for the fields they correspond to, and the name of the corresponding field has been entered in their Tag property. The TripleState property of the check boxes has been set to Yes, and they are named Check1 through Check 25. The three text boxes are named Text1 through Text3. This is the code for a "Filter" command button named cmdApplyFilter:<pre>Private Sub cmdApplyFilter_Click()
    Dim strFilter As String
    Dim i As Integer
    On Error GoTo Err_Handler
    ' Check boxes
    For i = 1 To 25
    If Not IsNull(Me.Controls("Check" & i)) Then
    strFilter = strFilter & Me.Controls("Check" & i).Tag & " = " & _
    Me.Controls("Check" & i) & " And "
    End If
    Next i
    ' Text boxes
    For i = 1 To 3
    If Me.Controls("Text" & i) <> "" Then
    strFilter = strFilter & Me.Controls("Text" & i).Tag & " = '*" & _
    Me.Controls("Text" & i) & "*' And "
    End If
    Next i

    If strFilter <> "" Then
    ' Strip Last " And "
    strFilter = Left$(strFilter, Len(strFilter) - 5)
    ' Set the Filter property
    Me.Filter = strFilter
    Me.FilterOn = True
    Else
    Me.Filter = ""
    End If
    Exit Sub
    Err_Handler:
    MsgBox Err.Description, vbExclamation
    End Sub</pre>


  5. #5
    New Lounger
    Join Date
    May 2003
    Location
    Washington, District Of Columbia, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering by check boxes (2000)

    Hans,

    Thanks for the help - the filter works well. I'm having a related problem now. The filter is used as the criteria for opening a summary page listing all the matching records. The summary form has a button to open a separate form showing a single record. When I try to open a single record, it says one of my tables is currently in use by another user or is already open by the user interface and cannot be manipulated programmatically. Do you know what this means and how I can fix it? Thanks.

    Brandon

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtering by check boxes (2000)

    This error can have several causes:
    - A table in the record source of the form is open in design view. You can't open a form as long as one of the tables contributing to its record source is open in design view.
    - You have opened a recordset of type Snapshot, or you have set the Recordset Type of a form to Snapshot. See <!mskb=197952>Microsoft Knowledge Base Article 197952<!/mskb> for the nitty-gritty.
    Does one of these explain what is happening?

  7. #7
    New Lounger
    Join Date
    May 2003
    Location
    Washington, District Of Columbia, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering by check boxes (2000)

    I made sure all the tables were closed and nothing was set to Snapshot, but the problem is still occuring. Any other ideas? Thanks for the help.

    Brandon

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtering by check boxes (2000)

    Try this: in the On Click code of the command button, you have a line DoCmd.OpenForm .... Immediately above this line, insert the following line:

    If Me.Dirty Then RunCommand acCmdSaveRecord

    Does that help?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •