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!
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!