I have database with over 20,000 files. I've created a form into which you can enter criteria by selecting an option box as a way of choosing the type of filtering. I would like to add a serious of check boxes that are unrelated to the option boxes and would allow the user to include all or some of the check box selections in additon to the option box they choose as a way to filter the information. Does that make any sense? and is it possible?

As it stands my option boxes work with out a hitch I just need to figure away to add the check boxes to my criteria.

any help would be greatly appreciated...

Hello,

Just continue building a dynamix SQL string in code, adding checkbox criteria as you go. Then assign the finished SQL string to the Form's RecourdSource property. I put together a little app below that uses option buttons to select a country (mutually exclusive) and check boxes (not mutually exclusive) to select a city or cities within a country, then display the result set on a continuous form. Might give you some ideas:

HTH,

Bob

Ok I've been playing around with this adapting it to my application. I'm receiving a runtime error '2427' "you have entered an expression that has no value" at[ If .Value Then ]
I'm not sure what i need to do to correct it. I've tried adding
[If dir.Value Then ]
-dir is the field that the check boxes apply to-
but then I get a run time error '13' typed mismatch
What do i need to do to fix the run time error.

    ' Check boxes for criteria defined as
    ' not mutually exclusive - City
    ' See if any checkboxes are checked.
    Set col = New Collection
    For Each m_ctl In Me.Controls
        With m_ctl
            If .ControlType = acCheckBox Then
                If .Value Then
                    col.Add m_ctl
                End If
            End If
        End With
    Next m_ctl

Hmm…that’s really bizarre. A checkbox control has to have on of three values (True = -1, False =0, or Null (that kinda’ greyed-out state before you ever select it).

If .Value Then

Is just shorthand for:

If m_ctl.Value = True Then

or, it the checkbox value = -1, then add it to the collection.

A checkbox’s value is still 0, -1, or Null even if its disabled.

Run this:

Set col = New Collection
    For Each m_ctl In Me.Controls
        With m_ctl
            Debug.Print .ControlType 'Should be 106 for a checkbox
            If .ControlType = acCheckBox Then
                Debug.Print m_ctl.Value 'What's this print before your code breaks???
                If .Value = True Then
                    col.Add m_ctl
                End If
            End If
        End With
    Next m_ctl


Is it possible that you somehow declared m_ctl as a type of control that doesn’t support the value property. I used:

Private m_ctl As Control

The disadvantage of this type of declaration is that is uses late binding, that is, your program doesn’t know at compile time what type of control this variable will eventually reference. So if you try and use Intellisense by typing m_ctl.Value you won’t see a value property listed, just the class members for a generic Control class. But Value refers to the value of a checkbox (either 0, -1, or Null).

Or try this. Set a break point in your code at “Private Sub Form_Load()”, open the Locals Window (Alt + V + S, or open it from the View Menu), and open the form. When the code runs you’ll see a treeview open in the Locals window with a root node named “Me” (it should be collapsed with a “+” sign next to the expression “Me”. Expand the “Me” node and scroll down through the child nodes until you see “m_ctl” in the “Expression” column. Its Value should read “Nothing” and its Type should read “Control”. As you go through the Controls collection loop, its type should change to "Control/TextBox" when you reach a text box, and you should be able to expand the node and see a value property.

Maybe some of that will help you get a little further, if not, post again.

Bob

Hey Bob,

I'd like to take a moment and thank you for your time. I surly do appreciate it.

I went ahead and edited the line like this:

 If Me.Check171.Value = True Then

that seem to get me pass that but I dont know if thats correct. That probably will only work for that check box, I'm guessing. Keep in mind I've only been working in access for 3 weeks. thats all the experience I have. This is all very new to me.

But it did get me past that but I ran into another run-time error '2101' "setting you entered isn't valid for this property.

so it makes me wonder if I have to go into more detail about my form.
and my search criteria's. But let me show you how I've altered this and maybe you can point out my problems.

Private Sub cmdFsearch_Click()

    ' Variable to hold filter string.
    Dim strFilter As String
    Dim strSQL As String I placed this here instead of calling out the Const strSQL - if you see in case 4 I want to use a name search. It was having a problem defining this as a constant.
    Dim strSearchCriteria As String
    Dim dbs As Database
    Dim qdf As QueryDef
    Dim col As Collection 'To hold collection of checked check boxes

    ' Option group for criteria defined as
    ' mutually exclusive - Tcounty
    Select Case Me.FsearchOption
        Case 1
            strFilter = strSQL & " WHERE [Range] = txtRange"
I have 2 more txtBoxes I would like to add into this case. How do can I go about doing that?
        Case 2
            strFilter = strSQL & " WHERE [Recording Date] = Between txtStartdate And txtEnddate"
        Case 3
            strFilter = strSQL & " WHERE [Recording No] = txtRecNo"
        Case 4
        strSearchCriteria = Me.txtName

        If Not IsNull(strSearchCriteria) Then

            Set dbs = CurrentDb

            dbs.QueryDefs.Refresh

            ' If qryData (the form's recordsource) exists, delete it.
            For Each qdf In dbs.QueryDefs
                If qdf.Name = "Qname" Then
                    dbs.QueryDefs.Delete qdf.Name
                End If
            Next qdf

            ' Create SQL string based on new search parameters.
            strSQL = "SELECT * FROM Tcounty WHERE [Name] LIKE '*" & strSearchCriteria & "*'"

            ' Create new QueryDef object.
            Set qdf = dbs.CreateQueryDef("Qname", strSQL)

            ' Assign new query def object to form's recordsource property
            Me.RecordSource = "Qname"
            DoCmd.OpenQuery "Qname"
            DoCmd.OpenForm "Fname"


            Set dbs = Nothing

        End If
        'No criteria specified.
        Case Else
            strFilter = strSQL
    End Select

    ' Check boxes for criteria defined as
    ' not mutually exclusive - dir
    ' See if any checkboxes are checked.
    Set col = New Collection
    For Each m_ctl In Me.Controls
        With m_ctl
            If .ControlType = acCheckBox Then
                If Me.Check171.Value = True Then
                    col.Add m_ctl
                End If
            End If
        End With
    Next m_ctl

    ' Was a Dir criteria (checkbox) selected?
    If col.Count <> 0 Then
        'Add chekbox criteria to filter string
        strFilter = strFilter & " AND [Dir] IN ("
        For Each m_ctl In col
            Select Case m_ctl.Name
                Case "check171"
                    strFilter = strFilter & "'NE', "
                Case "chkSE"
                    strFilter = strFilter & "'SE', "
                Case "chkSW"
                    strFilter = strFilter & "'SW', "
                Case "chkNW"
                    strFilter = strFilter & "'NW', "
                Case "chkN"
                    strFilter = strFilter & "'N', "
                Case "chkE"
                    strFilter = strFilter & "'E', "
                Case "chkS"
                    strFilter = strFilter & "'S', "
                Case "chkW"
                    strFilter = strFilter & "'W', "
            End Select
        Next
        ' Remove trailing "," and " " and append closing paren and semi-colon
        strFilter = Left$(strFilter, Len(strFilter) - 2) & ");"
    End If

    ' Assign strFilter to Form's RecordSourece property.
    Me.RecordSource = strFilter
    Me.Requery

End Sub

As i said I'm in the process of altering it to work for my needs. does this help explain my issues?

Bob,

does

Private m_ctl As Control

belong in the general - declarations?

Ok I tried running your suggestion and had a run-time error at

Debug.Print m_ctl.Value 'What's this print before your code breaks???

  Yes.

UncheckAllCheckBoxes, DisableAllCheckBoxes, and cmdApplyFilter_Click all need to be able to see it.

I'll get back to you in a bit after reading over your other post.

Best,

Bob

First change:


to:

Your variables need to be exposed outside of the string (double quotes)

Let me know how that runs. But you have too hurry! Just got the call.

The silver bullets are runnin'!
Off to steelhead alley!!
Its tight lines and screamin' reels time!!!
Goin' fishin'!!!!
No idea when I'll be back!!!!!

Bob

Who put that single quote their!?!?! Turn my back for a second...:):)

Should be:

As you build your SQL string be sure and do a lot of:

Debug.Print strSQL

and paste the results into Notepad or something so you can be sure your SQL looks right as your program progresses.

Bob