Object Required Error

[klynch0803]

ANy idea why why I keep getting this?

I have a query named "qdatinventorylevelcurrent" and I'm trying to run this query in VBA using VBA Code below but I keep getting error Object required

setrst = MyDB.OpenRecordset("qdatInventoryLevelCurrent")

then I want to write these records to a table "tdatinventorycheckup" using code as well but not sure what I need here I cant get past step one LOL..

 

[boblarson]

What is MyDB?

You can use:

Set rst = CurrentDb.OpenRecordset("qdatInventoryLevelCurrent")

 

[petehilljnr]

Couple simple things to start with:

Assume you have spotted the missing space: setrst = ... set rst =

And also, somewhere before that line, you have:

Dim MyDB as DAO.Database
Dim rst as DAO.Recordset

Set MyDB = CurrentDB

Pete

 

[klynch0803]

Ok Ty to each of you I believe I'm now getting past the original error but now getting the following...

So you know my objective is to run the query and insert the records into the table..

Syntax Error in Insert to Method..

Here is my full code

Private Sub cmdopeninvcheckup_Click()
On Error GoTo Err_cmdopeninvcheckup_Click
Dim RUNSQL As Boolean
Dim rst As DAO.Recordset


    Set rst = CurrentDb.OpenRecordset("qdatInventoryLevelCurrent")
    
    DoCmd.RUNSQL "INSERT INTO tdatInventoryCheckup (Item,currentsystemlevel) from rst ITEM,CURRENTSYSTEMLEVEL"
    

Exit_cmdopeninvcheckup_Click:
    Exit Sub

Err_cmdopeninvcheckup_Click:
    MsgBox Err.Description
    Resume Exit_cmdopeninvcheckup_Click
    
End Sub

 

[pbaldy]

The correct syntax is either:

INSERT INTO TableName(Field1, Field2...)
VALUES (Value1, Value2)

or

INSERT INTO TableName(Field1, Field2...)
SELECT Field1, Field2...
FROM TableName

You likely want the first, concatenating the recordset values. Try:

"INSERT INTO tdatInventoryCheckup (Item,currentsystemlevel) VALUES(" & rst!ITEM & ", " & rst!CURRENTSYSTEMLEVEL & ")"

If either value is text rather than numeric, it will require single quotes around it.

 

[klynch0803]

You likely want the first, concatenating the recordset values. Try:

"INSERT INTO tdatInventoryCheckup (Item,currentsystemlevel) VALUES(" & rst!ITEM & ", " & rst!balance & ")"

If either value is text rather than numeric, it will require single quotes around it.

Ok this seems to be in the right path.. It tried to insert the first record but it gave me a popup window with the item name and a field to enter something.. Why?

 

[klynch0803]

Maybe the easier thing would be to create an append query and then just supress messages and run from VB?

 

[pbaldy]

Executing a query would almost certainly be more efficient.

 

[WayneRyan]

K,

You currently have a query --> qdatInventoryLevelCurrent

It has two columns = [Item] and [currentsystemlevel]

Why do you need to "transfer" them to a table? You can use them just like they are!

If you really need to put them in a table:

The table exists:

DoCmd.RunSQL "Insert Into tdatInventoryCheckup Select * From qdatInventoryLevelCurrent"

The table doesn't exist:

DoCmd.RunSQL "Select * Into tdatInventoryCheckup From qdatInventoryLevelCurrent"

And to copy it as a RecordSet, you need to loop through it with .MoveNext

Anyway, I think it was alright in the query to start with. How many of them do you need?

hth,
Wayne

 

[klynch0803]

K,

You currently have a query --> qdatInventoryLevelCurrent

It has two columns = [Item] and [currentsystemlevel]

Why do you need to "transfer" them to a table? You can use them just like they are!



Anyway, I think it was alright in the query to start with. How many of them do you need?

hth,
Wayne

You are correct I do have that query but I want to see what the inventory level was when it was checked ad what the difference was. Therefore I need a table in my view to derive this list.. I tried making a form to enter the actual inventory into for the items and it do a comparison and then insert into the table but was not having much luck.. So I created the append query to do this action so I could make a form for this effort to update the new table and adjust the inventory on hand as well so that it is correct.

If you know a better way I'm open for suggestions cause i hate lots of queries as well...