OpenArgs

[Duane]

I am trying to pass the value of an option group in the Forms OpenArgs
property to my report in order to select the correct recordsource at
runtime. I have one report (had three but I want to cut it down to only
one). I have three queries, each using the same fields but different
results.

Dim iPrtRpt as Integer

If iReports = 1 Then
iPrtRpt = 1
ElseIf iReports = 2 Then
iPrtRpt = 2
ElseIf iReports = 3 Then
iPrtRpt = 3
End If

I used a msgbox to verify the variable has the correct value. I am confused
on how to pass the value to the report and call the correct query for the
recordsource.

DoCmd.OpenReport ReportName:= "rptInventory" , OpenArgs:= iPrtRpt '''Tried
with quotes and without the quotes.

On the Reports Open Event I tried to use the the following:

If iPrtRpt = 1 Then
Reports!repInventory.RecordSource = "qryDrawer"
ElseIf iPrtRpt = 2 Then
Reports!repInventory.RecordSource = "qryLocation"
ElseIf iPrtRpt = 3 Then
Reports!repInventory.RecordSource = "qryWorkArea"
End If

I am not getting any errors, just a blank report. Also, I would like to
ensure the report opens in acPreview. Any help would be greatly
appreciated.

Maybe in the near future I will be asking for clarification on writing the
select statement in VBA and bypassing the queries all together. Next week I
hope!

 

[John W. Vinson]

I am trying to pass the value of an option group in the Forms OpenArgs
property to my report in order to select the correct recordsource at
runtime. I have one report (had three but I want to cut it down to only
one). I have three queries, each using the same fields but different
results.

If the three queries differ only in the *criteria* you apply, then I'd
strongly suggest that you need only one Paramter query.

Dim iPrtRpt as Integer

If iReports = 1 Then
iPrtRpt = 1
ElseIf iReports = 2 Then
iPrtRpt = 2
ElseIf iReports = 3 Then
iPrtRpt = 3
End If
I used a msgbox to verify the variable has the correct value. I am confused
on how to pass the value to the report and call the correct query for the
recordsource.

DoCmd.OpenReport ReportName:= "rptInventory" , OpenArgs:= iPrtRpt '''Tried
with quotes and without the quotes.

Exactly equivalent to the single line

DoCmd.OpenReport ReportName:= "rptInventory" , OpenArgs:= Me!iReports

if iReports is the name of the Option Group control. Don't need the variable
or the rigamarole of setting 3 equal to 3!

On the Reports Open Event I tried to use the the following:

If iPrtRpt = 1 Then
Reports!repInventory.RecordSource = "qryDrawer"
ElseIf iPrtRpt = 2 Then
Reports!repInventory.RecordSource = "qryLocation"
ElseIf iPrtRpt = 3 Then
Reports!repInventory.RecordSource = "qryWorkArea"
End If

I am not getting any errors, just a blank report. Also, I would like to
ensure the report opens in acPreview. Any help would be greatly
appreciated.

Without knowing what the various queries are, I have no clue what might be
wrong.

Maybe in the near future I will be asking for clarification on writing the
select statement in VBA and bypassing the queries all together. Next week I
hope!

Well... just one query. Perhaps you could open one or two of these queries in
SQL view and posting the SQL text here.

 

[Cheese_whiz]

I think you can do this in the 'on open' event of the report. Assuming that
the form that you open the report from is called frmReportMaker and the
option group is called Frame1, then something like this in the reports on
open event I think should work:
_______________________________
Sub Report_Open(Cancel as Integer)

If CurrentProject.AllForms("frmReportMaker").IsLoaded = True then
Select Case Frame1
Case 1
Me.RecordSource = "whatever"
Case 2
Me.RecordSource = "whatever else"
Case 3
Me.RecordSource = "third option"
End Select
Exit Sub
Else
End If

End Sub
____________________________

Just change the form name, the option group name, and the names of the
recordsources.

This shouldn't do anything unless the form is loaded, which means it
shouldn't prevent you from opening the report from somewhere else in your
app. It also means you still have the wherecondition and openargs to play
with for other purposes.

If you really wanted to bulletproof it, you could add another if block to
the top of it and check to see if a choice has been made in frame1. Of
course, if you use a default value for the option group I think you'd prevent
any issues with that...

CW

 

[Tony Toews [MVP]]

On the Reports Open Event I tried to use the the following:
Change

If iPrtRpt = 1 Then

to

If me.openargs = 1 Then

However a Select Case would be slightly more elegant.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/