Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 20th, 2006, 11:17 AM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default Using check boxes to filter

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...

Reply With Quote
  #2 (permalink)  
Old January 21st, 2006, 03:31 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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:

Code:
Option Compare Database
Option Explicit

'Set Form's default recordset
Const strSQL = "SELECT CustomerName, Country, City FROM tblCustomers"
Private m_ctl As Control

Private Sub cmdApplyFilter_Click()

    ' Variable to hold filter string.
    Dim strFilter As String
    Dim col As Collection 'To hold collection of checked check boxes

    ' Option group for criteria defined as
    ' mutually exclusive - Country
    Select Case Me.opgCountry
        Case 1
            strFilter = strSQL & " WHERE [Country] = 'USA'"
        Case 2
            strFilter = strSQL & " WHERE [Country] = 'Canada'"
        'No criteria specified.
        Case Else
            strFilter = strSQL
    End Select

    ' 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

    ' Was a City criteria (checkbox) selected?
    If col.Count <> 0 Then
        'Add chekbox criteria to filter string
        strFilter = strFilter & " AND [City] IN ("
        For Each m_ctl In col
            Select Case m_ctl.Name
                Case "chkBoston"
                    strFilter = strFilter & "'Boston', "
                Case "chkMontréal"
                    strFilter = strFilter & "'Montréal', "
                Case "chkSanFrancisco"
                    strFilter = strFilter & "'San Francisco', "
                Case "chkVancouver"
                    strFilter = strFilter & "'Vancouver', "
            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

Private Sub cmdRemoveFilter_Click()

    Call UncheckAllCheckBoxes
    chkBoston.Visible = True
    chkMontréal.Visible = True
    chkSanFrancisco.Visible = True
    chkVancouver.Visible = True
    Me.opgCountry.Value = Null

    ' Restore original RecordSource property value
    Me.RecordSource = strSQL & ";"

End Sub

Private Sub opgCountry_Click()

    Call UncheckAllCheckBoxes

    ' Display Apporpriate city checkbox criteria
    Select Case Me.opgCountry
        Case 1 'USA
            chkBoston.Visible = True
            chkMontréal.Visible = False
            chkSanFrancisco.Visible = True
            chkVancouver.Visible = False
        Case 2
            chkBoston.Visible = False
            chkMontréal.Visible = True
            chkSanFrancisco.Visible = False
            chkVancouver.Visible = True
    End Select
End Sub

Private Sub UncheckAllCheckBoxes()
    'Clear City checkboxes
    For Each m_ctl In Me.Controls
        With m_ctl
            If .ControlType = acCheckBox Then
                .Value = 0
            End If
        End With
    Next m_ctl
End Sub
HTH,

Bob



Reply With Quote
  #3 (permalink)  
Old January 25th, 2006, 04:02 PM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Reply With Quote
  #4 (permalink)  
Old January 25th, 2006, 05:08 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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


Reply With Quote
  #5 (permalink)  
Old January 25th, 2006, 05:44 PM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?


Reply With Quote
  #6 (permalink)  
Old January 25th, 2006, 05:50 PM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Bob,

does

Private m_ctl As Control

belong in the general - declarations?

Reply With Quote
  #7 (permalink)  
Old January 25th, 2006, 05:56 PM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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???



Reply With Quote
  #8 (permalink)  
Old January 25th, 2006, 06:01 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

 
Quote:
quote:does
Quote:

Private m_ctl As Control

belong in the general - declarations?
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

Reply With Quote
  #9 (permalink)  
Old January 25th, 2006, 06:22 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

First change:

Code:
Case 1
   strFilter = strSQL & " WHERE [Range] = txtRange"
Case 2
   strFilter = strSQL & " WHERE [Recording Date] = Between txtStartdate And txtEnddate"
Case 3
   strFilter = strSQL & " WHERE [Recording No] = txtRecNo"

to:

Code:
Case 1
    strFilter = strSQL & " WHERE [Range] = " & txtRange '
Case 2
    strFilter = strSQL & " WHERE [Recording Date] = Between " & txtStartdate & " And " & txtEnddate
Case 3
   strFilter = strSQL & " WHERE [Recording No] = " & txtRecNo
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

Reply With Quote
  #10 (permalink)  
Old January 25th, 2006, 06:29 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Code:
strFilter = strSQL & " WHERE [Range] = " & txtRange '
Who put that single quote their!?!?! Turn my back for a second...:):)

Should be:

Code:
strFilter = strSQL & " WHERE [Range] = " & txtRange
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


Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Subform Filter based on Multiselect Combo boxes natwong Access 2 November 16th, 2006 10:05 AM
Subform Filter using Multiple Combo boxes natwong BOOK: Expert One-on-One Access Application Development 0 November 13th, 2006 03:07 PM
Check Boxes Allan320 Access VBA 3 June 2nd, 2006 05:45 AM
Filter by Combo Boxes Corey Access VBA 5 June 1st, 2006 07:33 PM
Using check boxes in datagrid ractim ADO.NET 2 September 8th, 2004 08:28 AM



All times are GMT -4. The time now is 11:12 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.