Label caption based on text input

O

Outatym

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 , ""
..
..
..
 
C

Chris Reveille

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]
 
A

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."
 
O

Outatym

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!

Chris Reveille said:
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]

Outatym said:
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 , ""
.
.
.
Click to expand...
Click to expand...
 
C

Chris Reveille

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

Outatym said:
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!

Chris Reveille said:
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]

Outatym said:
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 , ""
.
.
.
Click to expand...
Click to expand...
Click to expand...
 
O

Outatym

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

Thanks for the help!!!
 
O

Outatym

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???



Chris Reveille said:
In the query criteria for month put
[Forms]![NameOfForm]![NameOfTextbox]

Outatym said:
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!

Chris Reveille said:
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 , ""
.
.
.
Click to expand...
Click to expand...
Click to expand...
 
O

Outatym

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

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

Outatym said:
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???



Chris Reveille said:
In the query criteria for month put
[Forms]![NameOfForm]![NameOfTextbox]

Outatym said:
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 , ""
.
.
.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Report Data Splitting 2
passing OrderBy criteria from Form to Report in accde file 1
Passing OrderBy criteria from Form to Report 1
Print Reports and Sub Reports 7
Setting Report label caption from VB6 2
Preview and print report problems 1
Report displays by Month based on user input 0
Show Label & Text Box in Report based on Checkbox 3

Top