Filtering a form with checkboxes

[viperRider]

This question has probably been asked, I just can't find it - sorry if I am double posting.

I have a form that displays cselect info from my homebrew database (ie Name, date brewed, date bottled, alc. content, what kind of batch it is (wine, beer, mead) and certain check boxes for things like if the batch has been bottled or is completed)

From this screen I would like to have a few check boxes along the bottom in the form footer that will filter the data in the form detail section. Like filter out everything that is beer (a text field) and is bottled (a yes/no field). I guess I just need a good idea to start with. some help with the code would be helpful as well. I'm not new, just rusty :)

[Minion]

This question has probably been asked, I just can't find it - sorry if I am double posting.

I have a form that displays cselect info from my homebrew database (ie Name, date brewed, date bottled, alc. content, what kind of batch it is (wine, beer, mead) and certain check boxes for things like if the batch has been bottled or is completed)

From this screen I would like to have a few check boxes along the bottom in the form footer that will filter the data in the form detail section. Like filter out everything that is beer (a text field) and is bottled (a yes/no field). I guess I just need a good idea to start with. some help with the code would be helpful as well. I'm not new, just rusty :)

Now that you've made me thirsty I guess I'll lend a bump in the right direction.

What you're going to probably want to do here is a create one sub that will apply the filter. This sub will hold all the possible filters you'll need (actually it will create it) and will be accessed by clicking the various check boxes. I don't have enough particulars at the moment on how the form and table are constructed to put together any code that would make sense but I'll give you the general idea of what will need to be done.

Essentially, you'll want the sub to create a string that will act as filter for the form (strFilter for the following example). Then use the filter property for the form. The following code is how you would do that.

Me.Filter = strFilter
Me.FilterOn = True
Me.Requery
 

Hope this gets you moving in the right direction. If you want to provide a little more details about the construction of the table and form maybe I can put together some more exacting code.

Hope this helps.

- Minion -

[viperRider]

Well, the continuous form just displays certain fields from my database, enough to get the basic idea from each batch. to the left of each row is a button to take you to another form to edit the fields. The continuouos form displays all records, but not all fields. When i apply one of these filters, i really only need to be looking at 3 yes/no fields (Future, Bottled, Completed) and one text field for the type (beer, wine, mead, or soda). i can do this pretty easily by right clicking on a field that i want to filter, but the check boxes would just make it nice!

i will try to play around with the idea you had, but might need a little more help.

if you can help me with this, consider a bottle of mead yours! (you just have to wait for it to age 6 to 12 months, my last batch was just bottle last week) :^)

[jambonjamasb]

I am doin something similar, but am having trouble getting the script working

Private Sub Pending_Changes_Click()
Dim strSQL As String
If IsNull(Me.{POL_Change_Allocated) Then
' If the combo is Null, use the whole table as the RecordSource.
Me.RecordSource = "OCP_Base_tables"
Else
strSQL = "SELECT DISTINCTROW tblOCP_Base_Tables.* FROM tblOCP_Base_Tables " & _
"INNER JOIN tblPOL_Actions ON " & _
"tblOCP_Base_Tables.OCP_Ref = tblPOL_Actions.OCP_Ref " & _
"WHERE tblPOL_Actions.POL_Change_Allocated = " & Me.Pending_Changes & ";"
Me.RecordSource = strSQL
End If
End Sub


POL Change allocated is a tickbox, if this is blank it means that the change record hasnt been updated so I am trying to get my button to return all these records on my forms page. I have to tables linked into my forms as a form "OCP_Base_tables" and subform "POL_Actions"

maybe we can mdle through together.

[araman]

I'm not an access wizard but i believe if your checkbox is a yes/no then unchecked is "false"

[Minion]

First off Araman is correct that a checkbox reads a couple values, but the easiest to work with is "True/False" so I made this alteration. Now bear with me as I don't have the db to work with I'm having to work from knowledge along and I'm still building it. I've changed a couple things around that should help. Mainly just how the recordsets were being called. I don't believe the orginal would have called the recordset correctly.

Private Sub Pending_Changes_Click()
Dim strSQL As String
Dim dbs As DAO.Database
 
Set dbs = CurrentDb
 
    If Not (Me.POL_Change_Allocated) Then
        'If combo is empty, use whole table as RS
        Me.RecordSource = dbs.OpenRecordset("OCP_Base_tables")
    Else
        strSQL = "SELECT DISTINCTROW tblOCP_Base_Tables.* FROM tblOCP_Base_Tables " & _
        "INNER JOIN tblPOL_Actions ON " & _
        "tblOCP_Base_Tables.OCP_Ref = tblPOL_Actions.OCP_Ref " & _
        "WHERE tblPOL_Actions.POL_Change_Allocated = " & Me.Pending_Changes & ";"
 
        Me.RecordSource = dbs.OpenRecordset(strSQL)
    End If
 
End Sub
 

Another possible way of accomplishing this would be to use the filter property of the form and filter the records when the box is checked or unchecked. If we can't get this working that may be a possibility.

Hope this actually works and helps.

- Minion -

[jambonjamasb]

Minion,

I changed part of the sub to

Private Sub Command56_Click()
Dim strSQL As String
Dim dbs As DAO.Database

Set dbs = CurrentDb

If Not (Me.Command56) Then

As that is the name of the button.

I then get an error for the follwoing line _

Me.RecordSource

Help states

Type mismatch (Error 13)


Visual Basic is able to convert and coerce many values to accomplish data type assignments that weren't possible in earlier versions. However, this error can still occur and has the following causes and solutions:

The variable or property isn't of the correct type. For example, a variable that requires an integer value can't accept a string value unless the whole string can be recognized as an integer.
Try to make assignments only between compatible data types. For example, an Integer can always be assigned to a Long, a Single can always be assigned to a Double, and any type (except a user-defined type) can be assigned to a Variant.

An object was passed to a procedure that is expecting a single property or value.
Pass the appropriate single property or call a method appropriate to the object.

A module or project name was used where an expression was expected, for example:
Debug.Print MyModule

Specify an expression that can be displayed.

You attempted to mix traditional Basic error handling with Variant values having the Error subtype (10, vbError), for example:
Error CVErr(n)

To regenerate an error, you must map it to an intrinsic Visual Basic or a user-defined error, and then generate that error.

A CVErr value can't be converted to Date. For example:
MyVar = CDate(CVErr(9))

Use a Select Case statement or some similar construct to map the return of CVErr to such a value.

At run time, this error typically indicates that a Variant used in an expression has an incorrect subtype, or a Variant containing an array appears in a Print # statement.
To print arrays, create a loop that displays each element individually.

For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).

Does this mean that recordsource needs to be changed to the table it is looking at?

[jambonjamasb]

I bet you are sick I ever joined this site.