Hi, I am sure this has been addressed and is probably very simple but I have wasted a great deal of time and have been unable to find the answer.
I have a typical database which has an Orders Table and a Detail Table that lists the individual line items being purchased. I have created a Subtotal field in the Form footer of the Sub Form based on the Detail table. I want to have the a calculated Total field on the Orders Form that will add Tax and Shipping to the SubTotal from the Detail Form. That much I've been able to do BUT:
1. If I make the source of the Total field a calculation of the 3 fields in question then I can't sort or search on the Total field.
2. If I make the entire form based on a query which would has the calculated field in question, then it lets me sort and search on the Total field but tells me that "This recordset is not updateable."
I need to be able to search and sort on the total field and then be able to make changes to the other fields on the Order. I know that everyone says you should NEVER have a total field as part of your database, but this seems to be the only answer I can come up with.
Please help!
Thanks,
Susan
I have a typical database which has an Orders Table and a Detail Table that lists the individual line items being purchased. I have created a Subtotal field in the Form footer of the Sub Form based on the Detail table. I want to have the a calculated Total field on the Orders Form that will add Tax and Shipping to the SubTotal from the Detail Form. That much I've been able to do BUT:
1. If I make the source of the Total field a calculation of the 3 fields in question then I can't sort or search on the Total field.
2. If I make the entire form based on a query which would has the calculated field in question, then it lets me sort and search on the Total field but tells me that "This recordset is not updateable."
I need to be able to search and sort on the total field and then be able to make changes to the other fields on the Order. I know that everyone says you should NEVER have a total field as part of your database, but this seems to be the only answer I can come up with.
Please help!
Thanks,
Susan