serially incrementing INSERT statement...

[krsherm]

Hello all. Does anyone know if there is a way to insert multiple serial values into a table executing only one INSERT statement? Is it possible to insert the values 1 through 10 into a table, or must this be done in a do loop construct?

For I would like to be able to add a variable number of due dates for a customer depending on the term of his payment schedule.

Here's a sloppy code snippet:

dim intTerm as Integer 'This is the total number of payments to be made.

dim dtmStartDate as Date 'This is the first date that payments will be due

dim strSQL as string

dim db as database

set db = currentdb

'Here is where I am stuck
say that the value of intTerm is 10 and dtmStartDate = #8/15/02#. I want to insert into tblCustomerInvoices the following values

tblCustomerInvoices:
InvoiceNumber,DateDue,CustomerID
01,08/15/02,xxx
02,09/15/02,xxx
03,10/15/02,xxx
04,11/15/02,xxx ...
10,05/15/03,xxx

'Please help out with strQL:
strSQL = "INSERT INTO tblCustomerInvoices (InvoiceNumber, DateDue, CustomerID) SELECT ('WHAT GOES HERE')"

If my poor presentation has confused anyone... please advise and I'll clarify.

Rock ON!

Kevin

 

[gol4]

unless some one else has a better way or you want to append the values into a table first
treat it as a value

dim x as integer
dim strsql as string
docmd.setwarnings false
for x = 0 to intterm-1
strSQL = "INSERT INTO tblCustomerInvoices (InvoiceNumber, DateDue, CustomerID) values(" & x+1 & ", #" & dateadd("m",x,dtmstartdate & "#, " & customerid & &quot"
docmd.runsql strsql
next x
docmd.setwarnings true