Problem with Not In List event

  • Thread starter Thread starter Darhl Thomason
  • Start date Start date
D

Darhl Thomason

I'm trying to set up the Not In List event for my form. The code I am using
is below:

Private Sub cboFilterItem_NotInList(NewData As String, Response As Integer)
If MsgBox("That item does not appear on the list. Would you like to add a
new item?", vbYesNo, "Item not found") = vbYes Then
DoCmd.GoToRecord , , acNewRec
Else
cboFilterItem = Null
cboFilterItem.SetFocus
End If
End Sub

If I enter an item that is not in the list, I do get the msgbox that asks
yes/no. If I click yes, the msgbox stays there until I hit no, then it
gives me a Run-time error '2105': You can't go to the specified record. If
I hit debug, the "DoCmd.GoToRecord,,acNewRec" line is highlighted in my
code.

If I click no, it tells me "The text you entered isn't an item on the list.
Select an item from the list, or enter text that matches one of the listed
items." This is a system generated message. After I click OK on this box,
it does set cboFilterItem to Null and sets the Focus in that box, I just
don't want that box to appear.

To troubleshoot it, I created a cmdButton (which works fine) that took you
to create a new record and that's where I got the line
"DoCmd.GoToRecord,,acNewRec" from. The only other stuff in the "On_Click"
event was error routine...

Thanks for any help and Merry Christmas!

Darhl
 
The Not In List event is to add an item to the combo box's drop down
selection list, not to add an item to the form. When you answer Yes, the
current record is still dirty with the item you've typed into the combo box.
Access will try to save this and since the item isn't in the list, you get
the message again. When you answer No, you set the combo box to Null, but
you don't Undo, so the record is still dirty but now the new value is Null.
 
Wayne,

So if I set the cboFilterItem.Dirty = False in my code, would that help?
I'm really not familiar with the Dirty property.

Thanks for any help!

Darhl
 
No, the Dirty property is for the form. You can undo the combo box by using

Me.cboFilterItem.Undo

You can undo the current record on the form with

Me.Undo
 
Back
Top