Append into table all integers between A and B (1 Viewer)

[aranj]

Hi.
I have a form with two text boxes, call them FIRSTNO and LASTNO.
The user inputs a first and last number into these boxes, and what I want to do is append to table NOS all integers between these two.

E.g. user inputs 6 and 11 and I would like table NOS to contain 6 rows, with the first field containing this number, like so:
6
7
8
9
10
11

It sounds easy, but I’m struggling!
Any thoughts appreciated. Thanks in advance.

 

[Matt Greatorex]

I'm not sure whether you want this to run as soon as the user enters the second figure, or whether there will be a button to click. Assuming the former, you could put something like the following behind the After Update event of the second field.

Dim str_SQL as String
Dim li_First as Integer
Dim li_Last as Integer

li_First = [Forms]![[I]name of form[/I]]![FirstNo]
li_Last = [Forms]![[I]name of form[/I]]![LastNo]

Do While li_First <= li_Last
      str_SQL = "INSERT INTO Nos ([I]column name[/I]), Values (" & li_First & ");"
      DoCmd.RunSQL str_SQL
      li_First = li_First + 1
Loop

 

[aranj]

Many thanks Matt - works perfectly (without the comma after column name as it gave me a syntax error).

Much appreciated - thanks again.