I have created a simple inventory management Access DB which in its basic functionality works perfectly.I would like to start to make some polish here and there and I've found the next issue.
I do have a main form for inventory transactions. It has a Header section (with a table of transactions header as a recordsource) and a details section (datasheet type subform into the main form; which means a multiple records form where I can add or remove multiple lines for each header transaction and it is linked to the main form, that works OK).
In this detail/lines subform I have the fields: Category (combobox), Item (combobox) and Quantity (category and item fields are linked to "master categories" table and "master items" table).
I do want to add the following behavior:
- When I select a category on category combobox field, the item field updates its selection to show only the items in the selected category.
I can do this easily on a single record form with this code:
Private Sub Category_AfterUpdate()
Me.Item.Requery
End Sub
On the Item field datasource I do have a simple query:
SELECT ID,DESC FROM ARTICLES WHERE ARTICLES.CATEGORY = [FORMS]![SUBFORM].[CATEGORY]
This works flawlessly with a single record form.
However as this is a multiple lines/records form when I select a category on a line, it updates the "Item" selection field for ALL the lines/records, even changing values depending on the selected category.
Is there a way to update controls/fields in an independent way for each record on a datasheet form? Let's say something like:
Private Sub Category_AfterUpdate()
Me.Item(current record).Requery
End Sub
I'm thinking on adding an index to the combobox article field and update only the field on that index but I don't know how to proceed or if it's possible to do that.
Any ideas or workaround to do this?