Set field value of current record to be qual to previous record field value

[djungna]

Hi friend
I am making easy database to collect water consumption
If I have field as below

RecordID
Date
Startmeter
Todayusage
Finalmeter

If I want to set startmeter value to be equal to Finalmeter value of prevouis day

How can I do it.





Also If I enter the date in one field, and want to set date in another filed automatically to be 2 day apart of first filled field.

Example.
Today field = 03/12/04
Other day filed = 01/12/04 (shown automatic)

Thank you for your help.

 

[trucktime]

Problems

About the Datefields

Suppose you have Datefield1 and Datefield2.
In the After Update Event of Datefield1 put the following code:

Me.DateField2 = DateAdd("d", -2, [Datefield1])

When you put a date in Datefield1 it will automatically put
the date of two days before in Datefield2. (after the field has been updated)

If you use:
Me.DateField2 = DateAdd("d", 2, [Datefield1])
the date in Datefield2 will be two days later.

As for the meter value, I assume you have a record for each day.
Put a command button on the form that creates a new record, call it NewRecord, and put the following code in the OnClick Event.

*******************************
Private Sub NewRecord_Click()
On Error GoTo Err_NewRecord_Click
Dim cMeter As Integer
cMeter = Me.FinalMeter


DoCmd.GoToRecord , , acNewRec
Me.Startmeter = cMeter

Exit_NewRecord_Click:
Exit Sub

Err_NewRecord_Click:
MsgBox Err.Description
Resume Exit_NewRecord_Click

End Sub
*******************************
This code assumes the Meter Fields are Number fields.
If they are Text fields use:
Dim cMeter As String

So, when you create a record for the next day, the StartMeter is
automatically filled with the value of the FinalMeter field of the
previous day.

One last comment.
In your table you have a field called Date. This is a reserved word in
Access and therefore it may cause problems at some point.
You should rename it to something like: MeasureDate or M_Date.

Good Luck and Happy New Year

 

[aleb]

Hi
Well finalmeter should not be in a table.
Other than that ... here we go

 

[djungna]

Ask some more about "OVerflow"

Dear Trucktime

Thank you so much for your help.
I have already try your command , it works.

Then I copy your command in another access datebase.
It show "Overflow".

Do you have any idea , what should be the problem.

Thank you for your help.

 

[djungna]

Ask some more about "OVerflow"

Dear Trucktime

Thank you so much for your help.
I have already try your command , it works.

Then I copy your command in another access datebase.
It show "Overflow".

Do you have any idea , what should be the problem.

Thank you for your help.

 

[Oldsoftboss]

Change

Dim cMeter As Integer

to

Dim cMeter As Long

 

[djungna]

Thank you

Thank you so much "old softboss"

 

[Oldsoftboss]

From the VBA help file:

Integer variables are stored as 16-bit (2-byte) numbers ranging in value from -32,768 to 32,767.

Long (long integer) variables are stored as signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647.

HTH

Dave