Stop Not In The List message

[jerseyboy]

I have a combo box set to fire off the 'not in the list' event. I would like to tailor the message to my own (and have been able to accomplish that) but the the wizard commences to readdress the problem when the user addresses my message box.
How do I supress the Access Message? I tried an error handling event to see if it was an error message and no luck!
Thanks for any help.
JerseyBoy
Remember: self-praise is no recommendation

 

[jimmythegeek]

If you use the NotInList Event, you have to limit the combo box to the values on the list, so this only give you 2 options when a user enters something not in the list; Add it to the list, or make them choose again. Following is a typical NotInList event. This example uses an Insert to put the value into a table, If you use a value list, you would replace the Insert line with (ctrl.RowSource = ctrl.RowSource & ";" & NewData)


==========
Private Sub YourField_NotInList(NewData As String, Response As Integer)
Dim ctrl As Control

Set ctrl = Me!YourField
If MsgBox("Do you wish to add " & NewData & " to the list?", vbYesNo + vbQuestion, " Continue?&quot = vbYes Then
Response = acDataErrAdded
CurrentDb.Execute "Insert into YourTable (YourTableField) values ('" & NewData & "')"
Else
Response = acDataErrContinue
ctrl.Undo
End If
End Sub
========== Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[jerseyboy]

I appreciate your response. I agree that this is a very acceptable way of responding, but I should have been more descriptive in my circumstance.
In my case, the dropdown was intended to pick an already established record (in another table) and tie it to this persons affiliation list. If the affiliation did not exist, I wanted the user to be able to add a new record.

Normally I do this via a double click on the combo box, but this set of users may not be keen to this method of updating records, so I was trying to make it easier to trigger the add.

Thanks for your help in this post and others that you have answered for me in the past...what I would do to serve an apprenticeship under your command!

JerseyBoy
Remember: self-praise is no recommendation

 

[jimmythegeek]

If I understand correctly, that is exactly what the code I posted does. If the user enters a value not on the list, the code will ask them if they want to add it. If they do, it inserts the value they entered into any table (YourTable in the code), and will refresh the list with their value.

Am I misunderstanding what you are trying to do? Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[jerseyboy]

A little...let me try to explain in further detail using my current project.

The database is for a non-profit historical society and is primarily geared toward keeping track of the contact for the organization.

They have a contact and that contact can be affiliated with the database owner in many ways. For example, they may be a school administrator, a business owner, a volunteer or a government official.

The premise is that once that business or school is already entered, all you have to do is link this particular contact with that business or school.

What I have done is offer the users a form that allows them to associate a contact with a affiliate.

If the affiliate exists, the table stores their key so that mail merge and other resource can identify who is connected to whom.

If however, the affiliate is brand new, when the user tries to type the new name in the box I want to take them to the affiliate form (open form...acNew) and let them add ALL of the information required.

When that form is completed and saved, it will return the to the previous form (and select that affilitate for them).

Essentially I am updating the combo box with a new record, BUT I want to do more than just add one piece of information about that record.

I know how to do all of that (and have many times under different circumstances) but I normally tell the users..
"If the value isn't in the box, "double click the control" and it will open a form to let you add a new record for that field"

What I am trying to do is eliminate the need to double click the combo box and automatically offer the user a yes/no question base on the fact that they typed something 'not in the list'

Here is a sample of the code that I am using:
'''''''''''''''''''''''''''''''''''''''''''''

Private Sub cboName_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cboName_NotInList


Dim intResponse As Integer

intResponse = MsgBox("You have entered a value that is not recognized. Do you want to add a new value?" & Chr(13) & _
"If you feel that the value may exist answer NO, and search again, otherwise press YES." & Chr(13) & _
"NOTE: Duplicate values will not be permitted.", vbQuestion + vbYesNo, "Unrecognized Name&quot

Select Case intResponse

Case 6 'vbyes

'open the form (typically a pop up) and let them add a new affiliate

Case Else 'vbno

Exit Sub

End Select

Err_cboName_NotInList:

Select Case Err.Number

Case 0
Err.Clear

Case Else

MsgBox Err.Number & " " & Err.Description

End Select

''''''''''''''''''''''''''''''''''''''''''''''''''''
THis part all works fine, but when the user chooses an answer to my inquiry the Access message box follows and GUMS UP THE WORKS!
Hence my original question: Is there a way to prevent the default message from appearing?

by the way... what is err.number=0 mean?

Hope this makes more sense of what I am trying to do, and thanks again for the help.

JerseyBoy
Remember: self-praise is no recommendation