autonumbers relating to another field

[rolfrobinson]

Hi I have tried searching for an answer with no luck.
I am writing a table to do a filing cabinet archive.

I have a table with 3 fields in one is box no, second is item no and third is a description.

I have the first 2 linked as a primary key. The description is just text.
I want to be able to enter a box no and to have the second (item) to enter a number automatically following a sequential no which is relevant to that box. ie
box no Item No
1...........1
1...........2
1...........3
2...........1
2...........2
3...........1
1...........4 this one takes the next no available for box no 1
2...........3 and this one does the same for box no 2

if you go back to a box and start reinputting it picks up the next number.
should i sack trying to do it with the autonumber?

 

[FoFa]

The easiest approach is to use a count function + 1 (it handles zero that way).
So basically do a count of BoxNo and add 1 to it for your sequence.
You can use Dcount or some other method.

MySeqNo = 1 + Dcount("boXNo","MyTable","boXNo = " & Me.BoxNo)

as a "top o' the head" example

 

[rolfrobinson]

This may sound like a daft question but which part of the table do i put this in?? default value. validation rule?
Thinking about it would i be better doing it in a form? if yes then could you help me with the expression?

if my box no is {1} Item No {2} (its the item{2} that I want TO update automatically by looking at the box no{1} and see what the last item no is{2}.) and say the table where its from is {3}.
Then where do they fit into the equations below?
If its in a form do i update before /on exit/ etc.. and which form box do i put it the code in {1} or {2}

MySeqNo = 1 + Dcount("boXNo","MyTable","boXNo = " & Me.BoxNo) or
SomeField = Nz(DMax(......),0) + 1