Duplicates in a Combo Box

[Danielle17]

I have a simpe database that contains one table. This table contains Model#, Manufacturer, Description & Price fields. I have created a form so that the user can search for the equipment for a certain Manufacturer. But the problem is that when I create a combo box using the wizard to look up a record (and then display the results in the subform) it lists the manufacturer as many times as they are in the table. How do I get it to display it only once but still bring up all the records for that manuf. in the subform? I've tried it with a query by using the DISTINCT method but when I change the control source for the combo box I get an error. Then the combo box is blank. Any suggestions?

 

[TTThio]

Hi,

I'll say, create a Select Query, the field only the manufacturer. Use this qryManufacturer as cbobox' row source.
Then just link the cbobox with manufacturer in the subform.

Have a try.

Tin Tin

 

[Danielle17]

I think that is what I tried. I have a query that only has the Manufacturer in it. I then select/copy the SQL statement and put it in the control source for the combo box. But it comes up blank. I then thought that maybe it was because I didn't have the primary key in there. I tried to put that in there but then a parameter pops up and asks for the ID(primary key), if I click cancel then I can go to the combo box(which does NOT have duplicates) but when I try to select one of the manuf. it gives me a new error that sends me to the code. If the ID is in the control source then I get the duplicates but if it's not in there then I get nothing in the combo box. Do I just want to create a combo box without the wizard?

 

[TTThio]

Sorry Dan,

I forgot to mention, in the qryManufaturer, set the UniqueValues property to yes. It will return no duplicate records. Then just use it as the cbobox's row source.

I guess it will work. Let me know.

Tin Tin

 

[Danielle17]

That property was already set to YES. When I make it the row source of the CB then it returns nothing. Here's the Row Source:

SELECT DISTINCT PriceList.Manufacturer
FROM PriceList;

And the code itself for the CB:

Sub Combo48_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[Manufacturer] = " & Me![Combo48]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub


When I created the form the control source was the table and not the query so if I try to set the control source to manufacturer it still returns nothing. ?????

 

[TTThio]

Now I get confused with what you want to do.

Isn't it that you want to create a main form with cbobox with manufacturer as the row source with no duplicate value, and then when you select a value from the combo box, it will just display related data of the manufacturer in the subform?

 

[Danielle17]

I just figured it out!

I made a new form and had the query as the source and then created a relationship between the manuf from the table and the query. Now that it works I guess I have another question:

How do I search in that subform now for a specific model#? Does that have something to do with connecting another CB to this one?

 

[Danielle17]

sorry to confuse you.
I DO want that but then when I get the info into the subform I'm going to need to search the results for a particular model#. I would have just searched by the model# but since there are so many manufacturers there are duplicate model#'s i.e. 105 for Tektroniks and 105 for Hiptroniks.

 

[TTThio]

How about create qryMode# and make a cboMode# with the qry as record source and add the new cbo in the main/child link box?

By the way, I don't quite understand when you mentioned you've created a relationship, do you mean in the relationship database?

I never do that way. So far, by creating query with unique value as row source of cbobox, have a table as a subform record source, then set the main/child link, it always works as intended.

Well, probably it's just a lot of way to do the same thing after all...he..