Object Variable or with Block Variable Not Set

[TJC]

I recently had an Access 97 database converted to ADP. I can't get any of the forms to work. I've updated my references and added DAO to a statement in my code. I still get Error 91, Object variable or with block variable not set. Debugger highlights the "Set rst" statement. Here is my code which is in a module.
Sub UpdateFindInventor(frm As Form, myID As Long)
If myID > 0 Then
frm.Filter = "InventorID = " & myID
frm.FilterOn = True
End If

frm.CbName.Requery
Dim dbs As Database
Set dbs = CurrentDb
Dim rst As DAO.Recordset
Set rst = dbs.OpenRecordset(frm!CbName.RowSource, dbOpenDynaset)
rst.FindFirst "[InventorID] = " & myID
If Not rst.NoMatch Then
frm!CbName = rst!InventorName
Else
frm!CbName = ""
End If
frm!CbName.SetFocus
End Sub

Any help would be appreciated.

 

[FancyPrairie]

Try changing the Dim statement for dbs to:
Dim dbs as DAO.Database

 

[TJC]

Thanks for your response. I changed the Dim statement for dbs as you suggested. I still get the same error message. Any other suggestions?

 

[JPeters]

frm.CbName.Requery
maybe change to
frm!Cbname.Requery...

Before rst.Findfirst, do With RST
and after end if do End With

Sub UpdateFindInventor(frm As Form, myID As Long)
If myID > 0 Then 'hrmmm the problem could be up here too
frm.Filter = "InventorID = " & myID
frm.FilterOn = True
End If

frm!cbName.Requery
Dim dbs As Database
Set dbs = CurrentDb
Dim rst As DAO.Recordset
Set rst = dbs.OpenRecordset(frm!CbName.RowSource, dbOpenDynaset)

With RST
rst.FindFirst "[InventorID] = " & myID
If Not rst.NoMatch Then
frm!CbName = rst!InventorName
Else
frm!CbName = ""
End If
End With
frm!CbName.SetFocus
End Sub

-Josh
------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[JPeters]

Whoops, I misread your original post... what kind of recordset are you trying to open. I've never opened a recordset from within a form.

Shouldn't it be something like
Set RST = DBS.OpenRecordset("qrymyrecordset&quot ' or tblmyrecordset.

Hrmm, eh, we'll figure it out. ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[TJC]

JPeters, thanks for response as well. The table name is Inventors that the data is being pulled if I undersstand your question correctly. The previous code worked in Access 97 and I also converted this database to Access 2000 with no problem. I pasted your code as written above but still encounter the error. I can send you the database if you wish.

 

[FancyPrairie]

I don't see anything wrong now. However, if I read this code right, you have a combo box in which you want to determine if a given item exists in it. If it does, then set the value of the combo box equal to it, so it shows up.
If my assumption is correct, then you could do the same thing by looping thru the items in the combo box to see if it exists.

for example:

    For i = 0 To frm!CbName.ListCount - 1
        If (CbName.Column(0, i) = myID Then
            CbName.Selected(i) = True
            Exit For
        End If
     Next i