A real learner's question!

[Guest]

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

 

[strive4peace]

Is this on a report or a form?

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Rick Brandt]

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.

 

[strive4peace]

To add on to what Rick said...

the autonumber field cannot be used to number the lines --
but you should still have an autonumber field in your table
to uniquely identify each record. The code Rick gave you
will do the trick. If you want the number to be assigned
when you first begin to add a record so that you can see it
on the form, run the same routine on the BeforeInsert event
(in this code, you do not need to test for NewRecord but if
you do, that is ok). If your application is multi-user, run
it again on the BeforeUpdate to make sure the number
assigned is still available as the record is actually being
saved.

You should create a seperate field for LineNumber and its
data type should be Number -- field size --> Integer (up to
32K) or Long Integer for higher numbers



Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Rick Brandt]

To add on to what Rick said...

the autonumber field cannot be used to number the lines --
but you should still have an autonumber field in your table to uniquely
identify each record. [snip]

Actually I would just use a composite PK on the Details table consisting of
Order Number and the Line Number. No need for a separate AutoNumber.

 

[strive4peace]

Rick,

Guess it comes down to personal preference, I like to have a
unique field to identify records -- much greater flexibility
(I have an "anywhere notes" feature that needs that -- as
well as other reasons)

If there may be related records, it is a lot better to use
one field, not 2 or more -- also, IMHO, PKs should not be
composed from user-data.

I would put a a unique index on the combination of OrderID
and LineNum


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

To add on to what Rick said...

the autonumber field cannot be used to number the lines --
but you should still have an autonumber field in your table to uniquely
identify each record. [snip]

Actually I would just use a composite PK on the Details table consisting of
Order Number and the Line Number. No need for a separate AutoNumber.

 

[Guest]

Awesome thanks Rick and Crystal.
BD

Rick,

Guess it comes down to personal preference, I like to have a
unique field to identify records -- much greater flexibility
(I have an "anywhere notes" feature that needs that -- as
well as other reasons)

If there may be related records, it is a lot better to use
one field, not 2 or more -- also, IMHO, PKs should not be
composed from user-data.

I would put a a unique index on the combination of OrderID
and LineNum


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

To add on to what Rick said...

the autonumber field cannot be used to number the lines --
but you should still have an autonumber field in your table to uniquely
identify each record. [snip]

Actually I would just use a composite PK on the Details table consisting of
Order Number and the Line Number. No need for a separate AutoNumber.

 

[strive4peace]

you're welcome happy to help

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Awesome thanks Rick and Crystal.
BD

:

Rick,

Guess it comes down to personal preference, I like to have a
unique field to identify records -- much greater flexibility
(I have an "anywhere notes" feature that needs that -- as
well as other reasons)

If there may be related records, it is a lot better to use
one field, not 2 or more -- also, IMHO, PKs should not be
composed from user-data.

I would put a a unique index on the combination of OrderID
and LineNum


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

"strive4peace" <"strive4peace2006 at yahoo dot com"> wrote in message


To add on to what Rick said...

the autonumber field cannot be used to number the lines --
but you should still have an autonumber field in your table to uniquely
identify each record. [snip]


Actually I would just use a composite PK on the Details table consisting of
Order Number and the Line Number. No need for a separate AutoNumber.

 

[Guest]

Thanks Rick, this is what I was looking for.

Mu table names are "Orders" and "Order Details".
My relevant field names are "Order Id" and "Line No".

So I think your code would need to read

If Me.NewRecord Then
Me.Line No = Nz(DMax("Line No", "Order Details", "Orders = " &
Me.Parent!Order Id),0) + 1
End If

Is this correct?

I currently get an error message saying "Microsoft Access can't find macro
'If Me.'

BD
PS I do have a PK of Order Id*Line No

 

[strive4peace]

If Me.NewRecord Then
Me.Line_No = _
Nz(DMax("[Line No]", _
"[Order Details]", _
"Orders = " & _
Me.Parent!Order_Id),0) _
+ 1
End If

since your control name has a space in it, the reference
will have an underscore

when you refer to field and table names in your dMax
equation, you must use brackets if it contains a space or
other special characters -- that is one reason why it is
good not to use spaces in names.


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Guest]

Thanks Crystal.
I have renamed everything without spaces but I still get the error message
"Microsoft Access can't find the macro "If Me. The macro (or its macro group)
doesn't exist, or the macro is new and hasn't been saved".

Any ideas?

Cheers,
BD

If Me.NewRecord Then
Me.Line_No = _
Nz(DMax("[Line No]", _
"[Order Details]", _
"Orders = " & _
Me.Parent!Order_Id),0) _
+ 1
End If

since your control name has a space in it, the reference
will have an underscore

when you refer to field and table names in your dMax
equation, you must use brackets if it contains a space or
other special characters -- that is one reason why it is
good not to use spaces in names.


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Thanks Rick, this is what I was looking for.

Mu table names are "Orders" and "Order Details".
My relevant field names are "Order Id" and "Line No".

So I think your code would need to read

If Me.NewRecord Then
Me.Line No = Nz(DMax("Line No", "Order Details", "Orders = " &
Me.Parent!Order Id),0) + 1
End If

Is this correct?

I currently get an error message saying "Microsoft Access can't find macro
'If Me.'

BD
PS I do have a PK of Order Id*Line No

:

 

[RoyVidar]

BigDaddy wrote in message

Thanks Rick, this is what I was looking for.

Mu table names are "Orders" and "Order Details".
My relevant field names are "Order Id" and "Line No".

So I think your code would need to read

If Me.NewRecord Then
Me.Line No = Nz(DMax("Line No", "Order Details", "Orders = " &
Me.Parent!Order Id),0) + 1
End If

Is this correct?

I currently get an error message saying "Microsoft Access can't find
macro 'If Me.'

BD
PS I do have a PK of Order Id*Line No

Just to be sure - you have placed this code into the event procedure of
the before update event of the form?

In the property dialog, when in the Before Update line, hit the button
with three dots at the right, and place the code within the

private sub form_beforeupdate(cancel as integer)

' and

end sub

Another question - just to be sure you do use a composite primary key
consisting of the fields Orders and [Line No] (selected both fields in
the design view of the table, and hit the primary key button), you
haven't concatenated this into a new field?

 

[Rick Brandt]

Thanks Rick, this is what I was looking for.

Mu table names are "Orders" and "Order Details".
My relevant field names are "Order Id" and "Line No".

So I think your code would need to read

If Me.NewRecord Then
Me.Line No = Nz(DMax("Line No", "Order Details", "Orders = " &
Me.Parent!Order Id),0) + 1
End If

Is this correct?

No. The third argument needs to apply criteria against the [Order Id] field of
the Order Details table. The Orders table doesn't enter into this at all.

If Me.NewRecord Then
Me.Line No = Nz(DMax("[Line No]", "[Order Details]", "[Order Id] = " &
Me.Parent![Order Id]),0) + 1
End If

I currently get an error message saying "Microsoft Access can't find macro
'If Me.'

Sounds like you entered the code directly into the property sheet. That works
with the names of macros or the names of VBA functions. For VBA code you need
to enter "[Event Procedure]" and then press the build button to the right [...].
That will take you to the VBA editor window and that is where you put the code.