Re: Integrated user input to run 2 reports

From: Stephm (Stephm_at_discussions.microsoft.com)
Date: 02/13/05 

Date: Sun, 13 Feb 2005 11:37:27 -0800

Thanks for the help- I'm very close but it's still not
working quite right. The input from the 1st form doesn't
seem to be getting to the 2nd form. Either I have to feed
the parameter again to Report2 or Report2 prints ALL
labels.

Here's what I have:
Month selection form is MonthParam with combo box FindMonth
Report1 is [Pet Partner Birthdays]
Report2 is [Pet Partner Birthday Lables]

For FindMonth:
Private Sub FindMonth_AfterUpdate()
Me.Visible = False
End Sub

For [Pet Partner Birthdays]:
Private Sub Report_Close()
DoCmd.Close acForm, "MonthParam"

Dim MsgStr As String
Dim TitleStr As String
MsgStr = "Do you want to print labels for the birthday Pet
Partners?"
TitleStr = "Print Labels?"
    If MsgBox(MsgStr, vbYesNo, TitleStr) = vbYes Then
        DoCmd.OpenReport "Pet Partner Birthday Labels"
    Else
        DoCmd.Close acForm, "MonthParam"
    End If

End Sub

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "MonthParam", , , , , acDialog
    If Not IsLoaded("MonthParam") Then
        Cancel = True
    End If
End Sub
(maybe this report_open sub is the problem?)

For [Pet Partner Birthday Labels]:
Private Sub Report_Close()
DoCmd.Close acForm, "MonthParam"
End Sub

Now, [Pet Partner Birthday Labels] is asking for
Forms!Param!FindMonth where it used to pull up MonthParam
to chose FindMonth combo box. So I'm getting the same
behavior where the "labels" report doesn't know the input
parameter value.

For both reports, I'm using the same query:
SELECT DISTINCT Animals.AnimalName, AnimalType.AnimalType,
Animals.AnimalBreed, Month([DateOfBirth]) AS Expr1, Nz
([NickName],[FirstName]) & " " & [LastName] AS [Member
Name], Contacts.MailingAddress, Contacts.City,
UCase$([StateOrProvince]) AS State, Contacts.PostalCode,
Animals.DateOfBirth, Months.MonthID, Contacts.ContactID,
Months.Month, Animals.PrimaryOwner
FROM Months, Contacts INNER JOIN (AnimalType INNER JOIN
(Animals INNER JOIN CertResults ON Animals.AnimalsID =
CertResults.AnimalsID) ON AnimalType.AnimalTypeID =
Animals.AnimalTypeID) ON Contacts.ContactID =
CertResults.ContactID
WHERE (((Month([DateOfBirth]))=[Forms]![MonthParam]!
[FindMonth]) AND ((Months.MonthID)=Month
([Animals.DateOfBirth])) AND ((Animals.PrimaryOwner)=
[Contacts].[ContactID]) AND ((Animals.Retired) Is Null)
AND ((Animals.Deceased) Is Null))
ORDER BY Contacts.PostalCode;

I tried making a second query for [Pet Partner Birthday
Labels] leaving out (Month([DateOfBirth])=[Forms]!
[MonthParam]![FindMonth]) but that made the "labels"
report print everything.

No doubt I'm doing something stupid, but I can't quite
figure it out. Thanks for taking the time- I appreciate
it. Steph

>-----Original Message-----
>Put the following in the Open event of the first report:
>DoCmd.OpenForm "NameOfYourInputForm",,,,,acDialog
>
>Put the following in the AfterUpdate event of the
combobox on your input
>form:
>Me.Visible = False
>
>Modify the code I previously gave you in the Close event
of the first report
>to:
>Dim MsgStr As String
>Dim TitleStr As String
>MsgStr = "Do you want to print labels for the birthday
people?"
>TitleStr = "Print Labels??"
>If MsgBox(MsgStr,vbYesNo,TitleStr) = vbYes Then
> DoCmd.OpenReport "NameOfReport2"
>Else
> DoCmd.Close acForm, "NameOfYourInputForm"
>End If
>
>Put the following in the Close event of the second report:
> DoCmd.Close acForm, "NameOfYourInputForm"
>
>When you click the button to open the first report, your
input form will
>first appear. After you make a selection from the
combobox on the input
>form, the input form will disappear (become invisible)
and the first report
>will open. When you try to close the first report, a
message will come up
>asking you if you want to print labels. If you answer no,
the first report
>will close and your input form will close in the
background. If you answer
>Yes, your label report will appear with the labels for
the month you
>selected in the input form. You don't need to input the
month a second time
>because the input form is still open just invisible. When
you close the
>label report, the input form will also be closed.
>
>--
> PC Data***
>Your Resource For Help With Access, Excel And Word
Applications
> resource@pcdata***.com
> www.pcdata***.com
>
>
>
>"Stephm" <Stephm@discussions.microsoft.com> wrote in
message
>news:224101c51070$f09538e0$a601280a@phx.gbl...
>> Hi. PC Data*** showed me how to run a report and then
>> have a message box that kicks off another report.
>>
>> The first report, Birthdays, requires user input to
>> select the correct birthday month. I have an input form
>> with an unbound combo box. The second report, Birthday
>> Labels, also requires the same user input (using the
same
>> input form). It's not very streamlined to have the user
>> select the same criteria twice. Is there a way to
>> streamline the process so the second report knows what
>> the user input criteria is from the first report? That
>> way when the user selects "yes, print" from the message
>> box, the appropriate labels will print. Thanks, Stephm
>
>
>.
>