How to put form calculated field in a report 1

[tburrows]

I have a form which is the interface for the database. It has several calculated fields which are based on what value a field or fields are. I need a report with these calculated fields in them. I tried putting the form field in the report and all the records are the same. All the calculated fields are done in VB. This is a personnel budget application. Example: If a person changes pay step during the year, a field calculates the number of months in the old step and what the pay would be and then calculates the number of months in the new field and calculates the pay and adds the two pays together. Total of 5 fields. How do I get these calculated fields onto the report?
Thanks
Tom

 

[SteveR77]

Tom,

If the control source for the form and report are the same then you should be able to add your calculated fields to the detail section of the report and get the same values unless there is something about the design of the form that at this point I am not aware of such as a main/sub form.

Steve

 

[tburrows]

I have no subforms or subreports. The problem is that in order to get a value from the calculated field on the form is that the form has to be open. So, the only value I am getting is the value from the first record in the form. All the personnel are on the report, but how do I get the values to recalculate for each person on the report? Do I have to run all the code from the form in the report? SQL statements?

Thanks
Tom

 

[SteveR77]

Tom,

I apologize for not being clear. The easiest way for you to get the calculations from your form to your report would be to recreate a field on your report and copy the control source from the calculated fields on your from to your report.

So if you have =[AveRate]*[Quanity] on the form you need to have the same calculation for the reports calculated field.

I hope this makes since.

Steve

 

[tburrows]

I wish it was that simple. I have almost 100 lines of code involved with just 5 of these calculated fields. They have to compare dates to see what step a person is, then it figures out if the person is due for a step this year and then figures out how many months and the pay at the old step and the same for the new step.

Tom

 

[mkasson]

Tom,

I have the same situation: database contains raw data that is used in moderately complicated number crunching; reports data is based on number crunch results.

I'm not done figuring out the best way to implement this yet, but here's what I've got so far:

Part of each record in my database is about 40 numeric fields used in the number crunch. As you do, I have a form that we can scroll through. The form has VBA functions for the number crunching, so the calculation results will show up in the form fields. The number of fields is so large that I just referred to Me.Field to in the form's VBA functions, rather than pass all 40 items as arguments. This means I can't put the functions in a Public place like a module.

I tried setting up a report that actually does the calculating (which it sounds like you are doing). Two things I didn't like.
First, I had to now have all my functions (which are still subject to some fine tuning and improvements) in two places - 1 in the forms and another in the report. Tough to make changes.
Second, I couldn't find a way to make (sub)totals, grouping and sorting on the calculated fields.

What I am doing now: When I want to run the report on the crunched data, I am using a procedure to crunch the numbers and output the data to a table. The table has one row per record of original data and contains one column for each column of the report I want. Then I can run a very simple report off that database with all the grouping/sorting/totalling I want.

I am having an unexpected problem in that the report seems to be only starting to report at the 20th record. I will post that problem separately if I search here and don't find anything.

Hopefully the lengthy answer above is on point and helpful. If so feel free to ask for more guidance.



- MSK

 

[tburrows]

MSK -
That sounds like it may be a solution. How do you create the table? Are you doing it from the report or the form?

Thanks for the help.

Tom

 

[mkasson]

The button that I click for the report, runs the sub that clears out the databases old data and populates it with new database.

My sub looks like this.

Public Sub CycleLiens()

    Dim cnn As New ADODB.Connection
    Dim rstLiens As New ADODB.Recordset
    Dim rstPortValRes As New ADODB.Recordset
    Dim cmd As ADODB.Command
    Dim lngAffected As Integer
    
    cnn.Open CurrentProject.Connection
    
    rstLiens.Open "Select * FROM Liens", cnn 

    'Clear out the old data
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandType = adCmdText
    cmd.CommandText = "DELETE * FROM PortValRes"
    cmd.Execute lngAffected
    
    rstPortValRes.Open "SELECT * FROM PortValRes", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    
Form_frmLiens.Visible = True
Form_frmLiens.FilterOn = True
    With rstLiens
        .MoveFirst
        Do While Not .EOF
                rstPortValRes.AddNew
                ' An example of pulling raw data from the database
                Form_frmLiens.Filter = "[LienKey] = " & .Fields("LienKey")
                ' Two examples of getting calculated data from the form
                rstPortValRes.Fields("ValClass") = Form_frmLiens.ValClass
                rstPortValRes.Fields("Balance") = Form_frmLiens.oCurBal
                'Insert rest of your columns here.
                rstPortValRes.Update
                rstPortValRes.MoveNext
            .MoveNext
        Loop
        .Close
    End With
Form_frmLiens.Visible = False
End Sub

Note that I created the new database (PortValRes) with its column definitions before using this the first time.
I'm pretty sure the above could be improved upon and I welcome any suggestions.

One note on the problem I'm having. It may be that the form isn't finishing its calculations before I try to copy the data and that's why I'm not getting all my records. On the other hand, the PortValRes table and the query (Select *) that feeds the report seem to have more records than print out in the report. I am not using any filters, etc.

- MSK

 

[mkasson]

Oops. I realize that I commented incorrectly in the actualy copying part. I set the filter on the form so I can use the main table's index to pull up just the record that calculations should be done for.
Please refer to the below Do While Loop section.

        Do While Not .EOF
                rstPortValRes.AddNew
[b]                ' Filter to select the correct record from the main db
                Form_frmLiens.Filter = "[LienKey] = " & .Fields("LienKey")
                ' An example of pulling raw data from the database
                rstPortValRes.Fields("LienKey") = .Fields("LienKey")
[/b]
                ' Two examples of getting calculated data from the form
                rstPortValRes.Fields("ValClass") = Form_frmLiens.ValClass
                rstPortValRes.Fields("Balance") = Form_frmLiens.oCurBal
                'Insert rest of your columns here.
                rstPortValRes.Update
                rstPortValRes.MoveNext
            .MoveNext
        Loop

- MSK

 

[tburrows]

MSK -

rstLiens.Open "Select * FROM Liens", cnn

I understand the PortValRes is the new table which you are storing all your values that the report will need. And that you getting these values from the form. What is Liens? Is this your original database that has the raw data you were talking about?

Tom

 

[mkasson]

Yes, Liens is my raw data. I've changed the line of code that you included to refer to a query ("qPortfolio") so that I can run the query independently to check the results. Also will make it easier as I decide to make the query a little more complex (e.g. filtering).

By the way, I should point out that SteveR77 in particular (and others) helped me in ultimately coming up with this.

That reminds me of another problem with doing the calcs in the Report: you have to have any fields you want to use in any calculations on the report. I did this by having all of the raw data fields in the detail line in the report, but I had them as .1" x .1" and invisible. Still, its not as clean as I'd like.

- MSK

 

[tburrows]

I am starting out simple and just copy one field into the table to do the report. However I am getting an input box as soon as it starts to run. The input box is "Enter Parameter Value" and gives me the parameter of LB7. I have no parameters.

With rstBudget
.MoveFirst
Do While Not .EOF
rstBudgetRes.AddNew
Form_frmPersonnelData.Filter = "[PCN] = " & .Fields("PCN")
rstBudgetRes.Fields("PCN") = .Fields("PCN")
.MoveNext
Loop
.Close
End With

rstBudget is the original database
rstBudgetRes is the new database
PCN is a unique field that each record has.
When it is starting it is asking for parameter for this value. Don't know where this is coming from.

Any ideas????
Thanks Tom

 

[mkasson]

This usually happens when you are trying to refer to something that is out of scope.

My guess is it happens when your form or report is trying to call something that is not in scope.

Does it actually make it to the code? (Try setting a breakpoint.) What is LB7?

- MSK

 

[mkasson]

Tom,

It seems that the problem I was having may relate to the two different kinds of table manipulations I was making. I replaced the delete query with a record by record delete; to the extent you are using the code I provided, I thought you'd like to know.

I replaced the opening and delete query portion with the following:

cnn.Open CurrentProject.Connection
    
rstLiens.Open "qPortfolio", cnn, adOpenKeyset, adLockReadOnly, adCmdTable
    
rstPortValRes.Open "PortValRes", cnn, adUseClient, adLockOptimistic, adCmdTableDirect
    
'First Delete all the previous records from the PortValRes table
lngAffected = 0
With rstPortValRes
    If .RecordCount > 0 Then
        .MoveFirst
        Do Until .RecordCount = 0
            .Delete
            lngAffected = lngAffected + 1
            .MoveNext
        Loop
    End If
End With
    
Form_frmLiens.Visible = True
'From this point on its all the same (except a few .Close and Set = Nothing statements)

I'm not certain that the tables are .Opened in the best way, but its working.

- MSK

 

[tburrows]

LB7 is the value of PCN in the first record of the table.

Tom

 

[mkasson]

It is evaluating your line of code:

Form_frmPersonnelData.Filter = "[PCN] = " & .Fields("PCN")

and setting the filter equal to

PCN = LB7

You need quotes around the LB7.

Try:

Form_frmPersonnelData.Filter = "[PCN] = '" & .Fields("PCN") & "'"

That should set the filter equal to:
and setting the filter equal to

PCN = 'LB7'

which is what I think you need. Its different from filter because my LienKey is a number. I'm guessing that PCN is a text field.


- MSK

 

[tburrows]

I figured out the LB7 problem. Somehow on the form properties that was put in the filter property. Mine is working now. Here is my code. I modified what you did a little. What I did was, I copied the fields that were on the form that I needed for the report and then copied those fields into the new database and then based the report on that new table. I didn't try and pull values from the original table as all of those values are on the form anyway.

Thanks for all your help.

Dim cnn As New ADODB.Connection
Dim rstBudget As New ADODB.Recordset
Dim rstBudgetRes As New ADODB.Recordset
Dim cmd As ADODB.Command
Dim lngAffected As Integer

cnn.Open CurrentProject.Connection
rstBudget.Open "SELECT * FROM tblPersonnel", cnn
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = "DELETE * FROM tblBudgetReport"
cmd.Execute lngAffected
rstBudgetRes.Open "SELECT * FROM tblBudgetReport", cnn, adOpenKeyset, adLockPessimistic, adCmdTableDirect
Form_frmPersonnelData.Visible = True
Form_frmPersonnelData.FilterOn = True

With rstBudget
.MoveFirst
Do While Not .EOF
rstBudgetRes.AddNew
Form_frmPersonnelData.Filter = "[PCN] = '" & .Fields("PCN") & "'"
rstBudgetRes.Fields("CostCenter") = Form_frmPersonnelData.CostCenter
rstBudgetRes.Fields("PCN") = Form_frmPersonnelData.PCN
rstBudgetRes.Fields("Title") = Form_frmPersonnelData.Title
rstBudgetRes.Fields("LastName") = Form_frmPersonnelData.LastName
rstBudgetRes.Fields("FirstName") = Form_frmPersonnelData.FirstName
rstBudgetRes.Fields("MiddleInitial") = Form_frmPersonnelData.MiddleInitial
rstBudgetRes.Fields("FTE") = Form_frmPersonnelData.FTE
rstBudgetRes.Fields("GradeChange") = Form_frmPersonnelData.GradeChange
rstBudgetRes.Fields("M1") = Form_frmPersonnelData.txtM1
rstBudgetRes.Fields("M1Salary") = Form_frmPersonnelData.txtM1Salary
rstBudgetRes.Fields("M2") = Form_frmPersonnelData.txtM2
rstBudgetRes.Fields("M2Salary") = Form_frmPersonnelData.txtM2Salary
rstBudgetRes.Fields("AnnualSalary") = Form_frmPersonnelData.txtAnnualSalary
.MoveNext
Loop
.Close
End With
Form_frmPersonnelData.FilterOn = False
Form_frmPersonnelData.Filter =

 

[mkasson]

Lookin good.

Let me know if you have the same problem that I did when mixing the record level access with the SQL commands. My problem was that sometimes not all the records wound up in the results table.

- MSK

 

[tburrows]

MSK -
The only problem I am having is that it is not saving the last record into the database. Otherwise it is working fine.

Tom