Combo box issue - can it do a search on that field?

[eurojourney]

Hello,

I am new to Access but so far my forms and tables seem to be working. However, I need help with one item right now.

I am in real estate and the forms I created are to register Brokers/Agents and Clients. These two forms feed two main tables. Besides this two main table I have several other small tables that feed choices or options to combo boxes primarily in the Client's form, such as "Mr. Mrs, Ms" for the title to the Clients name, for example.

Now this is my issue: I have a combo box in my Client table that shows all the brokers I have registered so far in the other form, i.e. Broker table. The list will be huge eventually having several hundreds if not thousands of brokers and agents. I did a lookup column in my Client table and named the field Broker Name - this field in my Client table shows the Name, Last Name, Company and Phone Number for every broker registered in the Broker Table so I have more information when selecting the broker in the Client form from a combo box. Then I created the combo box in the Client Form with that field (Broker Name) and with the following information in the Row Source

SELECT [Broker].[Broker ID], [Broker].[First Name], [Broker].[Last Name], [Broker].[Company], [Broker].[Mobile Phone] FROM Broker ORDER BY [Last Name];

the Control Source is Broker Name.

The good thing about doing it this way from what I can see is that it allows me to select one or more brokers should there be more than one broker involved so that's perfect. However, once I have several hundres of brokers and agents in the Broker Table is going to be a pain to search through all of them to select the right one. So, in other combo boxes I have noticed that you can type the first letters of the items you want to check and it pulls those items that begin with the same letters, sort of like an index - but in my combo box for the broke's name in my Client Form it does not work, it does not allow me to type anything. Is there a way that I can achieve this so I don't have to scroll down through hundreds of records until I find the right broker?

Please let me know and remember, I am new to Access so I'll try my best to follow any explanation you may provide me with but please be patient should I ask something basic.

Thank you so much for your help!!!

Jorge/eurojourney

 

[HiTechCoach]

Hello,

I am new to Access but so far my forms and tables seem to be working. However, I need help with one item right now.

I am in real estate and the forms I created are to register Brokers/Agents and Clients. These two forms feed two main tables. Besides this two main table I have several other small tables that feed choices or options to combo boxes primarily in the Client's form, such as "Mr. Mrs, Ms" for the title to the Clients name, for example.

Now this is my issue: I have a combo box in my Client table that shows all the brokers I have registered so far in the other form, i.e. Broker table. The list will be huge eventually having several hundreds if not thousands of brokers and agents. I did a lookup column in my Client table and named the field Broker Name - this field in my Client table shows the Name, Last Name, Company and Phone Number for every broker registered in the Broker Table so I have more information when selecting the broker in the Client form from a combo box. Then I created the combo box in the Client Form with that field (Broker Name) and with the following information in the Row Source

SELECT [Broker].[Broker ID], [Broker].[First Name], [Broker].[Last Name], [Broker].[Company], [Broker].[Mobile Phone] FROM Broker ORDER BY [Last Name];

the Control Source is Broker Name.

The good thing about doing it this way from what I can see is that it allows me to select one or more brokers should there be more than one broker involved so that's perfect. However, once I have several hundres of brokers and agents in the Broker Table is going to be a pain to search through all of them to select the right one. So, in other combo boxes I have noticed that you can type the first letters of the items you want to check and it pulls those items that begin with the same letters, sort of like an index - but in my combo box for the broke's name in my Client Form it does not work, it does not allow me to type anything. Is there a way that I can achieve this so I don't have to scroll down through hundreds of records until I find the right broker?

Please let me know and remember, I am new to Access so I'll try my best to follow any explanation you may provide me with but please be patient should I ask something basic.

Thank you so much for your help!!!

Jorge/eurojourney

Have you tried setting the Auto Expand property to Yes?

Potential issues I have spotted:

1) You have spaces in object names. I would urge you do avoid this.

2) In the Client table you should be storing [Broker ID] (this should be the primary key) not the name.

I would urge you to consider doing the following:

1) All people really should be in a single table.

2) I would use a junction table to link clients to brokers/agentgs/etc is you need to link multiple.

 

[boblarson]

did a lookup column in my Client table and named the field Broker Name -

Not a good idea. Here's why you should not use lookups at table level. You can do the same thing at form level and not have issues, but table level can create some problems.

 

[eurojourney]

I would like to post the way I structured my tables as shown in the relationships view. Is that possible. I do have a junction table - is a many-to-many relationship - but I don't know how to use a form to fill fields on different tables, i.e. fill name, last name, email, etc for a client in the Client table while selecting from a pull-down box or a combo box on the same form a name of a broker that is located in a different table, i.e. Broker Table. The only way I was able to achieve filling a name into the table from data from another table was by doing it the way I did it. Any suggestions now that it seems I am in bigger trouble than I thought?

 

[eurojourney]

I read the article about lookup columns. How Do I link my two tables so I can plug in two both tables from the same Form?

 

[HiTechCoach]

I read the article about lookup columns. How Do I link my two tables so I can plug in two both tables from the same Form?

You still use a combo box to pick a record from another table. You do not use the combo box look up at the table level. You do use a combo box on a form set up exactly like a table look up, but it is at the form level.