Recordset issues...

[Villarreal68]

Newbie!
While creating my "First Database" it being a OnLoan/inventory database I needed to create a process to remove "OnLoan" Items from the Inventory count. I created a command buton (AddRecord) and added the following code:

-------------------------------------------------------
Private Sub AddRecord_Click()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

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

rst.Index = "PrimaryKey"
rst.Seek "=", Me!ItemID
rst.Edit
rst("ItemsInv") = rst("ItemsInv") - Me!Quantity
rst.Update
rst.Close

Set rst = Nothing
Set dbs = Nothing

End Sub

----------------------------------------------------------

It worked as expected up to one point.

I found out that it needed to be a "Front-End" and a "Back-end" solution. After I copied the database to create the "Back-End" database and linked the tables to the "Front-End", this procedure does not work any longer.

When I click on the "AddRecord" button it gives me the following error:

------------------------
Run-time error '3219':

Invalid operation
------------------------

When I click on the Debug button it takes me to the following location (highlighted in Yellow):


Set rst = dbs.OpenRecordset("Items", dbOpenTable)


Is this procedure not possible on "Linked" tables? And if it isn't what is the work around?


Any help is greatly appreciated. (I've read so much to get to this point and to find out that it's not working, is wrong! )

 

[pbaldy]

Change dbOpenTable to dbOpenDynaset

 

[Villarreal68]

Thanks pbaldy!

Thanks pbaldy! I'll try that!

 

[pbaldy]

BTW, it's not really efficient to open the whole table and then seek the desired record. Better to open the recordset using an SQL string that limits the records returned to the one you want. And one of those types of operations (like Seek) doesn't work with linked tables, though offhand I can't remember which.

 

[Villarreal68]

Further, but not completely reolved...

Thanks pbaldy I truly appreciate your help... that got me further.

Now it's stating the following error:

-------------------------------------------------
Run-time error '3251':

Operation is not supported for this type of object.
-------------------------------------------------

Debug takes me to:

---------------------------
rst.Index = "PrimaryKey"
---------------------------

what could be a substitute for it?

 

[pbaldy]

See previous response.

 

[Villarreal68]

pbaldy thanks for your help...could you do me a favor if it's not too much to ask...could you direct me to the right location to find info on the "SQL string" you refer to on the earlier reply??

Thanks for your help once again.
René

 

[pbaldy]

Yours would look something like this (each in the appropriate spot in the code):

Dim strSQL as String
strSQL = "SELECT * FROM Items WHERE PrimaryKey = " & Me!ItemID
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

and dump these 2 lines:

rst.Index = "PrimaryKey"
rst.Seek "=", Me!ItemID

 

[Villarreal68]

This is what I have in place now:

--------------------------------------------------------------------------------
Private Sub AddRecord_Click()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String 'New entry
strSQL = "SELECT * FROM Items WHERE PrimaryKey = " & Me!ItemID 'New Entry

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, , dbOpenDynaset) 'Modified Entry

'rst.Index = "PrimaryKey" 'Removed Entry
'rst.Seek "=", Me!ItemID 'Removed Entry
rst.Edit
rst("ItemsInv") = rst("ItemsInv") - Me!Quantity
rst.Update
rst.Close

Set rst = Nothing
Set dbs = Nothing

End Sub

-----------------------------------------------------------------------------------

But I get a different error now...

--------------------------------------
Run-time error '3061':

Too few parameters. Expected 1.

----------------------------------------

debug takes me to this line:

Set rst = dbs.OpenRecordset(strSQL, , dbOpenDynaset) 'Modified Entry

anything comes to mind??

 

[pbaldy]

Is that field numeric or text? If it's text:

strSQL = "SELECT * FROM Items WHERE PrimaryKey = '" & Me!ItemID & "'"

 

[Villarreal68]

pbaldy I want to truly thank you for your time and efforts! It worked!

I had to change the "PrimaryKey" statement to "ItemID" and it then worked. But i would not have been even close to the solution without your help. Thanks!

It took me 1 week of reading to get to were i was before it failed. It would have taken me at least that amount of time to resolve this new issue.

I may need your help again later! Truly appreciate it!
René

 

[pbaldy]

No problemo, and sorry; I assumed that was the name of your field.