Dlookup Error #Name?

Question

Could anyone help me with this error?

I'm building a form where I have a combo box bringing the names of Broker Shop, based on this combo box (that's pulling the information from a specific table) I want to be able to bring automatically the phone number and the email. So I created a text box where I am using the Dlookup function.

800 936 4900.

2:48 pm

=DLookUp("[Broker]![Phone]","[Broker]","[Broker]![BrokerShop]='" & [Forms]![Data BookDataEntry]![BrokerShopComboBox].[Text] & "'")

I have tried so many suggestions from different website, but can get to pass through this error.

I'm not an expert on Access and I don't know much of coding, I'm learning as I go, which makes harder.

Thanks

Answer 1

Try:

=DLookUp("[Phone]","[Broker]","[BrokerShop]='" & [Forms]![Data BookDataEntry]![BrokerShopComboBox] & "'")

If the combo box Row Source uses the table [Broker] you are better off adding the Phone (and other columns) and then using a control source like:

=BrokerShopComboBox.Column(x)

x is the column number from the Row Source. Numbering begins with 0.

Answer 2

I would expect the broker ID field to be stored in all related tables. 

When you state "giving me the error" I don't know exactly what you have setup and what you are seeing.

Did you try the .Column(x) solution I suggested?

Answer 3

I'm using the broker table to select a broker but still giving me the error in the end.

Control Source: Broker Shop

Row Source: SELECT [Broker].[ID], [Broker].[BrokerShop] FROM Broker ORDER BY [BrokerShop]; 

Row Source Type: Table/Query

On my Broker table I have the following fields: Id | BrokerShop | Phone | Email

I don't use the ID for anything, it was just the default from when I imported the table. 

Do you think the ID field is causing this error?

Answer 4

If you are simply creating a drop down to select a broker, I'm not sure why you wouldn't use the broker table or at least include the table and desired fields.

If you have additional questions, please reply back with your Row Source for the combo box as well as significant fields from the Broker table.

Answer 5

Hi Dhookom,

Thanks for the reply.

The combo box comes from the main table (Data Book) which I'm feeding with this form, there the Broker Shop has a lookup that brings the list from Broker table.

Should I delete this one that I have now and create a combo box from scratch allowing to have all the fields (Broker shop, phone, email) and use the option you gave me above?