Please help with a query/recordset operation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there, I'm pretty new to Access and VB so I'm still having problems
getting my head around various things.

I have a recordset on a continuous subform, representing a number of items
to be ordered (Purchase Order or PO). The SQL for the query is.

Select *
FROM Fixed_Cost_Item
WHERE (((Fixed_Cost_Item.Inv_Number) Like [Forms]![Entry_Screen]![PO_ID])
AND ((Fixed_Cost_Item.Active)=Yes) AND ((Fixed_Cost_Item.Send_PO)=Yes));

I would like to be able to execute an INSERT statement into another table
(expenses) for each of the records in the recordset. I figure the best way to
do this is to loop through the recordset. I've never tried before and in all
the articles about using DAO talk about references to about 1000 different
access/jet/something objects that I've never heard of.

I made some skeleton code

Dim db As DAO.Database 'DB Variable
Dim rst As DAO.Recordset 'Recordset

Set db = CurrentDb 'Point
Set rsA = db.OpenRecordset("TableA") 'FIX - Query

Do Until rst.EOF

*Insert statement
*Set Send_PO to false and PO_sent to true

rsA.MoveNext

Loop

rst.Close

Set rst = Nothing
Set db = Nothing

I get told "User defined type not defined" before I try the insert statement
out. Do I have to make some other references for this thing to work? Also, I
want the recordset to run against a query, not a table.

thanks in advance for any help, I really need it =)

cheers
Nick
 
OK, I've changed the name of the recordset to the query but it still spits
the Definition error at me. Eeeep

Set rsA = db.OpenRecordset("PO_Fixed_Item_Work")
 
OK, I've got rid of the def error, and fixed rsA -> rst (duh). But now I'm
getting an error "Too few parameters - expected 1" on the line:

Set rst = db.OpenRecordset("PO_Fixed_Item_Work")

I haven't put any of the insert sql code in there yet, I figure that's
simple. Once access let me execute a simple do - until loop.

nick said:
OK, I've changed the name of the recordset to the query but it still spits
the Definition error at me. Eeeep

Set rst = db.OpenRecordset("PO_Fixed_Item_Work")

nick said:
Hi there, I'm pretty new to Access and VB so I'm still having problems
getting my head around various things.

I have a recordset on a continuous subform, representing a number of items
to be ordered (Purchase Order or PO). The SQL for the query is.

Select *
FROM Fixed_Cost_Item
WHERE (((Fixed_Cost_Item.Inv_Number) Like [Forms]![Entry_Screen]![PO_ID])
AND ((Fixed_Cost_Item.Active)=Yes) AND ((Fixed_Cost_Item.Send_PO)=Yes));

I would like to be able to execute an INSERT statement into another table
(expenses) for each of the records in the recordset. I figure the best way to
do this is to loop through the recordset. I've never tried before and in all
the articles about using DAO talk about references to about 1000 different
access/jet/something objects that I've never heard of.

I made some skeleton code

Dim db As DAO.Database 'DB Variable
Dim rst As DAO.Recordset 'Recordset

Set db = CurrentDb 'Point
Set rst = db.OpenRecordset("TableA") 'FIX - Query

Do Until rst.EOF

*Insert statement
*Set Send_PO to false and PO_sent to true

rst.MoveNext

Loop

rst.Close

Set rst = Nothing
Set db = Nothing

I get told "User defined type not defined" before I try the insert statement
out. Do I have to make some other references for this thing to work? Also, I
want the recordset to run against a query, not a table.

thanks in advance for any help, I really need it =)

cheers
Nick
Click to expand...
Click to expand...
 
Back
Top