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).
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).
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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)
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)
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(Ne
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([FIELDNAMEHE
"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
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(Ne
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([FIELDNAMEHE
"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
ASKER
Paretorian, thank you so much for the code and detailed instructions. I will try it out tonight.
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.
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.
ASKER
I tried this, which I found in the last comment in https://www.experts-exchange.com/questions/20296784/Invalid-Character-Msg-when-using-SendKeys.html?query=sendkeys+esc&topics=39
Private Sub cmbCategoryID_NotInList(Ne
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 . . . .
Private Sub cmbCategoryID_NotInList(Ne
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 . . . .
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
pdb, thanks for the tip. Will have to try tonight at home.
ASKER
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.
Praetorian269, I also really appreciate your advice. I will definitely attempt this in my next release.
Glad I could help.
Glad to help!
Change this
Private Sub GangNameID_NotInList(NewDa
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!