Sequential numbering

[cliff williams]

how can I set a form so that when i open it , it appears with the next
sequential number. I want to set the number pattern and not use
autonumber

[PC Datasheet]

Cliff,

Say you have a TblCustomer table with a CustomerNum field. You have a
FrmCustomer based on TblCustomer and the form has a textbox bound to the
CustomerNum field. Put this expression in the Default Value property(Data tab)
of the textbox:
DMax("[CustomerNum]","TblCustomer")+1

When you enter something in any field in the form and save the record,
CustomerNum will automatically have the next sequential number.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"cliff williams" <cw*******@multipak.com> wrote in message
news:78**************************@posting.google.c om...

how can I set a form so that when i open it , it appears with the next
sequential number. I want to set the number pattern and not use
autonumber

[Jim Allensworth]

On Thu, 11 Dec 2003 20:57:45 GMT, "PC Datasheet" <sp**@nospam.com>
wrote:

Cliff,

Say you have a TblCustomer table with a CustomerNum field. You have a
FrmCustomer based on TblCustomer and the form has a textbox bound to the
CustomerNum field. Put this expression in the Default Value property(Data tab)
of the textbox:
DMax("[CustomerNum]","TblCustomer")+1

When you enter something in any field in the form and save the record,
CustomerNum will automatically have the next sequential number.

If this is a multi-user environment (or could become one) it is better
to apply the sequence in the Before Insert event.

More than one user could be adding a record with the same CustomerNum.
Besides, the id has no relevance until a record is created.

- Jim

[Kinetic - Paul G]

I assume the form is linked to a table or is adding data to a table . .

so if the table is called tbl_INFO with a key of INFO_KEY (long number )
then you can get the next key number with

NextNumber = Dmax("[INFO_KEY]","tbl_INFO") + 1

I use this all the time even if the key is set to autonumber . . as it gives
the key to the new record for use elsewhere in any required code.

hope this helps
cheers
paul g

"cliff williams" <cw*******@multipak.com> wrote in message
news:78**************************@posting.google.c om...

how can I set a form so that when i open it , it appears with the next
sequential number. I want to set the number pattern and not use
autonumber

[Larry Linson]

The DMax domain aggregate function can be used to get the highest value in a
field in a table, and you can increment that to create the next one. Of
course, you want to be sure you do this only for new records... a little
slipup and you might overwrite existing key fields.

Larry Linson
Microsoft Access MVP
"cliff williams" <cw*******@multipak.com> wrote in message
news:78**************************@posting.google.c om...

how can I set a form so that when i open it , it appears with the next
sequential number. I want to set the number pattern and not use
autonumber