recordset issue using Access's ADP database

Hello all. I am not sure if I am in the right forum if not please direct me to the correct one....but here it goes:

I am working on ma ADP DataBase for my company and attempting to use the "Recordset" keyword. When I run the code through a Combo box I get an error:
"Run-time error '91': Object varible or With block varible not set"

the code breaks at the "*" below. Can anyone help me with this.....I am going crazy and I am sure it is something simple. I have checked the referances and I have ADO 2.1 and DAO 3.6 enabled as well as access 11.0 Object libraries.


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available phone number " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add the new Phone Number to the current system?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add or No to re-type it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new number?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
**** Set rs = db.OpenRecordset("tbl_Comm_Number", dbOpenDynaset) ****
On Error Resume Next
rs.AddNew
rs!phone_number = 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

'*********** Code End **************

'****** Code Start *********
'Move to the record selected in the control
Me.RecordsetClone.FindFirst "[phone_number] = " & Me![Find_phone_number]
Me.Bookmark = Me.RecordsetClone.Bookmark
'******* Code End *********

End Sub

Hello bleary,
"Run-time error '91': Object varible or With block variable not set"
Suggests that you initialise your Recordset Object first.

Set rs = New DAO.RecordSet

I dont know to what condition are you in why you use DAO instead of ADO. DAO technology is already obsolete. :)

Hi bleary,
I made you a code. :D
You are welcome to do editing to fit your needs. Its patterned from the code you gave. :)


Private Sub Proc_Sample()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection

Dim strConnectionString As String
Dim strMsg As String

strConnectionString = "YOUR DATABASE CONNECTION"
cn.Open strConnectionString

strMsg = "'" & NewData & "' is not an available phone number " & vbCrLf & vbCrLf & _
"Do you want to add the new Phone Number to the current system?" & _
vbCrLf & vbCrLf & "Click Yes to add or No to re-type it."



If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new number?") = vbNo Then
Response = acDataErrContinue
Else
'On Error Resume Next <-- as much as possible avoid this. Create an error handler instead.
cn.BeginTrans
cn.Execute _
"INSERT INTO tbl_Comm_Number (phone_number) VALUES ('" & NewData & "')"
cn.CommitTrans

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

cn.Close

Set cn = Nothing
End Sub

Hi,

instead of declaring your DAO.database and using set db = currentdb try just using:

Set rs = CurrentDB.OpenRecordset("tbl_Comm_Number", dbOpenDynaset)

IMHO the problem here's that you're referencing BOTH ADO and DAO in your project.
Uncheck ADO and try to run the code.