Referencing in forms

Question

I have two forms in microsoft access, one called Bill and the other one called Payment. They both have Total amount as a field in both of the forms. I am trying to reference the Bill total amount to the Payment total amount.

I have tried in the Payment total amount control source : =Forms!Bill![Total Amount]

but this doesnt seem to work. In Design view it says '#Name?' in the text box.

How would you do this?

Answer 1

Is either of the forms a subform? If so, you need to reference the subform control or the parent property:

Me.Parent.[Total order]
Me.[Subform Control name Here].form.[Total order]

Note that the Subform Control name is not always the same as the form contained.

EDIT: Either omit Me or use Form!FormName in a control.

EDIT2: Please note that the usual way of referencing forms, subform and controls is with either bang (!) or dot (.). parentheses and quotes are rarely used. This can be seen in 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? It will help ensure that you have the correct names.

As an aside, it is best to avoid spaces in the names of fields and controls, it will save you a good deal of work. It is also best to rename controls so they do not have the same name as the fields they contain, txtOrderTotal, for example.

Answer 2

Remou's answer only works in code - however it looks like you are defining the control source of a text box so try this:

=Forms("Bill")![Total order]

Answer 3

I have tried DJ's way and it did not work. It now says #Error in the text box. Does the tables have to be related or something? Am I doing something wrong?

Actually it works now. THank you DJ.

Answer 4

My favorite solution is here always to go back to recordsets and calculate corresponding totals, as you are not allways sure that totals at form level are correctly updated (there might always be a pending control/recordset update for any reason). You have then the possibility to use the DSUM and related functions!

Exemple:

dsum(Forms("Bill").recordsource, "unitPrice*lineQuantity")

Of course you could have more complex solutions such as defining a temporary recordset to get your total amount per 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 might seem complicated but once you get used to it you'll never hesitate to use it and you'll never ever have the kind of problem you are facing now.