How to increment line number on form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there

I'm looking to find a way to assign a 'line number' on each record on a sub form (write to the table) and then with the next header record, start at 1 again and assign the new line numbers for the sub records associated with that header record

I thought something like below would work, but I'm not sure how to apply -
do I need to use insert statement to enter the number into my field (how would this look)
And then how do I get it to reset for the next set of sub records

Dim NextReceiptLineNo As Lon

If IsNull(DMax("[ReceiptLineNo]", "tblReceiptLines")) The
NextReceiptLineNo = 1
Els
NextReceiptReceiptLineNo = DMax("[ReceiptLineNo]", "tblReceiptLines") +
End I

NextReceiptLineNo = DMax("[ReceiptLineNo]", "tblReceiptLines") +

Thank
Shawn
 
Hi there,

I'm looking to find a way to assign a 'line number' on each record on a sub form (write to the table) and then with the next header record, start at 1 again and assign the new line numbers for the sub records associated with that header record.

I thought something like below would work, but I'm not sure how to apply -
do I need to use insert statement to enter the number into my field (how would this look)
And then how do I get it to reset for the next set of sub records?

Dim NextReceiptLineNo As Long

If IsNull(DMax("[ReceiptLineNo]", "tblReceiptLines")) Then
NextReceiptLineNo = 1
Else
NextReceiptReceiptLineNo = DMax("[ReceiptLineNo]", "tblReceiptLines") + 1
End If

NextReceiptLineNo = DMax("[ReceiptLineNo]", "tblReceiptLines") + 1
Click to expand...

Your code would work if it were put into the form's BeforeInsert event
and if you referenced the receipt number... but you can do the same
thing in one line:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!ReceiptLineNo = NZ(DMax("[ReceiptLineNo]", "tblReceiptLines", _
"[ReceiptNo] = " & Me![ReceiptNo])) + 1
End Sub
 
Thank you for your suggestion, I can't seem to get it to work though. It is populating the line number with 0(zero) - do i initialize this number somewhere?.

Before Insert on form:

Me!ReceiptLineNo = NZ(DMax("[ReceiptLineNo]", "tblReceiptLines", _
"[ReceiptNo] = " & Me![ReceiptNo])) + 1


Thank you for your help.
 
Thank you for your suggestion, I can't seem to get it to work though. It is populating the line number with 0(zero) - do i initialize this number somewhere?.

Before Insert on form:

Me!ReceiptLineNo = NZ(DMax("[ReceiptLineNo]", "tblReceiptLines", _
"[ReceiptNo] = " & Me![ReceiptNo])) + 1
Click to expand...

I was assuming (without saying so) that you have a field named
ReceiptNo on the form, and that you want ReceiptLineNo to increment
from 1 for each new ReceiptNo; and that the code is on the Subform in
which you're entering receipt lines. The DMax() function will return
the largest existing ReceiptLineNo in the table tblReceiptLines for
the current record's ReceiptNo; if there is no such record, NZ() will
return a 0; the + 1 will put a 1 into the control named ReceiptLineNo
on the subform if there was no line already, and one more than the
largest value if there was.

I suspect you may have the code in the wrong place but since I can't
see your database I can't be sure!
 
Thanks again

I'll look into this, I think I did put the code on the main form, rather then the sub?
 
Back
Top