Need values from unbound controls to appear in report

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

Jaycee

I have a form with three unbound controls --
Interval2004, Interval2005, and Interval2006 -- that
calculate the number of months between various dates.
The code for this calculation is included below.

What needs to be done in order for these calculated
values to appear in a report?

Any help is greatly appreciated!

Jaycee
_____________
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
 
Add unbound controls to the report.
Set their control source to:
=forms!FormName!Interval2004
=forms!FormName!Interval2005
=forms!FormName!Interval2006

or ..
Using VBA in the Report's Open event
VariableA = forms!FormName!Interval2004
VariableB = etc.

Dim these variable in the Declarations section of the code window so you can
access the data in any of the other sections.

The form must be open when the report is run.

Did you receive my direct response to your sorting in the report?
 
Fred,

I created three unbound controls named VariableA, etc.
and entered

=forms!RiskAnalysisAuditSchedule!Interval2004 (etc.)

as the control source for each. It automatically puts
brackets around each of the sections and doesn't return
anything on the report. (The form is open.)

I went to the second solution and added

VariableA = Forms!RiskAnalysisAuditSchedule!Interval2004
etc.

to the report's open event, so that the entire code reads

Option Compare Database

Private Sub Report_Open(Cancel As Integer)

VariableA = Forms!RiskAnalysisAuditSchedule!Interval2004
VariableB = Forms!RiskAnalysisAuditSchedule!Interval2005
VariableC = Forms!RiskAnalysisAuditSchedule!Interval2006

End Sub

When I went to view the report, it returned the error
message "You can't assign a value to this ..." and
highlighted the VariableA line. I didn't really
understand what you were saying about dimming the
variables in the Declarations section.

What am I doing wrong? Thanks much, as always!

Jaycee

P.S. I did receive your most recent date sorting
solution, but I opened it at home last night and failed
to forward it to myself so that I could access it here at
work. I'll have to check it out on Monday. Thanks!
 
The Declarations section of the code window is way up on top, before the
first Sub procedure:

Option Compare Database
Option Explicit

Dim VariableA as Integer (or string? or variant? etc.)
Dim VariableB as Integer
Dim VariableC as Integer
========
Sub Whatever_Here

By declaring the variable up in the declarations section,
they are available in any section of the report.
If you Dim the variables in the Open event, they can only
be used in that event, which may be OK if that's the only
place you would need them.
If you notice the 2 drop-down boxes at the top of the code sheet,
when you place your cursor within an event procedure, the
left dropdown shows the name of the section and the right one
the name of the event.
Click up above the topmost procedure and you will notice
the left shows 'General' and the right dropdown shows 'Declarations'.
Click in any function or procedure that is not an event, and
the boxes will show "General" and the name of the Procedure/Function.

I have no idea why
=forms!RiskAnalysisAuditSchedule!Interval2004
as a control source in the report isn't working.
Are you sure the form is open when the report is run?
Does the [Interval4] control on the Form have the correct data displayed?
Are you using the correct form event for this?
I think AfterUpdate and the Form's Current event would be better.

If the form controls don't have data, neither method will work in the
Report.
 
Fred,

I entered the Dim VariableA etc. lines in the
Declarations section, but still got the "You can't assign
a value to this object" message (referring to the same
line as mentioned below) when I tried to run the report.
The form (the only form) is open and the intervals
display correctly in it.

After much futzing around with code, I decided to clear
out all that I'd added and go back to Plan A -- i.e.,
putting

=forms!RiskAnalysisAuditSchedule!Interval2004 (etc.)

in the unbound controls in the report. When I did that,
I realized what was going on. The report is picking up
the intervals from whichever record the form is open to
and assigns the intervals from that record to every
record in the report. Whenever I'd opened the form
before, I left it on the first record, which has no audit
dates -- therefore nothing showed up on the report. This
last time, I'd moved through the records and stopped on
one that had both 2004 and 2006 audit dates, with the
intervals being 23 and 27 months, respectively. Lo and
behold, those intervals appeared in the report for each
record.

Did we just move closer to the solution, or further away?
-----Original Message-----
The Declarations section of the code window is way up on top, before the
first Sub procedure:

Option Compare Database
Option Explicit

Dim VariableA as Integer (or string? or variant? etc.)
Dim VariableB as Integer
Dim VariableC as Integer
========
Sub Whatever_Here

By declaring the variable up in the declarations section,
they are available in any section of the report.
If you Dim the variables in the Open event, they can only
be used in that event, which may be OK if that's the only
place you would need them.
If you notice the 2 drop-down boxes at the top of the code sheet,
when you place your cursor within an event procedure, the
left dropdown shows the name of the section and the right one
the name of the event.
Click up above the topmost procedure and you will notice
the left shows 'General' and the right dropdown shows 'Declarations'.
Click in any function or procedure that is not an event, and
the boxes will show "General" and the name of the Procedure/Function.

I have no idea why
=forms!RiskAnalysisAuditSchedule!Interval2004
as a control source in the report isn't working.
Are you sure the form is open when the report is run?
Does the [Interval4] control on the Form have the correct data displayed?
Are you using the correct form event for this?
I think AfterUpdate and the Form's Current event would be better.

If the form controls don't have data, neither method will work in the
Report.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Jaycee said:
Fred,

I created three unbound controls named VariableA, etc.
and entered

=forms!RiskAnalysisAuditSchedule!Interval2004 (etc.)

as the control source for each. It automatically puts
brackets around each of the sections and doesn't return
anything on the report. (The form is open.)

I went to the second solution and added

VariableA = Forms!RiskAnalysisAuditSchedule! Interval2004
etc.

to the report's open event, so that the entire code reads

Option Compare Database

Private Sub Report_Open(Cancel As Integer)

VariableA = Forms!RiskAnalysisAuditSchedule! Interval2004
VariableB = Forms!RiskAnalysisAuditSchedule! Interval2005
VariableC = Forms!RiskAnalysisAuditSchedule! Interval2006

End Sub

When I went to view the report, it returned the error
message "You can't assign a value to this ..." and
highlighted the VariableA line. I didn't really
understand what you were saying about dimming the
variables in the Declarations section.

What am I doing wrong? Thanks much, as always!

Jaycee

P.S. I did receive your most recent date sorting
solution, but I opened it at home last night and failed
to forward it to myself so that I could access it here at
work. I'll have to check it out on Monday. Thanks!
Click to expand...


.
Click to expand...
 
Fred,

I entered the Dim VariableA etc. lines in the
Declarations section, but still got the "You can't assign
a value to this object" message (referring to the same
line as mentioned below) when I tried to run the report.
The form (the only form) is open and the intervals
display correctly in it.

After much futzing around with code, I decided to clear
out all that I'd added and go back to Plan A -- i.e.,
putting

=forms!RiskAnalysisAuditSchedule!Interval2004 (etc.)

in the unbound controls in the report. When I did that,
I realized what was going on. The report is picking up
the intervals from whichever record the form is open to
and assigns the intervals from that record to every
record in the report. Whenever I'd opened the form
before, I left it on the first record, which has no audit
dates -- therefore nothing showed up on the report. This
last time, I'd moved through the records and stopped on
one that had both 2004 and 2006 audit dates, with the
intervals being 23 and 27 months, respectively. Lo and
behold, those intervals appeared in the report for each
record.

Did we just move closer to the solution, or further away?
-----Original Message-----
The Declarations section of the code window is way up on top, before the
first Sub procedure:

Option Compare Database
Option Explicit

Dim VariableA as Integer (or string? or variant? etc.)
Dim VariableB as Integer
Dim VariableC as Integer
========
Sub Whatever_Here

By declaring the variable up in the declarations section,
they are available in any section of the report.
If you Dim the variables in the Open event, they can only
be used in that event, which may be OK if that's the only
place you would need them.
If you notice the 2 drop-down boxes at the top of the code sheet,
when you place your cursor within an event procedure, the
left dropdown shows the name of the section and the right one
the name of the event.
Click up above the topmost procedure and you will notice
the left shows 'General' and the right dropdown shows 'Declarations'.
Click in any function or procedure that is not an event, and
the boxes will show "General" and the name of the Procedure/Function.

I have no idea why
=forms!RiskAnalysisAuditSchedule!Interval2004
as a control source in the report isn't working.
Are you sure the form is open when the report is run?
Does the [Interval4] control on the Form have the correct data displayed?
Are you using the correct form event for this?
I think AfterUpdate and the Form's Current event would be better.

If the form controls don't have data, neither method will work in the
Report.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Jaycee said:
Fred,

I created three unbound controls named VariableA, etc.
and entered

=forms!RiskAnalysisAuditSchedule!Interval2004 (etc.)

as the control source for each. It automatically puts
brackets around each of the sections and doesn't return
anything on the report. (The form is open.)

I went to the second solution and added

VariableA = Forms!RiskAnalysisAuditSchedule! Interval2004
etc.

to the report's open event, so that the entire code reads

Option Compare Database

Private Sub Report_Open(Cancel As Integer)

VariableA = Forms!RiskAnalysisAuditSchedule! Interval2004
VariableB = Forms!RiskAnalysisAuditSchedule! Interval2005
VariableC = Forms!RiskAnalysisAuditSchedule! Interval2006

End Sub

When I went to view the report, it returned the error
message "You can't assign a value to this ..." and
highlighted the VariableA line. I didn't really
understand what you were saying about dimming the
variables in the Declarations section.

What am I doing wrong? Thanks much, as always!

Jaycee

P.S. I did receive your most recent date sorting
solution, but I opened it at home last night and failed
to forward it to myself so that I could access it here at
work. I'll have to check it out on Monday. Thanks!
Click to expand...


.
Click to expand...
 
Jaycee said:
Fred,

I entered the Dim VariableA etc. lines in the
Declarations section, but still got the "You can't assign
a value to this object" message (referring to the same
line as mentioned below) when I tried to run the report.
The form (the only form) is open and the intervals
display correctly in it.

After much futzing around with code, I decided to clear
out all that I'd added and go back to Plan A -- i.e.,
putting

=forms!RiskAnalysisAuditSchedule!Interval2004 (etc.)

in the unbound controls in the report. When I did that,
I realized what was going on. The report is picking up
the intervals from whichever record the form is open to
and assigns the intervals from that record to every
record in the report.
Click to expand...

If the form controls are not bound then they have no relationship to individual
records. They are just values sitting there and your report will always display them
for every record in your report. If you need a calculation Per-Record then you need
to do it in your query that the report is using or replicate the calculations in the
detail section code of your report so they are evaluated for each record.
 
The report is based on a table rather than a query. The
code that produces the calculations is in the form. If I
construct a query upon which to base the report, what
portion of the code (shown below) should I copy into the
SQL window? Or into the detail section of the report? I
experimented with both, but with no success.

Thanks!
________________________
FORM EVENT 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
-----Original Message-----
If the form controls are not bound then they have no relationship to individual
records. They are just values sitting there and your
Click to expand...
report will always display them
for every record in your report. If you need a
Click to expand...
calculation Per-Record then you need
to do it in your query that the report is using or
Click to expand...
replicate the calculations in the
 
Back
Top