Placing a date on a report

[Garyj]

I have a date that needs to have a date range placed in the header. This report is ran by a query that can't be ran by the users because it is in runtime mode. I tried to get the query to run for the user but it will not work.

question: I would like to place the date range on the report before it opens. Example, when the report opens a input box appears which will prompted for a beginning date and then an ending date. Once the user place the date in the box I would like for it to place in the head of the report.

Is this possible?

Thanks

 

[Fornatian]

add a label to the head of the document and then set the caption in the OnOpen event.


Dim inputFrom as string
Dim inputTo as string
Dim strCaption as string

inputFrom = InputBox("Enter Start Date")
inputTo = InputBox("Enter End Date")

strCaption = "from " & inputFrom & " to " & inputTo

Me.YourLabel = strCaption


this does not catch when the user doesn't enter a date, btw, if the dates correspond directly to criteria for the report you can directly reference criteria on a report to 'copy' the date into the report. Consequently, by collecting the criteria in a form allows you to catch failed entries before it gets to the report opening stage.

 

[Garyj]

Place a specific number in a field

I tried placing a label in my report but without a control source it will not stay. So I placed text in it to get to stay than ran the report with the code but I received a complie error. However two input boxes did come up prompting me to enter the date. This is great but getting to display in the report did not work. I also used a text box and it gave the same error.

Any sugguestions.

thanks again

 

[Garyj]

Place a specific number in a field

Corrected the complie error now I'm getting and error in the code where Me.YourLabel = strCaption is located. It is highlighted in yellow. I gave it a new name and this error still occurrs. Am I using and label or a text field in the report. If so, what am I doing wrong. I used both and place a name in the name field above the control source and this did not work.

Any sugguestions

Thanks again

 

[Garyj]

Place a specific number in a field

Here is a copy of the error

 

[Fornatian]

sorry, my mistake, should be:

Me.YourLabel.Caption = strCaption

 

[Garyj]

Place a specific number in a field

THANKS IT WORKS GREAT

 

[rramon]

I used the code in this post and it works great. The only problem is that both the code and the query I'm using generate input boxes for the same information. I need 1 input box that will run my query and populate my label is this possible?

 

[Fornatian]

This would be so easy if you would move away from Input Boxes and use a criteria form instead. That way you can referebce the fields on the form from the query and on the resultant report and it looks really professional instead of input boxes which frankly are pretty ugly creatures IMHO.

 

[rramon]

Ian,

I've never used a criteria form before. Let me read up on them if I have a question I post here for more help. Thank you for the advice.

Rocky

 

[rramon]

I've created a frmMachines with cboMachines

I entered [forms]![frmMachines]![cboMachines] in the criteria field in my query.

When I run the query it gives me a parameter box with the following:

Forms!frmMachines!cboMachines

What am I doing wrong?

 

[rramon]

OK I figured it out.

One thing though on the on click event of cboMachineSelect I entered the following:

DoCmd.OpenReport "Machine Select", acNormal

It automatically prints. Can I preview before the report prints?

 

[Fornatian]

DoCmd.OpenReport "Machine Select", acPreview

If you haven't figured it out how to put the form's dates on a report yet you just add an unbound box and then set it's control source to something like:

="from "& [forms]![frmMachines]![StartDateField] & " to " & [forms]![frmMachines]![EndDateField]

 

[rramon]

Fornatian

Not only did you answer my printing question you read my mind on the dates.

Thank you very much!!! All work great!!!!

Rocky

 

[Fornatian]

good to hear...

BTW, here's a useful little function you can use to give your users the opportunity to print or preview a report from the same button click:

...add this function to a public module...

Public Function PrintOrPreview(stDocName As String)
On Error Resume Next
'resume next to prevent error 2051 - open report method cancellation
'occurs when no data event for stDocName report
Dim resp As Byte
resp = MsgBox("Do you want to preview the report before printing?", vbYesNoCancel, conAppName)
If resp = vbYes Then
    DoCmd.OpenReport stDocName, acViewPreview
ElseIf resp = vbNo Then
    DoCmd.OpenReport stDocName, acViewNormal
End If
End Function

...then call it from your button click...

PrintOrPreview "YourReportName"

 

[rramon]

I get the following error when using the module:

Compile error:
Expected variable or procedure, not module

I saved the module as PrintOrPreview

I add an on click event to my ComboBox of:
PrintOrPreview "My Report Name"

I've never used Modules before this may be the problem.

 

[Fornatian]

Change the name of the module (NOT the function) to something else. Because they are the same name it causes a conflict.

 

[rramon]

I saved the module as ReportPreview

I add an on click event to my ComboBox of:
ReportPreview "My Report Name"

I still get the same error.

 

[Fornatian]

Don't change the call in on the click event. When you create a public function, that function becomes available as if it were a 'normal' command, so you can call it from your form. The module itself is just a container (carrier bag) which stores all your common functions and sub procedures.

You should have a module called ReportCreation that you can see in the database window. When you open this module you should then see the VBA window presented with the PrintOrPreview function displayed. If this is correct, then you should have a call to this procedure in your form:

Private Sub YourButton_Click()

PrintOrPreview "YourReport"

End Sub

 

[rramon]

I figured it out I changed the name of the module but then I also change my on click code to read:

ReportPreview "My Report Name"

It should still read:

PrintOrPreview "My Report Name"

PrintOrPreview being the function in the module.

I think I am beginning to understand modules. Let me see if this is correct:

I can use the PrintOrPreview function to recall the ReportPreview Module anywhere in my database instead of having to retype the entire code everytime.

Is this correct?