Autonumbers (range)

[mike250]

I need to populate a table column with a range of numbers beginning with 40000000001 through to 400000000500.

So the first record would be 40000000001 and the last record would be 400000000500.

Is there a quick and dirty way to insert each record that sequentially adds a number between this range without me having to type it in for every record?

Thanks in advance!

 

[BuGlen]

Look in Access help under "Change the starting value of an AutoNumber field (MDB)".

This will provide all the steps necessary to accomplish the task.

Hope this helps.

Glen Appleton

VB.Net student.

 

[mike250]

What I want is this.

We receive a bunch of barcoded customer loyalty cards that are sequential in barcodes. I am building an Avery Label report that also has the same barcodes for affixing to the customers completed form while we hand them their new card.

I want to have an entry form for entering the value of the first barcode on the first sheet, and then the last barcode on the last sheet. It would then automatically populate a table with each and every barcode inbetween that range.

Then my Crystal Report will point to that table and print the avery label sheet based off the values in the table.

Make sense? Can it be done?

 

[JeremyNYC]

Mike,

It can be done, but an autonumber is exactly the wrong tool.

The best thing to do is write code to do this.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[mike250]

Does anyone has code? An example? Cheers!

 

[lespaul]

check the FAQ area of this forum and
Forum700
Forum701

you should find plenty of examples!

Leslie


Leslie

 

[BuGlen]

The following example shows how to append sequential field table using the RunSQL method of the DoCmd object. Make sure you define your barcode field in the table as a Double so it will allow very large numbers.

-----

Private Sub cmdCreateCodes_Click()

    Dim dblStart As Double, dblEnd As Double, dblIdx As Double
    Dim strSQL As String
    
    ' Convert the textbox values to long integers
    dblStart = CDbl(Me.txtCodeStart.Value)
    dblEnd = CDbl(Me.txtCodeEnd.Value)
    
    ' Data validation
    If dblStart > dblEnd Then
        MsgBox "Input Error: Start value greater than End value.", _
            vbCritical, "Error"
        Exit Sub
    End If
    
    ' Turn off warnings
    DoCmd.SetWarnings False
    
    ' Delete any previous values
    strSQL = "DELETE * FROM Barcodes"
    DoCmd.RunSQL strSQL
    
    ' Add the records to the barcodes table
    For dblIdx = dblStart To dblEnd
        strSQL = "INSERT INTO Barcodes ( Barcode )" & _
                "SELECT " & dblIdx & " AS Barcode;"
        DoCmd.RunSQL strSQL
    Next dblIdx
    
    ' Turn the warnings back on
    DoCmd.SetWarnings True

End Sub

-----

Hope this helps.

Glen Appleton

VB.Net student.