Need one unbound txtbox to preform two functions

[MSUKevin]

If anyone has read my previous thread they know the pain I am in... this problem could be the solution to all of my problems so any help on this is FULLY APPRECIATED:

My question is this:

Is it possible to write code for an unbound txtbox that could evaluate a previous field and preform 1 of 2 calculations based on what is entered in the previous field????

For Example:

I have a form that has 4 fields:
ProductID|Unit Price|Quantity|Line Total|

When a product is selected from the combo box the unit price is automatically updated in the unit price field. Then the user enters the quantity of the product and the line total field prefoms this simple calculation:

=[Unit Price]*[Quantity]

My problem is that I have a product "Gasoline", that the future users wish to enter in the sale amount, and NOT the quantity. As a result, this "Gasoline" field has to be seperate from this table, and results in mucho problems with Null values between the two calculations.

Is it possible to write a code for this "Line Total" box that says:

If Gas = True then, override the equation and direct enter the total sale manually in the field.
If Gas = False then, run the equation [unit price]*[Quantity]

Is this possible???, iff so does anyone know the code for this??? I would apreciate all replys to this. I am NOT good at programming so please make reply simple!!!

Thanks a Million,
Kevin

 

[KevinM]

Me again!

Just had a thought (another one !)

I really think that your 'Gasoline' should be in your Products table and treated as such. This WILL solve all your problems.

For example...

'Products' like Delivery Cost, Labour, Services etc should always be kept in the Products table as that's what they really are, even though you don't actually 'see' them. I'm sure there is a more technical name for these 'products'.

This makes totalling your Product transactions much more accurate than having them stored in the 'One' side table.
You see the problems that you are having when you try to sum your products and a seperate one (gasoline).
If it is treated as just another line item product then it either gets included as such or doesn't. You original Total field in your subform will simply include it in the final total or not.

HTH

 

[Pat Hartman]

Rather than using "=[Unit Price]*[Quantity]" as the controlsource of the bound control, you can use a different method to store the value there. In the AfterUpdate event of the Quantity control put:

Me.[txtLineTotal] = [Unit Price]*[Quantity]

This will now enable the [Line Total] control to be manually updated and still have the value calculated when someone puts something in the [Quantity] field. You might want to do a little behind the scenes validating in the Form's BeforeUpdate event to make sure that when ProductID = "Gasoline" that the Line Total field is <> 0 and that the quantity field is = 0.

 

[MSUKevin]

I TOTALLY AGREE..... But,

The problem with putting Gasoline in with the products (even though this is where is SHOULD be) is that in the subform with the products the line total calculation is:

[Unit Price]*[Quantity]

This works fine for the other products becuase the the user selects the product the customer wants to purchase, the unit price is automatically updated in the unit price field, and then the user types in how much of the product the customer wishes to purchase.

The problem with putting Gasoline in this grouping is that the people for whom this database is being developed want to put in the total sale for the gasoline, i.e. $25, NOT 12.45 gallons. IF they would enter the gallons sold (i.e. 12.45) then the subform calculation would work just fine.

This would also clear up all the rest of the problems that are coming about becase of the Null values and the interpretation between these unbound txtboxes....UGHHH!!!!!!!

I have to goto a lunch meeting but I am going to e-mail a copy of this DB to you so you can take a look at what I'm talking about...

Thanks again

 

[charityg]

Is is possible to use the quantity field for the gallons of gas? Gallons is the quantity of the product purchased.
Okay...forget the above, I thought you wanted to enter the gallons.

Pat gave you a valid answer.

In the after update event of quantity, reset the value of the total sale.

For example:
You select widget from you dropdown. In the after update event of the combobox

if cboProduct <> gas then
qty=1
endif
totalsale=unitprice

so after you select a product that isn't gasoling, the quantity defaults to 1 and the totalsale SEEMS to look like a calculation.

Then afterupdate of the qty field, reset the totalsale value with
if cboProduct<>Gas then
me!totalsale=qty*unitprice
endif

if your totalsale field is bound to the table, this will overwrite the previous value when the record is updated.

I've tried to be as clear as I could in explain

[This message has been edited by charityg (edited 05-31-2001).]

 

[MSUKevin]

Absolutely possible, and it works fine.

I have run it where I enter in Gas in the poducts table and set its unit price to $1.87.

Then when in the form you can select Gas from the product field, the db automatically updates the unit price field with $1.87 and all you have to do is enter the amount of gallons in the quantity field.

PROBLEM IS... the future users don't want it that way, they want to enter the amount of gasoline sale (i.e $25.00) NOT, the gallons....

This is where my problem starts....

 

[KevinM]

I see what you're getting at, but whenever I fill my car up with petrol (gasoline) the bill is calculated by the number of litres x Cost per litre as it is in most forecourts.
Nobody simply adds it up in their head and puts in the total cost.
I would imagine this is how it is done everywhere.

Why do your users want to adopt such an unorthodox approach?