AUTOMATICALLY RESTARTING AUTONUMBER FOR NEW CUSTOMER- TOUGH!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

word_novice - 26 Jul 2006 11:49 GMT

Dear Highly Esteemed Access Gurus,

Thanks for reading my post.

Ok, the situation is that I have a database with a one-many relationship. So
one customer, with one customer reference can have many items of different
types such as:

CUSTOMER 1
4 pots
2 plates
9 cups

What I want to achieve is that for customer 2, the numbering starts all over
again so for customer 2 the numbering will start again from 1 for pots,
plates amd cups.

At the moment, for customer 2, the numbering continues from 5 pots, 3 plates
10 cups but I want it to restart, or can i use filters? please suggest.
I am desperately in need of help and I have been trying to solve this for 2
months now with little success. Please Help.

Reply to this Message

Craig Alexander Morrison - 26 Jul 2006 12:07 GMT

Forget AutoNumber!

RYO such as NextValue = DMax(......) + 1

Signature

Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

> Dear Highly Esteemed Access Gurus,
>
[quoted text clipped - 21 lines]
> 2
> months now with little success. Please Help.

Reply to this Message

John Vinson - 26 Jul 2006 18:59 GMT

>What I want to achieve is that for customer 2, the numbering starts all over
>again so for customer 2 the numbering will start again from 1 for pots,
>plates amd cups.

You cannot use Autonumber for this purpose... period. It simply won't
work; that's not what autonumbers are for.

Instead, you'll need a bit of pretty easy VBA code in the Form in
which you're entering the data. (If you're using table datasheets to
enter data... well, *don't*.)

For instance, you could have a Customer form with a subform for the
items ordered; on the subform you could have code in the BeforeInsert
event like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtSeqNo = NZ(DMax("[SeqNo]", "[Orders]", "[CustomerID] = " & _
Me![CustomerID])) + 1
End Sub

This will look up the largest existing value of SeqNo (the
self-maintained "autonumber" field) in the Orders table) for this
customer; if there is no orders record yet the DMax() function will be
NULL. The NZ() function will turn that null to 0; adding 1 will give 1
if this is the first order for the customer, and increment the largest
existing SeqNo if there are already orders.

John W. Vinson[MVP]

Reply to this Message