If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help with a number range generation
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. |
#2
|
|||
|
|||
Help with a number range generation
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 "Stu Dongel" wrote in message ... 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. |
#3
|
|||
|
|||
Help with a number range generation
Here is a method using DAO. Also, I am assuming that you are storing you 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 you are in the VBA Explorer Window under *Tools - References*. Look for *Microsoft DAO X.X Object Library* (-X.X will depend on what version you are using, e.g. *3.6*-) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Thread Tools | |
Display Modes | |
|
|