Report is blind to form calculations

  • Thread starter Thread starter Jaycee
  • Start date Start date
J

Jaycee

I have three fields in a form that calculate intervals
between various dates. The calculation is achieved via
code (included below) rather than formula. The results
appear correctly in the form, but appear as 0 in the
report. What changes do I need to make to the report to
get the calculated results to appear?

BTW, I'm not a programmer, so please talk slowly and use
small words. Thanks!
___________________
FORM CODE:

Option Compare Database

Private Sub AddNewRecord_Click()
On Error GoTo Err_AddNewRecord_Click


DoCmd.GoToRecord , , acNewRec

Exit_AddNewRecord_Click:
Exit Sub

Err_AddNewRecord_Click:
MsgBox Err.Description
Resume Exit_AddNewRecord_Click

End Sub

Private Sub Date2004_BeforeUpdate(Cancel As Integer)
funIntervalUpdate
End Sub

Private Sub Date2005_BeforeUpdate(Cancel As Integer)
funIntervalUpdate
End Sub

Private Sub Date2006_BeforeUpdate(Cancel As Integer)
funIntervalUpdate
End Sub

Private Sub Form_Current()
funIntervalUpdate
End Sub

Private Sub LastAudit_BeforeUpdate(Cancel As Integer)
funIntervalUpdate
End Sub

Private Function funIntervalUpdate()

'Set 2004 Interval
If Not IsDate(Date2004) Then
Interval2004 = ""
ElseIf Not IsDate(LastAudit) Then
Interval2004 = ""
Else
Interval2004 = DateDiff("m", LastAudit, Date2004)
End If

'Set 2005 Interval
If Not IsDate(Date2005) Then
Interval2005 = ""
ElseIf Not IsDate(Date2004) Then
If Not IsDate(LastAudit) Then
Interval2005 = ""
Else: Interval2005 = DateDiff("m", LastAudit,
Date2005)
End If
Else: Interval2005 = DateDiff("m", Date2004, Date2005)
End If

'Set 2006 Interval
If Not IsDate(Date2006) Then
Interval2006 = ""
ElseIf Not IsDate(Date2005) Then
If Not IsDate(Date2004) Then
If Not IsDate(LastAudit) Then
Interval2006 = ""
Else: Interval2006 = DateDiff("m", LastAudit,
Date2006)
End If
Else: Interval2006 = DateDiff("m", Date2004,
Date2006)
End If
Else: Interval2006 = DateDiff("m", Date2005,
Date2006)
End If

End Function
 
What have you done to get the information to the report.

If the Controls you calculate are bound to a Field in a Table, then the
Record has to be saved before the Report will show it... that means you must
move off it to another record, explicitly save it from the menu or function
key shortcuts, or close the form.

Otherwise, if it isn't bound, then you'll need code in the report to pick up
the information from the Control on the Form, and the Form must still be
Open.

Please clarify or follow up here in the newsgroup, not by e-mail. Thanks.

Larry Linson
Microsoft Access MVP
 
The controls in the form (Interval2004, Interval2005, and
Interval2006) are unbound.

What code do I need in order for the report to pick up
the calcs?

Also, what's the best way to notify those who want to
view the report that they need to open the form first?

Thanks for your help!
 
Back
Top