One value from previous record

I

Isla

This question was asked in the Access:Macros forum previously but feelings
were hurt and the question was never fully answered the name of this post is
"copy value from field to another in same table, different record"

Fields to be used: Filter One Influent Gallons, Filter One Influent MGD,
same for Filter two, then Pump 1 Run Hours, Pump 1 hours by previous

Filter1InfGallons = an 8 digit number entered once everyday. What I need to
do is take the filter influent gallons number from today (entered in the
field for gallons) subtract that from yesterdays filter one gallons (previous
record) and then divide by 1,000,000. I want it to populate the next field
(Filter One Influent MGD) with that calculation. Record one Fliter One
Influent MGD will be blank. I need this to happen in real time.. like after
they tab out of Filter One Gallons. This MGD calculated number needs to be
seen directly after that variable (filter 1 influent gallons) has been typed
in. The purpose of seeing this value, is that if it is off from what it
SHOULD be it will prompt them to re-check their log or re-check the filter.
The purpose of storing this information on the table is that if it IS off and
the readings are correct, they have that calculated amount already stored.

Same type of scenerio for Pump 1 run hours... the formula would be Todays
run hours minus only yesterdays days run hours then divide that number by 24.

According to the other post it CAN be done and automated... but that it can
be tricky. Please advise!!!!!!
 
D

David C. Holley

Sounds like you need to create a custom function and use it as the
DefaultValue.

Would you please post be the following information?

FieldNames, TableNames and relevant Form/Control names with description
below them as in
[PriorDayReading]
Reading from the day before

Its a bit difficult to follow all of the information from the text.
 
I

Isla

TableName = RoundsTable

FieldNames = Fliter1InfTOTAL Filter1InfMGD Filter1EffTOTAL Filter1EffMGD
Record1 34708987 0 47907858
0
(Yesterdays Reading)(No Calc since (Yesterdays Reading)(No
Calc ")
1st record)

Record2 47609876 .257 48906785
..678
(Todays reading (Calc field) (Todays reading)
(Calc field)
was just typed in)

!Math is not correct just numbers to give an example

Now you take [Filter1InfTOTAL (Today) - Filter1InfTOTAL (Yesterday)] /
1,000,000
and that ='s Filter1InfMGD for TODAY (i.e. record 2)

I have to be able to do this for every MGD field... there is a Filter2 with
the same calculations and other fields that have similar calculations. This
is for a research facilty so i need something that with will not be deathly
slow and will enter this data into the database. Did I explain this a little
bit better? So Record3 will take it's data field - yesterdays / by 1,000,000
to create Record3 MGD field.



David C. Holley said:
Sounds like you need to create a custom function and use it as the
DefaultValue.

Would you please post be the following information?

FieldNames, TableNames and relevant Form/Control names with description
below them as in
[PriorDayReading]
Reading from the day before

Its a bit difficult to follow all of the information from the text.
This question was asked in the Access:Macros forum previously but feelings
were hurt and the question was never fully answered the name of this post is
"copy value from field to another in same table, different record"

Fields to be used: Filter One Influent Gallons, Filter One Influent MGD,
same for Filter two, then Pump 1 Run Hours, Pump 1 hours by previous

Filter1InfGallons = an 8 digit number entered once everyday. What I need to
do is take the filter influent gallons number from today (entered in the
field for gallons) subtract that from yesterdays filter one gallons (previous
record) and then divide by 1,000,000. I want it to populate the next field
(Filter One Influent MGD) with that calculation. Record one Fliter One
Influent MGD will be blank. I need this to happen in real time.. like after
they tab out of Filter One Gallons. This MGD calculated number needs to be
seen directly after that variable (filter 1 influent gallons) has been typed
in. The purpose of seeing this value, is that if it is off from what it
SHOULD be it will prompt them to re-check their log or re-check the filter.
The purpose of storing this information on the table is that if it IS off and
the readings are correct, they have that calculated amount already stored.

Same type of scenerio for Pump 1 run hours... the formula would be Todays
run hours minus only yesterdays days run hours then divide that number by 24.

According to the other post it CAN be done and automated... but that it can
be tricky. Please advise!!!!!!
Click to expand...
Click to expand...
 
T

Tim Ferguson

Sounds like you need to create a custom function and use it as the
DefaultValue.
Click to expand...

Not legal. DefaultValue is a dbengine function, and it cannot see any vba
or access functions.


Tim F
 
D

David C. Holley

??? I'm certain that I've set the .DefaultValue for controls to
functions before. Now when it comes to the DV for a field in a table I
can see that.
 
D

David C. Holley

If you're not capturing the DATE of the reading, I would *HIGHLY*
recommend that you do as it will greatly help in writting the code.
Additionally, if there can be only 1 reading per date, you can enforce
this by setting the Indexed property of the field capturing the Date to
YES(NO DUPLICATES). You will however have to go back and manually enter
the dates for any records that already exist in the table.
TableName = RoundsTable

FieldNames = Fliter1InfTOTAL Filter1InfMGD Filter1EffTOTAL Filter1EffMGD
Record1 34708987 0 47907858
0
(Yesterdays Reading)(No Calc since (Yesterdays Reading)(No
Calc ")
1st record)

Record2 47609876 .257 48906785
.678
(Todays reading (Calc field) (Todays reading)
(Calc field)
was just typed in)

!Math is not correct just numbers to give an example

Now you take [Filter1InfTOTAL (Today) - Filter1InfTOTAL (Yesterday)] /
1,000,000
and that ='s Filter1InfMGD for TODAY (i.e. record 2)

I have to be able to do this for every MGD field... there is a Filter2 with
the same calculations and other fields that have similar calculations. This
is for a research facilty so i need something that with will not be deathly
slow and will enter this data into the database. Did I explain this a little
bit better? So Record3 will take it's data field - yesterdays / by 1,000,000
to create Record3 MGD field.



:

Sounds like you need to create a custom function and use it as the
DefaultValue.

Would you please post be the following information?

FieldNames, TableNames and relevant Form/Control names with description
below them as in
[PriorDayReading]
Reading from the day before

Its a bit difficult to follow all of the information from the text.
This question was asked in the Access:Macros forum previously but feelings
were hurt and the question was never fully answered the name of this post is
"copy value from field to another in same table, different record"

Fields to be used: Filter One Influent Gallons, Filter One Influent MGD,
same for Filter two, then Pump 1 Run Hours, Pump 1 hours by previous

Filter1InfGallons = an 8 digit number entered once everyday. What I need to
do is take the filter influent gallons number from today (entered in the
field for gallons) subtract that from yesterdays filter one gallons (previous
record) and then divide by 1,000,000. I want it to populate the next field
(Filter One Influent MGD) with that calculation. Record one Fliter One
Influent MGD will be blank. I need this to happen in real time.. like after
they tab out of Filter One Gallons. This MGD calculated number needs to be
seen directly after that variable (filter 1 influent gallons) has been typed
in. The purpose of seeing this value, is that if it is off from what it
SHOULD be it will prompt them to re-check their log or re-check the filter.
The purpose of storing this information on the table is that if it IS off and
the readings are correct, they have that calculated amount already stored.

Same type of scenerio for Pump 1 run hours... the formula would be Todays
run hours minus only yesterdays days run hours then divide that number by 24.

According to the other post it CAN be done and automated... but that it can
be tricky. Please advise!!!!!!
Click to expand...
Click to expand...
Click to expand...
 
Top