Changing info on a Report

[XwBrendanXw]

Hi,

I'm currently developing an application in Microsoft Access 2000, and I've run into a bit of a problem.

I have a Report which I am using to generate weekly invoices, rptInvoice. The invoices are generated for each Friday, listing all items from the previous Saturday up to and including the Friday. The invoice is dated on the Friday, and the due date is Friday of the next week.

Each invoice is numbered, with invoice #1 dated 18 Feb 2005, invoice #2 the friday after that (25 Feb 05) etc.

I've worked out how when you open the report, to work out which invoice number we are up to, find the invoice date and the due date. However, this code is placed on the Report. What I want it to do is have code on a Form, where you enter the invoice number in a text box, then when you click the button it works out the dates (using code I already have), opens the report, searching only for items within the correct date range (week up to the Invoice date), then (this is the part I'm having trouble with) change the value of text boxes (or labels) on the invoice Report to the invoice date, due date and invoice number.

So what I'm basically asking is, after I've opened a report via a command button on a form, how can I change the values of text boxes/labels on that report?

I'm guessing this is probably very easy, but I haven't done much of this stuff in Access before

Thanks in advance,

Brendan

 

[Remou]

Hi
How about one of these options?
1. Open the report in design view (DoCmd.OpenReport RptName, acDesignView), make the changes and save it.
2. Change your labels to textboxes and then you could set the ControlSource to (eg):
=[Forms]![FormWithDate]![Date1]
(I am guessing your form will still be open.)

 

[VBslammer]

In the report's Report_Open() event, check whether the calling form is loaded using:
[tt]
If CurrentProject.AllForms("MainForm").IsLoaded Then
[green]'get values from form[/green]
End If
[/tt]
Then modify your report code to use the values on the form instead of using DLookup() or whatever method you're using currently. For example:
[tt]
Me.Recordsource = "SELECT * FROM tblInvoices WHERE [InvDate] BETWEEN #" & Forms![MainForm]![BegDate] & "# AND #" & Forms![MainForm]![EndDate] & "#"
[/tt]

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[VBslammer]

Normally though, you would include a where clause when opening the report:
[tt]
Private Sub cmdOpenReport_Click()
DoCmd.OpenReport "rptInvoices", acViewPreview, , _
"[InvDate] BETWEEN #" & Me![BegDate] & "# AND #" & Me![EndDate] & "#"
End Sub
[/tt]

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[Remou]

Hi

So what I'm basically asking is, after I've opened a report via a command button on a form, how can I change the values of text boxes/labels on that report?

Using VBslammers methods to get the data, you could then put one or two text boxes on your form with their Control Source properties set to:
=Forms![MainForm]![BegDate]
=Forms![MainForm]![EndDate]
which would give date labels. Or even
= "Invoices from " & Forms![MainForm]![BegDate] & " to " & Forms![MainForm]![EndDate]
The dates can be formatted to long dates if that suits the reporting style better.
A similar method can be used for the numbers, if they appear on your main form and you wish to include them as labels.

 

[Tranman]

Brendan,
The contents of all of the fields are available to you in the "onPrint" event of the report page(they have all already been formatted when this event fires). At this point, you can just open a recordset that contains the values you want, and plug them(the values) into unbound controls on the report page.

Here is some code I use to set picture control values"

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

If Not IsNull([01]) Then
Image1.Visible = True
Image1.Picture = [01]
Else
Image1.Visible = False
End If
If Not IsNull([02]) Then
Image2.Visible = True
Image2.Picture = [02]
Else
Image2.Visible = False
End If
End Sub
This is sourced from a pivot query, but the effect is the same.

For that matter, if you have already keyed the values in a form, just plug <forms!myform!mytextbox> into the control on the report page.

Good Luck,
Tranman