Hello there people, long time no post! My consulting life has left access behind with few exceptions... It's interesting looking at a DB that I built three years ago and trying to satisfy a customer request... My question is, "Who built this freakin database?!?!". oh yeah - I did.
So, here is a relatively simple problem, I think:
I am trying to get a subform to requery every time that a particular combobox changes vailue or a different record is loaded in the main form. Sounds simple, but I can't nail it!
here's how it goes...
The main form [ADC_ORDER_TRACKER] has a combo box [cbSupplier] that allows for the selection of a supplier.
There is a subform [ORDERS_SUBFORM] on the main form that offers datasheet view for selection of products from that supplier, or displays the already selected products and allows for selection of new products. The products are selected via another combobox in the [ORDERS_SUBFORM] called [PRODUCTS], which is based on the following SQL:
SELECT DISTINCTROW tblProducts.ProductID, tblProducts.ProductName, tblProducts.SupplierID
ORDER BY tblProducts.ProductName;
When I run this code on its own, and manually populate the [cbSupplier] value, I get the desired results. The problem is, when I use the main form and change records, then the first value encountered in the [cbSupplier] field is used for all the rest of the records, or for new orders. It doesn't limit the list to the different suppliers, or ALL products are available for all suppliers.
I need to limit it only to the supplier selected.
I've tried Me.Orders_Subform.Form.Requery on the afterupdate for the [cbSupplier] combo to no avail, along with two hours worth of other useless things!
Did I totally lose you guys? Sorry, I'm so out of the loop anymore - I lost my chops!
I realized that the reason that the field was not updating properly is because one of the key fields in the subform was actually a query itself... SO, I was requerying the subform and it delivered the proper results. Once I set the command to requery the field, I got the proper results...
I then set the OnCurrent action as required and voila.
Sorry for the bother! Thanks for the suggestions...