DB field in form won't copy to field in table

B

Beth Moser

I am creating a database for the 1st time and I have come a long ways! Now I
have encountered a glitch where the field in the subform which calculates a
value there correctly doesn't copy that value to the corresponding field in
the table. I have another field that also does a calculation, and it works
fine. I have compared the properties of the 2 and they appear to me to be
the same. Any help would be appreciated.
Also - Brief instructions in how to get it to look up values as in Excel
would be helpful - I don't understand the help page at all -
Does it have an "IF" function like Excel?
 
D

Duane Hookom

Most calculated values should not be stored in tables. A control source can
contain an expression/calculation or it can contain a field from your form's
record source. A calculation will not be stored in your table.

If you can provide a good reason for storing a calculated value, perhaps
someone will provide a solution if you provide some calculations, control
names, and field names.
 
B

Beth Moser

OH! Maybe I am going about this entirely wrong! I am making a form to enter
a farmer's chemical applications and keep track of what was applied to each
field. The subform is a list of the chemicals applied. What I had was a
formula that figured the gallons of actual chemical applied to the field. I
had to go about it in a convuluted fashion to make it work, I am sure there
is a better way. I made a table of chemicals, listing their names and the
various rates they can be applied at. the problem is some are OZ/GAL, some
are OZ/Acre, some are Lbs/ gal.
I was able to make those fields combo boxes with a query referring back to
the table for the values.
Then I had a field for gal. start, gal end and gal used was (=gal start-gal
end) and that worked fine.
What I probably have done that can be simplified is then I have fields to
enter the multiplier per gal or acre, then a combo box to choose the word gal
or acre, then a field to enter either the gal used or the # of acres, then a
combo box to choose 128 or 1 (depending if it is oz to convert to gal or if
it is lbs) - then the final field is chemical used - this formula takes
(OZ or LBS/Unit * (either #acres or #gallons))/(either 128 or 1)
IF I knew how to do IF formulas like in EXCEL I could simplify this a lot -
but I don't see where there is a choice for that in the expression builder.
I also can't figure out how to make it know that it is acres or gal, and
choose that number for that record, and not have to manually enter it in a
field -
this is probably too complicated for my first project but I had someone ask
me to do it for them - and I have been like a dog with a bone for over a week
now ---
Eventually I need to be able to also figure the cost for each chemical and
do reports for each field and crop,
 
D

Duane Hookom

Since the calculations seem fairly complex, I would suggest that you create
user-defined functions in modules. These can remove complexity from queries
and control sources and store them all in one place where they can be used
over and over.

You may be making some selections and storing results of calculations.
Normally you would do this in the after update event of one control to place
a value in another control.

There is an IIf( expression, TrueResult, FalseResult) that can be used.
 
B

Beth Moser

I really don't understand the "modules" - Can you give me an example?
I did figure out how to make a look up field for my chemical rates so all
the info is together - ez - 22 , 0z , gal each in their own field, but all
come up together in a combo box now - it really is slick!
Can these still be used in calculations separately now?
All the chemical info is in the subform and all the general field info is in
the main form, so for the chemicals that are applied by the acre, I will need
to pull that number from the "acres" field in the main form.
 
D

Duane Hookom

Creating user-defined functions isn't too difficult. A simple example would
Age() function found at http://www.mvps.org/access/datetime/date0001.htm.
You would create a new, blank module and paste this code into the module.

Function Age(Bdate, DateToday) As Integer
' Returns the Age in years between 2 dates
' Doesn't handle negative date ranges i.e. Bdate > DateToday

If Month(DateToday) < Month(Bdate) Or (Month(DateToday) = _
Month(Bdate) And Day(DateToday) < Day(Bdate)) Then
Age = Year(DateToday) - Year(Bdate) - 1
Else
Age = Year(DateToday) - Year(Bdate)
End If
End Function

Then save the module with a name like "basDateCalcs". You can now use this
function in code, queries, and control sources.
 
Top