Continous forms and calculations

[willir]

For a meter readng system, I am having a problem using the current reading less the previous reading to calculate the net. My issue is more of a problem depicting previous reading and the calculated net value for the the current record / row in a continous form.

We track utility meter readings for gas, water, electricity, etc for a plant -- there are dozens of meters read every day. We have grown way beyond using Excel (I am sure you have heard this one before). Although Access does a much better job of tracking meters, I am finding it awkward to caclulate the net daily consumption -- something that is a piece of cake in Excel.

Sure, I can store the previous reading along with the current reading but two issues ... 1) breaks normalization rule, and 2) a data entry error or missed entries can be more problematic to fix. I would prefer to store the current reading only, find the previous reading when needed (report or form) and then calculate the net.

I have tried a complex nested select statement -- works for a single record, but not for a continous form.

I have tried using an array - calculates the numbers correctly, but does not display properly.

Both the "select" and "array" attempts failed for the same reason -- Access uses the current or last record to display the same calculated value to each line of the continous form. For example, 961,000 is displayed on every row for the prevous reading. (Yep - some of you have been here.)

From the history file, I have...
fldHistory_id, fldMeter_id, fldRead_date, fldReading

In addition, on the form, I have fields using an unbound control source or a select statement as a control source (tried both) for...
fldPrev_reading

And then the calculated field...
fldNet

I guess my next path will be to use a temp table, but I was hoping for a less messy solution. (Temp files, add / delete records would suggest more DB maintenance would be required more frequently. Plus logic to avoid data conflict from other users.)

I suppose an alternate would be to use non-continous form and manually manipulate the array. Again messy, and I am not sure how the code would work when creating the reports.

Any ideas?
Richard

 

[hermanlaksko]

Sometime ago another of our colleages here on tek-tips asked a similar Q, he/she let me have the database and I made this solution:

Option Compare Database
Dim AccTtl
Private Sub Form_Current()
UpdFld
End Sub
Private Sub POINT_AfterUpdate()
UpdFld
End Sub
Function UpdFld()
AccTtl = 0
Dim Re As DAO.Recordset
Set Re = CurrentDb.OpenRecordset("Select * From TRAIN_DETAIL Where STAFF_NO='" & Me!STAFF_NO & "' Order by TrainID&quot
Do While Not Re.EOF
Re.Edit
Re!Acc_Point = Re!POINT + AccTtl
Re.Update
AccTtl = AccTtl + Re!POINT
Re.MoveNext
Loop
Me!Acc_Point.Requery
'You can make a me.requery to get the perfect result, but then you will have to
'drop your user on the same or next record
End Function

Droped on a form it works but I was not entirely happy about it he/she was though

Let me know what you think.

Herman

 

[willir]

Herman

First, thank you for your reply.

I am trying to implement your code. Seems to be similar to line numbering solutions I have seen for continous forms with the exception of...
Re.Edit
Re!Acc_Point = Re!POINT + AccTtl
Re.Update

Is this code updating the table? (Train_detail in your example) My current history table consists of...
fldHistory_id, fldMeter_id, fldRead_date, fldReading

Are you suggesting that I add another field to the table? What is the purpose of the field?

Thanks again.
Richard

 

[hermanlaksko]

Yes the code re.edit etc is updating a tbl (field in a tbl) and yes you will need an extra field to keep track of the calculations made.

Herman

 

[willir]

Thanks Herman for your replies.

I guess I am stuck with either breaking the rules for normalization (not to store calculated values and redundent data in the table) and develop a fix-it routine to recalculate net values when the operators "break it", or use a temp table, or use a regular form that fakes a continous form.

I guess I will put it on my wish list for Access future development where we need more programming control over continous forms.

Thanks again.
Richard

 

[hermanlaksko]

Richard..... bending it, not breaking..... B-)

As I sad I was not happy about this solution, but it worked and the program moving.

Herman

 

[VBOnly]

How about the following code in the current event. I have not test it. Assume you data is sorted by reading date.

private Sub Form_Current()
    dim prev, cur
    dim rst as Recordset

    'cur = xxx value for current reading

    set rst=me.Recordsetclone
    set rst.bookmark = me.recordset.bookmark
    if rst.absoluteposition=1 then
        'the different/consumption will not be calculated
    else
        rst.moveprev
        prev = rst![reading]
        'now set the consumption to cur-prev
    endif
end sub