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.
Author |
Topic |
Lin100
Yak Posting Veteran
70 Posts |
Posted - 2006-09-10 : 10:12:31
|
Access 2003 and SQL 2000 ServerRun 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 LarssonHelsingborg, 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.ConnectionStr_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 LarssonHelsingborg, Sweden |
|
|
Lin100
Yak Posting Veteran
70 Posts |
Posted - 2006-09-10 : 19:13:33
|
Hi Peter Larsson. I did a debug.print cnthisconnect.connectionstring, andthe 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.ConnectionSet cmd = New ADODB.CommandSet cmd.ActiveConnection = CurrentProject.Connection cnThisConnect Peter LarssonHelsingborg, 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 LarssonHelsingborg, Sweden |
|
|
|
|
|
|
|