Passing Variables Question ....Revisited

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gobi - 30 Jun 2004 17:13 GMT

I asked a question here earlier about passing variable and received
some help his is my finished product (this is done in the code window
in MS Access)

Dim stDocName As String
Dim strAttyName As String
Dim strLAName As String
Dim strReportName As String

strReportName = "By_ATTY_AND_LA"

strAttyName = lstR_ATTY.Column(0)
strLAName = lstLEGAL_ASST.Column(0)
MsgBox ("Atty Name = " & strAttyName & " Legal Asst. Name = " &
strLAName)

DoCmd.OpenReport reportname:=strReportName, view:=acViewPreview,
wherecondition:="R_ATTY = '" & strAttyName _
& "' and LEGAL_ASST = '" & strLAName & "'"

My new question is how could I re-create this but using it to open a
query that has the same variable....How is the whereconditon:
re-created for opening queries in MS Access as opposed to Reports?

Reply to this Message

Michael B. Johnson - 30 Jun 2004 18:17 GMT

>I asked a question here earlier about passing variable and received
>some help his is my finished product (this is done in the code window
[quoted text clipped - 21 lines]
>query that has the same variable....How is the whereconditon:
>re-created for opening queries in MS Access as opposed to Reports?

Will this work for you?

Public Sub CreateQuery(sQueryName As String, sSQL As String)
' Purpose: To create an MS Access query from a SQL string
' Example/Note: CreateQuery( "SELECT * FROM Contacts" )
' !! Assumes/Pre: Nothing
' Parameters:
' sQueryName- Name of the Query
' sSQL- SQL String that the query should contain
' Returns: Nothing
' Success- Creates a new Query Definition
' Failure- Raises error on failure
' Dependencies: None
' Revision history:
' Orginal author: Mihail Milataru, Royal Bank
' Michael Johnson 2001-May-28 Initial creation
Dim DB As DAO.Database
Dim qry As DAO.QueryDef

Set DB = CurrentDb
On Error GoTo errDoesNotExist
Set qry = DB.QueryDefs(sQueryName)
qry.SQL = sSQL
qry.Close
ExitThis:
Set qry = Nothing
Set DB = Nothing
Exit Sub
errDoesNotExist:
Set qry = DB.CreateQueryDef(sQueryName, sSQL)
qry.Close
GoTo ExitThis
End Sub

Public Sub ShowDynamicReport(sReportName As String, sQueryDefName As
String, sSQL As String)
' Purpose: To display a report based on a dynamic record source
' Example/Note: xxx
' !! Assumes/Pre: Warning- code not yet tested.
' Parameters:
' sReportName - Name of the report to display
' sQueryDefName- Name of the query to overwrite with the SQL string
' sSQL- SQL string that is to be used to generate the records on
which to base the report
' Returns: Nothing
' Success- Displays a report, based on the supplied SQL string
' Failure- Raises error on failure
' Dependencies:
' modMisc->CreateQuery
' Revision history:
' Michael Johnson 2001-Jun-06 1539 Initial creation
' Michael Johnson 2001-Jun-12 1152 Modified to become generic
ShowDynamicReport.
' Michael B. Johnson 2002-Oct-19 1126 Modified to fix syntax error
and remove references to ActiveProc

' ActiveProc = "ShowDynamicReport"
Call CreateQuery(sQueryDefName, sSQL)
Call DoCmd.OpenReport(sReportName, acViewPreview, sQueryDefName)
ExitThis:
Exit Sub
End Sub

E X A M P L E:

Call ShowDynamicReport( "MyReport", "MyTempQry", "SELECT * FROM
Authors WHERE R_ATTY = '" & strAttyName _
& "' and LEGAL_ASST = '" & strLAName & "'" )
_______________________
Michael B. Johnson

Reply to this Message

VB Forum / General 2 / June 2004

Passing Variables Question ....Revisited