Report Parameters forms value list set by VBA?

[cbgraham_asu]

NOTE: I"m a VBA novice

I have a set of reports that use a common Report Parameter form.
When I first designed them it was convenient as the same set of
filters were applicable to all the reports:

Example: report for invoices, report for open orders, report for
processing orders
Filters were: Location, Manager & Employee

Now they also want to filter by date range, which won't consistent
between reports:
IE. Invoices = 30, 60 or 90 days old
vs Pending Orders = 10, 15, 30 days old

I'd like to just add one more drop down box to the parameter report
and have the value list change based on what report it was called
from. I have within the report code:

'define drop down values
Dim Vl_Rng As String
Vl_Rng = "0;30;60;90"

' Open report parameter form
DoCmd.OpenForm "FrmCRP", , , , , acDialog

And then in report parameters form I want to set something like
me.cboRng.RowSource = VL_Rng

but I"m obviously missing something that will allow this variable to
pass from the reports event to the forms opening event.

Any help is appreciated.

-Christine

 

[Guest]

Hi Christine,

Perhaps something like this will work. In the report's open event procedure,
use the optional OpenArgs statement when opening your report selection form
in dialog mode:

Private Sub Report_Open(Cancel As Integer)
On Error GoTo ProcError

Dim strRowSource As String
strRowSource = "0,days;30,days;60,days;90,days"

DoCmd.OpenForm "FrmCRP", _
WindowMode:=acDialog, _
OpenArgs:=strRowSource

'Cancel the report if "cancel" was selected on the dialog form.
If Forms.FrmCRP.txtContinue = "no" Then
Cancel = True
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub

Note: The above procedure assumes an unbound, hidden, text box on the form
FrmCRP, named txtContinue.

Code in FrmCRP would include this Open event procedure:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo ProcError

Dim strRowSource As String

If Not IsNull(Me.OpenArgs) Then
strRowSource = Me.OpenArgs
With Me.cboRng
.RowSourceType = "Value List"
.RowSource = strRowSource
.ColumnCount = 2
End With
End If

Me.Visible = True

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub

The FrmCRP form also includes two command buttons, cmdOK and cmdCancel. The
click event procedures for these two buttons includes the following:

Private Sub cmdCancel_Click()
On Error GoTo ProcError

Me.txtContinue = "no"
Me.Visible = False

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub

Private Sub cmdOK_Click()
On Error GoTo ProcError

Me.txtContinue = "yes"
Me.Visible = False

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub




Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________