troubleshooting Question

requery combo box

Avatar of nevman
nevman asked on
Microsoft Access
3 Comments1 Solution551 ViewsLast Modified:
On my data entry form I have a combo box, which gives a list of cities.  If the required city is not in the list the user enters it the field.  The not-in-list event triggers and a popup form appears with a text box, which has the new value already entered and two buttons - cancel and save.  When saved, the new city is added to the underlying city table, and then the combo box on the data entry form is requeried with the new value.

That is what should happen, and used to happen under ver 2, but does not happen under ver 2000 after conversion.  The requery fails as I get an error message, saying to save the current field before you run the requery action.

My code  is below:

Not-in-List

Private Sub cboCity_NotInList(NewData As String, Response As Integer)
On Error GoTo cboCity_NotInListError

    ' Add a new location by typing a name in combo box.
    Dim NewCityID As Integer, TruncateName As Integer, Title As String, MsgDialog As Integer
    Const MB_OK = 0
    Const MB_YESNO = 4
    Const MB_YESNOCANCEL = 3
    Const MB_ICONQUESTIONMARK = 32
    Const MB_ICONEXCLAMATION = 64
    Const MB_DEFBUTTON1 = 0, IDYES = 6, IDNO = 7, IDCANCEL = 2
    ' Display message box asking if user wants to add a
    ' new City.
    Title = "City Not In List"
    MsgDialog = MB_YESNOCANCEL + MB_ICONQUESTIONMARK + MB_DEFBUTTON1
    NewCityID = MsgBox("Invalid City.  Do you want to add a new city?", MsgDialog, Title)
    If NewCityID = IDYES Then
        ' Remove new name from combo box so
        ' control can be requeried when user returns to form.
        DoCmd.DoMenuItem A_FORMBAR, A_EDIT, A_UNDOFIELD, , A_MENU_VER20
       
        '  Open frmAddCity.
        DoCmd.OpenForm "frmAddCity", A_NORMAL, , , A_ADD
        Forms![frmAddcity]![txtCityName] = NewData
        '  Continue without displaying default error message.
        Response = DATA_ERRCONTINUE
       
    ElseIf NewCityID = IDCANCEL Then
        DoCmd.DoMenuItem A_FORMBAR, A_EDIT, A_UNDOFIELD
        Response = DATA_ERRCONTINUE
    End If


and the save code from the popup form

Private Sub Form_Unload(Cancel As Integer)
On Error GoTo Form_unLoadError

    Dim mycontrol As Control

    If IsLoaded("frmView") Then
        Set mycontrol = Forms![frmView]![cboCity]
        DoCmd.SelectObject A_FORM, "frmView"
        'MsgBox "the line below fails"
        mycontrol.Requery

    End If

   
Form_unLoadExit:

    Exit Sub

Form_unLoadError:

    MsgBox Error$, 16, prog
    Call Inspector("ErrorHandler.Inspector")
    Resume Form_unLoadExit
   
   
End Sub


if anyone can tell me what needs changing I would be very grateful.

thanks
ASKER CERTIFIED SOLUTION
nico5038

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros