Label caption based on text input

[Guest]

I have a series of reports that open on a command button click, but the users
have to select a month from a list box that they wants the report's queries
run for. I would like the report's label captions to have "Report for
(**listbox value**) 2007". These need to be able to be changed at runtime.
I've already declared a variable called Month at the global level.

How would I go about doing this? Also, how could I have that same values
transfer over to the queries?

Here is part of my coding so far....

Option Compare Database
Dim Month As String
Private Sub CMDBUTTON_Click()
On Error GoTo Err_Command4_Click
lstMonth.Value = Month
DoCmd.Close , "Printreports"

DoCmd.OpenReport "AL Report", acViewDesign, "", "", acNormal
DoCmd.RunCommand acCmdPrint
DoCmd.Close , ""
..
..
..

 

[Guest]

You don't need to do all that. Just reference the month listbox the user
uses on the form

Add an unbound text box to the report
for the control source put
[forms].[nameofform].[name of listbox on form]

 

[Al Campagna]

Outatym,
An unbound text control with...
= "Report for " & Forms!frmYourFormName!lstYourListName & " 2007"
would include the listbox value from the Print form in the concatenation...
** if the form is still open.**
If you "must" close the form while the report runs, pass the lsibox value to the
report via the OpenReport-OpenArgs argument.

Also, I'm wondering if the "2007" shouldn't be hard wired as it is, but also derived
from the Print form. What happens when you run the report for 2006 or 2008?

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

OK...guess I could do that, but would I still have to keep the lform with the
listbox open?

Also, how could I use their selection to run the queries, as of now it asks
what month to run for.

Thanks!

You don't need to do all that. Just reference the month listbox the user
uses on the form

Add an unbound text box to the report
for the control source put
[forms].[nameofform].[name of listbox on form]

I have a series of reports that open on a command button click, but the users
have to select a month from a list box that they wants the report's queries
run for. I would like the report's label captions to have "Report for
(**listbox value**) 2007". These need to be able to be changed at runtime.
I've already declared a variable called Month at the global level.

How would I go about doing this? Also, how could I have that same values
transfer over to the queries?

Here is part of my coding so far....

Option Compare Database
Dim Month As String
Private Sub CMDBUTTON_Click()
On Error GoTo Err_Command4_Click
lstMonth.Value = Month
DoCmd.Close , "Printreports"

DoCmd.OpenReport "AL Report", acViewDesign, "", "", acNormal
DoCmd.RunCommand acCmdPrint
DoCmd.Close , ""
.
.
.

 

[Guest]

In the query criteria for month put
[Forms]![NameOfForm]![NameOfTextbox]

OK...guess I could do that, but would I still have to keep the lform with the
listbox open?

Also, how could I use their selection to run the queries, as of now it asks
what month to run for.

Thanks!

You don't need to do all that. Just reference the month listbox the user
uses on the form

Add an unbound text box to the report
for the control source put
[forms].[nameofform].[name of listbox on form]

I have a series of reports that open on a command button click, but the users
have to select a month from a list box that they wants the report's queries
run for. I would like the report's label captions to have "Report for
(**listbox value**) 2007". These need to be able to be changed at runtime.
I've already declared a variable called Month at the global level.

How would I go about doing this? Also, how could I have that same values
transfer over to the queries?

Here is part of my coding so far....

Option Compare Database
Dim Month As String
Private Sub CMDBUTTON_Click()
On Error GoTo Err_Command4_Click
lstMonth.Value = Month
DoCmd.Close , "Printreports"

DoCmd.OpenReport "AL Report", acViewDesign, "", "", acNormal
DoCmd.RunCommand acCmdPrint
DoCmd.Close , ""
.
.
.

 

[Guest]

Didn't think about that...Guess I could have another listbox for the year
also...

Thanks for the help!!!

 

[Guest]

I have the report pulling up no problem with the new textbox...but the query
runs but doesn't have any results...and I know there are results...

Here is the SQL:
SELECT [August Insurance].Company, [August Insurance].MCN, [August
Insurance].[Ins Company], [August Profile].ADDRESS1, [August
Profile].ADDRESS2, [August Profile].CITY AS [F-M Profile_CITY], [August
Profile].STATE AS [F-M Profile_STATE], [August Profile].ZIP AS [F-M
Profile_ZIP], [August Profile].NAME, [August Profile].TITLE, [August
Profile].TELEPHONE AS [F-M Profile_TELEPHONE], [August Profile].REVENUE,
[August Profile].TRACTORS, [August Insurance].DD, [August Insurance].MM
FROM [August Profile] LEFT JOIN [August Insurance] ON [August Profile].MCN =
[August Insurance].MCN
WHERE ((([August Profile].STATE)="AL") AND (([August
Insurance].MM)="Forms!PrintReports!lstMonth ") AND (([August
Insurance].TYPE)="BIPD"));

See any reason that it wouldn't???

In the query criteria for month put
[Forms]![NameOfForm]![NameOfTextbox]

OK...guess I could do that, but would I still have to keep the lform with the
listbox open?

Also, how could I use their selection to run the queries, as of now it asks
what month to run for.

Thanks!

You don't need to do all that. Just reference the month listbox the user
uses on the form

Add an unbound text box to the report
for the control source put
[forms].[nameofform].[name of listbox on form]

:

I have a series of reports that open on a command button click, but the users
have to select a month from a list box that they wants the report's queries
run for. I would like the report's label captions to have "Report for
(**listbox value**) 2007". These need to be able to be changed at runtime.
I've already declared a variable called Month at the global level.

How would I go about doing this? Also, how could I have that same values
transfer over to the queries?

Here is part of my coding so far....

Option Compare Database
Dim Month As String
Private Sub CMDBUTTON_Click()
On Error GoTo Err_Command4_Click
lstMonth.Value = Month
DoCmd.Close , "Printreports"

DoCmd.OpenReport "AL Report", acViewDesign, "", "", acNormal
DoCmd.RunCommand acCmdPrint
DoCmd.Close , ""
.
.
.

 

[Guest]

Nevermind....found a lonely space after the code to call the list box value...

It works now...THANKS FOR EVERYONE'S HELP!

I have the report pulling up no problem with the new textbox...but the query
runs but doesn't have any results...and I know there are results...

Here is the SQL:
SELECT [August Insurance].Company, [August Insurance].MCN, [August
Insurance].[Ins Company], [August Profile].ADDRESS1, [August
Profile].ADDRESS2, [August Profile].CITY AS [F-M Profile_CITY], [August
Profile].STATE AS [F-M Profile_STATE], [August Profile].ZIP AS [F-M
Profile_ZIP], [August Profile].NAME, [August Profile].TITLE, [August
Profile].TELEPHONE AS [F-M Profile_TELEPHONE], [August Profile].REVENUE,
[August Profile].TRACTORS, [August Insurance].DD, [August Insurance].MM
FROM [August Profile] LEFT JOIN [August Insurance] ON [August Profile].MCN =
[August Insurance].MCN
WHERE ((([August Profile].STATE)="AL") AND (([August
Insurance].MM)="Forms!PrintReports!lstMonth ") AND (([August
Insurance].TYPE)="BIPD"));

See any reason that it wouldn't???

In the query criteria for month put
[Forms]![NameOfForm]![NameOfTextbox]

OK...guess I could do that, but would I still have to keep the lform with the
listbox open?

Also, how could I use their selection to run the queries, as of now it asks
what month to run for.

Thanks!

:

You don't need to do all that. Just reference the month listbox the user
uses on the form

Add an unbound text box to the report
for the control source put
[forms].[nameofform].[name of listbox on form]

:

I have a series of reports that open on a command button click, but the users
have to select a month from a list box that they wants the report's queries
run for. I would like the report's label captions to have "Report for
(**listbox value**) 2007". These need to be able to be changed at runtime.
I've already declared a variable called Month at the global level.

How would I go about doing this? Also, how could I have that same values
transfer over to the queries?

Here is part of my coding so far....

Option Compare Database
Dim Month As String
Private Sub CMDBUTTON_Click()
On Error GoTo Err_Command4_Click
lstMonth.Value = Month
DoCmd.Close , "Printreports"

DoCmd.OpenReport "AL Report", acViewDesign, "", "", acNormal
DoCmd.RunCommand acCmdPrint
DoCmd.Close , ""
.
.
.