Line Items for Invoices

[larochy]

Hi,

I have an Invoice table linked to a Products purchased table. I created a
form showing the Invoice information and a subform to enter in the Products
purchased. On the subform, a customer can purchase many products and I want
the subform to generate a "Line Item" number for each product purchased on a
given invoice. This is how I would like it to look when filtering through
the Invoices.

Invoice = 1000
LineItem Product InvoiceNumber
001 A 1000
002 B 1000

Invoice = 1001
LineItem Product InvoiceNumber
001 A 1001
002 B 1001
003 C 1001


My question is, on the form, how do I get the Line Item to automatically
generate each time I add a new line to enter a new product purchased? I
would like it to start at 001 for each Invoice and add 1 each time I enter a
new line. I was thinking I would need to add some code to the Before_Insert
command but I'm not sure how to write the code for this. Any help would be
much appreciated.

 

[larochy]

I figured it out....I built a query called "LineItem" with two fields,
Invoice Number and Line Item with the criteria set to the Invoice Number in
the open form. Added this code to the BeforeInsert command of the Form.

Private Sub Form_BeforeInsert(Cancel As Integer)
Me![LineItem] = Nz(DMax("[LineItem]", "LineItem"), 0) + 1
End Sub