Solved Keep subform data present while refreshing mainform (1 Viewer)

[krissy21]

I have an order form in which I select parts/items from a listbox (lstItemsPerVendor), relating to a specific Vendor, to be ordered and then adjust the quantity and purchase price in the subform (frmOrderItemsPerID). However, I have also made the form allow for specific categories of parts to be selected for an easier search.

When not using a specific category, everything works great. However, if I select a category from the combobox and then change it, the items in the subform refresh because of the vba code and are no longer visible. Same if I use the "List all Parts" button. Shouldn't the Refresh still run the query that the subform is based off of if the rest of the mainform information is still present? The query criteria is on the mainform. My thought is that I should adjust the code for the combobox and button but I'm not sure how to go about writing it.

Here is my code below for one part:

Private Sub cboFilterCategory_Change()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim sqlString As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryItemsPerVendor")

qdf.SQL = "SELECT Items.ItemID, Items.ItemName, Items.Brand, Items.[Product#], Items.QtyPerPackage, Items.RegPrice, Items.VendorID, Items.CategoryID " & "FROM [Items]" & "WHERE (((Items.VendorID)=[Forms]![frmInventoryOrders]![cboVendorName]) AND ((Items.CategoryID)=[Forms]![frmInventoryOrders]![cboFilterCategory]));"

Me.lstItemsPerVendor.Requery
Me.Refresh

qdf.Close

End Sub

Thank you!

 

[arnelgp]

firstly don't use the Change event of the combobox.
this event is Called on Each keypress you make.
better put it in AfterUpdate event, so it is only triggered once.

also Remove this Sub:

cboFilterCategory_Change()

you already has AfterUpdate event for the combo, the above is redundant.

 

[krissy21]

firstly don't use the Change event of the combobox.
this event is Called on Each keypress you make.
better put it in AfterUpdate event, so it is only triggered once.

I had an AfterUpdate event also but it didn't seem to work by itself so that's why the OnChange. I have removed it now like you suggested and it still works the way I had intended it to but the problem still remains that the mainform refreshes and clears the subform when the combobox selection is changed or the button is clicked.

 

[arnelgp]

can you share a sample db.

 

[Pat Hartman]

Here is an explanation of the problem along with one solution. There are others.

 

[krissy21]

Pat, that was a helpful read but doesn't fix my issue.

Please see the database attached.

 

[arnelgp]

your subform, frmOrderItemsPerID has on its "Data Entry" Property Set To Yes. Change it to No.

also remove this Sub:

cboFilterCategory_Change()

you already has AfterUpdate event for the combo, the above is redundant.

 

[krissy21]

your subform, frmOrderItemsPerID has on its "Data Entry" Property Set To Yes. Change it to No.

also remove this Sub:

cboFilterCategory_Change()

you already has AfterUpdate event for the combo, the above is redundant.

I just got a whole new perspective on how those particular properties work that I hadn't ever realized! It refers to the records and not so much what the form can do. I can't thank you enough for helping to clear up my subform issue!

And I'd forgotten to remove that other OnChange sub, thank you.

 

[arnelgp]

yes, that is the issue, since the subform is set as Data Entry, whenever you refresh
the mainform/subfom, the subform goes to "new" record instead of staying on same record.

goodluck