How to start at number 1 for each new record in a related table?

[Ryno Bower]

Hello everyone*
I have a quoestion to ask regarding lines like for instance on a quote.
*
I have two tables: QuoteHdr and QuoteDet
*
On QuoteHdr I have fields: QuoteNo, Customer, QuoteDate, CustomerPoNo etc
And on QuoteDet I have*: Line(AutoNumber), Item, Item descr, Qty, Retail, QuoteNo*etc.
I have a 1-many relationship between QuoteNo and QuoteNo.
*
What I want is that when I enter a new quote and I add lines, the lines must start at 1 angain for each new quote.
Do I have to create another table, maybe have a foreign key, what kind of field format should I use?* What should I do?
Please help.
*
For each new QuoteNo, the quote detail lines must start at 1 again -
*
Thanks
Ryno

[Rabbit]

You can do it at runtime using a ranking query or you can store it by getting the count of the lines for that quote and add 1.

[Ryno Bower]

Hey, I'm sorry. I don't understand what you are trying to say. Could you please explain or maybe give another suggestion.
Thanks

[TheSmileyCoder]

If you make a field called lngQuoteLine and then in your subform (The form you use to enter the quote details) you have an event in either Before_Insert or before_Update where you assign the number to lngQuoteLine.

It could look something like this:

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.tb_LineQuoteNumber = nz(DMax("lngQuoteLine", "tbl_QuoteDetails","lngQuoteNo=" & me.tb_QuoteNo),0)+1
End Sub