Populating Table With Calculated Report Values

[shobhit_shanker]

Here are the relevant "givens" to my problem...

Form: frmLaunchRpt
- Text Box: txtAsOfDate
- Check Box: chkLogEval
- Command Button: cmdLaunchRpt

Report: rptEvaluation
- Text Box: txtGreen (Percent of items on schedule)
- Text Box: txtYellow (Percent of items slightly off schedule)
- Text Box: txtRed (Percent of items greatly off schedule)

Here is a sample of what information I want to output and store on a
table...

Table: tblLogEvaluation

[Date] [Green] [Yellow] [Red]
txtAsOfDate(1) txtGreen(1) txtYellow(1) txtRed(1)
txtAsOfDate(2) txtGreen(2) txtYellow(2) txtRed(2)
txtAsOfDate(3) txtGreen(3) txtYellow(3) txtRed(3)
..
..
..
txtAsOfDate(n) txtGreen(n) txtYellow(n) txtRed(n)

The problem...

I have a percentage calculation in the report footer of my
"rptEvaluation" report. The information in the footer of the report
needs to be logged to a table, but not every time the report is run.

To accomplish this, the user will have a check-box selection called
"chkLogEval" on a form (frmLaunchRpt) that launches rptEvaluation. So
the information in rptEvaluation will ONLY be logged to a table when
the condition chkLogEval = TRUE. The user presses the launch button
(cmdLaunchRpt) to open the report.

The Solution...

I want to write a VBA script under the "On Open" property of
rptEvaluation to check to see if chkLogEval = TRUE. If it is, then it
will append an entry to tblLogEvaluation, retrieving the fields shown
in the sample table.

Please assist me with the proper coding to solve this problem.
Thanks in advance.

[Rich P]

Greetings,

You won't be able to retrieve the calculated value from the Report Open
event because that is where the calculation is taking place. You can
retrieve the calculated result in the Detail Event. Note: If you have
several records but only one calculated result, you may want to add a
boolean flag so that you don't keep retrieving the same value.

You can use basic sql to retrieve the value in the Detail Event like
this:

Dim bGetData 'declare boolean var at module level of report

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If bGetData = True Then
if Forms!frmLaunchRpt!chkLogEval = True Then
DoCmd.RunSql "Update tblLog Set PercentVal = " & txtPercent
bGetData = False
End If
End If
End Sub

Private Sub Report_Open(Cancel As Integer)
bGetData = True 'set boolean var to true here
End Sub

This code assumes that the Table field (PercentVal from tblLog) which
will receive the value from the report is a numeric field and thus does
not require delimiting with single quotes like a string value would when
using Sql.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!