Link in forms
I have two forms available from Microsoft, one is called Bill and the other is called Payment. They both have a total as field in both forms. I am trying to link the total invoice amount to the total payment amount.
I have tried in the payment total amount control source: = Forms! Bill! [Total Amount]
but that doesn't seem to work. In Design view, it says "#Name?" in the text box.
How do you do it?
Is any of the forms a form? If so, you need to reference the subform control or parent property:
Me.Parent.[Total order]
Me.[Subform Control name Here].form.[Total order]
Note that the name of the subform element is not always the same as the form.
EDIT: Either omit Me or use Form! FormName on the control.
EDIT2: Please note that the usual way to reference forms, sub-elements, and controls is either a hit (!) Or a period (.). parentheses and quotes are rarely used. This can be seen on both the Microsoft Access MVPs site ( http://www.mvps.org/access/forms/frm0031.htm ) and Microsoft's own site ( http://support.microsoft.com/kb/209099 ) as mentioned by Knox.
If not, have you tried the expression builder? This will help make sure you have the correct names.
As an aside, it's best to avoid spaces in field and control names as it will save you a lot of work. It's also better to rename the controls so that they don't have the same name as the fields they contain, txtOrderTotal for example.
source to share
My favorite solution here is always to go back to the recordsets and calculate the corresponding totals as you are not always sure if the totals at the form level are updated correctly (there can always be a pending control / recordset update for whatever reason). You have the option to use DSUM and related functions!
Exemple:
dsum(Forms("Bill").recordsource, "unitPrice*lineQuantity")
Of course, you may have more complex solutions, such as defining a temporary set of records to get the total amount due to the invoice.
Dim rs as DAO.recordset, _
myBillNumber as variant, _
myBillAmount as variant
set rs = currentDb.openRecordset(_
"SELECT billNumber, sum(unitPrice*lineQuantity) as totalPrice FROM " & _
Forms("Bill").recordset.name " GROUP BY billNumber")
myBillNumber = rs.fields(0)
myBillAmount = rs.fields(1)
It may sound daunting, but once you get used to it, you will never hesitate to use it and you will never have the problem that you are currently facing.
source to share