Use check boxes on form to filter datasheet

[Guest]

I have a table with 35 attributes. I would like to use a form with check
boxes on it so that the user could select only the attributes that they
would like to view, i.e. if they only needed to compare 3 of the 35 columns
they could just check those 3 boxes. I have the form with the check boxes
and a button to run the query but I can't figure out the query. Is this even
possible?

 

[Guest]

You could amend a query's SQL property in code behind a button on the form.
Firstly create and save a query to return all columns. In form design by
naming the checkboxes so that they reflect the column names you can then
build a string expression by looping through them. Say they are named
chkFirstName, chkLastName etc where FirstName and LastName are column names
the code in a button's Click event would go something like this:

Dim dbs as DAO.Database, qdf As DAO.QueryDef
Dim ctrl As Control
Dim strSQL As String

Set dbs = CurrentDb
Set qdf = dbs.Querydefs("YourQuery")

For each ctrl In Me.Controls
If Left(ctrl.Name,3) = "chk" Then
If ctrl = True Then
strSQL = strSQL & ",[" & Mid(ctrl.Name,4) & "]"
End If
End If
Next ctrl

' remove leading comma
strSQL = Mid(strSQL,2)
' build SQL for query
strSQL = "SELECT " & strSQL & " FROM [YourTable]"
' assign query's SQL property
qdf.SQL = strSQL
' open query
DoCmd.OpenQuery "YourQuery"

The above code uses DAO, so ensure that you have a reference to the
Microsoft DAO object library (Tools|References on the VBA menu bar) as since
2000 Access has used ADO as the default data access technology.

Ken Sheridan
Stafford, England

 

[Guest]

Thanks for your help Ken. I still can't get it to work though. When it runs
it says "Item not found in this collection." In the IF statement when it
calls ctrl.Name, what is "Name" referencing?

You could amend a query's SQL property in code behind a button on the form.
Firstly create and save a query to return all columns. In form design by
naming the checkboxes so that they reflect the column names you can then
build a string expression by looping through them. Say they are named
chkFirstName, chkLastName etc where FirstName and LastName are column names
the code in a button's Click event would go something like this:

Dim dbs as DAO.Database, qdf As DAO.QueryDef
Dim ctrl As Control
Dim strSQL As String

Set dbs = CurrentDb
Set qdf = dbs.Querydefs("YourQuery")

For each ctrl In Me.Controls
If Left(ctrl.Name,3) = "chk" Then
If ctrl = True Then
strSQL = strSQL & ",[" & Mid(ctrl.Name,4) & "]"
End If
End If
Next ctrl

' remove leading comma
strSQL = Mid(strSQL,2)
' build SQL for query
strSQL = "SELECT " & strSQL & " FROM [YourTable]"
' assign query's SQL property
qdf.SQL = strSQL
' open query
DoCmd.OpenQuery "YourQuery"

The above code uses DAO, so ensure that you have a reference to the
Microsoft DAO object library (Tools|References on the VBA menu bar) as since
2000 Access has used ADO as the default data access technology.

Ken Sheridan
Stafford, England

I have a table with 35 attributes. I would like to use a form with check
boxes on it so that the user could select only the attributes that they
would like to view, i.e. if they only needed to compare 3 of the 35 columns
they could just check those 3 boxes. I have the form with the check boxes
and a button to run the query but I can't figure out the query. Is this even
possible?

 

[Guest]

Name is the Name property of the control to which the object variable ctrl
refers. So as the code loops through the Controls collection ctl.Name will
be the Name of each control on the form. By testing for the first 3
characters of the name being 'chk' it identifies the controls you've named as
chk<name of field>. It then used the Mid function to extract all the
characters in the name from the 4th one on, which should be the name of the
field in the table you want to return if the checkbocx is checked.

I've tested the code with a simple form and query and it does work. Can you
post the actual code you are using; I might be able to spot the source of the
problem better then.

Ken Sheridan
Stafford, England

Thanks for your help Ken. I still can't get it to work though. When it runs
it says "Item not found in this collection." In the IF statement when it
calls ctrl.Name, what is "Name" referencing?

You could amend a query's SQL property in code behind a button on the form.
Firstly create and save a query to return all columns. In form design by
naming the checkboxes so that they reflect the column names you can then
build a string expression by looping through them. Say they are named
chkFirstName, chkLastName etc where FirstName and LastName are column names
the code in a button's Click event would go something like this:

Dim dbs as DAO.Database, qdf As DAO.QueryDef
Dim ctrl As Control
Dim strSQL As String

Set dbs = CurrentDb
Set qdf = dbs.Querydefs("YourQuery")

For each ctrl In Me.Controls
If Left(ctrl.Name,3) = "chk" Then
If ctrl = True Then
strSQL = strSQL & ",[" & Mid(ctrl.Name,4) & "]"
End If
End If
Next ctrl

' remove leading comma
strSQL = Mid(strSQL,2)
' build SQL for query
strSQL = "SELECT " & strSQL & " FROM [YourTable]"
' assign query's SQL property
qdf.SQL = strSQL
' open query
DoCmd.OpenQuery "YourQuery"

The above code uses DAO, so ensure that you have a reference to the
Microsoft DAO object library (Tools|References on the VBA menu bar) as since
2000 Access has used ADO as the default data access technology.

Ken Sheridan
Stafford, England

I have a table with 35 attributes. I would like to use a form with check
boxes on it so that the user could select only the attributes that they
would like to view, i.e. if they only needed to compare 3 of the 35 columns
they could just check those 3 boxes. I have the form with the check boxes
and a button to run the query but I can't figure out the query. Is this even
possible?

 

[Guest]

Dim dbs As DAO.Database, qdf As DAO.QueryDef
Dim ctrl As Control
Dim strSQL As String

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs(GAA_TBL_Query)

For Each ctrl In Me.Controls
If Left(ctrl.Name, 3) = "CHK" Then
If ctrl = True Then
strSQL = strSQL & ",[" & Mid(ctrl.Name, 4) & "]"
End If
End If
Next ctrl

strSQL = Mid(strSQL, 2)
strSQL = "SELECT" & strSQL & "FROM GAA_TBL"
qdf.SQL = strSQL

DoCmd.OpenQuery GAA_TBL_Query

the table that I'm pulling from is "GAA_TBL" and the query is
"GAA_TBL_Query". I've named the check boxes on the form CHK_"object" and the
"object" matches the column names in the GAA_TBL.

Name is the Name property of the control to which the object variable ctrl
refers. So as the code loops through the Controls collection ctl.Name will
be the Name of each control on the form. By testing for the first 3
characters of the name being 'chk' it identifies the controls you've named as
chk<name of field>. It then used the Mid function to extract all the
characters in the name from the 4th one on, which should be the name of the
field in the table you want to return if the checkbocx is checked.

I've tested the code with a simple form and query and it does work. Can you
post the actual code you are using; I might be able to spot the source of the
problem better then.

Ken Sheridan
Stafford, England

Thanks for your help Ken. I still can't get it to work though. When it runs
it says "Item not found in this collection." In the IF statement when it
calls ctrl.Name, what is "Name" referencing?

You could amend a query's SQL property in code behind a button on the form.
Firstly create and save a query to return all columns. In form design by
naming the checkboxes so that they reflect the column names you can then
build a string expression by looping through them. Say they are named
chkFirstName, chkLastName etc where FirstName and LastName are column names
the code in a button's Click event would go something like this:

Dim dbs as DAO.Database, qdf As DAO.QueryDef
Dim ctrl As Control
Dim strSQL As String

Set dbs = CurrentDb
Set qdf = dbs.Querydefs("YourQuery")

For each ctrl In Me.Controls
If Left(ctrl.Name,3) = "chk" Then
If ctrl = True Then
strSQL = strSQL & ",[" & Mid(ctrl.Name,4) & "]"
End If
End If
Next ctrl

' remove leading comma
strSQL = Mid(strSQL,2)
' build SQL for query
strSQL = "SELECT " & strSQL & " FROM [YourTable]"
' assign query's SQL property
qdf.SQL = strSQL
' open query
DoCmd.OpenQuery "YourQuery"

The above code uses DAO, so ensure that you have a reference to the
Microsoft DAO object library (Tools|References on the VBA menu bar) as since
2000 Access has used ADO as the default data access technology.

Ken Sheridan
Stafford, England

:

I have a table with 35 attributes. I would like to use a form with check
boxes on it so that the user could select only the attributes that they
would like to view, i.e. if they only needed to compare 3 of the 35 columns
they could just check those 3 boxes. I have the form with the check boxes
and a button to run the query but I can't figure out the query. Is this even
possible?

 

[Guest]

There are a few problems with your code:

1. The query name needs to be in quotes in the following line:

Set qdf = dbs.QueryDefs("GAA_TBL_Query")

2. As you've used the underscore character before the field name part of
the check box names you need to extract the characters from the 5th position
on with:

strSQL = strSQL & ",[" & Mid(ctrl.Name, 5) & "]"

3. You need to include spaces in the string expression for the SQL
statement before and after the field list:

strSQL = "SELECT " & strSQL & " FROM GAA_TBL"

4. The query name should be in quotes in the following line:

DoCmd.OpenQuery "GAA_TBL_Query"

Ken Sheridan
Stafford, England

 

[Guest]

Ken,
It works great! Thank you very much!

There are a few problems with your code:

1. The query name needs to be in quotes in the following line:

Set qdf = dbs.QueryDefs("GAA_TBL_Query")

2. As you've used the underscore character before the field name part of
the check box names you need to extract the characters from the 5th position
on with:

strSQL = strSQL & ",[" & Mid(ctrl.Name, 5) & "]"

3. You need to include spaces in the string expression for the SQL
statement before and after the field list:

strSQL = "SELECT " & strSQL & " FROM GAA_TBL"

4. The query name should be in quotes in the following line:

DoCmd.OpenQuery "GAA_TBL_Query"

Ken Sheridan
Stafford, England

Dim dbs As DAO.Database, qdf As DAO.QueryDef
Dim ctrl As Control
Dim strSQL As String

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs(GAA_TBL_Query)

For Each ctrl In Me.Controls
If Left(ctrl.Name, 3) = "CHK" Then
If ctrl = True Then
strSQL = strSQL & ",[" & Mid(ctrl.Name, 4) & "]"
End If
End If
Next ctrl

strSQL = Mid(strSQL, 2)
strSQL = "SELECT" & strSQL & "FROM GAA_TBL"
qdf.SQL = strSQL

DoCmd.OpenQuery GAA_TBL_Query

the table that I'm pulling from is "GAA_TBL" and the query is
"GAA_TBL_Query". I've named the check boxes on the form CHK_"object" and the
"object" matches the column names in the GAA_TBL.

 

[MDI Anne]

I'm in the same situation as the original poster, but I'm using Access 2007 &
it doesn't seem to like the If ctrl = True Then statement. I'm getting a
"Run-time error '438': Object doesn't support this property or method. When
I debug, it stops at the If ctrl statement.

Can anyone help? (I've even renamed my table & query to match the original
code posted)

Thanks!

There are a few problems with your code:

1. The query name needs to be in quotes in the following line:

Set qdf = dbs.QueryDefs("GAA_TBL_Query")

2. As you've used the underscore character before the field name part of
the check box names you need to extract the characters from the 5th position
on with:

strSQL = strSQL & ",[" & Mid(ctrl.Name, 5) & "]"

3. You need to include spaces in the string expression for the SQL
statement before and after the field list:

strSQL = "SELECT " & strSQL & " FROM GAA_TBL"

4. The query name should be in quotes in the following line:

DoCmd.OpenQuery "GAA_TBL_Query"

Ken Sheridan
Stafford, England

Dim dbs As DAO.Database, qdf As DAO.QueryDef
Dim ctrl As Control
Dim strSQL As String

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs(GAA_TBL_Query)

For Each ctrl In Me.Controls
If Left(ctrl.Name, 3) = "CHK" Then
If ctrl = True Then
strSQL = strSQL & ",[" & Mid(ctrl.Name, 4) & "]"
End If
End If
Next ctrl

strSQL = Mid(strSQL, 2)
strSQL = "SELECT" & strSQL & "FROM GAA_TBL"
qdf.SQL = strSQL

DoCmd.OpenQuery GAA_TBL_Query

the table that I'm pulling from is "GAA_TBL" and the query is
"GAA_TBL_Query". I've named the check boxes on the form CHK_"object" and the
"object" matches the column names in the GAA_TBL.