91: Object variable or with block variable not set

[mshakeelattari]

I am using a split database. In the front end I am using the following code:

Public Function RankList(ByVal TableName As String, _
                         ByVal Grp1Field As String, _
                         ByVal ValueField As String, _
                         Optional ByVal Grp2Field As String)
'-----------------------------------------------------------------
'Preparing Rank List
'Author : a.p.r.pillai
'Date   : August 2011
'Rights : All Rights Reserved by www.msaccesstips.com
'Remarks: Free to use in your Projects
'-----------------------------------------------------------------
'Parameter List:
'TableName  : Source Data Table
'Grp1Field  : Category Group to Sort on
'ValueField : On which to determine the Rank Order
'Grp2Field  : Sorted on for values with the same rank number
'-----------------------------------------------------------------
Dim db As Database, rst As Recordset, curntValue, prevValue
Dim srlRank As Byte, curntGrp1, prevGrp1
Dim prevGrp2, curntGrp2
Dim fld As Field, tbldef As TableDef, idx As Index
Dim FieldType As Integer

On Error Resume Next

Set db = CurrentDb
Set rst = db.OpenRecordset(TableName, dbOpenTable)

'Check for presence of Table Index "MyIndex"
'if not found then create
rst.Index = "MyIndex"

If Err > 0 Then
   Err.Clear
   On Error GoTo RankList_Err

Set tbldef = db.TableDefs(TableName)
Set idx = tbldef.CreateIndex("MyIndex")

FieldType = rst.Fields(Grp1Field).Type
Set fld = tbldef.CreateField(Grp1Field, FieldType)
idx.Fields.Append fld

FieldType = rst.Fields(ValueField).Type
Set fld = tbldef.CreateField(ValueField, FieldType)
fld.Attributes = dbDescending ' Line not required for sorting in Ascending
idx.Fields.Append fld

FieldType = rst.Fields(Grp2Field).Type
Set fld = tbldef.CreateField(Grp2Field, FieldType)
idx.Fields.Append fld

rst.Close

tbldef.Indexes.Append idx
tbldef.Indexes.Refresh
Set rst = db.OpenRecordset(TableName, dbOpenTable)
rst.Index = "MyIndex"
End If

curntGrp1 = rst.Fields(Grp1Field)
prevGrp1 = curntGrp1
curntValue = rst.Fields(ValueField).value
prevValue = curntValue

Do While Not rst.EOF
     srlRank = 1
     Do While (curntGrp1 = prevGrp1) And Not rst.EOF
       If curntValue < prevValue Then
          srlRank = srlRank + 1
       End If
          rst.Edit
          rst![Rank] = srlRank
          rst.Update
          rst.MoveNext
          If Not rst.EOF Then
             curntGrp1 = rst.Fields(Grp1Field)
             prevValue = curntValue
             curntValue = rst.Fields(ValueField).value
          End If
     Loop
     prevGrp1 = curntGrp1
     prevValue = curntValue
Loop
rst.Close
'Delete the Temporary Index
tbldef.Indexes.Delete "MyIndex"
tbldef.Indexes.Refresh

Set rst = Nothing
Set db = Nothing

RankList_Exit:
Exit Function

RankList_Err:
MsgBox Err & " : " & Err.Description, , "RankList()"
Resume RankList_Exit

End Function

When I use

Public Function TestRank()

  Call RankList("StudentsExams", "ClassID", "Result", "PaperID")

End Function

where StudentsExams is linked table
I get the following error:

91: Object variable or with block variable not set

Any help please?

[PhilOfWalton]

Standard way of sorting code problems:-
Put a halt on the first line of your Function RankList

Then step through line by line till you see where the error occurs.

Phil

[NeoPa]

It looks like your code hasn't been compiled before you tried to use it. I've lost count of the number of times I've made this point. It's crazy to ask for help with code you haven't even compiled yet.

1. Compile (with Option Explicit set).
2. Test and debug to whatever level you can manage.
3. Copy/Paste the code into your post.

There's really very little point asking for help with code that the compiler tells you all about anyway.

[zmbd]

just to add to the fire you should start here:
home > topics > microsoft access / vba > insights > debugging in vba

[mshakeelattari]

Compiled (with option explicit set) but no error shown there.

[zmbd]

Insert on line 23 of your function the stop command.
When execution hits this line, the Debugger will start
Press [F8] to VERY SLOWLY step thru your code.
At some point the error will pop-up, make note of which line of code this occurs on. At this point you should be able to determine Object isn't being properly set.

>> We have no way of doing this for you and until you complete this step to determine which line of code is failing there is very little we can do.

[mshakeelattari]

Line no. 27

Set rst = db.OpenRecordset(TableName, dbOpenTable)

Run-time error 3219
Invalid operation

[zmbd]

most likely the variable "TableName" is null value
Just above this line (line 27 in the posted code) insert
Stop
Debug.print TableName

Delete the previously inserted Stop at line 23

Press <Ctrl><g> to open the immediate pane

Run your code

At the Stop the IDE should open, [F8] thru the debug code. You should see the value of the "TableName" variable print to the immediate pane - if as I suspect this value is either null or the table-name is misspelled then this would be the start of your issues.
Given that the variable is passed with the function call you'll need to go back to the calling routine to find out what is happening - check to be sure that the passed table-name is spelled correctly!

BTW: All of the highlighted variables below are being defined as data type Variant. The data types must be explicitly declared in code starting with ACC2003 or they are implicitly assigned the Variant data type - this will lead to problems at some point in your coding career.

   Dim db As Database, rst As Recordset, [iCODE]curntValue[/iCODE], [iCODE]prevValue[/iCODE]  
   Dim srlRank As Byte, [iCODE]curntGrp1[/iCODE], [iCODE]prevGrp1[/iCODE]  
   Dim [iCODE]prevGrp2[/iCODE], [iCODE]curntGrp2
[/iCODE]

Finally you should get into the habit of declaring your database variables as DAO

Dim DB As DAO.Database
Dim RST As DAO.Recordset
etc...

I am glad to see that you are using the CurrentDB correctly by setting it to an object variable. Not many people understand CurrentDBand how it works.

[mshakeelattari]

The debug.print prints the correct table name but again in line 27 (as posted in code), Run-time error 3219 Invalid operation occurs. The table is in backend database and the vba code is being run from the front end. Is this causing any issue?

[zmbd]

Yes having the table in the backend will prevent you from using the dbOpentable:
http://allenbrowne.com/ser-29.html - section 2

There are workarounds; however, best to move to the dbOpenDynaset which will require re-writes for and "seek" and other table specific recordset actions.

My apologies for missing that you had split the database or I would have mentioned that in my prior post

[mshakeelattari]

When I replaced dbOpentable with dbOpenDynaset in line no. 27 in the code posted above, a new error appears in the line no. 31

rst.Index = "MyIndex"

Run-time error 3251: Operation is not supported for this type of object.


3057: Operation not supported on linked tables.

[zmbd]

mshakeelattari:
recordset.index is a table method
Recordset.Index Property (DAO)
Did you follow the link in my last post - that will explain why this failed? <!!>

You CANNOT use the table methods such as Seek, Index, etc... on linked tables.

When you open the record set on the linked table you will need to use one of the "Find" methods (FindFirst, FindLast, FindNext, FindPrevious)

If you insist on using the index/seek then you have to reference the backend database directly:

  Dim zWS as DAO.Workspace
  Dim zDB as DAO.Database
  Dim zRS as DAO.Recordset
'
'Create a new workspace
  set zWS = DBEngine(0)
'
'Explicitly open the backend database in the background
  Set zDB = zWS.OpenDatabase("Path to your backend here")
'
'now setup the reference to the table
  Set zRS = zDB.OpenRecordset("YourTableName",dbOpenTable)
'
'manipulate your recordset
  With zRS
    '... your code here
  End With
'
'clean up by making sure your record set, the second database instance are both closed and the memory correctly released.
  if not zRS is nothing then
    zRS.Close
    set zRS = nothing
  End IF
  if not zDB is Nothing  then
    'Because we OPENED this instance
       'of the database we should close it
       ' - DO NOT DO THIS to the CurrentDB or you WILL have issues
    zDB.Close
    set zDB = Nothing
  End If
  if not zWS is Nothing then set zWS = Nothing

[NeoPa]

Mshakeelattari:
Compiled (with option explicit set) but no error shown there.

Well done.

This turned out to be a run-time error. As Z says - you can't use certain index-related operations on linked tables. It looks like Z has completely explained why and what you need to change to avoid these errors.

It's well worth understanding what can and cannot be done with linked tables within Access. You're sure to come across similar issues again in future. When I first hit this problem I read up on this and I haven't had such problems again.