Number subform items sequentially

[Sammie]

My subform has a field called [order] where the user enters the order in
which the items are to appear in a report.
Default value is 1. How do I automatically increment the order field by 1
each time a new item is added to the subform, so the next item is 2, next is
3 etc.? The purpose of this field is so the user can manually re-order the
items, but usually it can be automatic.

 

[BruceM]

"Order" is a reserved word in SQL and in Jet, so you should use a different
name for the field, such as ItemOrder. Assuming the subform's record source
is the table tblItems, and that the linking field is the Number field
MainID, you could use something like this as the Default Value of a text box
bound to [ItemOrder]:
=DMax("[ItemOrder]","[tblItems]","[MainID] = " & Me.MainID) + 1
If MainID is text it could be (spaces between the quotes are added for
clarity here):
=DMax("[ItemOrder]","[tblItems]","[MainID] = " " " & Me.MainID & " " " ") +
1
This finds the maximum value in the subform's record source (tblItems) in
which MainID (the linking field) is the same as MainID in the current
record.

 

[Sammie]

I tried your suggestion. I renamed the 'order' field to ItemNo (numeric). I
set the following default value to ItemNo, where my record source is a query
called [invoice details extended] and the main and sub forms are linked by
numeric field shipmentID:

= DMax("[ItemNo]", "[invoice details extended]", "[ShipmentID] = " &
[Me].[ShipmentID]) + 1

No joy. Am I following your example correctly? Is it a problem that the
record source for the subform is a query and not a table?

Thanks.
Sammie Access 2003

"Order" is a reserved word in SQL and in Jet, so you should use a different
name for the field, such as ItemOrder. Assuming the subform's record source
is the table tblItems, and that the linking field is the Number field
MainID, you could use something like this as the Default Value of a text box
bound to [ItemOrder]:
=DMax("[ItemOrder]","[tblItems]","[MainID] = " & Me.MainID) + 1
If MainID is text it could be (spaces between the quotes are added for
clarity here):
=DMax("[ItemOrder]","[tblItems]","[MainID] = " " " & Me.MainID & " " " ") +
1
This finds the maximum value in the subform's record source (tblItems) in
which MainID (the linking field) is the same as MainID in the current
record.

My subform has a field called [order] where the user enters the order in
which the items are to appear in a report.
Default value is 1. How do I automatically increment the order field by 1
each time a new item is added to the subform, so the next item is 2, next
is
3 etc.? The purpose of this field is so the user can manually re-order
the
items, but usually it can be automatic.

 

[BruceM]

Oops! I used the syntax for VBA. Try:
= DMax("[ItemNo]", "[invoice details extended]", _
"[ShipmentID] = " & [ShipmentID] & "") + 1

The last part of DMax (the ampersand and the two quote marks) may not need
to be there, but they won't do any harm either. It seems to work both ways:
= DMax("[ItemNo]", "[invoice details extended]", _
"[ShipmentID] = " & [ShipmentID]) + 1

Note in both cases that the underscore is an attempt to control the line
breaks in the newsreader. The code goes on one line, and the underscores
aren't used in the actual expression.

I tried your suggestion. I renamed the 'order' field to ItemNo (numeric).
I
set the following default value to ItemNo, where my record source is a
query
called [invoice details extended] and the main and sub forms are linked by
numeric field shipmentID:

= DMax("[ItemNo]", "[invoice details extended]", "[ShipmentID] = " &
[Me].[ShipmentID]) + 1

No joy. Am I following your example correctly? Is it a problem that the
record source for the subform is a query and not a table?

Thanks.
Sammie Access 2003

"Order" is a reserved word in SQL and in Jet, so you should use a
different
name for the field, such as ItemOrder. Assuming the subform's record
source
is the table tblItems, and that the linking field is the Number field
MainID, you could use something like this as the Default Value of a text
box
bound to [ItemOrder]:
=DMax("[ItemOrder]","[tblItems]","[MainID] = " & Me.MainID) + 1
If MainID is text it could be (spaces between the quotes are added for
clarity here):
=DMax("[ItemOrder]","[tblItems]","[MainID] = " " " & Me.MainID & " " " ")
+
1
This finds the maximum value in the subform's record source (tblItems) in
which MainID (the linking field) is the same as MainID in the current
record.

My subform has a field called [order] where the user enters the order
in
which the items are to appear in a report.
Default value is 1. How do I automatically increment the order field
by 1
each time a new item is added to the subform, so the next item is 2,
next
is
3 etc.? The purpose of this field is so the user can manually re-order
the
items, but usually it can be automatic.