converting to common units via temp table?

[RWilly]

I'd like to convert values to a common unit for a set of records during data
entry. Water use can be reported in gallons or acre-inches, and I want a
robust way to convert acre-inches to gallons without errors. At present, when
a user reports, we create a new reporting year record for that pump, add 12
new records to the (child) Volume table-one for each month of the reporting
year - and convert to gallons as needed by hand. These records are linked to
the reporting year record via a FK.

These are my thoughts on how to do this

create a temp table in an auxiliary mdb
create a recordset of Volume records in the temp table with the correct FK.
Create form on temp table
Have users enter any data needing conversion, which is converted on the form
Insert/update records from temp table to permanent mdb via query in code
Clean up temp table

Is this the easiest/most straightforward way to do this?

I'd appreciate any critiques, ideas, improvements, and suggestions regarding
code. I'm somewhat familiar with simple VBA, mostly from splicing others'
code. TIA

 

[John W. Vinson]

I'd like to convert values to a common unit for a set of records during data
entry. Water use can be reported in gallons or acre-inches, and I want a
robust way to convert acre-inches to gallons without errors. At present, when
a user reports, we create a new reporting year record for that pump, add 12
new records to the (child) Volume table-one for each month of the reporting
year - and convert to gallons as needed by hand. These records are linked to
the reporting year record via a FK.

These are my thoughts on how to do this

create a temp table in an auxiliary mdb
create a recordset of Volume records in the temp table with the correct FK.
Create form on temp table
Have users enter any data needing conversion, which is converted on the form
Insert/update records from temp table to permanent mdb via query in code
Clean up temp table

Is this the easiest/most straightforward way to do this?

I'd appreciate any critiques, ideas, improvements, and suggestions regarding
code. I'm somewhat familiar with simple VBA, mostly from splicing others'
code. TIA

No, it's not the best way, in my opinion! I'd just use a form to directly
update the reporting table; convert the user entered value automatically in
the form. For example, if there is an unbound textbox for acre-inches and a
second bound textbox for gallons, you can use the Afterupdate event of the
acre-inches textbox to calculate the gallons:

Private Sub txtAcreInch_AfterUpdate()
Me!txtGallons = Me!txtAcreInch / 27154.2876
End Sub

What is the structure of your table (fields and datatypes)? Why do you need to
create any new records - much less twelve?

John W. Vinson [MVP]

 

[RWilly]

No, it's not the best way, in my opinion! I'd just use a form to directly
update the reporting table; convert the user entered value automatically in
the form. For example, if there is an unbound textbox for acre-inches and a
second bound textbox for gallons, you can use the Afterupdate event of the
acre-inches textbox to calculate the gallons:

Private Sub txtAcreInch_AfterUpdate()
Me!txtGallons = Me!txtAcreInch / 27154.2876
End Sub

What is the structure of your table (fields and datatypes)? Why do you need to
create any new records - much less twelve?

John W. Vinson [MVP]

Thanks for the reply.

The general design of this database is
Water User>>Pump/Well>>Reporting Year>>Monthly Water Volume
| >>Water Use

We receive information from large quantity water users throughout the state.
We record the data, and then use it for water resource management, planning,
mapping, and so forth.

The design of the Monthly Water Volume table (tblPumpVol) is Primary key
(Autonumber), Foreign key (Long Int, to Reporting Year table), Month (Int),
Water volume (Single), Reporting units (text).

Your suggestion would be much simpler and I will give it a shot. There
appears to be a typo in your response, as gallons = acre-inches * 27,154.

Thanks again.

 

[John W. Vinson]

There
appears to be a typo in your response, as gallons = acre-inches * 27,154.

oops! <blush> Yes, that would have given you some rather strange results.

Hope the suggestions help.


John W. Vinson [MVP]