Please stop Not In List error message!!!

[Sullaway]

I'm using the following code in a Not In List on cboReferralID combo box. If the name is not on the combo list then I want frmReferral to open, which it does, take the New Data from the combo box and put it in the name fields, which it does, then update the combo box list with new record, which it does but what I haven't been able to keep it from doing is firing off the Not In List Access message. Could someone please lend a hand and help out with this problem. Many thanks ahead of time.


Dim varNData As Variant
varNData = NewData
Dim intmsgresp As Integer
intmsgresp = MsgBox("The Referral you entered is not on our list." & vbCrLf & vbCrLf & "Would you like to add it?", _
vbYesNo + vbQuestion, "Referral Is Not On Our List&quot
If intmsgresp = vbYes Then
DoCmd.OpenForm "frmReferral", acNormal, , , acFormAdd, acDialog, varNData
Me.cboReferralID.Undo




Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

 

[tlbroadbent]

In the properties for the combo box set "Limit to List" = No. "Limit to List" is found on the data tab of the properties. Terry

X-) "I don't have a solution, but I admire your problem."

 

[Sullaway]

Terry,

I don't understand how changing Limit to List to no would help me. First I'm trying to make sure that if the referral name is not on the list that it gets added to the Referral table. Two if I try changing the Limit to List to no then I have to use the ReferralID field, because I'm using it to find the records in Referral table, and I don't want the users to see the ID field, I'm wanting them to see the Referral Names. Could you explain a little better how this would help me or what I'm missing.

Thanks,
Shane

 

[tlbroadbent]

I thought you asked how to stop the message from Access. Setting "Limit to List" to "No" would do that, leaving it up to you to handle the condition. I thought you would, for example, validate the user entry in the "After Update" event and take appropriate action.

Apparently, I didn't fully understand your question. You want "On Not in List" event to fire but desire to suppress the Access message and display your own message. This a better solution than I proposed. Therefore, do the following.

Display your custom dialog box asking if the user wants to add the new entry. If the response is Yes, add the new entry to the list and set the Response argument to acDataErrAdded. If the response is No, set the Response argument to acDataErrContinue. Your code in the "On Not in List" event would then look like this:

.
.
.
intmsgresp = MsgBox("The Referral you entered is not on our list." & vbCrLf & vbCrLf & "Would you like to add it?", _
vbYesNo + vbQuestion, "Referral Is Not On Our List&quot
If intmsgresp = vbYes Then
Response = acDataErrAdded
DoCmd.OpenForm "frmReferral", acNormal, , , acFormAdd
.
.
.
Else
Response = acDataErrContinue
end if
Terry

X-) "I don't have a solution, but I admire your problem."

 

[Sullaway]

Terry,

Thanks for your help. Message is still firing though. You know this is crazy. I am using the exact same code that I posted with my first message in a different combo box on a different form and it works fine (it did this same this at first). I have rearranged this code so many different ways and the error message just won't go away. I just tried your code and got the same results. Maybe I should do it the want you suggested the first time.

Thanks big time for taking the time to help.
Shane

 

[tlbroadbent]

It is strange that setting Response doesn't stop the message. Here is one note from help that may be useful.

acDataErrAdded: Doesn't display a message to the user but enables you to add the entry to the combo box list in the NotInList event procedure. After the entry is added, Microsoft Access updates the list by requerying the combo box. Microsoft Access then rechecks the string against the combo box list, and saves the value in the NewData argument in the field the combo box is bound to. If the string is not in the list, then Microsoft Access displays an error message.

Is it possible that the record was not inserted before exiting the "On Not in List" event? Terry

X-) "I don't have a solution, but I admire your problem."