Add item to combo box

  • Thread starter Thread starter Aniko
  • Start date Start date
A

Aniko

Hi,
I use Dev Ashish's code from The Access Web to add new
items to a table through the combo box.
The adding part works well, but when the user clicks No for
choosing not the add the new entry, I get a run-time error 91
Object variable or With block variable not set, pointing to
the rs.Close command.
Can you please help to fix the error?
Thank you,
Aniko
 
Hi,
I use Dev Ashish's code from The Access Web to add new
items to a table through the combo box.
The adding part works well, but when the user clicks No for
choosing not the add the new entry, I get a run-time error 91
Object variable or With block variable not set, pointing to
the rs.Close command.
Can you please help to fix the error?
Thank you,
Aniko
Click to expand...

Please post the code.
 
-----Original Message-----


Please post the code.


.
Click to expand...
Hi John,

There it is:

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

Regards,
Aniko
 
Make a slight modification to the code:

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
'**code steps moved here from end of procedure
rs.Close
Set rs = Nothing
Set db = Nothing

End If

End Sub

--

Ken Snell
<MS ACCESS MVP>
 
Fantastic, thanks Ken.
Regards,
Aniko
-----Original Message-----
Make a slight modification to the code:

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
'**code steps moved here from end of procedure
rs.Close
Set rs = Nothing
Set db = Nothing

End If

End Sub

--

Ken Snell
<MS ACCESS MVP>





.
Click to expand...
 
Back
Top