On Tuesday, January 3, 2006 at 12:16:34 PM UTC-6, NV wrote:
> Hi !
> I don't know if it's possible but I need to create in a table as many
> records as a number entered in a form. (Example: In a form I write 4
> and it creates 4 new records in a table).
> In each of those records I need to paste a code of 3 leters and the
> year (Example: In the same form I write in the field code ABC, and in
> the field year 2006, and it enters those values to each of the 4 new
> records).
> Does anybody know how this can be done ?
> Thank you all in advance
> Nuno

When doing multiple table updates, I like to run the edits in a transaction.. The following code can be used in a form's button click event. Replacing "ABC" and "2006" with a field reference. Change the query by replacing YourTable, [CodeCol] and [YearCol] with the proper table and column names. This code will add 4 rows or none.

Private Sub btnAdd4_Click()
on error goto errbtnAdd4_Click
dim transactionActive as boolean
dim add4QueryDef as DAO.QueryDef
transactionActive = false
set add4QueryDef = currentdb.CreateQueryDef(vbnullstring, "PARAMETERS [InsertCode] Text ( 255 ), [InsertYear] Text ( 255 ); INSERT INTO yourTable( [CodeCol], [YearCol]) VALUES([InsertCode], [InsertYear]);
add4QueryDef.Paramiters(0) = "ABC"
add4QueryDef.Paramiters(1) = "2006"
DBEngine.Workspaces(0).BeginTrans
transactionActive = true
add4QueryDef .Execute dbFailOnError + dbSeeChanges
add4QueryDef .Execute dbFailOnError + dbSeeChanges
add4QueryDef .Execute dbFailOnError + dbSeeChanges
add4QueryDef .Execute dbFailOnError + dbSeeChanges
donebtnAdd4_Click:
on error resume next
if transactionActive then
DBEngine.Workspaces(0).CommitTrans
endif
set add4QueryDef = Nothing
exit sub
errbtnAdd4_Click:
debug.print err.Description
if transactionActive then
on error resume next
DBEngine.Workspaces(0).Rollback
endif
transactionActive = false
Resume donebtnAdd4_Click
end Sub