insert array? values

[kim21]

Hi

I have a form whereby the user can enter a frequency (quarterly, half-yearly, monthly or yearly), initial date and number of years for a particular record. So for example, they may enter a frequency of Quarterly, an initial date of 01/12/2002 and duration of 2 years.
Then, somehow, I'd like to insert 8 records into a seperate table (ref, freq, initial, duration)as follows

001 quarterly 01/12/2002 2
001 quarterly 01/03/2003 2
001 quarterly 01/06/2003 2
and so on...............

Any help greatly appreciated
Kim

 

[KenReay]

Hi

In the on click event of a command button on your form:

Dim Db as Database
Dim i as Integer
Dim j As Integer
Dim strSQL as String
Hi

How much detail do you need?

In outline it would be something like
(in on click of command button)

Set Db = CurrentDb()
Select case txtFrequency
Case "Q"
i = 4
Case "M"
i = 12
...etc
End Select
i = i * txtYears
For j = 1 to i
strSQL = "INSERT ..."
db.Execute strSQL
Next j
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[kim21]

i've got the following working except for inserting the dates i.e. if it was half-yearly, starting at 01/01/02 and just 1 year, then I'd want 2 records (which i can do) but the dates would have to be 01/01/02 and 01/07/02 (thay i can't do)

thanks
kim

Dim myNum As Integer
Dim timesby As Integer
Dim loopby As Integer
Dim idate As Date
Dim idateadd As Integer
Dim x As Integer

If Me.review_frq = "Monthly" Then
myNum = 12
idateadd = 1
ElseIf Me.review_frq = "Quarterly" Then
myNum = 4
idateadd = 3
ElseIf Me.review_frq = "Half-Yearly" Then
myNum = 2
idateadd = 6
ElseIf Me.review_frq = "Annually" Then
myNum = 1
idateadd = 12
End If

timesby = Me.no_years
loopby = myNum * timesby

idate = DateAdd("m", idateadd, Format(Me.initial_month, "long date&quot)

For x = 1 To loopby
DoCmd.RunSQL "INSERT INTO tblreview(strat_met, review_month) values(" & Me.strat_met & " , #" & Format(idate, "long date&quot & "#)"
Next x

 

[KenReay]

Hi

As I see it you just need to put your date within the loop

For x = 1 To loopby
DoCmd.RunSQL "INSERT INTO tblreview(strat_met, review_month) values(" & Me.strat_met & " , #" & Format(idate, "long date&quot & "#)"
idate = DateAdd("m", idateadd, Format(Me.initial_month, "long date&quot)
Next x
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[gwilym40]

great thanks

I just changed
idate = DateAdd("m", idateadd, Format(Me.initial_month, "long date&quot)

to

idate = DateAdd("m", idateadd, Format(idate, "long date&quot)
and it works fine

thanks very much