VBA not requerying form on not in list event, Access 2016

[MS Access Probz]

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 not completely working.

Private Sub btnClearForm_Click()
    Me.Refresh
    Me.Requery
    'this button clears any selected data from the comboboxes
End Sub

Thanks to this thread here, I added a new trusted location, and this button now partially works. But it won't clear any not-in-list text I typed into the field just before the second form is triggered to launch.

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!

[Nauticalgent]

Quick question: What is the source/data for ProductSubCat_IDFK and is it bound or unbound?

Without too much more info, I don't know where to start.

That being said, I do know that you cannot assign a Null value to a control that has a number assigned to it.

IDFK = I Don't Freaking Know?

[PhilOfWalton]

I tend to use a different approach. Rather than use the NotInList, I double click the combo box to bring up a form to add/edit a record.

Here is an example. I have a form "Town" on which there is a Combo Box to select the appropriate county.

Here is the code

Private Sub CountyID_DblClick(Cancel As Integer)
 
   If Not CurrentProject.AllForms("County").IsLoaded Then
        DoCmd.OpenForm "County"
    End If
 
    DoCmd.SelectObject acForm, "County"
    DoCmd.Restore
    If Nz(CountyID) = 0 Then
        DoCmd.GoToRecord acForm, "County", acNewRec
    Else
        DoCmd.GoToControl "CountyID"
        DoCmd.FindRecord CountyID
    End If
 
End Sub
 

This is the code on the County Form which updates the Town form.

Private Sub Form_AfterUpdate()
 
    If CurrentProject.AllForms("Town").IsLoaded Then
        If Nz(Forms!Town!CountyID) = 0 Then          ' Empty
            Forms!Town!CountyID = 0
            Forms!Town!CountyID.Requery
            Forms!Town!CountyID = CountyID
        End If
    End If
 
End Sub
 

Phil

[NeoPa]

NauticalGent:
That being said, I do know that you cannot assign a Null value to a control that has a number assigned to it.

There are times when Nulls are not allowed in a Control but I suspect you may have misinterpreted the cause when you've seen it occur.

Nulls are only not available in a Control when that Control is bound to a Field which is itself set not to allow Nulls. This can happen for numeric Fields, but it can also happen for text Fields. The point is whether or not the Field is configured as Required.

[MS Access Probz]

@PhilOfWalton thanks! That's a pretty simple solution and it seems to be working.

It updates the darn list at least! Which is the most essential part.

The thing that attracted me to the method I was working on is that it autofilled the text I just entered from my edit form into my mainform. I thought that was a pretty slick interface feature. With this method I still have to go back and select the thing out of the list that I just defined.

Is there a line of code to add to your method that would accomplish that?

Also, I have a dumb question: the syntax you use CurrentProject.AllForms("County"). I haven't seen that before. It clearly works but usually I just see Forms("County"). Is there a rule for when I should use CurrentProject.AllForms?

Thanks!

[NeoPa]

MS Access Probz:
Is there a line of code to add to your method that would accomplish that?

If you set the .Value of the Control to your newly-created value then that item in the list will be selected.

MS Access Probz:
Also, I have a dumb question: the syntax you use CurrentProject.AllForms("County"). I haven't seen that before. It clearly works but usually I just see Forms("County"). Is there a rule for when I should use CurrentProject.AllForms?

Forms() refers to a Collection of open Forms. CurrentProject.AllForms() refers to a Collection of all Forms in the project.

Phil's code illustrates a way to determine if a Form is already open by checking the .IsLoaded property. If it isn't already loaded then it won't be found in the Forms() Collection, and in this case will need to be opened by the code.

[MS Access Probz]

great thank you! I'll play around with .value and try to get that to work. Would it be something like .value = NewData?

[NeoPa]

MS Access Probz:
Would it be something like .value = NewData?

Broadly speaking - yes.

You'd obviously need to ensure that .Value is properly specified, but yes. That would be it.

[MS Access Probz]

totally. I'll give it a whirl.