sharing query created in VBA

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!

"John B. Smotherman" escribió en
el mensaje de noticias
...
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

--
Dave Hargis, Microsoft Access MVP


"John B. Smotherman" wrote:

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!

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

--
Dave Hargis, Microsoft Access MVP


"John B. Smotherman" wrote:

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!

Klatuu wrote:

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,

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, whe

Application.CodeContextObject.Name

might be helpful?

Thanks,

James A. Fortune

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.
--
Dave Hargis, Microsoft Access MVP


"James A. Fortune" wrote:

Klatuu wrote:

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,

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, whe

Application.CodeContextObject.Name

might be helpful?

Thanks,

James A. Fortune