Microsoft Access Dlookup Error: #Name?

Question

Hi Everyone,

Could someone help me with this error?

I'm trying to create a A dlookup in two boxes on my form where it would pull the phone number and the email from one of my tables.

I have a table called Broker where I have the broker Id, Name, Phone, Email 

My main table (Data) is the one I'm feeding with informations using the form, both forms has the same fields and also have a relationship linking Broker's Name, Phone and email.

The Dlookup I'm using is the following:

=DLookUp("[Broker]![Phone]", "[tblBroker]", "[Broker]![BrokerShop]=" & [Forms]![DataDataEntry]![BrokerShop])

=DLookUp("[Broker]![Email]", "[tblBroker]", "[Broker]![BrokerShop]=" & [Forms]![DataDataEntry]![BrokerShop])

The fuction is writen on the Control Source of each text box with a Plain Text Format.

I'm not sure of what I'm doing wrong...

Answer 1

Here is some screenshots of my form

Answer 2

Do you think is the relationship setup between the two tables that might be causing this error? Because I'm making sure that both tables has the same format on the matching fields.

I'm not an expert in Access and coding so it makes everything more complicated for me hahah.

Answer 3

Start by double checking the banged of the table and fields.

Answer 4

Thank for replying Daniel,

Yes, the BrokerShop Field is defined as short text in both tables. However, there no change when I use the option you just gave me.

Answer 5

The format is

DLookUp("FieldName", "TableName", "Condition")

So try something more like

=DLookUp("[Phone]", "[Broker]", "[BrokerShop]='" & [Forms]![DataDataEntry]![BrokerShop] & "'")

I'm assuming here BrokerShop is text and not numeric.  If it's numeric, then you don't need the single word the condition.