Searchable Total Field (1 Viewer)

oehlers64

New member
Local time
Today, 06:28
Joined
Feb 28, 2011
Messages
1
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
 

smig

Registered User.
Local time
Today, 12:28
Joined
Nov 25, 2009
Messages
2,209
you can't search by these fields on the top form (The Order) as it's calculated field.
 

vbaInet

AWF VIP
Local time
Today, 10:28
Joined
Jan 22, 2010
Messages
26,374
you can't search by these fields on the top form (The Order) as it's calculated field.
Susan gave two scenarios:
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 think she's aware of this.

However, your point is, you can't search on a calculated field IF (and only IF) the calculation is performed in the Control Source of a control. If the calculation resides in the record source (trial number 2), you can search but the recordset becomes unupdateable. A simple mathematical calculation won't make the recordset unupdateable but a totals query using Count() or Sum() (for example) will.
 

smig

Registered User.
Local time
Today, 12:28
Joined
Nov 25, 2009
Messages
2,209
Susan gave two scenarios:
I think she's aware of this.

However, your point is, you can't search on a calculated field IF (and only IF) the calculation is performed in the Control Source of a control. If the calculation resides in the record source (trial number 2), you can search but the recordset becomes unupdateable. A simple mathematical calculation won't make the recordset unupdateable but a totals query using Count() or Sum() (for example) will.
I know all of this.
you are right. I talked about option 1 only.

in vbaInet example it will be:
DSum("UnitPrice", "Order Details", "OrderID = " & [Orders].[OrderID] & "")
to get the Sum for each OrderID
 
Last edited:

Users who are viewing this thread

Top Bottom