• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

combo box / dialog / re-query combo box action

I have a combo box where the user can double-click to go to the lookup table maintenance, calling the lookup maintenance screen in dialog mode.

While on the lookup maintenance screen, the user can add some new values, then return to the main screen, at which time the screen the screen (being in the dialog mode and still has control of events), re-queries the combo box so the newly added values are now available in the drop-down.

But I get an error in this situation: Say the user, prior to double-clicking to go to the lookup maintenance screen, types some values in the combo box that aren't in the combo box's list. Upon return from the lookup maintenance screen, an error occurs due to the errant values typed in the combo box. Access says "you must save the current field before you run the re-query action."

So how do I handle this? The user needs to type in the combo box (so existing values pop-up), but I need to account for them typing in something that's not in the list.

Note: If you press Esc after typing something into a list box, your entry is cleared. Then if you press Esc again, the entire record is un-done.

So perhaps I just need to know how to say "Esc" on vba. "undo" didn't work, it still said "The text you entered isn't an item in the list".

I then tried to "Limit to List" = No, but that didn't work (the first visible column must equal the bound column).

0
Alaska Cowboy
Asked:
Alaska Cowboy
  • 5
  • 4
  • 4
2 Solutions
 
Praetorian269Commented:
Here is how I accomplish the NotInList event.  Just change what is in ALL CAPS!!!

Private Sub GangNameID_NotInList(NewData As String, Response As Integer)
    Dim intAnswer As Integer
    Dim strSQL As String
    intAnswer = MsgBox("WHATEVER THE NAME " & Chr(34) & NewData & _
        Chr(34) & " is not currently listed." & vbCrLf & _
        "Would you like to add it to the list now?" _
        , vbQuestion + vbYesNo, "NAME OF MESSAGE WINDOW")
    If intAnswer = vbYes Then
        strSQL = "INSERT INTO TABLENAMEHERE([FIELDNAMEHERE]) " & _
                 "VALUES ('" & NewData & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        MsgBox "The new name has been added to the list." _
            , vbInformation, "NAME OF MESSAGE WINDOW"
        Response = acDataErrAdded
    Else
        MsgBox "Please choose a WHATEVER NAME from the list." _
            , vbInformation, "NAME MESSAGE WINDOW"
        Response = acDataErrContinue
    End If

End Sub
0
 
Praetorian269Commented:
For got to change the event title

Change this
Private Sub GangNameID_NotInList(NewData As String, Response As Integer)

To your field name
Private Sub YOUR FILED NAME_NotInList(NewData As String, Response As Integer)

If you click the expression builder for your NotInList it will change it for you and you will not have to change anything.

Good luck!
0
 
Alaska CowboyAuthor Commented:
That's pretty slick and I think I will mark this post for Release 2.0 of my system.

When does NotInListFire? For now, if NotInList happens, I just want to clear the field (and perhaps column(0) which has the id of the lookup value) already has something in it.

(Off to work now, I'll check back in an hour or so)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Praetorian269Commented:
In your form design view.  Right click the field you want to fire this code for then select properties.  Then select the event tab.  Under the event tab click on NOTINLIST file and click the dropdown to select the dropdown called [event procedure].  Then select the 3 dotts to the right of the field of NOTINLIST.

This is where you will paste the code.  Make sure to edit the names in CAPS for it to run correctly.  Place between the
Private Sub YOURCOMBONAME_NotInList(NewData As String, Response As Integer)
and
End Sub

Dim intAnswer As Integer
    Dim strSQL As String
    intAnswer = MsgBox("WHATEVER THE NAME " & Chr(34) & NewData & _
        Chr(34) & " is not currently listed." & vbCrLf & _
        "Would you like to add it to the list now?" _
        , vbQuestion + vbYesNo, "NAME OF MESSAGE WINDOW")
    If intAnswer = vbYes Then
        strSQL = "INSERT INTO TABLENAMEHERE([FIELDNAMEHERE]) " & _
                 "VALUES ('" & NewData & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        MsgBox "The new name has been added to the list." _
            , vbInformation, "NAME OF MESSAGE WINDOW"
        Response = acDataErrAdded
    Else
        MsgBox "Please choose a WHATEVER NAME from the list." _
            , vbInformation, "NAME MESSAGE WINDOW"
        Response = acDataErrContinue
    End If
0
 
Alaska CowboyAuthor Commented:
Paretorian, thank you so much for the code and detailed instructions. I will try it out tonight.
0
 
puppydogbuddyCommented:
Hi Bill,
The Not in list event is the way to go. For now, try :

DoCmd.CancelEvent

and see if that works like an ESC key in this instance, so that you can solve your immediate problem.
0
 
Alaska CowboyAuthor Commented:
I tried this, which I found in the last comment in http://www.experts-exchange.com/Databases/MS_Access/Q_20296784.html?query=sendkeys+esc&topics=39

Private Sub cmbCategoryID_NotInList(NewData As String, Response As Integer)
   
    If MsgBox("The value you entered is not in the list.", vbOKOnly) = vbOK Then
        Response = acDataErrContinue
        Me.cmbCategoryID.Undo
    End If
End Sub

The above code was acceptable, it resulted in my error message ("The value you entered is not . . . ") and then the "Esc" key functionality worked.

But if I enter an invalid value and then double-click to the lookup maintenance screen, upon return (and continuation subsequent to the dialog), the NotInList does NOT fire, BUT it does tell me "you must save the current field before you run the re-query action."

Perhaps I can trap the error message (# 2118) and work around that? This is getting convoluted . . . .


0
 
puppydogbuddyCommented:
Bill,
Try this in your double click code:

If Err.Number = 2118 then
   'bypass requerying  for only an inquiry
   DoCmd.CancelEvent
End If


or this:
If Err.Number = 2118 then
   'bypass requerying  for only an inquiry
   Err.Clear
   Resume Next
End If
0
 
puppydogbuddyCommented:
correction: has to go with your error handler in the double click sub

Exit_Continue
   Exit Sub
Error_Routine:
   If Err.Number = 2118 then
       'bypass requerying  for only an inquiry
        Err.Clear
        Resume
  Else    
        MsgBox "Error# " & Err.Number & " " & Err.Description
        Resume Exit_Continue
  End If
0
 
Alaska CowboyAuthor Commented:
pdb, thanks for the tip. Will have to try tonight at home.
0
 
Alaska CowboyAuthor Commented:
pdb, worked like a champ ! This has been such a thorn in my side. First I couldn't replicate the error, so that was annoying. Finally I figured out what caused the error but couldn't fix it. Now I am all set.

Praetorian269, I also really appreciate your advice. I will definitely attempt this in my next release.
0
 
puppydogbuddyCommented:
Glad I could help.
0
 
Praetorian269Commented:
Glad to help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 5
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now