Form Option Group Filter

[Brook]

Good Day All,

I have just created my first Option Group Filter for an account balance
form, and I thought that I had everything set up properly based on a sample
that I looked at, but I am getting an error:

The Expression On Click you entered as an event property setting produced
the following error: Variable Not Defined

Below is my code if anyone can help.

Option Compare Database
Option Explicit

'Set default record source of form
Const strSQL = qrycheckingaccountfullreporting

Private Sub cmdFilterRecords_Click()
'Variable to hold filtered SQL string
Dim strFilterSQL As String



Select Case Me!optFilterBy
'Filter record source dependant on option checked
Case 1
strFilterSQL = strSQL & " Where [accounttype] = 'Checking';"
Case 2
strFilterSQL = strSQL & " Where [accounttype] = 'Expense';"
Case 3
strFilterSQL = strSQL & " Where [accounttype] =
'Checking/Expense';"
Case 4
strFilterSQL = strSQL & " Where [accounttype] = 'Checking/Wire';"
Case 5
strFilterSQL = strSQL & " Where [accounttype] = 'Checking/Loan';"
Case 6
strFilterSQL = strSQL & " Where [accounttype] = 'Nomad Weavers
Balance Sheet';"
'If filter applied with no option selected use default record source
Case Else
strFilterSQL = strSQL & ";"
End Select

' Set record source with filtered SQL
Me.RecordSource = strFilterSQL
Me.Requery
End Sub


Thanks,

Brook

 

[Douglas J. Steele]

While I wouldn't have expected the "Variable Not Defined" message, your
declaration of the Const is definitely incorrect.

Assuming you want strSQL to contain the actual SQL associated with that
query, you need to declare it as a variable, and then assign it a value:

Dim strSQL As String

strSQL = CurrentDb().QueryDefs("qrycheckingaccountfullreporting").SQL

Note that this is DAO code: if you're using Access 2000 or 2002, you'll need
to ensure that you've added a reference to DAO.