Creating Incremental numbers in a query

[Ymesei]

I am using an SQL statement to create a table, but I want the table to have a field with incremental numbers like an autonumber field.

Also, I want the query to use a starting number based on user input. Can this be done and how?

Any help would be greatly appreciated.

 

[gcole]

Move to the top of your query. Store the user number in a variable (cnt)and use a loop.

do while tbl.eof=true
tbl.edit
tbl.fields("number&quot = cnt+1
tbl.update
tbl.movenext
loop

 

[gcole]

Better:
do while tbl.eof=true
tbl.edit
tbl.fields("number&quot = cnt
tbl.update
cnt = cnt+1
tbl.movenext
loop

 

[MichaelRed]

This works, BUT you can only run it ONCE for each instance that you open the DATABASE! Subsquent 'runs' will only ever increment the value form the previous execution.

Also, note that if you want the numbering to start at something other than one (1), you supply the starting value as the second parameter.

Public Function basNumRecs(varCount As Variant, Optional StrtNum As Variant = 1) As Long

    Static lngCounter As Long       'Internal Counter for Return Value
    Static RunningFlg As Boolean    'Flag for starting Input on strtNum only ONCE
    Dim MyStrt As Long

    If (Not RunningFlg) Then        'Have we been here?
        RunningFlg = True           'If not yet, certainly NOW
        If (IsNull(StrtNum)) Then   'Did we (NOT) get a starting Value?
            MyStrt = 1              'Then assume ordinal 1
         Else
            MyStrt = StrtNum        'Otherwise use Input value
        End If
        lngCounter = MyStrt - 1     'Decrement start Value so User doesn't need to think
    End If
    
    lngCounter = lngCounter + 1     'Always increment
    
    basNumRecs = lngCounter         'Return Results
    
End Function

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[MichaelRed]

OOps, sorry, I forgot to mention.

1.[tab]You MUST supply a field name from the query's recordsource as the first argument.

1.[tab]In the query by grid, the thinggy "looks like":

RecNum: basNumRecs([ItemNum],[MyNum])

RecNum is the name I chose for the New Record Id (pseudo autonumber) field in my new table.

ItemNum is just the name of (any) field in the query recordsource. It CANNOT be a constant or an expression which evaluates to an expression or (In this instance "RecNum" (self referential!)

MyNum is the optional argument for a "[/i]RecNum[/i]" starting at something other than one.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[Ymesei]

Thanks for your help. Actually, Gcole's code worked out perfectly. My query is a Make-Table, so all I did was run the query from a module and use the code to insert the incremental numbers into the table after it was created. Thanks again.

Once again, Tek-Tips to the rescue. . .