insert into and repeating values

[Guest]

I am collecting 3 values from a form - [1stpayDate],[1stPayAmount],[LoanNo]
and appending it to a ScheduledPayments table through an "insert into" sql
statement (append query.

Query works fine, problem is I'd like to be able to add an additional 12
rows to the insert into - 1 for each additional month after the original
[1stPayDate] and leave the other 2 values the same.

I've seen arrays in vb script but didn't know if there is a simple way to
replicate through SQL code and accomplish what I've described above

 

[Guest]

You can do that using code with a loop from 1 to 12, run the code from the form

Dim I
For I = 0 To 12

CurrentDb.Execute("INSERT INTO
TableName([FieldName1],[FieldName2],[FieldName3]) VALUES (#" & DateAdd("m",I,
Me.1stpayDate) & "#," & Me.[1stPayAmount] & "," & Me.[LoanNo] & ")") ,
dbFailOnError

Next I

***********************************
If LoanNo is text field then add single quote before and after the value

CurrentDb.Execute("INSERT INTO
TableName([FieldName1],[FieldName2],[FieldName3]) VALUES (#" & DateAdd("m",I,
Me.1stpayDate) & "#," & Me.[1stPayAmount] & ",'" & Me.[LoanNo] & "')") ,
dbFailOnError
*******************************
Note: need to change the TableName and FieldName

 

[John W. Vinson]

I am collecting 3 values from a form - [1stpayDate],[1stPayAmount],[LoanNo]
and appending it to a ScheduledPayments table through an "insert into" sql
statement (append query.

Query works fine, problem is I'd like to be able to add an additional 12
rows to the insert into - 1 for each additional month after the original
[1stPayDate] and leave the other 2 values the same.

I've seen arrays in vb script but didn't know if there is a simple way to
replicate through SQL code and accomplish what I've described above

In addition to Ofer's VBA loop command, you can also do this in a Query with
the help of an auxiliary table. Create a table Num with one number field N,
with values from 0 through 10000 or so (you only need 12 here but the table
will be useful in other queries!)

Create an Append query

INSERT INTO ScheduledPayments(PayDate,PayAmount,LoanNo)
SELECT DateAdd("m", [N], [Forms]![YourForm]![1stPayDate]),
[Forms]![YourForm]![1stPayAmount], [Forms]![YourForm]![LoanNo]
FROM Num
WHERE N < 12;


John W. Vinson [MVP]