Hi all,

I'm new to this forum. My Access skills are above basic, but I seem to be having trouble getting to the next level regardless of how much time I invest in it. I'm using Access 2016.

Here's the current issue I'm working on. I have two forms. One called Frm_AddSku, the other called Frm_AddProdSubCat.

I know that Access has a built in form property called List Items Edit Form, but I've added some VBA in an On Not In List Event, that makes the interface a lot cleaner. The problem is that only like 80% of it works, and without that other 20%, I'm not at the goal.

Frm_AddSku has a combo box called ProductSubCat_IDFK and if I type something that's not in the list, my VBA kicks in and launches the MsgBox. 

Private Sub ProductSubCat_IDFK_NotInList(NewData As String, Response As Integer)
On Error GoTo errline

Dim MsgBoxAnswer As Variant

Response = acDataErrContinue

'Request permission
MsgBoxAnswer = MsgBox("Do you want to add this new Product SubCategory?", vbYesNo, "Add New Product SubCategory?")

If MsgBoxAnswer = vbNo Then 'You've decided not to add a new Product SubCategory.
    Me.ProductSubCat_IDFK = Null 'Make the list control empty for the time being.
    DoCmd.GoToControl "ProductSubCat_IDFK" 'Move the cursor back to the list control.

Else 'Permission granted to add a new Product SubCategory to the list.
    DoCmd.OpenForm ("Frm_AddProdSubCat") 'so open Frm_AddProdSubCat
    DoCmd.GoToRecord , , acNewRec 'go to new record
    Forms![Frm_AddProdSubCat]![ProductSubCategory] = NewData  'fill in new value on ProductSubCategory field.
    Me.ProductSubCat_IDFK = Null 'Make the list control empty for the time being.
    DoCmd.GoToControl "ProductCategory_IDFK" 'Move to the next desired field.
    
End If

errline:
    Exit Sub
End Sub
I have no clue what the root of the problem is, but the symptom is that it is none of my VBA events will requery the Frm_AddSku. So when I return to Frm AddSku after entering a new ProductSubCategory in Frm_AddProdSubCat, the new ProductSubCategory is not in the combobox list and the Frm_AddSku just relaunches my same msgbox in an endless loop. The database is being updated and the entries are going to the appropriate table. I just can't get Frm_AddSku to reflect that.

The lines that use the command = Null , in both the vbNo logic and the vbYes logic above, do not seem to be working as expected. That's just like an fyi, it's not my main concern. I'm just pointing it out because it might be the source of the problem. Or it might not be. I've also tried using ="" instead of = Null. It doesn't make a difference.

On my Frm_AddProdSubCat (the one that gets launched from Frm_AddSku) I have a button called SaveCloseAPSC with an On Click event as follows:

Private Sub SaveCloseAPSC_Click()
On Error GoTo errline
DoCmd.RunCommand acCmdSaveRecord 'save record before close form
On Error GoTo errline

DoCmd.Close

DoCmd.OpenForm "Frm_AddSku"

[Forms]![Frm_AddSku]![ProductSubCat_IDFK].Requery

errline:
    Exit Sub
End Sub
The line [Forms]![Frm_AddSku]![ProductSubCat_IDFK].Requery seems to be having no effect. ProductSubCat_IDFK on Frm_AddSku is certainly NOT requerying.

Additionally, I have a clear form button on the first form Frm_AddSku that is also not working. 

Private Sub btnClearForm_Click()
    Me.Refresh
    Me.Requery
    'this button clears any selected data from the comboboxes
End Sub
No such refreshing or requerying is occurring whatsoever.

I've tried peppering Me.Requery and Me.Refresh all over everything I can think of until the cows came home with no results. If I go up to the Access ribbon and click Refresh All, then Frm_AddSku will refresh. But it seems like I should be able to embed that same command inside my VBA so that I can get an air tight front end.

I've attached a couple screen shots of the forms for reference. As well as screen shots of the VBA.

I'm looking forward to someone guiding me in the right direction. Thanks!

Frm_AddSku.JPGFrm_AddProdSubCat.JPGVBA SCRN SHOT.jpgVBA SCRN SHOT-save and close.JPG