vba - Requery combo boxes in Access 2013 -

i've created 2 dependent combo boxes in form. if choose category in first box, filter list of products available in second. i've saved category-product selections table called test, categories 1 row each.

what i'd display product in product combo box if select category in test. example, if select category=condiments , product=ketchup in form, it's added test. then, next time select category=condiments in form, products combo box (the box type, not dropdown) show ketchup. seems work if have 1 row in test. if add more rows, productcombobox not change.

here's how form constructed.

in rowsource categorycombobox, select everything

select * categorytable 

in rowsource productcombobox, filter products based on category selected

select * producttable producttable.categoryid=[forms]![formtest]![category] 

the form source products left joined category on categoryid.

in on change event categorycombobox , on current event form, requery productcombobox

productcombobox.requery 

however, using productcombobox.requery requeries options available in dropdown. doesn't change current combo box value after selecting category before selecting product. how requery data table linked controlsource? there better vba function requery, or need use sql in macro?

in onchange event of first combobox, following:

 private sub category_change()       dim myid long       me.productcombobox.requery       if not isnull(dlookup("productid", "tbltest", "categoryid = " & me.category))            me.productcombobox = dlookup("productid", "tbltest", "categoryid = " & me.category)       end if  end sub 

then update test table, in event want update "default" option, put:

 currentdb.execute "update tbltest set productid = " & me.productcombobox & " categoryid = " & me.category 

this assumes "tbltest" has record each category. if not, can generate checks , insert category table.