Recordset Help

[Guest]

I am relatively new to Access, and definitely feeling my way. My issue is
this: I have an order entry database that I need to send daily output to my
client(s). Customers can order up to 6 diff products, each is in the order
details table (as a sep order detail id). The client needs all order details
in a single row in import into their database (meaning all 6 products). I
have been advised to open recordsets for my tables and loop through the order
details and append records to a temp table.

I have tried to open a recordset, but I keep getting a compile error
("invalid outside procedure") on the SetmyConnection... line.

The following is what I typed to open a recordset:

Dim myConnection As ADODB.Connection
Set myConnection = CurrentProject.Connection
Dim myRecordset As New ADODB.Recordset
myRecordset.ActiveConnection = myConnection
myRecordset.Open "Customers", , adOpenStatic, adLockOptimistic

Any ideas? Is there a tutorial that might help me with this?

Thanks!

 

[Douglas J. Steele]

The wording of the message seems to imply that you've just typed that into a
module, without putting it in a Sub or Function.

 

[Guest]

Douglas...Thank you for your response. You are correct, apparently I
*overlooked* that very important step. My bad. My instructions didn't
mention to do that.

However, I am still perplexed on how to write my code to loop my Order
Details table to append to the temp table, as the order details fields
(Product ID, Qty and Unit Price) would need to be appended to Product ID 1,
2, 3, 4, 5 or 6 depending on how many products are on that order (and where
that record sits in the order). I understand that this is probably rather
remedial, but I'm just learning.

Thanks,
Nancy

 

[Douglas J. Steele]

I'm sorry, I don't really understand what you're trying to do.

Why are you trying to append existing data somewhere else?

In actual fact, you seldom want to loop through a recordset adding specific
data to each row if you can use a SQL statement to do the same thing.