Thread: Selection to show in Report Page Header

Hi ... I'm stumped again !

What I want to do is to have the dates that have been inputted on a form, appear in the Report page header.

The form is called Select_Inception. This form only has two text boxes called Start Date and End Date, they select on a field called PeriodFrom. There is also an OK button, this is the vba code I have used

Code:

Private Sub cmdOK_Click()
Dim db As DAO.Database
Dim QD As QueryDef
Dim where As Variant

Set db = CurrentDb()

'Delete the existing dynamic query; trap the error if the query does not exist.
On Error Resume Next
db.QueryDefs.Delete ("Qry_By_Inception_Date")
On Error GoTo 0

If Not IsNull(Me![End Date]) Then
    where = where & " AND [PeriodFrom] between DateValue('" + _
    Me![Start Date] + "') AND DateValue('" & Me![End Date] & "')"
Else
    where = where & " AND [PeriodFrom] >= DateValue('" + Me![Start Date] _
    + "')"
End If

'MsgBox "Select * from Premium " & ("where " + Mid(where, 6) & ";")
Set QD = db.CreateQueryDef("Qry_By_Inception_Date", _
"SELECT * FROM Broker INNER JOIN Premium ON Broker.BrokerNo = Premium.BrokerNo " & (" where " + Mid(where, 6) & ";"))

DoCmd.OpenReport "Premium_by_Inception_Date", acViewPreview
End Sub

I've added a text box in the page header of the report and in the control source i've tried this

Code:

="For the period of" & [Forms]![Select_Inception]![Start Date] & "to" & [Forms]![Select_Inception]![End Date]

But when I open the report I get #Name?

I'm sure it's something daft I'm doing again.

Cheers
Wrightie

Well there are 2 ways to do it, the one I would use is using TempVars.

On the form you enter the dates on make an ok key or something that when clicked does something like:

Code:

Private Sub OK_Click()
    Dim MaxBase As Double
    Dim MaxCash As Double
    Dim MaxEarlyOrder As Double
    Dim MaxEarlyShip As Double
    Dim MaxProgram As Double
    Dim MaxDiscount As Double

    MaxBase = Nz(Me.MaxBase, 0)
    MaxCash = Nz(Me.MaxCash, 0)
    MaxEarlyOrder = Nz(Me.MaxEarlyOrder, 0)
    MaxEarlyShip = Nz(Me.MaxEarlyShip, 0)
    MaxProgram = Nz(Me.MaxProgram, 0)
    MaxDiscount = (1 - MaxBase) * (1 - MaxCash) * (1 - MaxEarlyOrder) * (1 - (MaxEarlyShip + MaxProgram))
    
    TempVars.Add "MaxBase", MaxBase
    TempVars.Add "MaxCash", MaxCash
    TempVars.Add "MaxEarlyOrder", MaxEarlyOrder
    TempVars.Add "MaxEarlyShip", MaxEarlyShip
    TempVars.Add "MaxProgram", MaxProgram
    TempVars.Add "MaxSeedTenderDiscount", MaxDiscount
    
    Me.Visible = False
End Sub

Then in the 2 text fields you enter the following in the control source: =[TempVars]![MaxBase] etc

These are examples from my database. I use the discounts in the header of a report comparing pricing.

If the form is still open when you open the report you can always use the "me."

Hope this helps,

Dan

P.S. TempVars only works in Access 2007, (I see you have excel 2007 so assumed.)

Np, glad you figured it out.

TempVars are a fantastic addition.