How to capture data from Report, save to table

[Bobby1st]

I have a monthly report that query a table for stats for various users with calculations and print results. I would like to capture those data to another table for year-to-date performance ratings.

I created a module that contains a public type Final Results variables. On the report module, upon closet it will (msgbox) ask if to save the result. When vbYes, it will save the reports data to the public variables and will open the year-to-date table and add new the data.

Here's my problem, when running the codes in the edit mode (F8), I can see the data being save on the public type variable but if run normally, the public variable will say <object variable not set>

Sample: Report Module
With YTDFinal
.EmpID = [txtEmpID]
.MONTH = [BEGDATE]
.SupvID = [txtSuprvId]
End With

set rst = dbs.open . . . . .("table name")
with rst
.AddNew
![EmpID] = YTDFinal.EmpID
![MONTH] = YTDFinal.MONTH
![SUPRVID] = YTDFinal.SupvID
with end

Will this save multi-user data? Is there a better way to accomplish this? Thanks

 

[namliam]

The source data obviously is allready in your DB right?
Then why save the YTD data seperatly and not just extract them from your DB ?

 

[Bobby1st]

Hi namliam,
I didn't want to elaborate my problem to create confusions but this is the situations. Data is input on the database table daily, I'll called tbl_Daily and monthly performance report is pulled for all staff. A report rollup the daily data to a monthly with formulas to compute unit per hours, standards and error ratios, etc. Since the task standards changes and other factors, the results for the month is save on another table (I'll call tbl_Month). At the year anniversary, a YTD report rollup from the tbl_Month is generated. That the big picture. Now my task is to capture and save the monthly scorecard results generated from by the formulas be save to a tbl_YTD. A YTD Performance rollup report would be generated using the tbl_Month data as source.

On my sample, a variable is used to capture data from a report to be save to the tbl_YTD. I know this works on Forms to a Table but doubt if it will work from Report to Table data transfer.

Note: Each month some tasks standard changes, with tiers (Needs, Meets, Meets +, and Exceeds) are used on the formulas. If I pull roll-up from the tbl_Daily and use the current standard (possible higher or lower) for the YTD report, then it would not reflect the true result if the standards is higher now than it was before. So saving the true value at the month level is the only way. Ops! the only way I know how, you're the expert.

Thank you

 

[namliam]

Yes that may well be a reason to store the values then again it may not...

Your "parameters" depend upon month period, so do your figures. So if you save both with a month parameter you dont need to store the YTD stuff.

Now if you want to store this... You could use something like...
Create your tblYTD (Dont use "_" really!)
Open it in your module (Set rsYTD = ...)
and store the totals there like
rsYTD.addnew
rsYTD!Field1 = x
rsYTD!Field2 = y
rsYTD!Field3 = z
rsYTD.update

Dont forget to close and clean up...

Good luck, either which way you go...

 

[Bobby1st]

Thanks, some piece is missing, I generate a calcsheet report, on this report it shows all the user's activities, rolled-up for the month and it shows if they meet the task standard. Every activity functions gets UPH (vol/time) vs standard. If the standard for the function is, 20 uph ( tiers: 1= < 20; 2= > 20; 2.5= >27; 3= >32 ). After using weighted average, the user might get 2.23 score for their Primanry functions; 1.98 Secondary functions; 83.65% equivalent 2 on %Time on Task; etc. These are the data that's save on the tblMonth (input manually) and generate a Final scorecard report. The Final report is further factored depend on their importance which total 100% on all categories. On the Final Report will show: Primary function gets 65% (2.23 * 65%) = 1.44; Secondary gets 15% (1.98 * 15% = .297); the Time on Task = 20% (2 * 20%= 0.04 ), 65%+15%+20% = 100%. The user gets a 1.77 Final score. I need all these data save to tblYTD.

Since the monthly Final report generate the data above, that's what I wanted to save to tblYTD and avoiding the manual input. The two tables contains different information.

I hope this helps. Thanks

 

[namliam]

But that is what I am doing right?? Storing the stuff you calculate in one query/report and stuff it into another table...

 

[Bobby1st]

namliam,

IT WORKS! Thanks. One more item. Since the supervisor will pull and print all his/her staff at one time, when the data is save to tblYTD the data, empId, supvId are save correctly but the data of the second staff is the same as the first even it is different. This is what I did and I think I forgotten how. . .

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblYTD", dbOpenDynaset)

Do Until rst.EOF <<xxxxxxxxxxxxxxxxx
With rst
.AddNew
![EmpID] = [txtEmpID]
![MONTH] = [BEGDATE]
![SUPRVID] = [txtSuprvId]
![PRIMARY TASK SCORE] = [txtPrimary]
![FinalScore] = [txtFinalScore]
.Update
End With
next <<< Error: Next without for
loop <<< won't work

===== I tried
Do
xxxx
xxxx
if rst.EOF then Exit Do
Loop or next (won't work)

Need your help, thanks

 

[namliam]

To move to the next record in the recordset you have tell access what recordset...
Much like if you want to know if something is EOF, you have to tell it what recordset too....

do while not rst.eof
....
rst.movenext
loop

Should do it...

 

[Bobby1st]

I must be doing something not right. I tried different ways, it either I get all except the first or get the first and then message already exist.

I added rst.MoveFirst before the Do statement. By the way, I placed this code on the Close Event Procedure. Does it matter what user report on the current screen? I have noticed it changed what it save dispite the MoreFirst.

Thanks

 

[namliam]

I have no clue... it should work... ??

 

[Bobby1st]

I think I have to break down what I am doing. I am pulling the data from a report query by supervisor (many staff) from the tblMonth. On the report module I have the ff.
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblYTD", dbOpenDynaset) <<<===== tblYTD
rst.MoveFirst
Do Until rst.EOF
With rst
.AddNew
-----
.Update
End With
rst.MoveNext
Loop

I can't say rst.Next, because the report source is not the recordset I called (tblYTD). The report source is tblMonth and only the month/supervisor I pulled from the query I wanted to save. How could I say save the next employee data from the query to the tblYTD?

Thanks

 

[namliam]

the rst you need to move in is the recordset for the query... not the one you are filling (offcourse), sorry for my blatant mistake....

Another option is to run an append query, maybe? Just run a query based on the same query as the report and append the records to the tblYTD???

 

[Bobby1st]

What do you think of this, I know it will work on a single pass. The situation is while the report is open there are multiple reports by employee, to use the rst.MoveNext, I have to run set.rst=rstOpenRecordset(. tblMonth. . . ) while the opened report is running the same. Will this work?

Module
Public Type ScorecardInfo
-------
End Type

Public YTDFinal As ScorecardInfo

---------------------------------------
Report Module <<===(source tblMonth)

With YTDFinal
.EmpID = [txtEmpID]
..........
End With

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblYTD", dbOpenDynaset)
With rst
.AddNew
![EmpID] = YTDFinal.EmpID
............
End With
rst.Close

I need to work a Loop. Do you think this will work or any better ideas?

Thanks

 

[Bobby1st]

What do you think of this, I know it will work on a single pass. The situation is while the report is open there are multiple reports by employee, to use the rst.MoveNext, I have to run set.rst=rstOpenRecordset(. tblMonth. . . ) while the opened report is running the same. Will this work?

Module
Public Type ScorecardInfo
-------
End Type

Public YTDFinal As ScorecardInfo

---------------------------------------
Report Module <<===(source tblMonth)

With YTDFinal
.EmpID = [txtEmpID]
..........
End With

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblYTD", dbOpenDynaset)
With rst
.AddNew
![EmpID] = YTDFinal.EmpID
............
End With
rst.Close

I need to work a Loop. Do you think this will work or any better ideas?

Thanks

 

[namliam]

It is something like that, but I dont see the point of using a user defined type....

First you put it into your type
With YTDFinal
.EmpID = [txtEmpID]

Then later from the type to the RST
With rst
.AddNew
![EmpID] = YTDFinal.EmpID

These are the same as
With rst
.AddNew
![EmpID] = [txtEmpID]

Which does it in one go, then put it into a loop... and done...

Or again, you can use an Append query... which is not only easier, but probably faster...

 

[Bobby1st]

These are the same as
With rst
.AddNew
![EmpID] = [txtEmpID]

Which does it in one go, then put it into a loop... and done...

Four to five down far back I showed that's what I did, it's good for one employee data. If all direct reports are pulled for a supervisor, the loop won't work because the source is different from the destination table. The rst refer to the destination table, therefore rst.MoveNext follow by a Loop manipulate the destination tblYTD not the tblMonth. There's where I ran out of ideas.

 

[namliam]

declare 2 recordsets

rstYTD
rstMonth
Now loop for rstMonth and rever to the fields with that using rstMonth!FieldName
Then store in rstYTD.

 

[Bobby1st]

declare 2 recordsets

rstYTD
rstMonth

The tblMonth have the raw stats, say Apple sold by employee at $1.10, this is pulled to the report and formulas applied to show cost 40%, ($1.10 x .4)= $.44, profit = 1.10-.44=$0.66. What is save on the tblYTD is the data $0.44 and $0.66, then report goes to the next employee, do again until EOF. My problem is more on the MoveNext employee data, compute and save.

To pull directly from the tblMonth won't work that's why I have the report running to get the data to save. My example to just to illustrate what's on the tblMonth. It's more complex as I explained on the previous thread above.

 

[Bobby1st]

Sorry, I think I deleted your last reply.

declare 2 recordsets

rstYTD
rstMonth

The tblMonth have the raw stats, say Apple sold by employee at $1.10, this is pulled to the report and formulas applied to show cost 40%, ($1.10 x .4)= $.44, profit = 1.10-.44=$0.66. What is save on the tblYTD is the data $0.44 and $0.66, then report goes to the next employee, do again until EOF. My problem is more on the MoveNext employee data, compute and save.

To pull directly from the tblMonth won't work that's why I have the report running to get the data to save. My example to just to illustrate what's on the tblMonth. It's more complex as I explained on the previous thread above.

 

[namliam]

If you have 'complex' calculations in your report (which is what I am getting from you) yes it is going to get harder...

Cannot you move the calcs into a query?