sharing query created in VBA

  • Thread starter Thread starter John B. Smotherman
  • Start date Start date
J

John B. Smotherman

I have a form with a couple of listboxes. The rowsource for the listboxes is
two queries that I build the SQL for in the form's module. I'd like to create
a report that also uses the same queries. Keeping in mind that the SQL for
these two could be rather lengthy, is there an easy way to share the queries
between the form and the report? I've thought about using openargs in the
DoCmd.OpenReport call, but is there a better way?

Thanks!
 
Here is a function that will do it. You first need to create a stored query
with the name of the query you want to use for both. Then, the following
code will change the SQL in the stored query to the string you build:

Public Function MakeQuery(QueryName As String, QuerySQL As String) As Boolean
Dim qdf As QueryDef

On Error GoTo MakeQuery_Error

MakeQuery = True

Set qdf = dbs.QueryDefs(QueryName)
qdf.SQL = QuerySQL
qdf.Close

Set qdf = Nothing


MakeQuery_Exit:

Exit Function
On Error GoTo 0

MakeQuery_Error:

MakeQuery = False

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure MakeQuery of Module modUtilities"
GoTo MakeQuery_Exit

End Function
 
Sorry, syntax error in the previous post. Here is the correction:

Public Function MakeQuery(QueryName As String, QuerySQL As String) As Boolean
Dim qdf As QueryDef

On Error GoTo MakeQuery_Error

MakeQuery = True

Set qdf = CurrentDb.QueryDefs(QueryName)
qdf.SQL = QuerySQL
qdf.Close

Set qdf = Nothing


MakeQuery_Exit:

Exit Function
On Error GoTo 0

MakeQuery_Error:

MakeQuery = False

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure MakeQuery of Module modUtilities"
GoTo MakeQuery_Exit

End Function
 
Klatuu said:
Public Function MakeQuery(QueryName As String, QuerySQL As String) As Boolean
Dim qdf As QueryDef

On Error GoTo MakeQuery_Error

MakeQuery = True

Set qdf = CurrentDb.QueryDefs(QueryName)
qdf.SQL = QuerySQL
qdf.Close

Set qdf = Nothing


MakeQuery_Exit:

Exit Function
On Error GoTo 0

MakeQuery_Error:

MakeQuery = False

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure MakeQuery of Module modUtilities"
GoTo MakeQuery_Exit

End Function
Click to expand...

klatuu,

I notice that you put the name of the module and procedure in your On
Error code. That's a good idea. Can you think of a situation, perhaps
something like multiple instances of a form, where:

Application.CodeContextObject.Name

might be helpful?

Thanks,

James A. Fortune
(e-mail address removed)
 
I was not even aware of that object. I can see where it would be useful in a
generic error handler as well as some other situations.
 
Back
Top