Integrated user input to run 2 reports

  • Thread starter Thread starter Stephm
  • Start date Start date
S

Stephm

Hi. PC Datasheet 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
 
Why not have the input come from an unbound textbox on a
form. Both reportd can reference the same input.
ex.
[Forms]![NameOfForm]![NameOfTextbox]

Chris
 
Hmm. Interesting, but I don't quite get it.
Where would I put [Forms]![NameOfForm]![NameOfTextbox]
for each form?
How is this different than having a form (MonthParam) with
an unbound combo box (FindMonth which is MonthID and
MonthName, so 1 and January)? Both forms call MonthParam
so the user can select the correct month. But that's also
the problem- requiring the user to select the month for
each form rather than passing "January" from the user
selection on Birthdays to Birthday labels. Thanks for
taking the time, I'd really like to understand this. Steph
-----Original Message-----
Why not have the input come from an unbound textbox on a
form. Both reportd can reference the same input.
ex.
[Forms]![NameOfForm]![NameOfTextbox]

Chris

-----Original Message-----
Hi. PC Datasheet 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
.
Click to expand...
.
Click to expand...
 
What do you mean both forms call monthparam?

I your reports are based on a query then both queries can
reference the same control on your form. It should be one
form with your monthname then all your reports reference
the monthname on that one form. Am I missing something here?

Chris
-----Original Message-----
Hmm. Interesting, but I don't quite get it.
Where would I put [Forms]![NameOfForm]![NameOfTextbox]
for each form?
How is this different than having a form (MonthParam) with
an unbound combo box (FindMonth which is MonthID and
MonthName, so 1 and January)? Both forms call MonthParam
so the user can select the correct month. But that's also
the problem- requiring the user to select the month for
each form rather than passing "January" from the user
selection on Birthdays to Birthday labels. Thanks for
taking the time, I'd really like to understand this. Steph
-----Original Message-----
Why not have the input come from an unbound textbox on a
form. Both reportd can reference the same input.
ex.
[Forms]![NameOfForm]![NameOfTextbox]

Chris

-----Original Message-----
Hi. PC Datasheet 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
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
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.
 
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
 
For [Pet Partner Birthdays]:
Private Sub Report_Close()
***DoCmd.Close acForm, "MonthParam"

*** Delete this line; you have it under the Else line. You're closing
MonthParam when you close the first report so the second report can not get
the month you select.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Stephm said:
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 Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com






.
Click to expand...
Click to expand...
 
That makes sense! I deleted the Else statement, but I'm
still having problems. [Pet Partner Birthday Labels] -
the second report- is asking for Forms!ParamMonth!FindMonth

So why is FindMonth selected in the "birthdays" report not
making it to the "labels" report? Does the second report
not know that the ParamMonth form (with combo box
FindMonth)is already opened? I used
Private Sub FindMonth_AfterUpdate()
Me.Visible = False
End Sub

But I'm wondering if it needs to be a Public Sub (since
we're dealing with 2 different reports). Never have used
Public so that's a guess and probably not a good one.
What's the real answer? Thanks for your patience! Steph

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

*** Delete this line; you have it under the Else line. You're closing
MonthParam when you close the first report so the second report can not get
the month you select.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

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
Click to expand...
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,
Click to expand...
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
Click to expand...
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.
Click to expand...
When
you close the
label report, the input form will also be closed.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com



Hi. PC Datasheet 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


.
Click to expand...
Click to expand...


.
Click to expand...
 
<< I deleted the Else statement>>
That's not what I told you to do! That's the wrong thing to do! Go back and
read my previous response again.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Stephm said:
That makes sense! I deleted the Else statement, but I'm
still having problems. [Pet Partner Birthday Labels] -
the second report- is asking for Forms!ParamMonth!FindMonth

So why is FindMonth selected in the "birthdays" report not
making it to the "labels" report? Does the second report
not know that the ParamMonth form (with combo box
FindMonth)is already opened? I used
Private Sub FindMonth_AfterUpdate()
Me.Visible = False
End Sub

But I'm wondering if it needs to be a Public Sub (since
we're dealing with 2 different reports). Never have used
Public so that's a guess and probably not a good one.
What's the real answer? Thanks for your patience! Steph

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

*** Delete this line; you have it under the Else line. You're closing
MonthParam when you close the first report so the second report can not get
the month you select.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

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 Datasheet
Your Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
www.pcdatasheet.com



message
Hi. PC Datasheet 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


.
Click to expand...


.
Click to expand...
Click to expand...
 
Ah! It's all in the semicolon!
Delete this line; you have it under the Else line
Thanks for the hand holding- all is well now. Sorry, Steph
-----Original Message-----
<< I deleted the Else statement>>
That's not what I told you to do! That's the wrong thing to do! Go back and
read my previous response again.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


That makes sense! I deleted the Else statement, but I'm
still having problems. [Pet Partner Birthday Labels] -
the second report- is asking for Forms!ParamMonth! FindMonth

So why is FindMonth selected in the "birthdays" report not
making it to the "labels" report? Does the second report
not know that the ParamMonth form (with combo box
FindMonth)is already opened? I used
Private Sub FindMonth_AfterUpdate()
Me.Visible = False
End Sub

But I'm wondering if it needs to be a Public Sub (since
we're dealing with 2 different reports). Never have used
Public so that's a guess and probably not a good one.
What's the real answer? Thanks for your patience! Steph

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

*** Delete this line; you have it under the Else line. You're closing
MonthParam when you close the first report so the
Click to expand...
second
report can not get
the month you select.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

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
Click to expand...
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
Click to expand...
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 Datasheet
Your Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
www.pcdatasheet.com



message
Hi. PC Datasheet 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
Click to expand...
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
Click to expand...
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


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Thanks for the replies. I did get the reports to work the
way I wanted them to. Cheers, Steph
-----Original Message-----
What do you mean both forms call monthparam?

I your reports are based on a query then both queries can
reference the same control on your form. It should be one
form with your monthname then all your reports reference
the monthname on that one form. Am I missing something here?
Chris
-----Original Message-----
Hmm. Interesting, but I don't quite get it.
Where would I put [Forms]![NameOfForm]![NameOfTextbox]
for each form?
How is this different than having a form (MonthParam) with
an unbound combo box (FindMonth which is MonthID and
MonthName, so 1 and January)? Both forms call MonthParam
so the user can select the correct month. But that's also
the problem- requiring the user to select the month for
each form rather than passing "January" from the user
selection on Birthdays to Birthday labels. Thanks for
taking the time, I'd really like to understand this. Steph
-----Original Message-----
Why not have the input come from an unbound textbox on a
form. Both reportd can reference the same input.
ex.
[Forms]![NameOfForm]![NameOfTextbox]

Chris


-----Original Message-----
Hi. PC Datasheet 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
.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top