Douglas Steele / Dev Ashish combobox NotInList code

M

Matt K.

Hi Douglas and/or Dev,
I found a thread that was helpful to what I was looking for - I wanted to be
able to add an item to the underlying recordsource of a combobox thru the
NotInList Event of the combobox. In the thread, Douglas answered a similar
question with:

Check http://www.mvps.org/access/forms/frm0015.htm at "The Access Web"

When I went there this is what I found:
'************ Code Start **********
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Sub cbxAEName_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available AE Name " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the current
DLSAF?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type
it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblAE", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!AEName = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
'*********** Code End **************

First, I changed the control, table, and field names to mine. When I tried
adding a new entry into the cbobox, but then clicked 'No' to the message box,
I would get a Run-time error '91': Object variable or With block variable not
set.

By moving the
rs.Close line of code immediately under the
rs.Update line of code
the event works like a charm.

I just wanted to pass this along in hopes it might help someone else
sometime. Thanks to both of you for contributing to this forum!
 
D

Douglas J. Steele

Yeah, Dev should have put the Close inside the If loop, or else put On Error
Resume Next before those statements.

I'll pass this on to Arvin, who maintains the site now.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

NotInList updated but not shown on form until close and reopen 12
ComboBox issue 4
"Not in list" event code 2
"Not in list" event code 2
Combo NotInList 2
Not in list 8
Not in List Error 4
NotInList not firing ? 2

Top