Is this possible? (Access 2K)

Topic

New Reply

Hello All

OK, I’ve got a database set up and running quite happily, now I’m asked to allow the user to restrict the records by clicking a checkbox. I could do this by duplicating all my forms and queries and setting the buttons I use to call these items to check the status of the checkbox……

However, I’m feeling a bit lazy and slightly adventurous, so, is it possible to write a piece of SQL code in VBA and use it to control my various queries depending on the status of the checkbox?? Would I be looking to use something like the code used in an unmatched query? Or is not possible?

Thanks

Ian

Reply | Quote

Replies

What do you mean by “restrict the records by clicking a checkbox”? Will there be a check box in each record, or one check box on a form, or what?

Reply | Quote

Hans

Sorry. So busy explaining what I wanted I didn’t cover the basics….

The user’s checkbox will be on a form, the idea in my mind is that they click the checkbox on this main form, then click the one of the buttons to open the form which is populated by the one of the queries. This button has code attached to it that checks the checkbox status and either runs the standard query, or runs the query with the additional SQL to restrict it.

Thanks

Ian

Reply | Quote

If you open a form from a command button, you are using DoCmd.OpenForm. One of the arguments to DoCmd.OpenForm is WhereCondition. You can use this to filter the data displayed in the form. For example:

Dim strFilter As String
If Me.chkSomething = True Then
strFilter = “[Discarded] = False”
End If
DoCmd.OpenForm FormName:=”frmMyForm”, WhereCondition:=strFilter

Reply | Quote

Hans

Thanks yet again for a solution.

The idea didn’t quite work as I’m using subforms to display some of my queries, but did lead me to the forms own Filters property and the FilterOn.

I simply added an:-
if ‘x’ = ‘chkY’ then
me.Filter = “[mycontrolname] = -1”
me.FilterOn = True
Else
me.FilterOn = False
End if

Seems to be working fine.

Thanks

Ian

Reply | Quote

Hans

Thanks yet again for a solution.

The idea didn’t quite work as I’m using subforms to display some of my queries, but did lead me to the forms own Filters property and the FilterOn.

I simply added an:-
if ‘x’ = ‘chkY’ then
me.Filter = “[mycontrolname] = -1”
me.FilterOn = True
Else
me.FilterOn = False
End if

Seems to be working fine.

Thanks

Ian

Reply | Quote

If you open a form from a command button, you are using DoCmd.OpenForm. One of the arguments to DoCmd.OpenForm is WhereCondition. You can use this to filter the data displayed in the form. For example:

Dim strFilter As String
If Me.chkSomething = True Then
strFilter = “[Discarded] = False”
End If
DoCmd.OpenForm FormName:=”frmMyForm”, WhereCondition:=strFilter

Reply | Quote

Hans

Sorry. So busy explaining what I wanted I didn’t cover the basics….

The user’s checkbox will be on a form, the idea in my mind is that they click the checkbox on this main form, then click the one of the buttons to open the form which is populated by the one of the queries. This button has code attached to it that checks the checkbox status and either runs the standard query, or runs the query with the additional SQL to restrict it.

Thanks

Ian

Reply | Quote

What do you mean by “restrict the records by clicking a checkbox”? Will there be a check box in each record, or one check box on a form, or what?

Reply | Quote