Question Increment field based on an autonumber field

[beachy]

Hi everyone,

Sorry to ask a question as soon as I have joined but I am really stuck with a timber stock program I am developing.

Basically each pack of timber is assigned a four digit ID no. using access autonumbers. We now want to develop the stock system further so that each board is allocated to a pack. I have created a form which shows my table tblTally in datasheet view on the second tab. This table is filtered by a combo box on the first tab.

Everything is working but I want to create a field Board No.which auto increments based on the pack ID. So it will start at 1 and increase by one each time a new board is added. What I dont want to happen is for the autonumber to continue increasing once a different pack has been selected. I want it to restart at one.

So you will end up with this:

1679 1
1679 2
1679 3
1679 4
1740 1
1740 2
1740 3
1679 5
1679 6
1020 1
1020 2

Any help is much appreciated. Thanks is advance!

 

[vbaInet]

Welcome to AWF

You don't want to save that number, you want to use a query to buil those numbers.

Have a look at this:

http://www.access-programmers.co.uk/forums/showthread.php?t=91574&highlight=sequential+numbering

 

[John Big Booty]

You could also use the DMax() function +1

In the sample I've used DMax to assign pack numbers and then to assign sequential plank numbers.

 

[beachy]

Thanks for the welcome!

Your example looks perfect just gonna take it apart to see how it works!

Many thanks!