defining tables in VBA 1

[zimmer9]

I was trying to attach code to the On Click event of a Command Button of a Form. When the Command Button is clicked, I wanted to write Dynamic SQL so that I could execute a query based on the values that the user entered into list boxes on the form.

The VBA code I started to write is as follows:
I get an error message "Runtime Error '424' Object Required
on the following line:

strSQLFallCy = Chr(34) + tblStatesAll.FallCycle = Chr(34) + "Yes" + Chr(34) + Chr(34)


Private Sub Command50_Enter()
Dim strSQLPrefix As String
Dim strSQLSpring As String
Dim strSQLFall As String
Dim strSQLFallCy As String
strSQLFallCy = Chr(34) + tblStatesAll.FallCycle = Chr(34) + "Yes" + Chr(34)
strSQLPrefix = "SELECT * FROM tblCustomers As C, tblProducts As P, tblStatesAll As S where "
If Me.Frame60.Value = 1 And (Me.lstStates.Value = "ALL" Or Me.lstStates.Value = "FALL STATES") Then
strSQLPrefix = strSQLPrefix + strSQLFallCy
End If
End Sub

Is the error caused because I am referencing a table which is not defined in the form? I am trying to build Dynamic SQL. If I need to define the table, where in the Class Module (form) should I define the table named tblStatesAll and how would I define this table name (data type) ?

The table is defined in the Tables Objects list and in the Queries Objects list. I have defined tables in some of the list boxes as the Row Source.

 

[PHV]

Why not simply this ?
strSQLFallCy = "S.FallCycle = Yes"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244