recordset issue using Access's ADP database

bleary
10-13-2004, 03:37 PM
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

NEOLLE
10-14-2004, 01:57 AM
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. :)

NEOLLE
10-14-2004, 02:08 AM
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

Granty
10-14-2004, 06:33 AM
Hi,

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

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

Shurik12
10-14-2004, 08:31 AM
IMHO the problem here's that you're referencing BOTH ADO and DAO in your project.
Uncheck ADO and try to run the code.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum