Search Combo Box from Subform (1 Viewer)

[JoseO]

Hello!

Here's my dilemma and any help would be greatly appreciated: I have a main form that contains vendor names with a subform whereby the end user can enter vehicle data that gets recorded in that vendor's table. So far all is good.

The problem: I added a search combo box to the SUB form in which I want the combo box to only search/display the vehicle number that is currently displayed in the sub form. Right now the combo box shows me all vehicle numbers.

Thank you

 

[Uncle Gizmo]

That doesn't sound right. The combobox should only show a single record, the one you selected. Or possibly a previously selected record, if you are traversing the record set.

 

[sneuberg]

Maybe if you upload you database we could understand your situation. Things like

What are the record sources of the form and subform?
What field is the combo box bound to?
What is the row source of the combo box?
What should the row source of the combo box be?

 

[jdraw]

Further to other responses:

Can you tell us a little about Vendors? And Vehicles?

Is this a Vendor can sell 1 or Many Vehicles type thing?

What tables do you have?

 

[JoseO]

Uncle Gizmo, sneuberg and jdraw - Thank you so much!!

My apologies for not explaining it well enough - round two. Let's see if I can do better.

First of, I created the search combo box by way of the toolbox in the ribbon. In the main form there is a text box where the user enters the name of the business that has vehicles - let's say ABC Deliveries.

Once they have entered the latter, right below the textbox, the user then clicks inside the subform (which is a continuous form) in order to enter the vehicle number along with the location of the business, say Houston, TX in that row. After a few entries, ABC Deliveries now has the following in each row under "Vehicles Number":

2311
4234
5444
3323
3232

There are total of 220 businesses with around 400 vehicle numbers. When I created the search combo box, I placed it in the form header and it currently displays ALL 400 plus vehicles. So technically, the combo box works.

What I wanting however, is for my end user to not have to see ALL 400 plus vehicles from the current record they are entering...make sense? I just want the combo box to show ONLY the current vehicle number in the current record they are currently working. So if the user click on the combo box, he/she will see only the following for ABC Deliveries and not the 400 plus vehicle numbers:

2311
4234
5444
3323
3232

I hope this makes better sense. Thanks again for being so willing to assist me. It is SUPER appreciated!!

 

[sneuberg]

I will assume you want the vehicles list in the combo box to be limited to those associated with the business and for the example I present I will assume the name of the text box with the business name is txtBusinessName and the main form with this text box is named MainFrm. Here's what to do.

1. Click Query Design under the CREATE tab to open the query builder
   
2. Select the tables with the vehicle numbers and business name
   
3. Join them together from the primary key to the foreign key. If the relationships have been defines in relationship there will already be a line between the tables.
   
4. Select the vehicle and business Name as fields for the query. Uncheck the business name so that it doesn't show in the result.
   
5. In the criteria of the business name. Type Forms!MainFrm!txtBusinessName but of course use the actual names in your system. After you type Forms! intelliSense should give you the options that actual exist in your database.
   
6. Save this query. Let's say you name it VehicleComboBoxRowSourceQRY.
   
7. Go the the properties of the combo box DATA tab and for the Row Source pick VehicleComboBoxRowSourceQRY in the drop down list.

The combo box values should now be limited to the business. This has the assumption that there are no surrogate keys involved, i.e., the combo box only has one column. If this is not the case and this doesn't work for you, please provide us with all the names of the fields involved.

 

[JoseO]

Thank you SO MUCH sneuberg - it worked! Much appreciate it!!