Easy Autonumber question of an newbe

[melrios98]

I'm new in access and have a question here for all of the experts. I read some other post on this issue but not found a good example that work for me. Here is my request. I have 2 tables
Table : PO
PONo - AutoNumber
Date - Text
Name - Text

Table:Transactions
PONo - Number
Line - Number
Item - Text
Qty - Number

I create a Form(PO)and Subform(Transactions) of this tables related by the PONo

I want that each time I enter a New record PoNo (That is a Auto Number) the Line number begin on 1, 2, 3, ....

Example of transaction Table
PONo Line Item Qty
1025 1 FB14 50000
1025 2 SS50 40000
1025 3 GG40 20000
1026 1 FD25 500
1026 2 GH40 600


Thanks for your help

 

[neileg]

Use Dmax() to find the largest line number already allocated to that order number, and add 1.

It's usually advised not to use an autonumber for this type of data - you will get gaps in the numbering. If you search in these forums you will see examples of how to create your own sequential number (using Dmax).

 

[melrios98]

Thanks Neil for your fast responce

I try to create a Set Value Macro on the Before Update Event but not work for me. Can you help me??
That is what i do

Item: [Forms]![PO]![Transacction subform].[Form]![Line]
Expression: DMax("[PONo]","[PO]","[Line]= ")=[Line]+1

Please give me an idea

 

[neileg]

Sorry, I don't use macros, so I can't help there. But your syntax for DMax is wrong.

The expression should look something like this
DMax("[Line]","Transactions","[PoNo]=Forms!MyForm!MyPoNoControl")