HELP --- Please

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
Back
Top