HELP --- Please

[Guest]

I am becomingly increasingly frustrated...
I have a large form with numerous buttons that use SQL to provide lists of
individuals dependant on a variety of parameters.

In an attemt to tidy my VBA code I have placed the SQL statements in a table
tblSQLStrings

I then call the strings when required by a buttion using the following module

Public Sub GENERALBUTTON(qryNAME As String)

Dim rstVar As Recordset
Dim strSQL As String
Dim sqlstring As String

strSQL = "SELECT qryTitle, qrySQL FROM tblSQLString WHERE qryTitle = '" &
qryNAME & "'"

Set rstVar = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

With rstVar

sqlstring = .Fields("qrySQL")

End With

Dim rstData As QueryDef
Set rstData = CurrentDb.CreateQueryDef(qryNAME, sqlstring)

DoCmd.OpenQuery qryNAME
CurrentDb.QueryDefs.Delete (qryNAME)
End Sub

THIS WORKS and produces the string from the table, however I have a number
of 'embeded variables' within the SQL string called entered as '" & variable
& "' throughout the code. These variables are global and I can check they are
in scope and available using a MsgBox witnin the above Sub Routine. These
variables are however not replicated in the SQL code when it is called from
the Table ...
Is there anyway I can force these variables to be updated in the SQL code
prior to the query being executed ????

Any pointers would be appreciated

Many Thanks in advance

 

[Douglas J Steele]

You can't use variables in QueryDef objects.

You may have to rethink your approach: perhaps defined Parameters for your
queries, then have an additional table to your tblSQLString table that you
can use to determine how to assign values to the Parameters.