Sequence Number

[reterrig]

I have created a form "Expense Reports" & a subfrom "Expense Reports Subform
Linked on Fields "ExpenseReportID".
Subform get the data from the table "Expense Details". Among the other
fields there is a filed "ExpenseNumber". Subform is on a dataview form
I need each time that I add a new line in the Subform, a sequence number to
be displayed in the "ExpenseNumber" field that should be unique and in case
of deletion of a line the sequence not to be interrupted and the new line to
get the next in sequence number so to be a conistency in the numbering.
Please help

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "AutonumberProblem.mdb" which illustrates how to do this. You
can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=395

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[reterrig]

Roger,
Thanks for your reply. Regret but I have inserted the relevant command in
the Default Value of the ExpenseNumber field but does not work properly. On
the ExpenseNumber field shows "#Error" and displays a Zero when I inserst
data in the rest fields

 

[Roger Carlson]

You can't put a user defined function in the default value of a field. You
have to do it in a control on a form.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[reterrig]

Roger,

I have copied your set up :=DMax("ProductID";"Product")+1
I replaced the filed name and the table name and i inserted in the same
position that you have done in your example. Is anything else that I have to
do

 

[BruceM]

If these are subform records you will need a Where condition (assuming you
mean to start numbering from one for every set of subform records). You
will also need to use Nz. I think it would work also to have the default
value for the field set to 0 instead, but I'm not sure.

=Nz(DMax("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " & Forms!frmExpenseReport!ExpenseReportID),0) + 1

The DMax part is saying: "Find the highest value for ExpenseNumber in the
ExpenseDetails table in the record in which the ExpenseReportID field is the
same as ExpenseReportID on the parent form (frmExpenseReport). The Nz says:
"If this value is null, substitute the value 0."

This may work if the default value for ExpenseNumber is 0:

=DMax("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " & Forms!frmExpenseReport!ExpenseReportID) + 1

This same approach can be taken using VBA code. For instance, in the form's
Current event:

If Me.NewRecord Then
Me.ExpenseNumber = Nz(DMax(("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " & Me.Parent.ExpenseReportID),0) + 1
End If

Note that the underscores in the text box expressions are for ease of
reading here, and need to be removed in the actual expression. The
underscore in the VBA expression should work as written.

Sometimes when I increment a number there are several variables: "Find the
largest value for SomeField in the record in which {Condition1} and
{Condition2} and {Condition3}". In that case I find it easier to manage in
VBA code. In your case the text box expression, as Roger's sample database
shows, should be fine.

Note too that whatever approach you take you may need to take precautions
against producing a duplicate value in a multi-user environment. Roger's
sample code shows how to manage that in the context of the method he
demonstrated.

 

[reterrig]

Bruce,

I had used the commmands in the VBA code and works perfectly. Its starts
numbering from one for every set of subforms records. That was I needed.

Greate thanks for your assistance.

If these are subform records you will need a Where condition (assuming you
mean to start numbering from one for every set of subform records). You
will also need to use Nz. I think it would work also to have the default
value for the field set to 0 instead, but I'm not sure.

=Nz(DMax("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " & Forms!frmExpenseReport!ExpenseReportID),0) + 1

The DMax part is saying: "Find the highest value for ExpenseNumber in the
ExpenseDetails table in the record in which the ExpenseReportID field is the
same as ExpenseReportID on the parent form (frmExpenseReport). The Nz says:
"If this value is null, substitute the value 0."

This may work if the default value for ExpenseNumber is 0:

=DMax("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " & Forms!frmExpenseReport!ExpenseReportID) + 1

This same approach can be taken using VBA code. For instance, in the form's
Current event:

If Me.NewRecord Then
Me.ExpenseNumber = Nz(DMax(("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " & Me.Parent.ExpenseReportID),0) + 1
End If

Note that the underscores in the text box expressions are for ease of
reading here, and need to be removed in the actual expression. The
underscore in the VBA expression should work as written.

Sometimes when I increment a number there are several variables: "Find the
largest value for SomeField in the record in which {Condition1} and
{Condition2} and {Condition3}". In that case I find it easier to manage in
VBA code. In your case the text box expression, as Roger's sample database
shows, should be fine.

Note too that whatever approach you take you may need to take precautions
against producing a duplicate value in a multi-user environment. Roger's
sample code shows how to manage that in the context of the method he
demonstrated.

Roger,

I have copied your set up :=DMax("ProductID";"Product")+1
I replaced the filed name and the table name and i inserted in the same
position that you have done in your example. Is anything else that I have
to
do

 

[BruceM]

I have no idea how that is working with Roger's sample code since it does
not contain a provision for starting over from one(at least not the version
I have seen), but if it does what you need that's all you need.
..

Bruce,

I had used the commmands in the VBA code and works perfectly. Its starts
numbering from one for every set of subforms records. That was I needed.

Greate thanks for your assistance.

If these are subform records you will need a Where condition (assuming
you
mean to start numbering from one for every set of subform records). You
will also need to use Nz. I think it would work also to have the default
value for the field set to 0 instead, but I'm not sure.

=Nz(DMax("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " & Forms!frmExpenseReport!ExpenseReportID),0) + 1

The DMax part is saying: "Find the highest value for ExpenseNumber in the
ExpenseDetails table in the record in which the ExpenseReportID field is
the
same as ExpenseReportID on the parent form (frmExpenseReport). The Nz
says:
"If this value is null, substitute the value 0."

This may work if the default value for ExpenseNumber is 0:

=DMax("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " & Forms!frmExpenseReport!ExpenseReportID) + 1

This same approach can be taken using VBA code. For instance, in the
form's
Current event:

If Me.NewRecord Then
Me.ExpenseNumber = Nz(DMax(("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " & Me.Parent.ExpenseReportID),0) + 1
End If

Note that the underscores in the text box expressions are for ease of
reading here, and need to be removed in the actual expression. The
underscore in the VBA expression should work as written.

Sometimes when I increment a number there are several variables: "Find
the
largest value for SomeField in the record in which {Condition1} and
{Condition2} and {Condition3}". In that case I find it easier to manage
in
VBA code. In your case the text box expression, as Roger's sample
database
shows, should be fine.

Note too that whatever approach you take you may need to take precautions
against producing a duplicate value in a multi-user environment. Roger's
sample code shows how to manage that in the context of the method he
demonstrated.

Roger,

I have copied your set up :=DMax("ProductID";"Product")+1
I replaced the filed name and the table name and i inserted in the same
position that you have done in your example. Is anything else that I
have
to
do

:

You can't put a user defined function in the default value of a field.
You
have to do it in a control on a form.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger,
Thanks for your reply. Regret but I have inserted the relevant
command
in
the Default Value of the ExpenseNumber field but does not work
properly.
On
the ExpenseNumber field shows "#Error" and displays a Zero when I
inserst
data in the rest fields

:

On my website (www.rogersaccesslibrary.com), is a small Access
database
sample called "AutonumberProblem.mdb" which illustrates how to do
this.
You
can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=395

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
I have created a form "Expense Reports" & a subfrom "Expense
Reports
Subform
Linked on Fields "ExpenseReportID".
Subform get the data from the table "Expense Details". Among the
other
fields there is a filed "ExpenseNumber". Subform is on a dataview
form
I need each time that I add a new line in the Subform, a sequence
number
to
be displayed in the "ExpenseNumber" field that should be unique
and
in
case
of deletion of a line the sequence not to be interrupted and the
new
line
to
get the next in sequence number so to be a conistency in the
numbering.
Please help

 

[reterrig]

Bruce,

I have followed your instructions.

When I re open an existing record of the form the sequence number works
perfectly. When I try to open a new record in the form ten an error is
appeared:" Run-tine error '3075':
Syntax error (missing operator) in query expression '[ExpenseReportID]='

I cannot solve this. Any thought?

Rgds
Peter

I have no idea how that is working with Roger's sample code since it does
not contain a provision for starting over from one(at least not the version
I have seen), but if it does what you need that's all you need.
..

Bruce,

I had used the commmands in the VBA code and works perfectly. Its starts
numbering from one for every set of subforms records. That was I needed.

Greate thanks for your assistance.

If these are subform records you will need a Where condition (assuming
you
mean to start numbering from one for every set of subform records). You
will also need to use Nz. I think it would work also to have the default
value for the field set to 0 instead, but I'm not sure.

=Nz(DMax("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " & Forms!frmExpenseReport!ExpenseReportID),0) + 1

The DMax part is saying: "Find the highest value for ExpenseNumber in the
ExpenseDetails table in the record in which the ExpenseReportID field is
the
same as ExpenseReportID on the parent form (frmExpenseReport). The Nz
says:
"If this value is null, substitute the value 0."

This may work if the default value for ExpenseNumber is 0:

=DMax("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " & Forms!frmExpenseReport!ExpenseReportID) + 1

This same approach can be taken using VBA code. For instance, in the
form's
Current event:

If Me.NewRecord Then
Me.ExpenseNumber = Nz(DMax(("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " & Me.Parent.ExpenseReportID),0) + 1
End If

Note that the underscores in the text box expressions are for ease of
reading here, and need to be removed in the actual expression. The
underscore in the VBA expression should work as written.

Sometimes when I increment a number there are several variables: "Find
the
largest value for SomeField in the record in which {Condition1} and
{Condition2} and {Condition3}". In that case I find it easier to manage
in
VBA code. In your case the text box expression, as Roger's sample
database
shows, should be fine.

Note too that whatever approach you take you may need to take precautions
against producing a duplicate value in a multi-user environment. Roger's
sample code shows how to manage that in the context of the method he
demonstrated.

Roger,

I have copied your set up :=DMax("ProductID";"Product")+1
I replaced the filed name and the table name and i inserted in the same
position that you have done in your example. Is anything else that I
have
to
do

:

You can't put a user defined function in the default value of a field.
You
have to do it in a control on a form.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger,
Thanks for your reply. Regret but I have inserted the relevant
command
in
the Default Value of the ExpenseNumber field but does not work
properly.
On
the ExpenseNumber field shows "#Error" and displays a Zero when I
inserst
data in the rest fields

:

On my website (www.rogersaccesslibrary.com), is a small Access
database
sample called "AutonumberProblem.mdb" which illustrates how to do
this.
You
can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=395

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
I have created a form "Expense Reports" & a subfrom "Expense
Reports
Subform
Linked on Fields "ExpenseReportID".
Subform get the data from the table "Expense Details". Among the
other
fields there is a filed "ExpenseNumber". Subform is on a dataview
form
I need each time that I add a new line in the Subform, a sequence
number
to
be displayed in the "ExpenseNumber" field that should be unique
and
in
case
of deletion of a line the sequence not to be interrupted and the
new
line
to
get the next in sequence number so to be a conistency in the
numbering.
Please help

 

[BruceM]

I thought you said it was working.

I just realized that the reason it is not working is that the subform loads
before the main form, so when the subform's Current event first runs there
is no parent record.

I haven't tested this, but I think you could use the same code in the form's
Before Insert event, except that you don't need to test for a new record
since the Before Insert event runs only for a new record:
Me.ExpenseNumber = Nz(DMax(("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " & Me.Parent.ExpenseReportID),0) +
1

If there is any chance of two users working on the same record at the same
time (that is, two users working with the same main form record) you will
need to guard against duplicate numbers in Expense Number.

Bruce,

I have followed your instructions.

When I re open an existing record of the form the sequence number works
perfectly. When I try to open a new record in the form ten an error is
appeared:" Run-tine error '3075':
Syntax error (missing operator) in query expression '[ExpenseReportID]='

I cannot solve this. Any thought?

Rgds
Peter

I have no idea how that is working with Roger's sample code since it does
not contain a provision for starting over from one(at least not the
version
I have seen), but if it does what you need that's all you need.
..

Bruce,

I had used the commmands in the VBA code and works perfectly. Its
starts
numbering from one for every set of subforms records. That was I
needed.

Greate thanks for your assistance.

:

If these are subform records you will need a Where condition (assuming
you
mean to start numbering from one for every set of subform records).
You
will also need to use Nz. I think it would work also to have the
default
value for the field set to 0 instead, but I'm not sure.

=Nz(DMax("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " & Forms!frmExpenseReport!ExpenseReportID),0)
+ 1

The DMax part is saying: "Find the highest value for ExpenseNumber in
the
ExpenseDetails table in the record in which the ExpenseReportID field
is
the
same as ExpenseReportID on the parent form (frmExpenseReport). The Nz
says:
"If this value is null, substitute the value 0."

This may work if the default value for ExpenseNumber is 0:

=DMax("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " & Forms!frmExpenseReport!ExpenseReportID) + 1

This same approach can be taken using VBA code. For instance, in the
form's
Current event:

If Me.NewRecord Then
Me.ExpenseNumber = Nz(DMax(("[ExpenseNumber]","[ExpenseDetails]",
_
"[ExpenseReportID] = " & Me.Parent.ExpenseReportID),0) + 1
End If

Note that the underscores in the text box expressions are for ease of
reading here, and need to be removed in the actual expression. The
underscore in the VBA expression should work as written.

Sometimes when I increment a number there are several variables: "Find
the
largest value for SomeField in the record in which {Condition1} and
{Condition2} and {Condition3}". In that case I find it easier to
manage
in
VBA code. In your case the text box expression, as Roger's sample
database
shows, should be fine.

Note too that whatever approach you take you may need to take
precautions
against producing a duplicate value in a multi-user environment.
Roger's
sample code shows how to manage that in the context of the method he
demonstrated.

Roger,

I have copied your set up :=DMax("ProductID";"Product")+1
I replaced the filed name and the table name and i inserted in the
same
position that you have done in your example. Is anything else that I
have
to
do

:

You can't put a user defined function in the default value of a
field.
You
have to do it in a control on a form.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger,
Thanks for your reply. Regret but I have inserted the relevant
command
in
the Default Value of the ExpenseNumber field but does not work
properly.
On
the ExpenseNumber field shows "#Error" and displays a Zero when I
inserst
data in the rest fields

:

On my website (www.rogersaccesslibrary.com), is a small Access
database
sample called "AutonumberProblem.mdb" which illustrates how to
do
this.
You
can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=395

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
I have created a form "Expense Reports" & a subfrom "Expense
Reports
Subform
Linked on Fields "ExpenseReportID".
Subform get the data from the table "Expense Details". Among
the
other
fields there is a filed "ExpenseNumber". Subform is on a
dataview
form
I need each time that I add a new line in the Subform, a
sequence
number
to
be displayed in the "ExpenseNumber" field that should be
unique
and
in
case
of deletion of a line the sequence not to be interrupted and
the
new
line
to
get the next in sequence number so to be a conistency in the
numbering.
Please help

 

[reterrig]

Bruce,

I have inserted following in the Before Insert event:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Forms![Expense Reports Subform].NewRecord Then
Forms![Expense Reports Subform].ExpenseNumber = Nz(DMax("Forms![Expense
Reports Subform].[ExpenseNumber]", "Forms![Expense Reports Subform].[Expense
Details]", "Forms![Expense Reports Subform].[ExpenseReportID] = " &
Forms![Expense Reports Subform].Parent.ExpenseReportID), 0) + 1
End If

End Sub

Still same error appears

Rgds
Peter

I thought you said it was working.

I just realized that the reason it is not working is that the subform loads
before the main form, so when the subform's Current event first runs there
is no parent record.

I haven't tested this, but I think you could use the same code in the form's
Before Insert event, except that you don't need to test for a new record
since the Before Insert event runs only for a new record:
Me.ExpenseNumber = Nz(DMax(("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " & Me.Parent.ExpenseReportID),0) +
1

If there is any chance of two users working on the same record at the same
time (that is, two users working with the same main form record) you will
need to guard against duplicate numbers in Expense Number.

Bruce,

I have followed your instructions.

When I re open an existing record of the form the sequence number works
perfectly. When I try to open a new record in the form ten an error is
appeared:" Run-tine error '3075':
Syntax error (missing operator) in query expression '[ExpenseReportID]='

I cannot solve this. Any thought?

Rgds
Peter

I have no idea how that is working with Roger's sample code since it does
not contain a provision for starting over from one(at least not the
version
I have seen), but if it does what you need that's all you need.
..
Bruce,

I had used the commmands in the VBA code and works perfectly. Its
starts
numbering from one for every set of subforms records. That was I
needed.

Greate thanks for your assistance.

:

If these are subform records you will need a Where condition (assuming
you
mean to start numbering from one for every set of subform records).
You
will also need to use Nz. I think it would work also to have the
default
value for the field set to 0 instead, but I'm not sure.

=Nz(DMax("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " & Forms!frmExpenseReport!ExpenseReportID),0)
+ 1

The DMax part is saying: "Find the highest value for ExpenseNumber in
the
ExpenseDetails table in the record in which the ExpenseReportID field
is
the
same as ExpenseReportID on the parent form (frmExpenseReport). The Nz
says:
"If this value is null, substitute the value 0."

This may work if the default value for ExpenseNumber is 0:

=DMax("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " & Forms!frmExpenseReport!ExpenseReportID) + 1

This same approach can be taken using VBA code. For instance, in the
form's
Current event:

If Me.NewRecord Then
Me.ExpenseNumber = Nz(DMax(("[ExpenseNumber]","[ExpenseDetails]",
_
"[ExpenseReportID] = " & Me.Parent.ExpenseReportID),0) + 1
End If

Note that the underscores in the text box expressions are for ease of
reading here, and need to be removed in the actual expression. The
underscore in the VBA expression should work as written.

Sometimes when I increment a number there are several variables: "Find
the
largest value for SomeField in the record in which {Condition1} and
{Condition2} and {Condition3}". In that case I find it easier to
manage
in
VBA code. In your case the text box expression, as Roger's sample
database
shows, should be fine.

Note too that whatever approach you take you may need to take
precautions
against producing a duplicate value in a multi-user environment.
Roger's
sample code shows how to manage that in the context of the method he
demonstrated.

Roger,

I have copied your set up :=DMax("ProductID";"Product")+1
I replaced the filed name and the table name and i inserted in the
same
position that you have done in your example. Is anything else that I
have
to
do

:

You can't put a user defined function in the default value of a
field.
You
have to do it in a control on a form.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger,
Thanks for your reply. Regret but I have inserted the relevant
command
in
the Default Value of the ExpenseNumber field but does not work
properly.
On
the ExpenseNumber field shows "#Error" and displays a Zero when I
inserst
data in the rest fields

:

On my website (www.rogersaccesslibrary.com), is a small Access
database
sample called "AutonumberProblem.mdb" which illustrates how to
do
this.
You
can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=395

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
I have created a form "Expense Reports" & a subfrom "Expense
Reports
Subform
Linked on Fields "ExpenseReportID".
Subform get the data from the table "Expense Details". Among
the
other
fields there is a filed "ExpenseNumber". Subform is on a
dataview
form
I need each time that I add a new line in the Subform, a
sequence
number
to
be displayed in the "ExpenseNumber" field that should be
unique
and
in
case
of deletion of a line the sequence not to be interrupted and
the
new
line
to
get the next in sequence number so to be a conistency in the
numbering.
Please help

 

[BruceM]

In the subform's Before Insert event:

If Me.NewRecord Then
Me.ExpenseNumber = Nz(DMax("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " & Me.Parent.[ExpenseReportID],0) + 1
End If

Another way to do this may be to use the DefaultValue property.
Me.ExpenseNumber.DefaultValue =
Nz(DMax("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " & Me.Parent.[ExpenseReportID],0) + 1

In this case there is no need to check for a new record, since Default Value
applies only to a new record. I tend to do it the first way since I often
have several things that happen when there is a new record.

In any case, VBA syntax is different from the syntax you would use in a
Control Source expression in a text box or in a query. The Me prefix in any
event in the subform's code module (the collection of VBA code that is
specific to a form or report) means that what follows is a member of a
collection relevant to the subform. A collection can include properties
such as DefaultValue and BackColor, controls such as text boxes and labels,
fields from the Record Source table or query, and a number of other things
that I won't try to get into beyond this brief mention. The point is that
if ExpenseNumber is a field in the form's Record Source, Me.ExpenseNumber
references that field.

However, if you are in an object such as a subform you need to use different
syntax to reference a control or field from another form (including the
subform's Parent form). That is why I used the Me.Parent syntax. The code
is looking for the highest value for ExpenseNumber in the ExpenseDetails
table where ExpenseReportID is the same as ExpenseReportID in the parent
form. This assumes ExpenseReport is the name of the linking field between
the ExpenseReport table and the ExpenseDetails table.

The Me.Parent syntax will work only for a subform's or subreport's parent
form or report. For another form you need to spell out the details:
Forms![FormName]![FieldOrControlName]
You can use the long syntax in place of, say, Me.ExpenseNumber, or to
reference the parent form, but I can see no good reason for doing so, at
least not in this case. If you do use the long syntax to reference the
parent form you would not use the Parent property.

Also, there is different syntax for referencing a subform:
If Forms![ExpenseReport]![Expense Reports Subform].Form.NewRecord
The trick is that you need to reference the Form property of the subform
control (the subform control is the "box" on the main form that contains the
subform) before you can reference the control.

The Me prefix works only in VBA. In an expression (such as in a text box)
you need to use the long syntax. In the Default Value property for a text
box bound to the ExpenseNumber field:
=Nz(DMax("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " & Forms!frmExpenseReport!ExpenseReportID,0) +
1
Remember, the underscore is for clarity only in this case. It can't be in
the actual expression.

Note that the syntax for DMax is exactly the same in VBA and in an
expression *except* for the last part of the Where condition. This syntax
is essentially the same for all domain functions such as DMax, DLookup,
etc., no matter where the expression is located. I explained the structure
of the DMax expession in an earlier posting.

This article contains some useful information about referencing fields and
controls:
http://my.advisor.com/doc/05352

BTW, all of the above assumes ExpenseReportID is a number field.

Bruce,

I have inserted following in the Before Insert event:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Forms![Expense Reports Subform].NewRecord Then
Forms![Expense Reports Subform].ExpenseNumber = Nz(DMax("Forms![Expense
Reports Subform].[ExpenseNumber]", "Forms![Expense Reports
Subform].[Expense
Details]", "Forms![Expense Reports Subform].[ExpenseReportID] = " &
Forms![Expense Reports Subform].Parent.ExpenseReportID), 0) + 1
End If

End Sub

Still same error appears

Rgds
Peter

I thought you said it was working.

I just realized that the reason it is not working is that the subform
loads
before the main form, so when the subform's Current event first runs
there
is no parent record.

I haven't tested this, but I think you could use the same code in the
form's
Before Insert event, except that you don't need to test for a new record
since the Before Insert event runs only for a new record:
Me.ExpenseNumber = Nz(DMax(("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " &
Me.Parent.ExpenseReportID),0) +
1

If there is any chance of two users working on the same record at the
same
time (that is, two users working with the same main form record) you will
need to guard against duplicate numbers in Expense Number.

Bruce,

I have followed your instructions.

When I re open an existing record of the form the sequence number works
perfectly. When I try to open a new record in the form ten an error is
appeared:" Run-tine error '3075':
Syntax error (missing operator) in query expression
'[ExpenseReportID]='

I cannot solve this. Any thought?

Rgds
Peter

:

I have no idea how that is working with Roger's sample code since it
does
not contain a provision for starting over from one(at least not the
version
I have seen), but if it does what you need that's all you need.
..
Bruce,

I had used the commmands in the VBA code and works perfectly. Its
starts
numbering from one for every set of subforms records. That was I
needed.

Greate thanks for your assistance.

:

If these are subform records you will need a Where condition
(assuming
you
mean to start numbering from one for every set of subform records).
You
will also need to use Nz. I think it would work also to have the
default
value for the field set to 0 instead, but I'm not sure.

=Nz(DMax("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " &
Forms!frmExpenseReport!ExpenseReportID),0)
+ 1

The DMax part is saying: "Find the highest value for ExpenseNumber
in
the
ExpenseDetails table in the record in which the ExpenseReportID
field
is
the
same as ExpenseReportID on the parent form (frmExpenseReport). The
Nz
says:
"If this value is null, substitute the value 0."

This may work if the default value for ExpenseNumber is 0:

=DMax("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " & Forms!frmExpenseReport!ExpenseReportID)
+ 1

This same approach can be taken using VBA code. For instance, in
the
form's
Current event:

If Me.NewRecord Then
Me.ExpenseNumber =
Nz(DMax(("[ExpenseNumber]","[ExpenseDetails]",
_
"[ExpenseReportID] = " & Me.Parent.ExpenseReportID),0)
+ 1
End If

Note that the underscores in the text box expressions are for ease
of
reading here, and need to be removed in the actual expression. The
underscore in the VBA expression should work as written.

Sometimes when I increment a number there are several variables:
"Find
the
largest value for SomeField in the record in which {Condition1} and
{Condition2} and {Condition3}". In that case I find it easier to
manage
in
VBA code. In your case the text box expression, as Roger's sample
database
shows, should be fine.

Note too that whatever approach you take you may need to take
precautions
against producing a duplicate value in a multi-user environment.
Roger's
sample code shows how to manage that in the context of the method
he
demonstrated.

Roger,

I have copied your set up :=DMax("ProductID";"Product")+1
I replaced the filed name and the table name and i inserted in
the
same
position that you have done in your example. Is anything else
that I
have
to
do

:

You can't put a user defined function in the default value of a
field.
You
have to do it in a control on a form.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger,
Thanks for your reply. Regret but I have inserted the relevant
command
in
the Default Value of the ExpenseNumber field but does not work
properly.
On
the ExpenseNumber field shows "#Error" and displays a Zero
when I
inserst
data in the rest fields

:

On my website (www.rogersaccesslibrary.com), is a small
Access
database
sample called "AutonumberProblem.mdb" which illustrates how
to
do
this.
You
can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=395

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
message
I have created a form "Expense Reports" & a subfrom "Expense
Reports
Subform
Linked on Fields "ExpenseReportID".
Subform get the data from the table "Expense Details".
Among
the
other
fields there is a filed "ExpenseNumber". Subform is on a
dataview
form
I need each time that I add a new line in the Subform, a
sequence
number
to
be displayed in the "ExpenseNumber" field that should be
unique
and
in
case
of deletion of a line the sequence not to be interrupted
and
the
new
line
to
get the next in sequence number so to be a conistency in
the
numbering.
Please help

 

[reterrig]

Breuce,

Thanks for your reply. I will test this and I will revert with the outcome

Rgds
Peter

In the subform's Before Insert event:

If Me.NewRecord Then
Me.ExpenseNumber = Nz(DMax("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " & Me.Parent.[ExpenseReportID],0) + 1
End If

Another way to do this may be to use the DefaultValue property.
Me.ExpenseNumber.DefaultValue =
Nz(DMax("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " & Me.Parent.[ExpenseReportID],0) + 1

In this case there is no need to check for a new record, since Default Value
applies only to a new record. I tend to do it the first way since I often
have several things that happen when there is a new record.

In any case, VBA syntax is different from the syntax you would use in a
Control Source expression in a text box or in a query. The Me prefix in any
event in the subform's code module (the collection of VBA code that is
specific to a form or report) means that what follows is a member of a
collection relevant to the subform. A collection can include properties
such as DefaultValue and BackColor, controls such as text boxes and labels,
fields from the Record Source table or query, and a number of other things
that I won't try to get into beyond this brief mention. The point is that
if ExpenseNumber is a field in the form's Record Source, Me.ExpenseNumber
references that field.

However, if you are in an object such as a subform you need to use different
syntax to reference a control or field from another form (including the
subform's Parent form). That is why I used the Me.Parent syntax. The code
is looking for the highest value for ExpenseNumber in the ExpenseDetails
table where ExpenseReportID is the same as ExpenseReportID in the parent
form. This assumes ExpenseReport is the name of the linking field between
the ExpenseReport table and the ExpenseDetails table.

The Me.Parent syntax will work only for a subform's or subreport's parent
form or report. For another form you need to spell out the details:
Forms![FormName]![FieldOrControlName]
You can use the long syntax in place of, say, Me.ExpenseNumber, or to
reference the parent form, but I can see no good reason for doing so, at
least not in this case. If you do use the long syntax to reference the
parent form you would not use the Parent property.

Also, there is different syntax for referencing a subform:
If Forms![ExpenseReport]![Expense Reports Subform].Form.NewRecord
The trick is that you need to reference the Form property of the subform
control (the subform control is the "box" on the main form that contains the
subform) before you can reference the control.

The Me prefix works only in VBA. In an expression (such as in a text box)
you need to use the long syntax. In the Default Value property for a text
box bound to the ExpenseNumber field:
=Nz(DMax("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " & Forms!frmExpenseReport!ExpenseReportID,0) +
1
Remember, the underscore is for clarity only in this case. It can't be in
the actual expression.

Note that the syntax for DMax is exactly the same in VBA and in an
expression *except* for the last part of the Where condition. This syntax
is essentially the same for all domain functions such as DMax, DLookup,
etc., no matter where the expression is located. I explained the structure
of the DMax expession in an earlier posting.

This article contains some useful information about referencing fields and
controls:
http://my.advisor.com/doc/05352

BTW, all of the above assumes ExpenseReportID is a number field.

Bruce,

I have inserted following in the Before Insert event:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Forms![Expense Reports Subform].NewRecord Then
Forms![Expense Reports Subform].ExpenseNumber = Nz(DMax("Forms![Expense
Reports Subform].[ExpenseNumber]", "Forms![Expense Reports
Subform].[Expense
Details]", "Forms![Expense Reports Subform].[ExpenseReportID] = " &
Forms![Expense Reports Subform].Parent.ExpenseReportID), 0) + 1
End If

End Sub

Still same error appears

Rgds
Peter

I thought you said it was working.

I just realized that the reason it is not working is that the subform
loads
before the main form, so when the subform's Current event first runs
there
is no parent record.

I haven't tested this, but I think you could use the same code in the
form's
Before Insert event, except that you don't need to test for a new record
since the Before Insert event runs only for a new record:
Me.ExpenseNumber = Nz(DMax(("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " &
Me.Parent.ExpenseReportID),0) +
1

If there is any chance of two users working on the same record at the
same
time (that is, two users working with the same main form record) you will
need to guard against duplicate numbers in Expense Number.


Bruce,

I have followed your instructions.

When I re open an existing record of the form the sequence number works
perfectly. When I try to open a new record in the form ten an error is
appeared:" Run-tine error '3075':
Syntax error (missing operator) in query expression
'[ExpenseReportID]='

I cannot solve this. Any thought?

Rgds
Peter

:

I have no idea how that is working with Roger's sample code since it
does
not contain a provision for starting over from one(at least not the
version
I have seen), but if it does what you need that's all you need.
..
Bruce,

I had used the commmands in the VBA code and works perfectly. Its
starts
numbering from one for every set of subforms records. That was I
needed.

Greate thanks for your assistance.

:

If these are subform records you will need a Where condition
(assuming
you
mean to start numbering from one for every set of subform records).
You
will also need to use Nz. I think it would work also to have the
default
value for the field set to 0 instead, but I'm not sure.

=Nz(DMax("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " &
Forms!frmExpenseReport!ExpenseReportID),0)
+ 1

The DMax part is saying: "Find the highest value for ExpenseNumber
in
the
ExpenseDetails table in the record in which the ExpenseReportID
field
is
the
same as ExpenseReportID on the parent form (frmExpenseReport). The
Nz
says:
"If this value is null, substitute the value 0."

This may work if the default value for ExpenseNumber is 0:

=DMax("[ExpenseNumber]","[ExpenseDetails]", _
"[ExpenseReportID] = " & Forms!frmExpenseReport!ExpenseReportID)
+ 1

This same approach can be taken using VBA code. For instance, in
the
form's
Current event:

If Me.NewRecord Then
Me.ExpenseNumber =
Nz(DMax(("[ExpenseNumber]","[ExpenseDetails]",
_
"[ExpenseReportID] = " & Me.Parent.ExpenseReportID),0)
+ 1
End If

Note that the underscores in the text box expressions are for ease
of
reading here, and need to be removed in the actual expression. The
underscore in the VBA expression should work as written.

Sometimes when I increment a number there are several variables:
"Find
the
largest value for SomeField in the record in which {Condition1} and
{Condition2} and {Condition3}". In that case I find it easier to
manage
in
VBA code. In your case the text box expression, as Roger's sample
database
shows, should be fine.

Note too that whatever approach you take you may need to take
precautions
against producing a duplicate value in a multi-user environment.
Roger's
sample code shows how to manage that in the context of the method
he
demonstrated.

Roger,

I have copied your set up :=DMax("ProductID";"Product")+1
I replaced the filed name and the table name and i inserted in
the
same
position that you have done in your example. Is anything else
that I
have
to
do

:

You can't put a user defined function in the default value of a
field.
You
have to do it in a control on a form.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger,
Thanks for your reply. Regret but I have inserted the relevant
command
in
the Default Value of the ExpenseNumber field but does not work
properly.
On
the ExpenseNumber field shows "#Error" and displays a Zero
when I
inserst
data in the rest fields

:

On my website (www.rogersaccesslibrary.com), is a small
Access
database
sample called "AutonumberProblem.mdb" which illustrates how
to
do
this.
You
can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=395

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
message
I have created a form "Expense Reports" & a subfrom "Expense
Reports
Subform
Linked on Fields "ExpenseReportID".
Subform get the data from the table "Expense Details".
Among
the
other
fields there is a filed "ExpenseNumber". Subform is on a
dataview