how to fix this issue in the subfrm?

[siamandm]

Hi,

i have a continues subform, which is nested into the main form, it shows data without any issue, but when i do changes or add a new record it mess the data inside the related tables

here is my database :

NewDistDB.zip

when you open the frmBenefciareies , and go to tab items received it shows the data as expected but when i add new items or modify the current one i get issue

also can we- filter item name according to the item category


regards

[HansV]

We'll probably have to change the record source of the subform, but I don't have enough time to go into the details right now. I'll take a closer look later today.

[HansV]

The category combo box on the subform should be unbound, and the record source of the subform should contain the ItemName_ID field from tblDist_Items, not from tblItemList.

I added code to set the row source of the item combo box in the After Update event of the category combo box and in the On Current event of the form.

I placed text boxes on top of the category and item combo boxes to display the correct category and item; this is necessary on a continuous form.

NewDistDB.zip

[siamandm]

thanks a lot Hans
i just noticed one thing, for the records already there, when you want to choose an item name from the combo box, it shows the last used category filtration
for example in the last record is summarization category, if i straight away wants to change the item name it shows me a list for winterization!
at least i have to re-choose summarization category in order to get a list for it.

another thing can we do something, if i choose the item it automatically gets the category for it and not need for the user to choose it from the combo box
i notice that from tblDist_item , if i choose an item name it will automatically add the category for it.

regards

[HansV]

Change the On Current event procedure of sbfrDist_Items to

Code: Select all

Private Sub Form_Current()
    Me.cboCat = DLookup("ItemCat_ID_FK", "tblItemList", "ItemNameID=" & Nz(Me.cboItemList, 0))
    Me.cboItemList.RowSource = "SELECT ItemNameID, ItemName FROM tblItemList WHERE ItemCat_ID_FK=" & Nz(Me.cboCat, 0)
End Sub

[siamandm]

thanks Hans
what about the other suggestion, removing the item Category inside the sub form ? does this will make my life easier?

reards

[HansV]

Is there no possibility that the same item would be listed in two categories, for example both in summer and winter?

[siamandm]

yes, there is sometimes an item can belong to two or more categories

[HansV]

We'd have to display both the item and the category in the item combo box, otherwise it wouldn't be clear which item you are selecting. Is that OK?

[siamandm]

if you think this make it better than the separate one will be ok

Regards

[HansV]

This simplifies the subform. We need less code. See the attached version.

NewDistDB.zip

[siamandm]

thanks a lot, that's much better.