Calculation

[Guest]

Hi,

I am working on a database and I need to come up with a way to create an
entity (form, query etc.) that will perform a calculation. Here is the basic
example. We collect data from facilities for 30 different materials.
Facilities will either report the data as cubic yards OR pounds/tons of
material. The goal is to create an entity which can take the raw cubic yard
numbers (ie 50 cubic yards) and multiply it by the necessary conversion
factor, divide by 2000 and make it into a final tonnage number. Ex. 50
cubic yards of wooden Pallets. Eacy cubic yard of wooden pallets weighs
363.64/lbs/cy therefore the final tonnage is 9.091 tons. So I would want the
reporter to select Pallets as the conversion factor, type in 50 for the cubic
yards and generate 9.091 as the tonnage value.

I don't know if there is an easy way to do this. Not too much experience
programming VBA but it seems to me that I should be able to create something
basic.

Thanks for any help anyone can give!

Sincerely,
Adam Schlachter

 

[Guest]

Hi, Adam.

If you need no other functionality, a simple database of three tables should
handle this.

Materials
-----------
MaterialID AutoNumber (Primary Key)
Material Text (Pallet, Earth, etc.)
ConversionFactor Single

Facilities
----------
FacilityID AutoNumber (Primary Key)
FacilityName Text
Address
City
State
Zip
Phone
Fax
Etc.

Data
-----
DataID AutoNumber (Primary Key)
Material Integer (Foreign Key to Material Table)
Tons Single
[Any other needed fields specific to this piece of data you want to track,
such as
Date, etc.]

The approach I'm giving for the data entry is to allow the user to either
enter the tons directly, or to use a combo box to select LB or CY, and a
textbox to enter the Qty. Its AfterUpdate event procedure then performs the
calculation and assigns it to the Tons field. You could put these two
controls off to the side if you like.

Set up a form based on the Data table, including all desired fields. Edit
the form in Design View, and be sure Wizards are enabled (View/Toolbox, then
toggle on the button with the magic wand and 5 stars). Remove the textbox
bound to the Material field, and replace it with a combo box, which the
wizard will guide you through. Select Look Up Values in a Table or Query,
select the Materials table, and select all three columns. Accept the default
of Hide Key Field (recommended), and
Store the selected value in the Material field. Now edit Column Widths
property, changing the third width to 0". This will prevent the conversion
factor from appearing in the dropdown list.

The combo box will now *store* the foreign key in the Material field of the
Data table, but *display* the description, like Pallet, Earth, etc.

Now add an unbound combo box to the form. Tell the wizard that you will
Type in the Values You Want, rather than looking up values from a tables, and
tell it to Remember the Value for Later use. I will assume its name is
cboUnit. Add a textbox for the quantity, naming it something meaningful like
txtQty.

Now edit the combo box' Value List property, entering "CY";"LB". This will
present the user with a dropdown list of these two values.

Next, click on the Event tab of the textbox' Properties and place the cursor
in the AfterUpdate field. A button with an ellipsis will appear to the
right, click on it and select Code Builder. Enter the following code between
Sub and End Sub. I've assumed you've named the Materials combo box
"cboMaterial", the Tons textbox "txtTons", the unit combo box cboUnit, and
the quantity textbox "txtQty".

Select Case Me!txtUnit
Case "CY"
Me!txtTons = Nz(Me!txtCY) * Nz(Me!cboMaterial.Column(2))/2000
Case "LB"
Me!txtTons = Nz(Me!txtLB)/2000
Case Else
End Select

Alternatively, you could add a command button that executes the same code
instead.

Hope that helps. Let me know if you need any other assistance.
Sprinks

 

[Guest]

Hi Sprinks,

Thanks so much! It works great! Your code was so easy to understand that I
was actually able to refine the process so that I could do a gallons and a
units conversion as well as cubic yards and pounds, instead of 2 conversions
I'm able to allow them to do all four on the computer.

Thanks Again!
Adam Schlachter