Adding an item number

[Jacinda]

Hello to all... I have a little question...

I have a form for creating purchase orders. Each purchase order has it's own
uniquie PO number, then there can be several items ordered under that order
number. I need to create a sequence starting with 1 up to what ever number of
items is created on the po.

For example PO 99999 item 1 of 6; Item 2 of 6.
The PO information goes on one table, and the items go onto a detail table.
I don't want to use auto number because it will not restart from 1 for the
next PO.

What is the best way to do this, as I need the "1 of" to print on a report.

 

[John W. Vinson]

Hello to all... I have a little question...

I have a form for creating purchase orders. Each purchase order has it's own
uniquie PO number, then there can be several items ordered under that order
number. I need to create a sequence starting with 1 up to what ever number of
items is created on the po.

For example PO 99999 item 1 of 6; Item 2 of 6.
The PO information goes on one table, and the items go onto a detail table.
I don't want to use auto number because it will not restart from 1 for the
next PO.

What is the best way to do this, as I need the "1 of" to print on a report.

If you JUST need it on the report, put a textbox on the report with a Control
Source of =1. Set that textbox's Running Sum property to Over Group (assuming
you're grouping by invoice number). There's no need to have a field in the
table or the query.

If you do have some other reason to want a permanently stored item number, put
a Long Integer number field SeqNo in the detail table (which I presume you
have, related one to many to the PO table... do you??) and set its value in
the Subform's BeforeInsert event;

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtSeqNo = NZ(DMax("[SeqNo]", "PODetails", "PONo = " & Me!PONo)) + 1
End Sub

using your own fieldnames and tablenames of course.