Combo boxes in subform (view as table)

[brucomela]

..I guess my problem is not new, so I hope somebody can tell me something I couldn't find in FAQS...in my db there are 2 tables with many-to-many relationship: commercial estimates and products. Therefore, in my form there's a subform (shown as a recorset/table - don't know how to say in english) where user can select products to add to estimate, in order to calculate total price required.

My aim is to link together two combos so that when user selects a products_category, the second combo shows only products belonging to specified category. Here is my code:

Private Sub CasellaCombinata15_AfterUpdate()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Prodotto.Requery
End Sub

...==save (otherwise it says it can't make requery) and requery second combo box (products one).

Problems are:
1.once user has selected a product in second combo, it's impossible to re-select it (this may not depend from code, maybe my mistakes!) AND, especially,
2.IF user decides to add a second product, when he selects a different category, also previous records in recordset view suffer Requery, generating mistakes! My question is: is there a way (in recordset, not form, view) to make this requery without conditioning previous records added (I mean previous products_combo_boxes)?

Hope I was clear, please give me detailed suggestions!!!

 

[brucomela]

to simplify: a subform IN DATASHEET VIEW, 2 combo boxes, (second one depends from first one with requery ufter update), can I set Combo1 to requery Combo2 ONLY IN THE SELECTED RECORD and not for all records previously inserted into datasheet?

Please, help me!...hope I won't need to turn into form-view instead than datasheet...

 

[Luther1978]

Brucomela, From what you are saying I think I'm having the same problem, which although no-one has been able to answer my questions to this problem its refreshing to know that someone else is looking to do the same.

So if anyone has any Ideas Help would greatly appreciated. I'm pulling my hair out here, as I want to use this method 4 times in my DB.


Martin

 

[brucomela]

...2 hours of my life today on this...I can't believ nobody can find a solution...by the way...using form-view instead than data sheet IS NOT considered a solution!!

 

[Luther1978]

Using a form view instead of a datasheet view doesn't solve it I'm afraid.

 

[PWise]

try this in the on current event
if me.newrecord = false then
me.combo2.rowsource=sqlstatement where = to combo
'what ever your sql is
end if
i did not try this but i don't think the display changes on the other records and the value stays the same