Maths

[lead 27]

Hi

I am trying to do a calculation using 2 fields in 2 different tables

I want the result to be displayed in a field so I have put this expression in the control source of that field:

=[test2]+Forms!table1!test

But all I get is #Name?

Any ideas what is wrong

 

[CraigDolphin]

Well you could try something like :
=Me.test2.Value + Forms!NameofOpenFormBoundtoTable1!Test.Value

Change the NameofOpenFormBoundtoTable1 part towhatever that name of that form is. Make sure both form are open. Now, this will only work if both forms are open, and as long as the other form is not a subform of the form which this control is found on. It also assumes that the control Test2 is located on the same form as this textbox.

If the other form is a subform your source would look something like:

=Me.test2.Value + Me.SubformContainerName.Form.Test.Value

where you would have to replace SubformContainerName with the relevant name of the subform control.

 

[lead 27]

Yeah I have ot it to work but like you said both forms have to be open which isnt good for my design.

Is there a way to get around this

 

[Moniker]

Instead of pulling the second value from a form, pull it from the underlying table that feeds the form.

 

[lead 27]

Just comes up with #Name? again

 

[Moniker]

Then you are not referencing the fieldname correctly. If your fieldname has spaces (it shouldn't, but can), it needs to be in brackets like [field name]. The same thing goes for the table name.

The #NAME? error arises when Access cannot resolve the field you are talking about. You cannot just reference a table like it's a variable. You either have to use a DLOOKUP or a recordset object to read the contents of the table. For example, this won't work:

NewValue = Value1 + TableName.FieldName

You have to use DLOOKUP like this:

NewValue = Value1 + DLOOKUP("FieldName","TableName","Criteria=YourCriteriaHere")

Or, for more flexibility, you have to connect to the table as an object:

Dim rsTable As ADODB.Recordset

Set rsTable = New ADODB.Recordset

rsTable.Open"SELECT * FROM YourTable WHERE YourCriteriaHere",CurrentProject.Connection,adOpenKeyset,adLockReadOnly

--- Navigate to the record you want ---

NewValue = Value1 + rsTable.Fields("YourFieldName")