Min to Max numbers Populated to table

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

I have a form with 2 text boxes [BeginningNumber] [EndingNumber] I will
be entering a beginning number in the first textbox of lets say 232000 and
in the second textbox 233000 (A difference of 1000) I need all 1000
numbers populated to one field of my table. How can I do this. I'm
stumped..Thanks...Randy
 
This function shows how to add records to MyTable, where the field
MyNumField contains each value:

Function MakeData(StartNum As Long, EndNum As Long)
Dim rs As DAO.Recordset
Dim lng As Long

Set rs = DBEngine(0)(0).OpenRecordset("MyTable", dbOpenDynaset)
For lng = StartNum to EndNum
rs.AddNew
rs![MyNumField] = lng
rs.Update
Next
rs.Close
End Function
 
I have a form with 2 text boxes [BeginningNumber] [EndingNumber] I will
be entering a beginning number in the first textbox of lets say 232000 and
in the second textbox 233000 (A difference of 1000) I need all 1000
numbers populated to one field of my table. How can I do this. I'm
stumped..Thanks...Randy
Click to expand...

One way is to create a table named Num, with one long integer field N;
fill this with numbers 0 through the most you'll ever want to insert
(using Excel fill-down and Import is a quick way).

Create an Append query

INSERT INTO targettable([fieldname])
SELECT N+[Forms]![yourform]![beginningnumber]
WHERE N+[Forms]![yourform]![beginningnumber] <=
[Forms]![yourform]![endingnumber];

Run this Query from a button on the form - the button wizard will walk
you through this.

Just one comment - it is VERY RARELY either necessary or appropriate
to create even one basically empty "placeholder" record, much less a
thousand! Might you be able to work with a Select query just like the
above, without storing a thousand records to disk with nothing in them
other than a number?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Thanks for the help...Randy
John Vinson said:
I have a form with 2 text boxes [BeginningNumber] [EndingNumber] I
will
be entering a beginning number in the first textbox of lets say 232000 and
in the second textbox 233000 (A difference of 1000) I need all 1000
numbers populated to one field of my table. How can I do this. I'm
stumped..Thanks...Randy
Click to expand...

One way is to create a table named Num, with one long integer field N;
fill this with numbers 0 through the most you'll ever want to insert
(using Excel fill-down and Import is a quick way).

Create an Append query

INSERT INTO targettable([fieldname])
SELECT N+[Forms]![yourform]![beginningnumber]
WHERE N+[Forms]![yourform]![beginningnumber] <=
[Forms]![yourform]![endingnumber];

Run this Query from a button on the form - the button wizard will walk
you through this.

Just one comment - it is VERY RARELY either necessary or appropriate
to create even one basically empty "placeholder" record, much less a
thousand! Might you be able to work with a Select query just like the
above, without storing a thousand records to disk with nothing in them
other than a number?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Back
Top