Selecting the correct records for mailing labels based on SQL Serverview

[vegathena]

I need to print some mailing labels. I work with an adp Access
frontend and a SQL Server backend.

The problem is this: I need to print address labels for all the people
whose birthday falls within a given month.

So far I have a SQL Server view that generates the names, addresses,
and birthday months (e.g. 1, 2, 3, ...) for the people I need to send
mail to.
I have a report for the labels that is based on this view and includes
the name and address.
I have a form that brings up the label report in preview mode. On this
form I have a combo box that allows the user to specify the birthday
month for which they want to print the labels.
So far my label report just brings up everybody who is included in
that view.

My question is the following: The user will use the form's combo box
to select the birthday month for which they want to print the labels.
How can I set up my report to accept this value as a parameter and to
only print the names and address associated with this month?

Thank you for your help.

 

[ND Pard]

Try this:

In your Report Module, at the module level (ie, above any subprocedures)
declare a variable similar to the following:

Dim my_Month as String ' this assumes your month is a string character

Then, in the Report's "On Open" event, place an [Event Procedure] like:

Private Sub Report_Open(Cancel As Integer)
my_Month = OpenArgs
Me.RecordSource = "Select * from (enter your table name here) _
Where [Enter the table's field containing the birthday month] = " & my_Month
& ";"
End Sub

Then, on the Form's command button that you use to open the above report,
place an "On Click" [Event Procedure] similar to: (my example assumes a
command button named: Cb_Open_Rpt is used)

Private Sub Cb_Open_Rpt_Click()
'This subprocedure passes the selected combobox month to the report
'via OpenArgs
DoCmd.OpenReport "Enter your Report Name here", _
acViewPreview, , , acWindowNormal, _
(Enter your ComboBox_Name_Here).Value
End Sub

The above method uses the OpenReport statements OpenArg parameter.

Good Luck.

 

[vegathena]

Thanks for your reply. That should definitely work.

Try this:

In your Report Module, at the module level (ie, above any subprocedures)
declare a variable similar to the following:

Dim my_Month as String ' this assumes your month is a string character

Then, in the Report's "On Open" event, place an [Event Procedure] like:

Private Sub Report_Open(Cancel As Integer)
my_Month = OpenArgs
Me.RecordSource = "Select * from (enter your table name here) _
Where [Enter the table's field containing the birthday month] = " & my_Month
& ";"
End Sub

Then, on the Form's command button that you use to open the above report,
place an "On Click" [Event Procedure] similar to: (my example assumes a
command button named: Cb_Open_Rpt is used)

Private Sub Cb_Open_Rpt_Click()
'This subprocedure passes the selected combobox month to the report
'via OpenArgs
DoCmd.OpenReport "Enter your Report Name here", _
acViewPreview, , , acWindowNormal, _
(Enter your ComboBox_Name_Here).Value
End Sub

The above method uses the OpenReport statements OpenArg parameter.

Good Luck.

I need to print some mailing labels. I work with an adp Access
frontend and a SQL Server backend.

The problem is this: I need to print address labels for all the people
whose birthday falls within a given month.

So far I have a SQL Server view that generates the names, addresses,
and birthday months (e.g. 1, 2, 3, ...) for the people I need to send
mail to.
I have a report for the labels that is based on this view and includes
the name and address.
I have a form that brings up the label report in preview mode. On this
form I have a combo box that allows the user to specify the birthday
month for which they want to print the labels.
So far my label report just brings up everybody who is included in
that view.

My question is the following: The user will use the form's combo box
to select the birthday month for which they want to print the labels.
How can I set up my report to accept this value as a parameter and to
only print the names and address associated with this month?

Thank you for your help.