guys ,

i have a bit of coding that works a charm (not mine)
anyway it get me a unique number in squence ---

Dim NextNo As Long
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT NextNum FROM tblnextnum")
NextNo = rs!nextnum + 1
'now update the table
rs.Edit
rs!nextnum = NextNo
rs.Update
rs.Close
Set rs = Nothing


Me.lastinvoiceno = NextNo

now I need to have 4-5 numberings so what I wanted was 1 table for all my numbers to be in-

i presume its just rename NextNo
in my first case it would be
Quoteno everywhere Nextno appears .(its late and I not thinkking straight ...)

does that sound right ???
tyour thoughts

gp

what this is, is a table in the backend called tblnextnum, which is only accessable via these procedures (ie not a linked table)

the idea is to temporarily lock the table, retrieve the number, increment it in the table and drop the recordset. although In this case you don't seem to be locking the lookup table.

in this case, you are using the field nextnum,

if you want multiple sequential numbers, simply add additional fields to the lookup table to suit, and change your references accordingly

GP,

Change the table tblNextNum to have a new column --> TableName.

TableName Sequence
========= ========
TableA....102
TableB....175
TableC....365

Then just pass the string TableName into your current code changing the select statement:

"SELECT NextNum FROM tblnextnum Where TableName = '" & TableName & "'"


Or an entirely different approach:


Rather than put the previous code for each table's form, I'd still use the BeforeInsert event,
but I'd have just this line:

NewSequence = Nz(DMax("[YourSeqField]", "YourTable", "[YourCriteriaField(s) if needed]"), 0) + 1


just some thoughts,
Wayne

what this is, is a table in the backend called tblnextnum, which is only accessable via these procedures (ie not a linked table)

the idea is to temporarily lock the table, retrieve the number, increment it in the table and drop the recordset. although In this case you don't seem to be locking the lookup table.

in this case, you are using the field nextnum,

if you want multiple sequential numbers, simply add additional fields to the lookup table to suit, and change your references accordingly

thought so - but it was late last night and i was not at my best --

thanks
and thank to Wayne (I am going with Gemma on this )

gary

these routines are all in i think access developers handbook