Refer to SQL statements globally

[Kurt Heisler]

I have two large SQL statements that I need to use across several
forms, reports, etc.

Rather than repeat them in each form, I'm trying to store and
reference them globally.

For example, I'd like to store strSQL1 and strSQL2 in a global module,
so I can use either like this in a form:

Random form:

Me!lstResults.RowSource = strSQL1

Random report:

Me.Report.RecordSource = strSQL2

Would I just store the SQL statements in a global module like:

Public Function SQLSource() As String

Dim strSQL1 As String
Dim strSQL2 As String

strSQL1 = "SELECT blah blah"
strSQL2 = "SELECT blah blah"

End Function

If so, how would I refer to it elsewhere. Like:

Me.Report.RecordSource = strSQL2 'need to call the function first
(SQLSource), and then pick the correct SQL

Thanks.

 

[Douglas J. Steele]

Why not store them in a table, and look them up using DLookup when you need
them?

If that's not sufficient, no, what you're proposing won't work. What you can
try is create a new module (not a class module or a module associated with a
form or report) and put the following in it:

Public Const strSQL1 As String = "SELECT blah blah"
Public Const strSQL2 As String = "SELECT blah blah"

 

[Tom van Stiphout]

On Wed, 26 May 2010 14:11:42 -0700 (PDT), Kurt Heisler

As a variant on what Doug suggested, I would say create some functions
in a standard module that return sqlstatements. Pass in the form name
into these functions, which would give you flexibility down the road
if you want to do things SLIGHTLY diffent based on which form it is
for. Sample code:
public function GetFirstSqlStatement(frm as Form)
dim sql as string
sql = "select ..."
GetFirstSqlStatement = sql
end function

In the future you could write:
if ControlExists(frm, "myControl") then
sql = sql & " where " & frm.Controls("myControl").ControlSource & "
= " & frm.Controls("myControl").Value

Or some other form-dependent processing, e.g.:
select case frm.name
case "thisForm"
sql = sql & " where StatusID = 1"
case "thatForm"
sql = sql & " where StatusID = 2"
end select

-Tom.
Microsoft Access MVP

 

[david]

Store the SQL as a query.

That makes it easy to test, find, develop and use.

For example, store SQL1 as query1, then use:

Me!lstResuts.RowSource = "Query1"

Sometimes you wish to modify the sql stored
in a query. You can get it like this:

strSQL1 = codedb.querydefs("Query1").SQL

(david)