[AccessD] QueryDefs - can't remember the solution!

Jeanine Scott jscott at mchsi.com
Tue Jan 28 17:15:32 CST 2003


[forgive me if this is a duplicate. I haven't received any message from the
list for several days so I thought I'd try sending this again]
I seem to remember running into this in the past and can't for the life of
me remember what I did to get around it.  I have a stored query that brings
back all financial data for all months for each vendor. I base a summary
form on that query. Within the summary form I drill down to specific
vendors-a detail form. Rather than using a separate stored query just for
the purpose of having vendor criteria I am resetting the SQL in the stored
query, on open of the detail form, using the querydefs collection.
The issue is,  I'll open my detail form  and all the data is there - not
just the data for that particular query. However, the right variable is
getting passed in AND I open the original query and it shows the criteria
correctly - in other words, my manipulation of the querydef is happening
correctly. I also refresh the querydefs collection before I requery my form
on open.
My code is below: Anyone have any suggestions for me? Thanks so much in
advance! I know one of you genius' out there will know what I'm doing wrong
immediately!

Dim qdf As QueryDef
Dim str As String
If gdb Is Nothing Then
    Set gdb = DBEngine(0)(0)
End If
Set qdf = gdb.QueryDefs("qryRptFinancialsGLSummaryByMonth")
str = "SELECT tblViewFinancialsGLSummary.RptYear, CInt([RptMonth]) AS
MonthSort, [RptMonth] & '/' & [rptYear] AS MonthYear,
tblViewFinancialsGLSummary.Vendor, tblViewFinancialsGLSummary.GLNumber,
tblViewFinancialsGLSummary.VendorID, tblViewFinancialsGLSummary.UnitNo,
tblViewFinancialsGLSummary.SumOfALLTELIMPORT AS ALLTELIMPORT,
tblViewFinancialsGLSummary.SumOfIN AS [IN],
tblViewFinancialsGLSummary.SumOfOUT AS OUT,
tblViewFinancialsGLSummary.SumOfALLTELDEPOSIT AS ALLTELDEPOSIT,
tblViewFinancialsGLSummary.SumOfCOPAYMENT AS COPAYMENT,
[ALLTELIMPORT]+[IN]+[OUT]+[ALLTELDEPOSIT]+[COPAYMENT] AS BALANCE " & _
" FROM tblViewFinancialsGLSummary " & _
" WHERE VendorID= " & gdblID & _
" ORDER BY tblViewFinancialsGLSummary.RptYear DESC , CInt([RptMonth]) DESC ,
[RptMonth] & '/' & [rptYear] DESC , tblViewFinancialsGLSummary.Vendor DESC ,
tblViewFinancialsGLSummary.GLNumber, tblViewFinancialsGLSummary.UnitNo"
qdf.sql = str
qdf.Close
Set qdf = Nothing
gdb.QueryDefs.Refresh

Me.Requery




More information about the AccessD mailing list