Run Time Error '91'. Object Variable or With Block

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Run Time Error '91'. Object Variable or With Block

Author

Topic

Lin100
Yak Posting Veteran

70 Posts

Posted - 2006-09-10 : 10:12:31

Access 2003 and SQL 2000 Server

Run Time Error '91'. Object Variable or With Block Variable Not Set.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Private Sub Form_Load()
Dim Str_SQL As String
Dim Number_Of_Records As Integer
Dim RecordSet_Meter As New ADODB.Recordset
Dim Recordset_Meter_Query As ADODB.Recordset
Dim cmd As ADODB.Command
Dim cnThisConnect As ADODB.Connection

Set cnThisConnect = CurrentProject.Connection
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection

Str_SQL = "SELECT * FROM Meter;"

RecordSet_Meter.Open Str_SQL, cnThisConnect, _
adOpenKeyset, adLockOptimistic, adCmdText

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Str_SQL = "SELECT * FROM Meter WHERE " _
& "Meter.Department_Name = '" & Forms!Selector!Dept & "' AND " _
& "Meter.SONumber = " & Forms!Selector!so & " AND " _
& "Meter.ItemNumber = '" & Forms!Selector!Item & " ' AND " _
& "Meter.Section_Number = '" & Forms!Selector!Sectionno & " '; "

Debug.Print Str_SQL

Recordset_Meter_Query.Open Str_SQL, cnThisConnect, _
adOpenKeyset, adLockOptimistic, adCmdText

Number_Of_Records = Recordset_Meter_Query.RecordCount

If Number_Of_Records = 0 Then
RecordSet_Meter.addnew
RecordSet_Meter!Department_Name = Public_Department_Name
RecordSet_Meter!SONumber = Public_SO_Number
RecordSet_Meter!ItemNumber = Public_Item_Number
RecordSet_Meter!Section_Number = Public_Section_Number
RecordSet_Meter.Update
Me.Requery
End If

End Sub

Lin100
Yak Posting Veteran

70 Posts

Posted - 2006-09-10 : 11:41:09

The error is on the code below. This is where Access VBA
highlighted the code in yellow .

Recordset_Meter_Query.Open Str_SQL, cnThisConnect, _
adOpenKeyset, adLockOptimistic, adCmdText

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-10 : 13:45:52

Don't you have to open a connection first?

Peter Larsson
Helsingborg, Sweden

Lin100
Yak Posting Veteran

70 Posts

Posted - 2006-09-10 : 14:32:16

Hi again Peter Larsson.

I looked at the code and it looks like open a connection are the one below.

Set cmd.ActiveConnection = CurrentProject.Connection

Str_SQL = "SELECT * FROM Meter;"

RecordSet_Meter.Open Str_SQL, cnThisConnect, _
adOpenKeyset, adLockOptimistic, adCmdText

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-10 : 14:54:00

Do a debug.print cnthisconnect.connectionstring and see what it gives.

Peter Larsson
Helsingborg, Sweden

Lin100
Yak Posting Veteran

70 Posts

Posted - 2006-09-10 : 19:13:33

Hi Peter Larsson. I did a debug.print cnthisconnect.connectionstring, and
the result is shown below.

Provider=Microsoft.Jet.OLEDB.4.0;
User ID=Admin;
Data Source=D:\Documents and Settings\Administrator\Desktop\Inspection_Reporting_Using_ADO_And_ODBC.mdb;
Mode=Share Deny None;Extended Properties="";
Jet OLEDB:System database=D:\Documents and Settings\Administrator\Application Data\Microsoft\Access\System.mdw;
Jet OLEDB:Registry Path=SOFTWARE\Microsoft\Office\10.0\Access\Jet\4.0;
Jet OLEDB:Database Password="";
Jet OLEDB:Engine Type=5;
Jet OLEDB:Database Locking Mode=1;
Jet OLEDB:Global Partial Bulk Ops=2;
Jet OLEDB:Global Bulk Transactions=1;
Jet OLEDB:New Database Password="";
Jet OLEDB:Create System Database=False;
Jet OLEDB:Encrypt Database=False;
Jet OLEDB:Don't Copy Locale on Compact=False;
Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 01:01:14

Why do you create a connection variable when you are not using it?

Set cnThisConnect = CurrentProject.Connection
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection cnThisConnect

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 01:05:49

Also DIM recordset_meter_query as NEW ADODB.Recorsdset, just as you do with recordset_meter variable.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources