add multiple records to a table

[askjacq]

Don't know if an append query is the way to go here or if it's possible to do this in a macro or in a sql query.



I want to create a procedure that will append to a table the number of records specified in another table.

Example:

table called tDaily
Fields: DID (autonumber primary key), qtymfg (number)

table called tSNs
Fields: ID (autonumber), DID (lookup to tDaily DID), SN

When I run the procedure, I want it to add to tSNs table the number of records specified in the qtymfg field of the active record. I want the DID to be the DID of the active record. I want the SN to be the SN of the last record in the tSN table +1 and then numbered in increments.

Example:

tDaily record: DID - 99; qtymfg - 3
Last SN of the tSN table - 1001

Run the procedure and 3 records are added to the tSNs table:

ID - 4; DID - 99; SN - 1002
ID - 5; DID - 99; SN - 1003
ID - 6; DID - 99; SN - 1004

What is the easiest way to do this?

Thanks!

[June7]

Probably need a VBA procedure with looping structure that will append as many records as required and that also increments the SN value. The append can be done by executing an SQL INSERT action or opening a recordset and adding records to the recordset.