Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

combo box / dialog / re-query combo box action

Posted on 2006-05-09
13
Medium Priority
?
322 Views
Last Modified: 2008-02-01
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
Comment
Question by:Alaska Cowboy
  • 5
  • 4
  • 4
13 Comments
 
LVL 1

Assisted Solution

by:Praetorian269
Praetorian269 earned 200 total points
ID: 16638594
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
 
LVL 1

Expert Comment

by:Praetorian269
ID: 16638611
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
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 16638645
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 1

Expert Comment

by:Praetorian269
ID: 16639535
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
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 16640470
Paretorian, thank you so much for the code and detailed instructions. I will try it out tonight.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16640509
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
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 16640903
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
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16641073
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
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 200 total points
ID: 16641161
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
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 16641555
pdb, thanks for the tip. Will have to try tonight at home.
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 16644298
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
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16644405
Glad I could help.
0
 
LVL 1

Expert Comment

by:Praetorian269
ID: 16648176
Glad to help!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Implementing simple internal controls in the Microsoft Access application.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question