Filter records in a combo box

[contrast]

I am trying to set up a form which contians a combo box that I want to use to display data on a continous subform.

The data I am working with are books. So when I pull down the combo box for publishers I see several instances of the publishers name (since one publisher can publish several books).

What I want is to only see the publisher's name once and after I select it have all of the records that contain that publisher to be displayed in the continious subform.

Thanks for any help.

 

[David R]

You can fix your combo box by updating the Row Source to start SELECT DISTINCT..., however, why does your combo have book names in it if you're only looking for Publisher names? There's probably a simpler way...

After you select your value from the combo box, change the recordsource of your subform to refer to the combo box as criteria.
Something like
Queryfield: Publisher, Criteria: Forms![FormName].[comboPublisherName]

Hope that helps. Post back if I haven't been clear or something doesn't work.

David R

 

[ListO]

Are the publisher names in the same table with the book titles? If so, it would be better if the publisher names were in their own table, with the book records only referencing the publisher names from the publisher table.

With the publisher table separate and apart, it's easy to have a combo box reference the publisher table, and that table should have unique names only.

 

[contrast]

Thanks for the responses.

David R, I tried the SELECT DISTINCT and that worked well. The only problem I have is with the text you want me to insert into the recordsourse of the subform.

I am still learning and don't completely understand it all. I know my FormName and ComboPublisherName, but I am unsure of the four statements before these two. Are they to be inserted as is or am I am supposed to substitute my values for them? Thanks.

I will try to clarify what I am trying to make. I simply want to give people a way to search for a book based on whatever criteria they want. So if they know the title they can use the title ComboBox, find the title in the list, and the entire record of that book appears on the continuous subform.

I want a continuous subform so incase the person searching wants to see all books by a particular author they can go to the author Combo Box, find the name of the author, and in the continuous subform all of the records which are by that author appear as a list that the user can scroll through.

There would be a separate combo box for each search criteria. Is there a better way? It would be nice if there was just a search box and a pull down list next to it where you can choose what category you want to search through. I imagine this would be more complicated. Thanks for your time and for bearing with me.

 

[David R]

I would put your combo boxes in the header of your form. Next to each one I would put a button to search with, to simplify things (rather than having one button and a giant code loop to figure out which one to search by, unless you WANT them to be able to search by both publisher and year, for example).

Build each combo on your form, don't pull them from your field list. When the combo box wizard pulls up, select the first option, "Look up these values in a table or query". Point to the table and field you want (only one field please per combo!), and the final option is "Store this value for later use." Don't store it in a field.

Behind your button you want to change the recordsource of your form. I would build the form itself into a continuous form, rather than throwing a subform into it. Something like this:

Me.RecordSource = "SELECT * FROM queryName WHERE [FieldForThisButton] = '" & Me.ThisComboBox & "'"

See if that works for you better.

David R