Combo query

[James Tebb]

My Table [PRICE LIST] has the fields [CATEGORY] and [TYPE]
The entries in the table mean that multiple records fall into the same category.
eg Category - Type
Socks - Red
Socks - Blue
Socks - Green
Trousers - Long
Trousers - Short

In my form [ORDER FORM] I have a combo which is to be populated with the categorys. To do this I created a query to control what appears in the combo.

How do I tell it to ignore duplicates in the list

eg at the minute the drop down list reads socks,socks,socks trousers etc

Sure this is easy

 

[James Tebb]

I have a further problem.
i have created a second combo to allow me to pick type.
This is populated by a query that sorts records that match the value in combo 1.

I tried it and it works fine however

If I go back to combo 1 and choose a different category the choices in combo 2 remain the same. How do I get them to change to suit my new choice?

thanks

 

[James Tebb]

Ok ive solved the part.

Any ideas on the second

 

[James Tebb]

I have solved the problem of duplicates within the first combo box. When I open the form I can select the category in combo 1 eg trousers. When I then go to combo 2 I can select from the correct options eg long / short.

If I then 'change my mind' and go back to combo 1 and select "socks" combo 2 still gives me the option long / short as opposed to Red / Blue Green.

How can I make the combo react to the changes?

 

[alansidman]

In the properties for the first combo box, in the events tab, click on the After Update event. Click on the ellipsis (...) and select the code option. Add this to the code for the after update

me.comboboxname.requery

This will tell access to refresh the event. Change the comboboxname to the your field name.

Alan

 

[James Tebb]

Hi Alan
Thanks for your help. I'm not too good with code. I tried your fix but couldnt get it to work. Would you be able to give a bit more detail on exactly how to do it.
Assume that the combo to pick category is COMBO1 and the combo to pick type is COMBO2

thanks + Apologies

 

[alansidman]

With your form open in design view, right click on the combo 1 box. A properties form will open. Click on the events tab. Click on the Afterupdate box. Click on the ellipsis. Select the last item in the list for code. A VBA window will open and it will indicate

sub Combo1 after update()
 
End sub

In the space in between the two lines above. Type

Me.Combo1.requery

Close the VBA window. It will auto save. Close and save the form.

Reopen the form and test it.

Alan