requerying combobox after NotInList event

[Sketchin]

Hello,

I have a form with a subform that contains a combobox where I choose companies to add to a project that is on the main form. If the user types in a company name that is not in the database, I run code on the NotInList event that passes the company name using openargs to a company entry form.

After this form is closed, I return to the subform to choose the company name that I just added, only it is not there because I can't figure out where to automatically requery this combobox. My duct tape solution that's working, is a command button that runs this code when clicked:

Forms!frmProjects_Detail_CSS!ASP_Project.Form!cbocompany.Requery

I have tried putting this code in the OnCurrent, OnFocus, Onclick, OnEnter....etc on the subform and on the combobox itself. So far the only way it requeries is if I use the command button.

Any ideas? Please let me know if you want to see the NotInList code, and the code on my company entry form.

 

[vbaInet]

After this form is closed, ...

Requery it On Close.

 

[pbaldy]

I would expect this to work in the not in list event:

Response = acDataErrAdded

I open the other form in dialog mode, hide it when done, and test it with IsLoaded in the not in list event.

 

[Sketchin]

I would expect this to work in the not in list event:

Response = acDataErrAdded

I open the other form in dialog mode, hide it when done, and test it with IsLoaded in the not in list event.

I would have expected that as well. When I had that code, I would get "the text you entered is not an item in the list" error. This is my not in list code:

Private Sub cboCompany_NotInList(NewData As String, response As Integer)

   On Error GoTo cboCompany_NotInList_Error

'Set default response so that access doesnt warn you that the company is not in the combo box list
response = acDataErrContinue

Dim LResponse As Integer
' Setup a SQL string so user can insert company name and flag ASP = true in tblcompanies
Dim strSQL As String

LResponse = MsgBox("The company, " & NewData & " , is not in the database.  Do you wish to add this company to the database as an ASP?", vbYesNo, "Continue")
    ' If user answers yes, open company details form to a new record
    If LResponse = vbYes Then

        Me!cbocompany.Undo
        DoCmd.OpenForm "frmCompanyDetail", acNormal, , , acFormEdit, , OpenArgs:="FrmProject_Detail_CSS|" & NewData

[COLOR="Red"]' This gives "the text you entered is not an item in the list" error.
response = acdataerradded[/COLOR] 

    Else
    ' If user answers no, set focus back to the combo box so they can make a different choice
       Me.cbocompany.SetFocus

   

End If

   On Error GoTo 0
   Exit Sub

cboCompany_NotInList_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure cboCompany_NotInList of VBA Document Form_subfrmASP_Project"
End Sub

 

[Sketchin]

Requery it On Close.

You Win!

I feel like that is the only thing I didn't try.

 

[pbaldy]

I would have expected that as well. When I had that code, I would get "the text you entered is not an item in the list" error.

Probably because you didn't open the form in dialog mode, and the new value hasn't been added at that point. When you open the form in dialog mode, code stops until the form is closed or hidden, then finishes.