Full Version: NotInListEvent
UtterAccess Forums > Microsoft® Access > Access Forms
OK...I am having issues with this NotInList event. Attached is my db. Basically when I type in a name in the customerID box on frmNewTicket, I would like it to open frmNewCustomer when the customer's name is not already in the list so I can add the customer.
irst issue is getting rid of the error message, I want the form to just open without any kind of error. Then it seems like when I add the new customer and close frmNewCustomer it isn't updating my combobox...probably because I didn't do a requery or something. If someone could show me or prod me in the right direction I would be greatful.
Thanks a lot.
R. Hicks
I see no attchment ...
But here is an example that uses a from to enter the value(s) ...
Private Sub YourCombo_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_YourCombo_NotInList
Dim strMsg As String, strTitle As String

strMsg = NewData & " is not in list. Add it?"
strTitle = " Add New Entry ?"

[color="green"]'  Prompt user to verify they wish to add new value[/color]
If MsgBox(strMsg, vbQuestion + vbYesNo, strTitle) = vbYes Then
  [color="green"]' Open your form to add new entry[/color]
  DoCmd.OpenForm "YourFormName", , , , acFormAdd, acDialog
  [color="green"]'  You can add NewData as OpenArgs in the line
  '  above to pass to new entry to opening form
  '  Set Response argument to indicate that data is being added[/color]
  Response = acDataErrAdded
  [color="green"]'  If user chooses Cancel, suppress error message[/color]
  Response = acDataErrContinue
End If

  Exit Sub
  MsgBox Err.Number & ":" & Err.Description
  Resume Exit_YourCombo_NotInList
End Sub

Mr. Hicks,
Always a pleasure to have you jump in on one of my questions..I know I am going to get a good answer. Forgot to darn attach my db so here it is. In the meantime I will mess around with what you gave me and see if I can make it work.
Not everything working except for the name I just entered in the cbo box being added automatically to the CustomerName control on frmNewCustomer when it opens...not sure how to use the openargs bit and Access help wasn't to clear on using it in this case..it showed a open to and findfirst example.
Thanks in advance for the help. The code above worked great.
Add it as the last arguement, after acdialog
.., acdialog, NewData
Then in the other forms on load/on open:
if not isnull(me.openargs) then
end if
Ah ha..so i had a grip on the openargs in the DoCmd but not in the on open of the other form, thanks Roy..this must be your week to help me out...like Shift-CTRL..that one busted my chops...LOL
You're welcome - the Shift/Control - use that a lot in Excel (and Ctrl +/Ctrl -, check those out wink.gif )
Can you use the above code to just add to the list on OK from message box without using the form? The reason I ask is I have a table with two fields tblTaskingLevel(LevelID and TaskingLevel). When a user enters a new value on frmZZZ in the TaskingLevel combobox and te value is not in the list I hate to open a form after the message box just to say yes again that they want to add it, not a real big deal and I could just set some code to open the form, defaul the openargs into the box and close the form without the user doing anything but still would be cleaner if and OK click on the MsgBox would just add the new value. I will continue to play with the code but VBA is not my strong point..but getting better thanks to help form you and others.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.