Totaling different records in a form

[hockey8837]

Hi,
I've got an accounting DB in which I have a form with 3 possible different currency values the user can enter; [Materials Cost], [Rate Cost], and [Contractor Cost].

I have a query which pulls any value from these (there will only ever be one of them filled out per record) and puts it into a [Billed Amount]. Here's the code in my query:

Billed Amount: IIf(IsNull([Rate Cost]),IIf(IsNull([DCH 12 Percent]),[Materials Cost],[DCH 12 Percent]),IIf(IsNull([Materials Cost]),[Rate Cost],[Materials Cost]))

The query works fine-I can look at it and see it totaling fine. The form WAS working fine, too, entering the values properly. But, I created a select query on a combo box for the [Rate Cost] to automatically fill in the salary when the Job Title is selected, and now, it's no longer entering the data in new records.

what did I do wrong?

Thanks!

 

[Galaxiom]

I created a select query on a combo box for the [Rate Cost] to automatically fill in the salary when the Job Title is selected, and now, it's no longer entering the data in new records.

Think you need to explain more.
How do the bold bits fit into this?

 

[hockey8837]

Think you need to explain more.
How do the bold bits fit into this?

Hi,

The select query in the combo box works like this: if a user is entering staff hours to figure out how much to bill for time, they select what position the hours are for (director, manager, technician, etc.), then fill in the hours in the [NumberOfHours] box. I set up the [Pay Rate] combo box to autofill with the salary of that associated position is selected (these are stored in the Rate table), so then, the [RateCost] box has a formula which multiplies hours by rate to find out the cost. This is working correctly.

My problem is the [BilledAmount] box on my form is not filling correctly, though the query the form is based off of runs properly and the IIf(Null) statement (previously quoted) appears to work to grab the data from either the [RateCost], [MaterialsCost], or [DCH12Percent].

So, why can I see the proper data in the query, but can't get the form to fill it properly when the box is set to that value? It seems only to be affected by new entries, the old entries still have their BilledAmount data stored. The only thing I've changed on the form since it was working was the dependent combo boxes...but not sure if this is the reason it stopped working.

Thanks!

 

[Galaxiom]

Not sure I fully understand but this sounds like the Control Source for the Billed Amount box on the form is set to a formula. Consequently it is no longer bound to the field in the query and so will not be stored in the table.

The BilledAmount calculation needs to be done in VBA in the AfterUpdate events of the box holding the Position and Hours controls. The BilledAmount box need to be bound to the field.