numbering output records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a make-table query that selects active records from a table and creates my output file. My output file needs an additional field called Voucher Number. I want the user to be able to enter a starting voucher number at the time the output file is created and then have the records numbered sequentially from there. Help - I have brain-lock.

TIA
 
Lynne said:
I have a make-table query that selects active records from a table and creates my
Click to expand...
output file. My output file needs an additional field called Voucher Number. I want
the user to be able to enter a starting voucher number at the time the output file is
created and then have the records numbered sequentially from there. Help - I have
brain-lock.Hi Lynne,

You don't say what version of Access you have,
but if you have Access 200x...

one way might be to:

after you run your make-table (I assume in code),
add your field (say "VoucherNum") as an Autonumber field
with the "seed" provided by your user
(say in "Me!txtStartingVoucher" on your form)
to your new table (say "tblNew"):

For example, *untested* code you might
add to your make-table routine:

Dim lngSeed as Long

'run your make-table query
'......

'test starting voucher number from user
If IsNumeric(Me!txtStartingVoucher) Then
lngSeed = CLng(Me!txtStartingVoucher)
Else
MsgBox "Please enter a number for Starting Voucher."
Exit Sub
End If

'add autonumber field using seed
CurrentProject().Connection.Execute "ALTER TABLE tblNew " _
& "ADD VoucherNum AUTOINCREMENT (" _
& lngSeed & ", 1)", dbFailOnError

MsgBox "Have successfully created table."


/////////////////////////////////////////////////////////////
From MSDN Help File:
AutoNumber enhancements. The Jet 4.0 ANSI SQL-92 extensions
add support for customizing the seed and increment values of
AutoNumber columns. The syntax is as follows:

column AUTOINCREMENT (seed, increment)

You can also use the synonyms IDENTITY or COUNTER
instead of AUTOINCREMENT.
Jet 4.0 SQL provides the only way to alter the seed and
increment values of AutoNumber fields. The Access UI
and ADOX provide no mechanism for customizing AutoNumber
seed or increment values. For example, to create the OrderId
AutoNumber column with a sequence that started at 1000 and was
incremented by 10,
you could use the following CREATE TABLE statement:

CREATE TABLE tblNewOrder2 (OrderId AUTOINCREMENT (1000, 10), ItemId LONG, Quantity
LONG)
{another ex:}
CREATE TABLE t1(ID IDENTITY, f1 TEXT(50) NOT NULL, CONSTRAINT PK_t1 PRIMARY KEY
(ID ))
////////////////////////////////////////////////////////////////

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
Back
Top