Split db Generates Errors

[bbeeching]

I have been trying to solve a problem in a db I split. In my efforts I found in the Knowledge Base article 210266, "How to use the Seek Method on Linked Tables". In trying to get it to work, I generate runtime error #3265, Item not found in this collection

Could someone please look at this code and show me where I'm making my mistake?

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Dim dbs As Database
Dim t As TableDef
Dim rst As DAO.Recordset
Dim dbspath, SourceTable



Set dbs = DBEngine.Workspaces(0)(0)
dbspath = Mid(dbs(tblFuelType).Connect, InStr(1, tblFuelType.Connect, "=") = 1)

SourceTable = dbs(tblFuelType).SourceTableName

Set dbs = DBEngine(0).OpenDatabase("W:\Vehicle & Fuel Log\VFL001_be.mdb")
Set rst = dbs.OpenRecordset(tblFuelType, DB_OPEN_TABLE)

rst.Index = "PrimaryKey"
rst.Seek "=", Me!Fuel
rst.Edit

rst("FuelQty") = rst("FuelQty") - Me!QtyPumped
rst.Update
rst.Close

Set rst = Nothing
Set dbs = Nothing
End If
End Sub


The line that seems to be failing is in red

I really appreciate the assistance!

 

[dcx693]

What is tblFuelType? Sounds like a table name, but it's being treated like a variable in your code. I don't see it being dim'ed. Is it a global variable? If it's a table name, wrap it in quotation marks.

 

[Pat Hartman]

A far better solution is to use a parameter query that selects ONLY the records that you are interested in. Why open a table and use the SLOW seek or find methods to find a record or set of records when you could have opened a recordset based on a query that contained only the records you needed.

 

[bbeeching]

What I'm doing is updating one record in tblFuelType with the result of a calculated control on the form the routine runs on. This keeps the inventory of available fuel current with the amount of fuel expended. This is how it looks in the design master (db not split)

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim msg As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblFuelType", dbOpenTable)

rst.Index = "PrimaryKey"
rst.Seek "=", Me!Fuel
rst.Edit

rst("FuelQty") = rst("FuelQty") - Me!QtyPumped
rst.Update
rst.Close

Set rst = Nothing
Set dbs = Nothing
End If
End Sub

This works GREAT! but it will fail when the db is split. The function I need help with is strait out of the Knowledge Base. I'm a babe in the woods when it come to figuring out problems in VBA. I don't know how to get it to do what it is supposed to. Any help would be much appreciated though....

 

[Pat Hartman]

Are you aware that you can use the edit and update methods on a recordset that is based on a query? There is NO reason to open a table and use the seek or find methods when you can open a query that selects ONLY the record you want. In fact, you don't even have to open a recordset at all, you can run an update query.

Set rst = dbs.OpenRecordset("yourqueryname")