Share String from Module to Multiple Forms

[develguy]

This may be a stupid question but...

I have a string that builds the an SQL statement (all but the
"WHERE"/criteria).

Several forms can use this first part of the SQL but each has it's own
criteria.

I would like to only have the SELECT part of the SQL in one place to
ease future editing.

My dumb question:

How can I refer to the "common" SQL from the module and string it
together in each forms' VBA.

Here's what I currently have:

MOD:

Public Function SearchSQL() As String

Dim modSQL As String
modSQL = "SELECT blah,blah FROM blah, blah"

End Function

FORM:

Private Sub cmdSearch_Click()

Dim s As String
s = SearchSQL() & " WHERE.... blahblah"
msgbox s

End Sub

The only thing the msgbox reports is the WHERE portion.

If I msgbox modSQL from w/in the Module, I get the correct modSQL.

As you can see I need:

modSQL & " WHERE... blahblah"

What am I doing wrong here?

Thanks!

Bob

 

[Klatuu]

What you are doing is a great way to do this. The only problem is the syntax
in your function. Try it this way:

Public Function SearchSQL() As String
SearchSQL = "SELECT blah,blah FROM blah, blah"
End Function

You have to assign the value to the Function's name to get it to return
anything.

 

[Bob]

Awesome! Thank you!!!

 

[Marshall Barton]

This may be a stupid question but...

I have a string that builds the an SQL statement (all but the
"WHERE"/criteria).

Several forms can use this first part of the SQL but each has it's own
criteria.

I would like to only have the SELECT part of the SQL in one place to
ease future editing.

My dumb question:

How can I refer to the "common" SQL from the module and string it
together in each forms' VBA.

Here's what I currently have:

MOD:

Public Function SearchSQL() As String

Dim modSQL As String
modSQL = "SELECT blah,blah FROM blah, blah"

End Function

FORM:

Private Sub cmdSearch_Click()

Dim s As String
s = SearchSQL() & " WHERE.... blahblah"
msgbox s

End Sub

If the SearchSQL function always returns the same string,
why not just declare it as a module level constant?

Const SearchSQL As String = "SELECT blah,blah FROM blah"

Private Sub cmdSearch_Click()
Dim s As String
s = SearchSQL & " WHERE.... blahblah"
msgbox s
. . .