Populate a Form Field With Calculation if ID Does Not Exist In Table

[Richard_Marx]

I am using 1 form for data entry and for record lookup. The source table of this form is the master table, so if a user inputs an id that exists in the master table it will fill in all fields on the form that exist in the master table. IF a user inputs an id that does not exist in the master table, then I need to use a calculation to populate the value for two fields.



For example, if id 5 does not exist in the master table then
Me!Total = SUM(price1+price2+price3+price4)

if id 5 does exist in the master table then
Me!Total = whatever the value in the table is

Second field if id 5 does not exist in the master table then
Me!Tax = Me!Total*.025

if id 5 does exist in the master table then
Me!Total = whatever the value in the table is

How can I set this up on my form fields?

[Bulzie]

Is ID your key field in the table? Can you give example of table structure and fields and sample data.

[davegri]

If 5 does not exist in the master table, how can you sum fields in a non-existing record?

[Richard_Marx]

If 5 does not exist in the master table, how can you sum fields in a non-existing record?

If 5 does not already exist in the master table then the data has not already been input, meaning the user would be inputting the data now (which is why I would want to perform the calculations)

Essentially a way for the form to check is this a new record being added to the table or is this an existing record that is already in the table?

[Richard_Marx]

Is ID your key field in the table? Can you give example of table structure and fields and sample data.

ID is a field in my table but the primary key of the table is masterID.

[Bulzie]

You could do the calculations after they enter values in each price field if you need to see it immediately, or in the BeforeUpdate Event on the form.

Me!Total = SUM(nz(price1,0)+(nz(price2,0)+(nz(price3,0)+(nz(p rice4,0)

[Richard_Marx]

You could do the calculations after they enter values in each price field if you need to see it immediately, or in the BeforeUpdate Event on the form.

Me!Total = SUM(nz(price1,0)+(nz(price2,0)+(nz(price3,0)+(nz(p rice4,0)

The field(s) is a calculated field in the table since initially this process was only for review not for entry. If I set the AfterUpdate() Event for each of the price fields to Sum in the Total field would that interfere/throw any errors with the field being a calculated field?

[davegri]

Me!Total = SUM(nz(price1,0)+(nz(price2,0)+(nz(price3,0)+(nz(price4,0)

You don't want or need SUM, just

Me!Total = nz(price1,0)+nz(price2,0)+nz(price3,0)+nz(price4,0)

[Richard_Marx]

Me!Total = SUM(nz(price1,0)+(nz(price2,0)+(nz(price3,0)+(nz(price4,0)

You don't want or need SUM, just

Me!Total = nz(price1,0)+nz(price2,0)+nz(price3,0)+nz(price4,0)

Will this cause any hiccups since the field Total is a calculated field in my table?

[Bulzie]

Not sure i what you mean by calculated field. Do you mean they have a Default Value calculation on them in the table? The fields will get recalculated and placed into the record if ID is null.

[Richard_Marx]

Not sure i what you mean by calculated field. Do you mean they have a Default Value calculation on them in the table? The fields will get recalculated and placed into the record if ID is null.

Ah, recalculating makes sense. So I would set the default value of Total to = nz(price1,0)+nz(price2,0)+nz(price3,0)+nz(price4,0 )

Inserting the formula gives me an error

#Size!

Could it be due to the fact that the fields I want to calculate on are empty? IE a blank form?

[Bulzie]

No on Default Value, you will not have any data when the record is first created. If you want to show those Total and Tax on the form, then put the Total and Tax calculations in the AfterUpdate of each price field. Then when you save the record Total and Tax will be populated. You could check for the ID also:

If isnull(Me.ID) Then

me.Total = nz(price1,0)+nz(price2,0)+nz(price3,0)+nz(price4,0 )

me.Tax = Me.Total *.025

End IF

[Richard_Marx]

No on Default Value, you will not have any data when the record is first created. If you want to show those Total and Tax on the form, then put the Total and Tax calculations in the AfterUpdate of each price field. Then when you save the record Total and Tax will be populated. You could check for the ID also:

If isnull(Me.ID) Then

me.Total = nz(price1,0)+nz(price2,0)+nz(price3,0)+nz(price4,0 )

me.Tax = Me.Total *.025

End IF

This is also giving me a #Size! error on the form

The field is set to currency and 2 decimal places. Should the formula be formatted the same?

[Bulzie]

So DataType on Tax field is "Number"? What is the Field Size? Should be Double I believe. Is your BE database (tables) SQL or Access?

[Richard_Marx]

So DataType on Tax field is "Number"? What is the Field Size? Should be Double I believe. Is your BE database (tables) SQL or Access?

I caught it. The issue was one of fields had a space in it, and I did not add brackets to it was split into two fields. SO instead of my formula reading as

Me.Tax = NZ(price1,0)+NZ([sale markup],0)

it was altered to

Me.Tax = NZ(price1,0)+NZ([sale] [markup],0)