Consulting

Results 1 to 3 of 3

Thread: VBA Is Not Requerying Form After On Not In List Event, Access 2016

  1. #1

    VBA Is Not Requerying Form After On Not In List Event, Access 2016

    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

  2. #2
    VBAX Newbie
    Joined
    Jul 2018
    Location
    Essex
    Posts
    3
    Location
    The following is untested but you could try:
    Private Sub ProductSubCat_IDFK_NotInList(NewData As String, Response As Integer)On Error GoTo errline
    
    Dim MsgBoxAnswer As Variant
    
    
    '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.
        Response = acDataErrContinue
    
    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.
        Response = acDataErrAdded
    
    End If
    
    errline:
        Exit Sub 
    End Sub
    I think you will need a different method to enter the new data, probably using a form opened in Dialog mode with the new data passed to it in the OpenArg property.

    If you post a copy of the db I'd be happy to have a play with it

  3. #3
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    As you say, something does not make sense, in particular the piece of code where you save a record, close the form and then open Frm_AddSku and then requery the combo.
    if Frm_AddSku is closed and you open a new version of it, then the Combo should reflect the new situation and not need requerying.
    I regularly use the Not In List to add records to a list, so it might be better to add the value directly from the combo (not in list) entry and then open the form to that record to add the extra data.
    This is an example of the code that I use for the Not In List Event

    Private Sub DoctorName_NotInList(NewData As String, Response As Integer)
    
      Dim rst As Object, txtName As String
    
       On Error GoTo errorcatch
    
    txtName = MsgBox("Add '" & NewData & "' to the list of Clients?", _
    vbQuestion + vbYesNo)
    If txtName = vbYes Then
    ' Add data stored in NewData argument to the Summary of App Err table.
    Set rst = CurrentDb.OpenRecordset("Summary of App Err")
    rst.AddNew
    rst!FieldName = NewData
    rst.Update
    Response = acDataErrAdded ' Requery the combo box list.
    Else
    Response = acDataErrDisplay ' Require the user to select
    End If
    rst.Close
    Set rst = Nothing
    Exit Sub
    errorcatch: 
    MsgBox Err.Description
    
      End Sub

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •