One Report different Query at Run time (2k, XP, 03)

Topic

New Reply

This may seem dumb, and I should probably know the answer, but I shall ask anyway. Is there a way for vb to call a a report using different queries? I know you can set filter, but I want to use a completely different data set based on user’s selection.
More detail:
I have two tables with same field names. I have one report with same field names. I can use different reports, I suppose, but that doesn’t seem very efficient. In the following example is there a creative way to use a different query for the same report, instead of different reports?
If Me.chkYes = True Then
stDocName = “rptShippingRpt”
DoCmd.OpenReport stDocName, acViewPreview, , , acWindowNormal
Else
stDocName = “rptShippingRpt2”
DoCmd.OpenReport stDocName, acViewPreview, , , acWindowNormal
End If

Reply | Quote

Replies

You can set the Record Source of the report in the On Open event of the report.

Private Sub Report_Open(Cancel As Integer)
If Forms!frmSomething!chkYes = True Then
Me.RecordSource = “ThisTable”
Else
Me.RecordSource = “ThatTable”
End If
End Sub

Change frmSomething to the name of your form; if it contains spaces or punctuation, put square brackets [ ] around the form name.
Replace ThisTable and ThatTable with the appropriate table or query names.

Reply | Quote

An even easier approach I’ve found is to set the datasource of the report to a named, saved query and then take your user’s selections and pass parameters to the sql property of that query (or even just write on the fly sql for it). That way, you don’t have to change the recordsource of the report at runtime, which sometimes can be problematic, and you only have one querydef object to maintain:

dim db as dao.database, qdef as querydef
set db=currentdb
set qdef = db.querydefs(“yourqueryname”)
qdef.sql = “your sql string here”
db.close
set qdef=nothing
set db=nothing

Reply | Quote

Hmmm … let’s make this a little broader ! Hans, if the query is ‘parameterized’ with a question msgbox, for example [What store do you want ?] in the query criterion, why do I find bad recordsets (i’m counting records to post a count on the form) that compute Recordset.count = 1 … I tried the clone and it’s a mirror of the recordset.

Reply | Quote

Count is not a property of a recordset – RecordCount is the one you want.

Reply | Quote

I realize that, Hans, but for sake of brevity I skipped over alot of code I have an open form, I have a user select a query name in a CBO, I take that name (a string) and “on update” for the CBO I want to count potential records, as if, that ‘parameterized’ query were actually run … I can place the query name in the form’s RecordSource, and do a requery .. but I get ‘iffy’ results … sometimes the correct count, else a “1” (that’s non-sense)… it’s flippin’ me out. Regards

Reply | Quote

Yet another option (that is not available in 2K) is to pass the name of the query to the report using OpenArgs:

So on the form:
stDocName = “rptShippingRpt”
If Me.chkYes = True Then
strQueryname = “query1″
Else
strqueryname =” query2″
End If
DoCmd.OpenReport stDocName, acViewPreview, , , acWindowNormal, strqueryname

Then in the onopen event of the report
Private Sub Report_Open(Cancel As Integer)
If not isnull(me.OpenArgs) Then
Me.RecordSource = me.openArgs

End If
End Sub

This is useful if you have more than pathway for opening the report.

Reply | Quote

Hans, Kathryn, and John,

THANKS! So far I have successfully accomplished the goal using the first two (Hans and Kathryn), and will test John’s later today. Thanks for the excellent ideas!

Ken

Reply | Quote

The RecordCount property of a recordset is not always dependable. You may have to perform a MoveLast before you get an accurate count.

Reply | Quote

I hope you are appreciated … I went too MS, in the meantime, to see if I could dig out the fault mechanism … sure enough …”Calling the Requery method of a form’s recordset (for example, Forms(0).Recordset.Requery) can cause the form to become unbound. To refresh the data in a form bound to a recordset, set the RecordSource property of the form to itself (Forms(0).RecordSource = Forms(0).RecordSource).” … That’s their way of saying it’s “unstable” ! LOL But the second sentence is interesting … we learn something every day. Thanks Hans

Reply | Quote

I changed the queries to “select” queries to get away from any “parameter’ issues .. I am unable to het a correct recordset count … one time it’s 598, the next zero, maybe even a ‘1’ … it’s all over the place. I’ve made sure to close the recordset between requests and set the rescordset object to nothing … there’s no consistency in this ??? Regards Wayne

Reply | Quote

Try using expressions with DCount instead of relying on the RecordCoount property of recordsets.

Reply | Quote