Re: A real learner's question!

"BigDaddy" <BigDaddy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A2F73378-11F3-4452-9E7B-B268BFE7B43A@xxxxxxxxxxxxxxxx
Within a sales order entry application how do I ensure that the detail line
number always start at 1 for each new order.

I have the order number on the order header and order detail line going up
by 1 with each new order, but I want the detail line number to start back at
1 for each new order.

At the moment I get
order 10001, line numbers 1, 2, 3 & 4
order 10002, line numbers 5, 6 & 7 (should be 1, 2 & 3).

Thanks for the pointer in the right direction (in advance),
BD

If you want this (it's not really necessary) then you cannot use an AutoNumber
for the details table. Instead you can assign the number in the BeforeUpdate
event of the details form.

If Me.NewRecord Then
Me.LineNumber = Nz(DMax("LineNumber", "DetailsTable", "Order = " &
Me.Parent!Order),0) + 1
End If

This expression translates to...Find the highest line number in the details
table having the same order number as the current order in the parent form and
add one to it. The Nz() is for the first detail per order when the DMax() will
return a Null value.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com


.