Help with a number range generation

  • Thread starter Thread starter Stu Dongel
  • Start date Start date
S

Stu Dongel

Hello All,

I am having a hell of a time tryin to right a form that generates a a number
range. What I need to do is as follows. I have a set of numbers, and they
are odd ex. 00010327 "it needs to be formatted as such becuase it is an id
number of sorts". The next number is in sequence, and so on. So what i
need to do is write this code out to generat a range from any given value.
EX. 327 500 would populate a table with a field calld "Num" with 0010327
0010328 00010329 00010330......00010500.

i have staarted out with 2 unbound textboxes calld StartNum and EndNum and a
command button in wich i am tryin to exec my code. Im failing miserable,
partly becuase of the number padding, and probebly becuase i was using
Dcount poorley... Im not much of a VB programmer, im still picking it up.

the end result for this is just to have that series of numbers, so i can
print up envelopes with the same number that is on these damn cards.

Im not sure if I am even goin about this the write way. If anyone has any
sugestions, or nudges, or help, i would be greatly appriciative.
 
Hi Stu

do they really need to be "numbers" or could they be text that looks like
numbers?

if they can be stored in the table as text then the following code should
give you what you want
***********
Private Sub Command0_Click()
Dim dbs As Database
Dim rst As Recordset
Dim s_num As Integer
Dim e_num As Integer
Dim newnum As String

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("table2", dbOpenDynaset) 'replace table2
with the name of your table

s_num = Me.startnum
e_num = Me.endnum

For s_num = s_num To e_num
newnum = "00010" & s_num
With rst
.AddNew
!Num = newnum
.Update
End With
Next
rst.Close

Set rst = Nothing
Set dbs = Nothing
msgbox "Finished!"
End Sub
********


Cheers
JulieD
 
Here is a method using DAO. Also, I am assuming that you are storing yo
field "Num" as text in a table called tblNum.

Code
-------------------
Dim myDB As DAO.Database
Dim myRST As DAO.Recordset

Set myDB = CurrentDb()
Set myRST = myDB.OpenRecordset("tblNum")

Dim StartAt, EndAt as Variable
Dim CountNum as Integer
Dim StartingNum as String

StartAt = 357
EndAt = 500
CountNum = EndAt - StartAt + 1
StartingNum = "00010"

For i = 1 To CountNum
With myRST
.AddNew
.Fields("Num") = StartingNum & StartAt
.Update
End With
StartAt = StartAt + 1
Next i

myRST.Close
myDB.Close

Set myRST = Nothing
Set myDB = Nothing


-------------------
Hope this helps!:)

*EDIT:* Be sure to set the reference for DAO library reference when yo
are in the VBA Explorer Window under *Tools -> References*. Look fo
*Microsoft DAO X.X Object Library* (-X.X will depend on what versio
you are using, e.g. *3.6*-
 
Back
Top