Auto-incrementing number that starts over

[Koala]

Hi all, this is my first post here. I ran into an interesting problem that I need a solution to.



I have a table, Purchases, which has a 1:N relationship with another table, Items. Each purchase can have multiple Items.

'PurchaseID' is the primary key of my Purchases table.



The Items table has a composite primary key, made up of PurchaseID (making it an identifying relationship) and ItemID.



As you can see, I have ItemID starting reverting back to 1 each time PurchaseID increments. PurchaseID is an autonumber on the Purchases table, however ItemID is just a Number and has to be entered manually. If I set ItemID to an autonumber, it will continue to increment even after PurchaseID changes.

What I am trying to do is to get ItemID to auto-increment but revert back to 1 each time PurchaseID increments.

The entry form for both looks like this:



The Record Source of the main form is the Purchases table, and the subform is a Multiple Items Form based on the Items table. Filling out the form will create a new Purchase record with an auto-incremented PurchaseID, and a number of new Item records with that same PurchaseID, and whatever ItemID's are specified in the subform. Currently the ItemID's have to be entered manually as 1, 2, 3, etc.

Is there a way to get them to auto-increment, but begin from 1 for each new Purchase record?

Much thanks to all who can help me solve this.

[orange]

Just curious why do you need an item number as such.
What if the "things" that were Purchased were Products and these Products had separate ProductNumbers or IDs? You would have them on your Purchase (Order) and they would be line items, but they wouldn't be numbered 1,2,3....

I can't think of any way to do that with an autonumber.
But you could have reference variable. Set the value of this refNo to 0.
When you add an Item increment the refNo by 1.
When you open a new PurchaseOrder set the refNo back to 0.

If it's only for counting or to get NumberOfItems, you could do that with DCount() and a filter.

Just my quick $.02