AUTOMATICALLY RESTARTING AUTONUMBER FOR NEW CUSTOMER- TOUGH! word_novice 26.07.2006 10:49:01 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.

Re: AUTOMATICALLY RESTARTING AUTONUMBER FOR NEW CUSTOMER- TOUGH! "Craig Alexander Morrison" <cam[ at ]microsoft.newsgroups.public.com> 26.07.2006 11:07:40 Forget AutoNumber! RYO such as NextValue = DMax(......) + 1 -- Slainte Craig Alexander Morrison Crawbridge Data (Scotland) Limited "word_novice" <wordnovice[ at ]discussions.microsoft.com> wrote in message news:97226327-1EF0-4DBD-A83F-D128F2B4E311[ at ]microsoft.com... [Quoted Text] > 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.

Re: AUTOMATICALLY RESTARTING AUTONUMBER FOR NEW CUSTOMER- TOUGH! John Vinson <jvinson[ at ]STOP_SPAM.WysardOfInfo.com> 26.07.2006 17:59:30 On Wed, 26 Jul 2006 03:49:01 -0700, word_novice <wordnovice[ at ]discussions.microsoft.com> wrote: [Quoted Text] >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]