CODEWITHSUNDEEP
ms-accessreference
Jane
Jane

Reputation:

Referencing in forms

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?

Upvotes: 1

Views: 145

Answers (3)

Fionnuala
Fionnuala

Reputation: 91376

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.

Upvotes: 2

Philippe Grondier
Philippe Grondier

Reputation: 11148

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.

Upvotes: 0

DJ.
DJ.

Reputation: 16267

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]

Upvotes: 0

Related Questions