Not updating correctly

[tikkat3]

I have some VBA code in the On NotInList event for my main form. It works fine except for one bit. If a new account manager is entered into the form's field, the frmAccount Manager is opened to enter the new name into the tblAccount Manager. The code should be checking to see if a name already exists.

My problem comes when I have just entered a new name. The pop-up message keeps telling me that the name does not exist and would I like to add it - this keeps looping even though I have just added the name. However, if I leave the record and come back to it, I will find the name in the drop-down list (i.e. it was added, but the checks did not pick it up.

Below is my code:

Private Sub Account_Manager_NotInList(NewData As String, Response As Integer)
Dim strAccount_Manager As String
Dim intReturn As Integer, varName As Variant
strAccount_Manager = NewData
intReturn = MsgBox("Account Manager " & strAccount_Manager & " is not in the system." & " Do you want to add this Account Manager?", vbQuestion + vbYesNo, "Safetell Ltd.")
If intReturn = vbYes Then
DoCmd.OpenForm FormName:="frmAccount Manager", DataMode:=acAdd, WindowMode:=acDialog, OpenArgs:=strAccount_Manager
If IsNull(DLookup("ID", "tblAccount Manager", "[Account Manager] = """ & strAccount_Manager & """")) Then Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
Exit Sub
Response = acDataErrDisplay
End Sub

 

[boblarson]

In the close event of the form "frmAccount Manager" you need to requery the combo box.

 

[RuralGuy]

I rewrote the process a bit. Try this one:

Private Sub Account_Manager_NotInList(NewData As String, Response As Integer)
   If MsgBox("Account Manager " & NewData & " is not in the system." & vbCrLf & _
            " Do you want to add this Account Manager?", _
            vbQuestion + vbYesNo, "Safetell Ltd.") = vbYes Then
      DoCmd.OpenForm FormName:="frmAccount Manager", DataMode:=acAdd, WindowMode:=acDialog, OpenArgs:=NewData
      If IsNull(DLookup("ID", "tblAccount Manager", "[Account Manager] = """ & NewData & """")) Then
         Response = acDataErrContinue
      Else
         Response = acDataErrAdded
      End If
   Else
      Response = acDataErrContinue
   End If
End Sub

 

[tikkat3]

Bob,

How would I go about that? Is there a specific bit of coding that needs to be used? I am very new to programing languages and am having real guess-and-hope for the best attempts at modifying code that others have written!

 

[boblarson]

From the form you opened up to put in the new information, requery the original form's combo box by using

Forms!YourFormWithTheComboBoxHere.Account_Manager.Requery

 

[tikkat3]

Thanks again Bob. Rural the rewrite solved the problem instantly! Thank you all.

 

[RuralGuy]

tikkat3,
Any chance you tried the code I supplied? acDataErrAdded tells the ComboBox to requery so it is not necessary to do so from the other form.

Edit: Oops, a little slow on the post.

 

[tikkat3]

tikkat3,
Any chance you tried the code I supplied? acDataErrAdded tells the ComboBox to requery so it is not necessary to do so from the other form.

RG,
You may not have seen the above post as it only just went in. Yes I tried it and it worked perfectly. Thank you so much!!!

 

[boblarson]

tikkat3,
Any chance you tried the code I supplied? acDataErrAdded tells the ComboBox to requery so it is not necessary to do so from the other form.

Cool, more stuff I keep learning - as I always say that there is always something more to learn. Thanks Allan!

 

[RuralGuy]

No problem Bob. I learn stuff from your posts all of the time.