Subform - Line Numbering

[Guest]

I need help - it almost works. I have a form [orders] with a subform [orderdetails]and need to have a lineitemno on each record for the order details.
From this forum (yea!) someone helped with the following code - but it always brings lineitemno to 1 - never increases as I add orderdetails. I have debugged (put a toggle point) and the new record is in the table with the lineitemno as 1 - so I am lost. I feel it must be something with the quotes but I can't get it run any other way.

Private Sub Form_BeforeInsert(Cancel As Integer)
Me![txtLineitem] = Nz(DMax("[LINEITEMNO]", "orderdetails", [ORDERNO] = " &Me![ORDERNO]")) + 1
End Sub

All help is greatly appreciated as I have spent 2 days with this already.
Thanks

 

[Douglas J. Steele]

Your quotes are wrong for the 3rd parameter

Me![txtLineitem] = Nz(DMax("[LINEITEMNO]", "orderdetails", "[ORDERNO] = " &
Me![ORDERNO])) + 1


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I need help - it almost works. I have a form [orders] with a subform

[orderdetails]and need to have a lineitemno on each record for the order
details.

From this forum (yea!) someone helped with the following code - but it

always brings lineitemno to 1 - never increases as I add orderdetails. I
have debugged (put a toggle point) and the new record is in the table with
the lineitemno as 1 - so I am lost. I feel it must be something with the
quotes but I can't get it run any other way.

Private Sub Form_BeforeInsert(Cancel As Integer)
Me![txtLineitem] = Nz(DMax("[LINEITEMNO]", "orderdetails", [ORDERNO] = " &Me![ORDERNO]")) + 1
End Sub

All help is greatly appreciated as I have spent 2 days with this already.
Thanks