control source with formula

[CJ-Hewitt]

I have 3 different types of calculations I am running, depending upon what is
given to me in the field. (IE one may give me total gallons while another
gives me meter readings)

On my form, I have the following
[tfuel1] (adds morning gallons to afternoon gallons)
[tfuel2] (subtracts meter readings to obtain gallons)
[tfuel3] (subtracts meter readings and adds the total to tfuel2's results)

I want to transfer the results to one of the fields above to [fuel1]
(control source that updates to the table)

I've pretty much figured out that I cannot add the formula into the control
source, so I tried an afterupdate code of
If IsNumeric(Me.tfuel3) Then
Me.fuel1 = Me.tfuel3
ElseIf IsNumeric(Me.tfuel2) Then
Me.fuel1 = Me.tfuel2
Else
Me.fuel1 = Me.tfuel1
End If
But I am pretty sure modifying that field after an update defeats the
purpose of what I am trying to do...

I tried this code in the code of the button that does the update, and the
numbers still do not transfer to the database table.

What am I doing wrong?

 

[BruceM]

What are [tfuel1], [tfuel], and [tfuel3]? Since it seems they are derived
from calculations, in most case you should not be storing them in your
database.
What exactly do you mean to test with IsNumeric? Also, tfuel3 as you have
described it seems to be the same as tfuel2 * 2. In that case if there is a
value in tfuel2 there will always be a value in tfuel3, so fuel1 will never
be the same as tfuel2. The only time fuel1 will equal tfuel1 is when there
are no meter readings.
You mention the After Update code. After Update of what?
Without knowing more about the database structure and the real-world
situation behind the database it is difficult to offer a specific
suggestion. Also, it will help to know if each record represents a
delivery, or a truck's daily record, or whatever.

 

[CJ-Hewitt]

What are [tfuel1], [tfuel], and [tfuel3]? Since it seems they are derived
from calculations, in most case you should not be storing them in your
database.

There is no hidden meaning here. They are exactly that, derived from
calculations and yes I most certainly DO want them in the database.

tfuel are the results of either meter readings or gallons reported.

What exactly do you mean to test with IsNumeric?

if the field is numerical, then fuel1=that field. If it is not, test the
next field. One of them will always have a result in it.

You mention the After Update code. After Update of what?

This is where I would be having the problem. Regardless of my real world
application, I am simply trying to add a calculated field based into the
database. The issue is where I do that during the submit process.

Without knowing more about the database structure and the real-world
situation behind the database it is difficult to offer a specific
suggestion. Also, it will help to know if each record represents a
delivery, or a truck's daily record, or whatever.

This record entry represents gallons of fuel for a piece of equipment. The
form itself functions fine, however at present I have to enter for example...

meter reading entry 1= 542
meter reading entry 2=598

tfuel2 calculates the difference and displays...

Then, insteand of fuel1 updating this, I have to manually enter the result.
I'm trying to remove this step.

 

[CJ-Hewitt]

What are [tfuel1], [tfuel], and [tfuel3]? Since it seems they are derived
from calculations, in most case you should not be storing them in your
database.

There is no hidden meaning here. They are exactly that, derived from
calculations and yes I most certainly DO want them in the database.

tfuel are the results of either meter readings or gallons reported.

What exactly do you mean to test with IsNumeric?

if the field is numerical, then fuel1=that field. If it is not, test the
next field. One of them will always have a result in it.

You mention the After Update code. After Update of what?

This is where I would be having the problem. Regardless of my real world
application, I am simply trying to add a calculated field based into the
database. The issue is where I do that during the submit process.

Without knowing more about the database structure and the real-world
situation behind the database it is difficult to offer a specific
suggestion. Also, it will help to know if each record represents a
delivery, or a truck's daily record, or whatever.

This record entry represents gallons of fuel for a piece of equipment. The
form itself functions fine, however at present I have to enter for example...

meter reading entry 1= 542
meter reading entry 2=598

tfuel2 calculates the difference and displays...

Then, insteand of fuel1 updating this, I have to manually enter the result.
I'm trying to remove this step.

And to clarify the last part, I have to enter the results of the fuel
calculation in [fuel1] manually, then hit the submit button to enter the new
record into the database.

Hope that helps.

 

[BruceM]

Comments inline.

What are [tfuel1], [tfuel], and [tfuel3]? Since it seems they are
derived
from calculations, in most case you should not be storing them in your
database.

There is no hidden meaning here. They are exactly that, derived from
calculations and yes I most certainly DO want them in the database.

tfuel are the results of either meter readings or gallons reported.

I was not suggesting a hidden meaning. You can see your database. I
cannot, nor am I familiar with the fuel delivery business.

If a value is the result of a calculation involving fields in the database,
you almost always do better to calculate the values on the fly rather than
storing the result. Of course you want the values; otherwise you would not
have asked the question. However, obtaining the values and storing the
values are two very different things.

What I was getting at is why you do not test for Null or 0. Since you are
specifically testing for a numeric value rather than the presence of any
value, I thought there may be some specific reason.

The recordset is updated when you navigate away from a new or changed
record, when you close the database, and for a variety of other reasons
including an explicit save. The After Update event is often a useful place
for code, but it does not represent the difference between saving the record
or not. Code to add a value to a field or to perform a calculation can
occur in any number of places.From what I understand, you may have either morning gallons plus afternoon
gallons, or meter reading2 minus meter reading1. If there are no meter
readings, use the gallons. I still don't know anything about how your
database is put together, so I can only make a very general suggestion. You
could make a command button, with something like the following as its Click
event:

If Nz(Me.Entry1,0) <> 0 And Nz(Me.Entry2,0) <> 0 Then
Me.txtFuel1 = Me.Entry2 - Me.Entry1
ElseIf Nz(Me.MorningGallons,0) <> 0 And Nz(Me.AfternoonGallons,0) <> 0
Then
Me.txtFuel1 = Me.MorningGallons + Me.AfternoonGallons
Else
MsgBox "Insufficient information to calculate gallons"
End If

txtFuel1 is an unbound text box.

You could also perform the calculation in a query, although the syntax would
be somewhat different. In any case, you would not store the value.

tfuel2 calculates the difference and displays...

Then, insteand of fuel1 updating this, I have to manually enter the
result.
I'm trying to remove this step.

And to clarify the last part, I have to enter the results of the fuel
calculation in [fuel1] manually, then hit the submit button to enter the
new
record into the database.

I don't know why there is a submit button, nor do I know what code is behind
it. In general you do not need to save a record explicitly, although there
are often sound reasons for doing so.

 

[CJ-Hewitt]

OK I think I see where the confusion of what I'm doing is coming into play.

Clarification #1
the table it's writing to is rather simple, it holds the following records.

equipment# Date Fuelamount MachineHours (heavy equipment is
tracked by hours not mileage)

The form writing to the table is not a direct entry form, meaning before you
enter the actual data you must hit an add record button to add the new record.

The actual form is in 2 parts, and designed this way due to the way our
field people report the fuel they are placing into the heavy equipment.

1 person enters it with gallons.
1 person uses meter readings.
1 person uses morning and afternoon meter readings.

I admit I probably could just enter new fields for meter readings and let
the report deal with the calculations, however the report I am running
currently is complicated enough, and I want to keep the fields coming into it
as simple as possible.

SO!
Back to the form.
Top section of the form contains no information for Database entry, it is
basically a spreadsheet to calculate the gallons for the current record.

The math for any of those fields (referring back to the original if/then
statement) ends up being one thing... gallons.

Now I took this a step further, and lets just eliminate that whole if/then
statement, and say I took all those calculated fields and wrote the result of
those fields to one field...

[tgal]
[tgal] now equals the gallons I need to place in the [FUELAMOUNT] control
that, when I press the submit button, updates the database.

How do I make [FUELAMOUNT] = [tgal] during an update?

 

[BruceM]

Comments inline.

OK I think I see where the confusion of what I'm doing is coming into
play.

Clarification #1
the table it's writing to is rather simple, it holds the following
records.

equipment# Date Fuelamount MachineHours (heavy equipment is
tracked by hours not mileage)

Do I understand that you are storing the calculation result rather than the
various readings (meter, gallons used)? You have to hope there are no data
entry errors, but I suppose you could trap for errors that are outside of an
expected range.

The form writing to the table is not a direct entry form, meaning before
you
enter the actual data you must hit an add record button to add the new
record.

Do you mean it is an unbound form, and that the submit button opens a
recordset and updates it, or something like that?

The actual form is in 2 parts, and designed this way due to the way our
field people report the fuel they are placing into the heavy equipment.

1 person enters it with gallons.
1 person uses meter readings.
1 person uses morning and afternoon meter readings.

I admit I probably could just enter new fields for meter readings and let
the report deal with the calculations, however the report I am running
currently is complicated enough, and I want to keep the fields coming into
it
as simple as possible.

You can use the basic code I suggested, except that something such as:
Nz(Me.Entry1,0) etc.
would be a reference to an unbound text box, and that at the end Fuel1 is
the only bound field. It may make it easier to follow if you use the txt
prefix to signify a text box:
Nz(Me.txtEntry1,0) etc.
However, don't worry about the complexity of the report. The computer
processes millions of instructions per second, and can handle this sort of
calculation instantly from your vantage point. Again, you may do well to
store the readings. If there is an anomaly some day it may be difficult to
see where it came from if you only have the calculation result. You're
going to type the readings anyhow, so why not save them?

SO!
Back to the form.
Top section of the form contains no information for Database entry, it is
basically a spreadsheet to calculate the gallons for the current record.

The math for any of those fields (referring back to the original if/then
statement) ends up being one thing... gallons.

Now I took this a step further, and lets just eliminate that whole if/then
statement, and say I took all those calculated fields and wrote the result
of
those fields to one field...

You've lost me here. Do you mean you could have meter readings and gallons
for the same piece of equipment in the same record?

[tgal]
[tgal] now equals the gallons I need to place in the [FUELAMOUNT] control
that, when I press the submit button, updates the database.

However the value gets into the record, make that happen when you press the
Submit button. If I understand correctly that you are not using a bound
form, you can set the field's value with a calculation in the Update code.
If you are using a bound form, just use the code to write the value to the
FuelAmount field.

How do I make [FUELAMOUNT] = [tgal] during an update?

 

[CJ-Hewitt]

Comments inline.



Do I understand that you are storing the calculation result rather than the
various readings (meter, gallons used)? You have to hope there are no data
entry errors, but I suppose you could trap for errors that are outside of an
expected range.

This is the goal, and the data entry is by me. I use the top part of this
form (all these are unbound fields designed specifically to calculate
gallons) for calculation only. I then take the gallon number it produces
(because I cannot figure out how to transfer that automatically) and enter it
in the bound field [FUELAMOUNT].

Do you mean it is an unbound form, and that the submit button opens a
recordset and updates it, or something like that?

it is a bound form, and I apologize for not knowing what to call the type.

When I start the form up, it would show 1 of 775, and I have to hit the "new
record" button to get to a blank part of the form.

BTW if you have a method of opening this up to a blank section, that would
be grand!

You can use the basic code I suggested, except that something such as:
Nz(Me.Entry1,0) etc.
would be a reference to an unbound text box, and that at the end Fuel1 is
the only bound field. It may make it easier to follow if you use the txt
prefix to signify a text box:
Nz(Me.txtEntry1,0) etc.
However, don't worry about the complexity of the report. The computer
processes millions of instructions per second, and can handle this sort of
calculation instantly from your vantage point. Again, you may do well to
store the readings. If there is an anomaly some day it may be difficult to
see where it came from if you only have the calculation result. You're
going to type the readings anyhow, so why not save them?

Oh its not the computer I am saving, it would be my own sanity.

You've lost me here. Do you mean you could have meter readings and gallons
for the same piece of equipment in the same record?

Meter readings would be gallons, just means I have to do the math rather
than data entry.
and no, it would be either one or the other for a piece of equipment per
record.


Just another focus on the [tgal] hidden field I've created.
This houses the number I've transferred, and this field is what I want
[FUELAMOUNT] to pick up.

I've got all the legwork pointing to [tgal], just need to get that last hop
over to the bound field [FUELAMOUNT]

How do I make [FUELAMOUNT] = [tgal] during an update?

 

[BruceM]

To open to a new record, try in the Open event:
Me.Recordset.AddNew

From what I understand, there is a hidden text box named tgal. You called
it a hidden field, but tables have fields; forms and reports have controls
that may or may not be bound to fields. Somehow a number is ending up in
that text box. I've lost track of how the number is ending up in tgal, but
rather than putting the number there, could you just put it into FuelAmount?

The thing about VBA is that code runs because it is called. If you update a
text box, you can have code in the text box After Update event to perform a
calculation or whatever. If you do not update the text box (for instance,
when you scroll through the records) the code will not run. If you need to
view the calculation result as you navigate through the records you will
need to put code into the form's Current event, which runs when you go to
another record, including a new record. However, sometimes code will
produce an error at a new record because there are no values with which the
code can work, so it tries to divide by 0 or whatever. So you may need to
run the code in several places. To do that, you can create a function to
perform the math, and call the function rather than copying the code. If
the code is not to run at a new record, the form's Current event could be:

If Not Me.NewRecord Then
YourFunction
End If

In short, one trick with code is figuring out when to run it. In your case
you want to run code to perform a calculation, then you want to store the
calculation result rather than the input that determines the result. Not
the choice I would have made. As I said, you are entering the various meter
readings, gallon readings, etc. anyhow, so why not store them. But if you
decide to go that route, you have to find a time to run the code. Since you
are controlling the data entry you could have something like this in the
MeterReading2 text box After Update event:
Me.FuelAmount = Me.txtMeterReading2 - Me.txtMeterReading1
In the After Update event for AfternoonGallons:
Me.FuelAmount = Me.txtMorningGallons + Me.txtAfternoonGallons

As long as the data entry proceeds in a particular order (MeterReading2
*after* MeterReading1), and does not occur in two places (Readings and
Gallons, for instance), you should be OK. Again, not the choice I would
have made, partly because I like to think others will one day to the data
entry. There are ways to assure the data is entered in only certain text
boxes. For instance, a Calculation Method option group could cause one pair
of text boxes to show if Gallon Method is selected, or another pair if Meter
Method is selected.

The form's After Udate event will not work for this sort of thing. After
the record is saved you would be going back and changing the record again,
which means when you navigate to another record the After Update event will
run again, and you would be in a loop.

With unbound text boxes you probably want to use the form's Current event to
clear them so they are blank when you go to a new record.

Comments inline.



Do I understand that you are storing the calculation result rather than
the
various readings (meter, gallons used)? You have to hope there are no
data
entry errors, but I suppose you could trap for errors that are outside of
an
expected range.

This is the goal, and the data entry is by me. I use the top part of this
form (all these are unbound fields designed specifically to calculate
gallons) for calculation only. I then take the gallon number it produces
(because I cannot figure out how to transfer that automatically) and enter
it
in the bound field [FUELAMOUNT].

Do you mean it is an unbound form, and that the submit button opens a
recordset and updates it, or something like that?

it is a bound form, and I apologize for not knowing what to call the type.

When I start the form up, it would show 1 of 775, and I have to hit the
"new
record" button to get to a blank part of the form.

BTW if you have a method of opening this up to a blank section, that would
be grand!

You can use the basic code I suggested, except that something such as:
Nz(Me.Entry1,0) etc.
would be a reference to an unbound text box, and that at the end Fuel1 is
the only bound field. It may make it easier to follow if you use the txt
prefix to signify a text box:
Nz(Me.txtEntry1,0) etc.
However, don't worry about the complexity of the report. The computer
processes millions of instructions per second, and can handle this sort
of
calculation instantly from your vantage point. Again, you may do well to
store the readings. If there is an anomaly some day it may be difficult
to
see where it came from if you only have the calculation result. You're
going to type the readings anyhow, so why not save them?

Oh its not the computer I am saving, it would be my own sanity.

You've lost me here. Do you mean you could have meter readings and
gallons
for the same piece of equipment in the same record?

Meter readings would be gallons, just means I have to do the math rather
than data entry.
and no, it would be either one or the other for a piece of equipment per
record.


Just another focus on the [tgal] hidden field I've created.
This houses the number I've transferred, and this field is what I want
[FUELAMOUNT] to pick up.

I've got all the legwork pointing to [tgal], just need to get that last
hop
over to the bound field [FUELAMOUNT]

How do I make [FUELAMOUNT] = [tgal] during an update?