Help with Number sequence function

[Patrice K via AccessMonster.com]

I am a beginner to access and need some helpful tips. I have a database for
Purchase orders and I would like for the PO Number to increment and be the
next number in sequence with the PO ID primary key autonumber when the form
opens. I used autonumber data type for PO ID the Primary Key and number data
type for PO Number. I am not sure how to code the sequence function or what
event to put the expression. I tried to use a DMax function but the code
doesn’t look like it is working in Default Value. The #Error will populate
the textbox not a number.

= DMax("PurchaseOrderNumber","Purchase Order")+1

Where "PO Number" is the Field name I want to put the number in, and
"Purchase Order" is the Table name.

The form will be a multiple user form. If I could get the PO Number to
increment with the PO ID I will be almost home with this project. If the DMax
function is not the way to go, any suggestion how to code this form to have
the PO Number move in sequence with the PO ID when the form open to a new
record.

I do appreciate any assistance with this issue.

Thanks

Patrice

 

[tina]

= DMax("PurchaseOrderNumber","Purchase Order")+1

as long as the field and table names are spelled correctly, it should work,
perhaps with a slight change to handle the "first" record, as

= Nz(DMax("PurchaseOrderNumber","Purchase Order"), 0) +1

**note**: assigning an incremented, sequential number to new records in a
multi-user database - without gaps or duplicates - is tricky.

if you assign a number using the DefaultValue property of a control in a
form, you greatly increase the chance of ending up with duplicate entries in
the table - or a unique index error when you try to save a new record to the
table. this is because the DefaultValue will be calculated as soon as you
move to a new record in the form, and *before you even begin to enter a new
record".

if you assign the value on the BeforeInsert event, you'll still face the
same issues - because during the time that one user is entering data into a
new record, another user can start a new record. whoever saves the record
first will have no problem. the second person to save will either cause a
duplicate or get the index error, as described above.

if you assign the value on the form's BeforeUpdate event, you lessen the
chances of the above problems occurring but do *not* prevent them entirely.
this issue has been debated numerous times here in the newsgroups, but i've
never seen a proposal for a 100 percent reliable solution that can be
implemented in Access. but then, i don't read every thread every day in
every Access newsgroup, either - so you might want to google the issue and
see what you can find out.

hth