Combo Box Adding to List--Error 91

M

maura

I have researched Adding to List and from the Access Web got this code and
modified it for my purposes, however I am getting an error 91, when I debug
it comes back to

rs.close ((this is what it is saying is the problem))
********
' Code Courtesy of
' Dev Ashish
********
Private Sub combo82_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 Color " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to associate the NEW COLOR to the current
COLOR LIST?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to ADD NEW COLOR 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("tblColor", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!Color = 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
_____________________________

Any suggestions as to why I am getting this error??? Thanks, m.
 
C

Carl Rapson

maura said:
I have researched Adding to List and from the Access Web got this code and
modified it for my purposes, however I am getting an error 91, when I
debug
it comes back to

rs.close ((this is what it is saying is the problem))
********
' Code Courtesy of
' Dev Ashish
********
Private Sub combo82_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 Color " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to associate the NEW COLOR to the
current
COLOR LIST?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to ADD NEW COLOR 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("tblColor", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!Color = 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
_____________________________

Any suggestions as to why I am getting this error??? Thanks, m.

Under what conditions does the error occur? Do you see the "An error
occurred. Please try again." message? It sounds like your recordset is not
being opened, which indicates a problem with the OpenRecordset call. Since
you're opening a table directly, have you tried dbOpenTable instead?

Also a note: you should move the following lines

rs.Close
Set rs = Nothing
Set db = Nothing

to before the final End If, because the recordset will never be opened if
you answer "No" to the "Add new name?" messagebox.

Carl Rapson
 

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


Top