Autonumber upon opening a form

[Guest]

I am using a form to populate my database of work orders. I have a text box
on the form named "Work Order Number" and I want it to automatically place
the next sequential number into this field upon opening the form. The field
is my table is named Work_Order_Number and is set at autonumber but it isn't
placing the number onto the form when opened. What have I not done?

 

[Guest]

If your form is defined as a Data Entry form, then it is for adding new
records and comes up will all controls empty. That is because a record is
not actually created until you start entering data.

 

[Guest]

It is a form. I'm not sure how to identify it as a data entry form...didn't
know there was a difference. However, even if I begin entering data in the
next field it will not populate the autonumber. Thanks for your help. Any
other thoughts?

 

[BruceM]

There are two things here. An autonumber will appear in the text box when
you start entering information into the record.
The second and most important thing is that you should not be using
autonumber in this case. You will inevitably have gaps in the numbering,
for all sorts of reason. In most cases autonumber should not be seen. It
certainly should not be used if gaps in the numbering sequence could be a
problem.
The good news is that it is easy to produce an incremented number. See this
link for one approach:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

 

[Guest]

If the Data Entry property of the form is set to Yes in design view, then it
is a data entry form. Data entry forms are for entering new data. They
always start off blank, but without acctually creating a record.

If you form opens up with the first record in the recordset showing, then it
is not a data entry form. If this is the case and your control for the
autonumber field is bound to the autonumber field, then when you create a new
record, the number should show up.

I am a little lost on a solution because I never use autonumbers as data.
IMHO, users should never see them. They are intended to be used for
establishing key values for table relationships. One thing to be aware of is
that you will experience gaps in the numbering sequence. If a user begins to
create a record, the cancels it, the number assigned is not reused.

Here is a method I use if I need a sequentially numbered field. It is
flexible enought it can be used even within a field that has other repeating
values. For example, in once case, a user wanted a unique number that was
Year, Month, and an incrementing number as in 2006040001, 2006040002,
2006050001, etc
But for example purposes, I will show you the basic way to do this. Use the
form's current event:

If Me.NewRecord then
Me.txtOrderNumber = Max("[OrderNumber]", "tblOrderHeader") + 1
End If

This emulates auto numbering, but leaves no gaps unless you delete a record.
What happens is when you add a new record to your table, it looks for the
highest value in the field you identify and adds 1 to it and puts it in the
text box for the order number.

 

[Guest]

Thanks! That sounds like just what I want it to do. I'm confused about
which values to enter into the field properties though. I'm kind of new at
this and not entirely familiar with codes or syntax or expressions and the
like. I've changed the field in the database from Autonumber to a text
field. Can I assume that in order to have the Work_Order_Number field
autopopulate that I should put an expression in the "default value" property?
And if so would the expression then be =DMax[("table name","field name")]+1
? (the table name is "2006 Work Orders" and the field name is "Work
Order Number"
--
Ridnaway

If the Data Entry property of the form is set to Yes in design view, then it
is a data entry form. Data entry forms are for entering new data. They
always start off blank, but without acctually creating a record.

If you form opens up with the first record in the recordset showing, then it
is not a data entry form. If this is the case and your control for the
autonumber field is bound to the autonumber field, then when you create a new
record, the number should show up.

I am a little lost on a solution because I never use autonumbers as data.
IMHO, users should never see them. They are intended to be used for
establishing key values for table relationships. One thing to be aware of is
that you will experience gaps in the numbering sequence. If a user begins to
create a record, the cancels it, the number assigned is not reused.

Here is a method I use if I need a sequentially numbered field. It is
flexible enought it can be used even within a field that has other repeating
values. For example, in once case, a user wanted a unique number that was
Year, Month, and an incrementing number as in 2006040001, 2006040002,
2006050001, etc
But for example purposes, I will show you the basic way to do this. Use the
form's current event:

If Me.NewRecord then
Me.txtOrderNumber = Max("[OrderNumber]", "tblOrderHeader") + 1
End If

This emulates auto numbering, but leaves no gaps unless you delete a record.
What happens is when you add a new record to your table, it looks for the
highest value in the field you identify and adds 1 to it and puts it in the
text box for the order number.

It is a form. I'm not sure how to identify it as a data entry form...didn't
know there was a difference. However, even if I begin entering data in the
next field it will not populate the autonumber. Thanks for your help. Any
other thoughts?

 

[Guest]

The data type of the field can be whatever is best for the situation. In
this case, since you are using an incrementing number, I would suggest it be
Long Integer.
The code I posted will not work as a Default Value. It goes in the Current
event of the form. If you want to put the code in the Default Value property
of the control, you would use this version:

= Max("[OrderNumber]", "tblOrderHeader") + 1

The only issue you will have with this method will be that the form will
open more slowly. The more records there are in the table, the slower it
will be because it has to search the entire table to determine the highest
value in the field. I tested it on a table with about 12000 records and it
took about 4 seconds for the form to display.

Thanks! That sounds like just what I want it to do. I'm confused about
which values to enter into the field properties though. I'm kind of new at
this and not entirely familiar with codes or syntax or expressions and the
like. I've changed the field in the database from Autonumber to a text
field. Can I assume that in order to have the Work_Order_Number field
autopopulate that I should put an expression in the "default value" property?
And if so would the expression then be =DMax[("table name","field name")]+1
? (the table name is "2006 Work Orders" and the field name is "Work
Order Number"
--
Ridnaway

If the Data Entry property of the form is set to Yes in design view, then it
is a data entry form. Data entry forms are for entering new data. They
always start off blank, but without acctually creating a record.

If you form opens up with the first record in the recordset showing, then it
is not a data entry form. If this is the case and your control for the
autonumber field is bound to the autonumber field, then when you create a new
record, the number should show up.

I am a little lost on a solution because I never use autonumbers as data.
IMHO, users should never see them. They are intended to be used for
establishing key values for table relationships. One thing to be aware of is
that you will experience gaps in the numbering sequence. If a user begins to
create a record, the cancels it, the number assigned is not reused.

Here is a method I use if I need a sequentially numbered field. It is
flexible enought it can be used even within a field that has other repeating
values. For example, in once case, a user wanted a unique number that was
Year, Month, and an incrementing number as in 2006040001, 2006040002,
2006050001, etc
But for example purposes, I will show you the basic way to do this. Use the
form's current event:

If Me.NewRecord then
Me.txtOrderNumber = Max("[OrderNumber]", "tblOrderHeader") + 1
End If

This emulates auto numbering, but leaves no gaps unless you delete a record.
What happens is when you add a new record to your table, it looks for the
highest value in the field you identify and adds 1 to it and puts it in the
text box for the order number.

It is a form. I'm not sure how to identify it as a data entry form...didn't
know there was a difference. However, even if I begin entering data in the
next field it will not populate the autonumber. Thanks for your help. Any
other thoughts?
--
Ridnaway


:

If your form is defined as a Data Entry form, then it is for adding new
records and comes up will all controls empty. That is because a record is
not actually created until you start entering data.

:

I am using a form to populate my database of work orders. I have a text box
on the form named "Work Order Number" and I want it to automatically place
the next sequential number into this field upon opening the form. The field
is my table is named Work_Order_Number and is set at autonumber but it isn't
placing the number onto the form when opened. What have I not done?

 

[BruceM]

I expect you will pay no more attention to this than to my previous posting,
but if you have a multi-user environment you will need to take steps to
guard against errors if two users enter a record at the same time.

Thanks! That sounds like just what I want it to do. I'm confused about
which values to enter into the field properties though. I'm kind of new
at
this and not entirely familiar with codes or syntax or expressions and the
like. I've changed the field in the database from Autonumber to a text
field. Can I assume that in order to have the Work_Order_Number field
autopopulate that I should put an expression in the "default value"
property?
And if so would the expression then be =DMax[("table name","field
name")]+1
? (the table name is "2006 Work Orders" and the field name is "Work
Order Number"
--
Ridnaway

If the Data Entry property of the form is set to Yes in design view, then
it
is a data entry form. Data entry forms are for entering new data. They
always start off blank, but without acctually creating a record.

If you form opens up with the first record in the recordset showing, then
it
is not a data entry form. If this is the case and your control for the
autonumber field is bound to the autonumber field, then when you create a
new
record, the number should show up.

I am a little lost on a solution because I never use autonumbers as data.
IMHO, users should never see them. They are intended to be used for
establishing key values for table relationships. One thing to be aware
of is
that you will experience gaps in the numbering sequence. If a user
begins to
create a record, the cancels it, the number assigned is not reused.

Here is a method I use if I need a sequentially numbered field. It is
flexible enought it can be used even within a field that has other
repeating
values. For example, in once case, a user wanted a unique number that was
Year, Month, and an incrementing number as in 2006040001, 2006040002,
2006050001, etc
But for example purposes, I will show you the basic way to do this. Use
the
form's current event:

If Me.NewRecord then
Me.txtOrderNumber = Max("[OrderNumber]", "tblOrderHeader") + 1
End If

This emulates auto numbering, but leaves no gaps unless you delete a
record.
What happens is when you add a new record to your table, it looks for
the
highest value in the field you identify and adds 1 to it and puts it in
the
text box for the order number.

It is a form. I'm not sure how to identify it as a data entry
form...didn't
know there was a difference. However, even if I begin entering data in
the
next field it will not populate the autonumber. Thanks for your help.
Any
other thoughts?
--
Ridnaway


:

If your form is defined as a Data Entry form, then it is for adding
new
records and comes up will all controls empty. That is because a
record is
not actually created until you start entering data.

:

I am using a form to populate my database of work orders. I have a
text box
on the form named "Work Order Number" and I want it to
automatically place
the next sequential number into this field upon opening the form.
The field
is my table is named Work_Order_Number and is set at autonumber but
it isn't
placing the number onto the form when opened. What have I not
done?

 

[Guest]

Your point is valid, Bruce; however, the OP is probably not ready to address
that issue, yet. I would suggest we get it working, than cover that ground.

Basically, there are two approaches to dealing with this issue in a multi
user environment. One is to create a record immediately to reserve the
number. The problem with that approach is that if the user cancels the
entry, there is a gap in the numbering. The other is to use error handling
to warn the user, try a new number, and make sure any child records are
updated correctly.

This is certainly a confusing issue for someone who is not yet a seasoned
database developer. So, my point is that although your concern is valid and
important, the primary goal at this time is to get a working model, then deal
with the other issues.

I expect you will pay no more attention to this than to my previous posting,
but if you have a multi-user environment you will need to take steps to
guard against errors if two users enter a record at the same time.

Thanks! That sounds like just what I want it to do. I'm confused about
which values to enter into the field properties though. I'm kind of new
at
this and not entirely familiar with codes or syntax or expressions and the
like. I've changed the field in the database from Autonumber to a text
field. Can I assume that in order to have the Work_Order_Number field
autopopulate that I should put an expression in the "default value"
property?
And if so would the expression then be =DMax[("table name","field
name")]+1
? (the table name is "2006 Work Orders" and the field name is "Work
Order Number"
--
Ridnaway

If the Data Entry property of the form is set to Yes in design view, then
it
is a data entry form. Data entry forms are for entering new data. They
always start off blank, but without acctually creating a record.

If you form opens up with the first record in the recordset showing, then
it
is not a data entry form. If this is the case and your control for the
autonumber field is bound to the autonumber field, then when you create a
new
record, the number should show up.

I am a little lost on a solution because I never use autonumbers as data.
IMHO, users should never see them. They are intended to be used for
establishing key values for table relationships. One thing to be aware
of is
that you will experience gaps in the numbering sequence. If a user
begins to
create a record, the cancels it, the number assigned is not reused.

Here is a method I use if I need a sequentially numbered field. It is
flexible enought it can be used even within a field that has other
repeating
values. For example, in once case, a user wanted a unique number that was
Year, Month, and an incrementing number as in 2006040001, 2006040002,
2006050001, etc
But for example purposes, I will show you the basic way to do this. Use
the
form's current event:

If Me.NewRecord then
Me.txtOrderNumber = Max("[OrderNumber]", "tblOrderHeader") + 1
End If

This emulates auto numbering, but leaves no gaps unless you delete a
record.
What happens is when you add a new record to your table, it looks for
the
highest value in the field you identify and adds 1 to it and puts it in
the
text box for the order number.

:

It is a form. I'm not sure how to identify it as a data entry
form...didn't
know there was a difference. However, even if I begin entering data in
the
next field it will not populate the autonumber. Thanks for your help.
Any
other thoughts?
--
Ridnaway


:

If your form is defined as a Data Entry form, then it is for adding
new
records and comes up will all controls empty. That is because a
record is
not actually created until you start entering data.

:

I am using a form to populate my database of work orders. I have a
text box
on the form named "Work Order Number" and I want it to
automatically place
the next sequential number into this field upon opening the form.
The field
is my table is named Work_Order_Number and is set at autonumber but
it isn't
placing the number onto the form when opened. What have I not
done?

 

[BruceM]

Always good to see what you have to say, Klatuu. Wouldn't the main record
be saved automatically when moving to a child record? In that case there
couldn't be any child records if there is a duplicate number error in the
main form. But I agree that I got ahead of things there.
One of the reasons I suggested Roger's example is that it not only provides
a simple means of incrementing a number, it provides multi-user error
handling. I had not known that the Max function in a default property would
bog down the database if there are a lot of records, so that's good to know.
There isn't as big a performance hit with the function in an Event? By the
way, Roger's example uses DMax; as an aside I don't quite get the
distinction between Max and DMax in this case.
If the user does not need to see the number from the beginning it could be
assigned in the form's Before Update event. That should guard against most
errors, but error handling is a good idea anyhow. But again, that's running
out ahead of the immediate question.

Your point is valid, Bruce; however, the OP is probably not ready to
address
that issue, yet. I would suggest we get it working, than cover that
ground.

Basically, there are two approaches to dealing with this issue in a multi
user environment. One is to create a record immediately to reserve the
number. The problem with that approach is that if the user cancels the
entry, there is a gap in the numbering. The other is to use error
handling
to warn the user, try a new number, and make sure any child records are
updated correctly.

This is certainly a confusing issue for someone who is not yet a seasoned
database developer. So, my point is that although your concern is valid
and
important, the primary goal at this time is to get a working model, then
deal
with the other issues.

I expect you will pay no more attention to this than to my previous
posting,
but if you have a multi-user environment you will need to take steps to
guard against errors if two users enter a record at the same time.

Thanks! That sounds like just what I want it to do. I'm confused
about
which values to enter into the field properties though. I'm kind of
new
at
this and not entirely familiar with codes or syntax or expressions and
the
like. I've changed the field in the database from Autonumber to a text
field. Can I assume that in order to have the Work_Order_Number field
autopopulate that I should put an expression in the "default value"
property?
And if so would the expression then be =DMax[("table name","field
name")]+1
? (the table name is "2006 Work Orders" and the field name is
"Work
Order Number"
--
Ridnaway


:

If the Data Entry property of the form is set to Yes in design view,
then
it
is a data entry form. Data entry forms are for entering new data.
They
always start off blank, but without acctually creating a record.

If you form opens up with the first record in the recordset showing,
then
it
is not a data entry form. If this is the case and your control for
the
autonumber field is bound to the autonumber field, then when you
create a
new
record, the number should show up.

I am a little lost on a solution because I never use autonumbers as
data.
IMHO, users should never see them. They are intended to be used for
establishing key values for table relationships. One thing to be
aware
of is
that you will experience gaps in the numbering sequence. If a user
begins to
create a record, the cancels it, the number assigned is not reused.

Here is a method I use if I need a sequentially numbered field. It is
flexible enought it can be used even within a field that has other
repeating
values. For example, in once case, a user wanted a unique number that
was
Year, Month, and an incrementing number as in 2006040001, 2006040002,
2006050001, etc
But for example purposes, I will show you the basic way to do this.
Use
the
form's current event:

If Me.NewRecord then
Me.txtOrderNumber = Max("[OrderNumber]", "tblOrderHeader") + 1
End If

This emulates auto numbering, but leaves no gaps unless you delete a
record.
What happens is when you add a new record to your table, it looks for
the
highest value in the field you identify and adds 1 to it and puts it
in
the
text box for the order number.

:

It is a form. I'm not sure how to identify it as a data entry
form...didn't
know there was a difference. However, even if I begin entering data
in
the
next field it will not populate the autonumber. Thanks for your
help.
Any
other thoughts?
--
Ridnaway


:

If your form is defined as a Data Entry form, then it is for
adding
new
records and comes up will all controls empty. That is because a
record is
not actually created until you start entering data.

:

I am using a form to populate my database of work orders. I
have a
text box
on the form named "Work Order Number" and I want it to
automatically place
the next sequential number into this field upon opening the
form.
The field
is my table is named Work_Order_Number and is set at autonumber
but
it isn't
placing the number onto the form when opened. What have I not
done?

 

[Guest]

I apologize if it seems that I paid no attention to your previous posting.
In fact I did follow your link and tried to apply it. I would have even paid
the $1 donation if I could've remembered my PayPal user/password. The
problem was that I became confused by the code language/strings/expressions
and was unable to make sense of how to apply it to my specific table names
and field names. Certainly, in our multiple user environment getting a
duplicate numbering error will occur sooner or later and it will be good to
have the knowledge to overcome that. For now, being an inexperienced user I
guess I'll just have to muddle through this all until I can discuss it in
terms the experts are familiar with.

Have a good day Sir
--
Ridnaway

I expect you will pay no more attention to this than to my previous posting,
but if you have a multi-user environment you will need to take steps to
guard against errors if two users enter a record at the same time.

Thanks! That sounds like just what I want it to do. I'm confused about
which values to enter into the field properties though. I'm kind of new
at
this and not entirely familiar with codes or syntax or expressions and the
like. I've changed the field in the database from Autonumber to a text
field. Can I assume that in order to have the Work_Order_Number field
autopopulate that I should put an expression in the "default value"
property?
And if so would the expression then be =DMax[("table name","field
name")]+1
? (the table name is "2006 Work Orders" and the field name is "Work
Order Number"
--
Ridnaway

If the Data Entry property of the form is set to Yes in design view, then
it
is a data entry form. Data entry forms are for entering new data. They
always start off blank, but without acctually creating a record.

If you form opens up with the first record in the recordset showing, then
it
is not a data entry form. If this is the case and your control for the
autonumber field is bound to the autonumber field, then when you create a
new
record, the number should show up.

I am a little lost on a solution because I never use autonumbers as data.
IMHO, users should never see them. They are intended to be used for
establishing key values for table relationships. One thing to be aware
of is
that you will experience gaps in the numbering sequence. If a user
begins to
create a record, the cancels it, the number assigned is not reused.

Here is a method I use if I need a sequentially numbered field. It is
flexible enought it can be used even within a field that has other
repeating
values. For example, in once case, a user wanted a unique number that was
Year, Month, and an incrementing number as in 2006040001, 2006040002,
2006050001, etc
But for example purposes, I will show you the basic way to do this. Use
the
form's current event:

If Me.NewRecord then
Me.txtOrderNumber = Max("[OrderNumber]", "tblOrderHeader") + 1
End If

This emulates auto numbering, but leaves no gaps unless you delete a
record.
What happens is when you add a new record to your table, it looks for
the
highest value in the field you identify and adds 1 to it and puts it in
the
text box for the order number.

:

It is a form. I'm not sure how to identify it as a data entry
form...didn't
know there was a difference. However, even if I begin entering data in
the
next field it will not populate the autonumber. Thanks for your help.
Any
other thoughts?
--
Ridnaway


:

If your form is defined as a Data Entry form, then it is for adding
new
records and comes up will all controls empty. That is because a
record is
not actually created until you start entering data.

:

I am using a form to populate my database of work orders. I have a
text box
on the form named "Work Order Number" and I want it to
automatically place
the next sequential number into this field upon opening the form.
The field
is my table is named Work_Order_Number and is set at autonumber but
it isn't
placing the number onto the form when opened. What have I not
done?

 

[Guest]

Yes, the DMax will take the same length of time to execute regardless of
where it is located in the form. The concept is user perception. If it take
a few seconds for a form to appear, the user preceives the applcation to be
slow. If the form pops right up, then starts looking in the database, the
user preceives the application is fast. The user will take a second or two
to react once the form is presented, so they don't notice the lag.

My using MAX was a typo. MAX is an SQL agregate function.... oops!.

As to moving to the Child record, you are correct.

Always good to see what you have to say, Klatuu. Wouldn't the main record
be saved automatically when moving to a child record? In that case there
couldn't be any child records if there is a duplicate number error in the
main form. But I agree that I got ahead of things there.
One of the reasons I suggested Roger's example is that it not only provides
a simple means of incrementing a number, it provides multi-user error
handling. I had not known that the Max function in a default property would
bog down the database if there are a lot of records, so that's good to know.
There isn't as big a performance hit with the function in an Event? By the
way, Roger's example uses DMax; as an aside I don't quite get the
distinction between Max and DMax in this case.
If the user does not need to see the number from the beginning it could be
assigned in the form's Before Update event. That should guard against most
errors, but error handling is a good idea anyhow. But again, that's running
out ahead of the immediate question.

Your point is valid, Bruce; however, the OP is probably not ready to
address
that issue, yet. I would suggest we get it working, than cover that
ground.

Basically, there are two approaches to dealing with this issue in a multi
user environment. One is to create a record immediately to reserve the
number. The problem with that approach is that if the user cancels the
entry, there is a gap in the numbering. The other is to use error
handling
to warn the user, try a new number, and make sure any child records are
updated correctly.

This is certainly a confusing issue for someone who is not yet a seasoned
database developer. So, my point is that although your concern is valid
and
important, the primary goal at this time is to get a working model, then
deal
with the other issues.

I expect you will pay no more attention to this than to my previous
posting,
but if you have a multi-user environment you will need to take steps to
guard against errors if two users enter a record at the same time.

Thanks! That sounds like just what I want it to do. I'm confused
about
which values to enter into the field properties though. I'm kind of
new
at
this and not entirely familiar with codes or syntax or expressions and
the
like. I've changed the field in the database from Autonumber to a text
field. Can I assume that in order to have the Work_Order_Number field
autopopulate that I should put an expression in the "default value"
property?
And if so would the expression then be =DMax[("table name","field
name")]+1
? (the table name is "2006 Work Orders" and the field name is
"Work
Order Number"
--
Ridnaway


:

If the Data Entry property of the form is set to Yes in design view,
then
it
is a data entry form. Data entry forms are for entering new data.
They
always start off blank, but without acctually creating a record.

If you form opens up with the first record in the recordset showing,
then
it
is not a data entry form. If this is the case and your control for
the
autonumber field is bound to the autonumber field, then when you
create a
new
record, the number should show up.

I am a little lost on a solution because I never use autonumbers as
data.
IMHO, users should never see them. They are intended to be used for
establishing key values for table relationships. One thing to be
aware
of is
that you will experience gaps in the numbering sequence. If a user
begins to
create a record, the cancels it, the number assigned is not reused.

Here is a method I use if I need a sequentially numbered field. It is
flexible enought it can be used even within a field that has other
repeating
values. For example, in once case, a user wanted a unique number that
was
Year, Month, and an incrementing number as in 2006040001, 2006040002,
2006050001, etc
But for example purposes, I will show you the basic way to do this.
Use
the
form's current event:

If Me.NewRecord then
Me.txtOrderNumber = Max("[OrderNumber]", "tblOrderHeader") + 1
End If

This emulates auto numbering, but leaves no gaps unless you delete a
record.
What happens is when you add a new record to your table, it looks for
the
highest value in the field you identify and adds 1 to it and puts it
in
the
text box for the order number.

:

It is a form. I'm not sure how to identify it as a data entry
form...didn't
know there was a difference. However, even if I begin entering data
in
the
next field it will not populate the autonumber. Thanks for your
help.
Any
other thoughts?
--
Ridnaway


:

If your form is defined as a Data Entry form, then it is for
adding
new
records and comes up will all controls empty. That is because a
record is
not actually created until you start entering data.

:

I am using a form to populate my database of work orders. I
have a
text box
on the form named "Work Order Number" and I want it to
automatically place
the next sequential number into this field upon opening the
form.
The field
is my table is named Work_Order_Number and is set at autonumber
but
it isn't
placing the number onto the form when opened. What have I not
done?

 

[BruceM]

I can get a bit testy at times. It doesn't help anything.

As Klatuu observed, get it working first. Multi-user can happen later. I
expect you are going to test it first for a while, so multi-user
environments aren't the first concern.

I apologize if it seems that I paid no attention to your previous posting.
In fact I did follow your link and tried to apply it. I would have even
paid
the $1 donation if I could've remembered my PayPal user/password. The
problem was that I became confused by the code
language/strings/expressions
and was unable to make sense of how to apply it to my specific table names
and field names. Certainly, in our multiple user environment getting a
duplicate numbering error will occur sooner or later and it will be good
to
have the knowledge to overcome that. For now, being an inexperienced user
I
guess I'll just have to muddle through this all until I can discuss it in
terms the experts are familiar with.

Have a good day Sir
--
Ridnaway

I expect you will pay no more attention to this than to my previous
posting,
but if you have a multi-user environment you will need to take steps to
guard against errors if two users enter a record at the same time.

Thanks! That sounds like just what I want it to do. I'm confused
about
which values to enter into the field properties though. I'm kind of
new
at
this and not entirely familiar with codes or syntax or expressions and
the
like. I've changed the field in the database from Autonumber to a text
field. Can I assume that in order to have the Work_Order_Number field
autopopulate that I should put an expression in the "default value"
property?
And if so would the expression then be =DMax[("table name","field
name")]+1
? (the table name is "2006 Work Orders" and the field name is
"Work
Order Number"
--
Ridnaway


:

If the Data Entry property of the form is set to Yes in design view,
then
it
is a data entry form. Data entry forms are for entering new data.
They
always start off blank, but without acctually creating a record.

If you form opens up with the first record in the recordset showing,
then
it
is not a data entry form. If this is the case and your control for
the
autonumber field is bound to the autonumber field, then when you
create a
new
record, the number should show up.

I am a little lost on a solution because I never use autonumbers as
data.
IMHO, users should never see them. They are intended to be used for
establishing key values for table relationships. One thing to be
aware
of is
that you will experience gaps in the numbering sequence. If a user
begins to
create a record, the cancels it, the number assigned is not reused.

Here is a method I use if I need a sequentially numbered field. It is
flexible enought it can be used even within a field that has other
repeating
values. For example, in once case, a user wanted a unique number that
was
Year, Month, and an incrementing number as in 2006040001, 2006040002,
2006050001, etc
But for example purposes, I will show you the basic way to do this.
Use
the
form's current event:

If Me.NewRecord then
Me.txtOrderNumber = Max("[OrderNumber]", "tblOrderHeader") + 1
End If

This emulates auto numbering, but leaves no gaps unless you delete a
record.
What happens is when you add a new record to your table, it looks for
the
highest value in the field you identify and adds 1 to it and puts it
in
the
text box for the order number.

:

It is a form. I'm not sure how to identify it as a data entry
form...didn't
know there was a difference. However, even if I begin entering data
in
the
next field it will not populate the autonumber. Thanks for your
help.
Any
other thoughts?
--
Ridnaway


:

If your form is defined as a Data Entry form, then it is for
adding
new
records and comes up will all controls empty. That is because a
record is
not actually created until you start entering data.

:

I am using a form to populate my database of work orders. I
have a
text box
on the form named "Work Order Number" and I want it to
automatically place
the next sequential number into this field upon opening the
form.
The field
is my table is named Work_Order_Number and is set at autonumber
but
it isn't
placing the number onto the form when opened. What have I not
done?

 

[BruceM]

Ah, perceptions! I'll have to keep that in mind.

Yes, the DMax will take the same length of time to execute regardless of
where it is located in the form. The concept is user perception. If it
take
a few seconds for a form to appear, the user preceives the applcation to
be
slow. If the form pops right up, then starts looking in the database, the
user preceives the application is fast. The user will take a second or
two
to react once the form is presented, so they don't notice the lag.

My using MAX was a typo. MAX is an SQL agregate function.... oops!.

As to moving to the Child record, you are correct.

Always good to see what you have to say, Klatuu. Wouldn't the main
record
be saved automatically when moving to a child record? In that case there
couldn't be any child records if there is a duplicate number error in the
main form. But I agree that I got ahead of things there.
One of the reasons I suggested Roger's example is that it not only
provides
a simple means of incrementing a number, it provides multi-user error
handling. I had not known that the Max function in a default property
would
bog down the database if there are a lot of records, so that's good to
know.
There isn't as big a performance hit with the function in an Event? By
the
way, Roger's example uses DMax; as an aside I don't quite get the
distinction between Max and DMax in this case.
If the user does not need to see the number from the beginning it could
be
assigned in the form's Before Update event. That should guard against
most
errors, but error handling is a good idea anyhow. But again, that's
running
out ahead of the immediate question.

Your point is valid, Bruce; however, the OP is probably not ready to
address
that issue, yet. I would suggest we get it working, than cover that
ground.

Basically, there are two approaches to dealing with this issue in a
multi
user environment. One is to create a record immediately to reserve the
number. The problem with that approach is that if the user cancels the
entry, there is a gap in the numbering. The other is to use error
handling
to warn the user, try a new number, and make sure any child records are
updated correctly.

This is certainly a confusing issue for someone who is not yet a
seasoned
database developer. So, my point is that although your concern is
valid
and
important, the primary goal at this time is to get a working model,
then
deal
with the other issues.

:

I expect you will pay no more attention to this than to my previous
posting,
but if you have a multi-user environment you will need to take steps
to
guard against errors if two users enter a record at the same time.

Thanks! That sounds like just what I want it to do. I'm confused
about
which values to enter into the field properties though. I'm kind of
new
at
this and not entirely familiar with codes or syntax or expressions
and
the
like. I've changed the field in the database from Autonumber to a
text
field. Can I assume that in order to have the Work_Order_Number
field
autopopulate that I should put an expression in the "default value"
property?
And if so would the expression then be =DMax[("table name","field
name")]+1
? (the table name is "2006 Work Orders" and the field name is
"Work
Order Number"
--
Ridnaway


:

If the Data Entry property of the form is set to Yes in design
view,
then
it
is a data entry form. Data entry forms are for entering new data.
They
always start off blank, but without acctually creating a record.

If you form opens up with the first record in the recordset
showing,
then
it
is not a data entry form. If this is the case and your control for
the
autonumber field is bound to the autonumber field, then when you
create a
new
record, the number should show up.

I am a little lost on a solution because I never use autonumbers as
data.
IMHO, users should never see them. They are intended to be used
for
establishing key values for table relationships. One thing to be
aware
of is
that you will experience gaps in the numbering sequence. If a user
begins to
create a record, the cancels it, the number assigned is not reused.

Here is a method I use if I need a sequentially numbered field. It
is
flexible enought it can be used even within a field that has other
repeating
values. For example, in once case, a user wanted a unique number
that
was
Year, Month, and an incrementing number as in 2006040001,
2006040002,
2006050001, etc
But for example purposes, I will show you the basic way to do this.
Use
the
form's current event:

If Me.NewRecord then
Me.txtOrderNumber = Max("[OrderNumber]", "tblOrderHeader") + 1
End If

This emulates auto numbering, but leaves no gaps unless you delete
a
record.
What happens is when you add a new record to your table, it looks
for
the
highest value in the field you identify and adds 1 to it and puts
it
in
the
text box for the order number.

:

It is a form. I'm not sure how to identify it as a data entry
form...didn't
know there was a difference. However, even if I begin entering
data
in
the
next field it will not populate the autonumber. Thanks for your
help.
Any
other thoughts?
--
Ridnaway


:

If your form is defined as a Data Entry form, then it is for
adding
new
records and comes up will all controls empty. That is because
a
record is
not actually created until you start entering data.

:

I am using a form to populate my database of work orders. I
have a
text box
on the form named "Work Order Number" and I want it to
automatically place
the next sequential number into this field upon opening the
form.
The field
is my table is named Work_Order_Number and is set at
autonumber
but
it isn't
placing the number onto the form when opened. What have I
not
done?

 

[Guest]

perception = reality

This is also why lately I have taken to keeping forms as free of code as
possible. if there is any realy processing at all, I create a standard
module and do as much of the code there as possible. To keep it all straight
for my 4K brain, I name the module the same as the form:
frmBuzFoo
modBuzFoo

Ah, perceptions! I'll have to keep that in mind.

Yes, the DMax will take the same length of time to execute regardless of
where it is located in the form. The concept is user perception. If it
take
a few seconds for a form to appear, the user preceives the applcation to
be
slow. If the form pops right up, then starts looking in the database, the
user preceives the application is fast. The user will take a second or
two
to react once the form is presented, so they don't notice the lag.

My using MAX was a typo. MAX is an SQL agregate function.... oops!.

As to moving to the Child record, you are correct.

Always good to see what you have to say, Klatuu. Wouldn't the main
record
be saved automatically when moving to a child record? In that case there
couldn't be any child records if there is a duplicate number error in the
main form. But I agree that I got ahead of things there.
One of the reasons I suggested Roger's example is that it not only
provides
a simple means of incrementing a number, it provides multi-user error
handling. I had not known that the Max function in a default property
would
bog down the database if there are a lot of records, so that's good to
know.
There isn't as big a performance hit with the function in an Event? By
the
way, Roger's example uses DMax; as an aside I don't quite get the
distinction between Max and DMax in this case.
If the user does not need to see the number from the beginning it could
be
assigned in the form's Before Update event. That should guard against
most
errors, but error handling is a good idea anyhow. But again, that's
running
out ahead of the immediate question.

Your point is valid, Bruce; however, the OP is probably not ready to
address
that issue, yet. I would suggest we get it working, than cover that
ground.

Basically, there are two approaches to dealing with this issue in a
multi
user environment. One is to create a record immediately to reserve the
number. The problem with that approach is that if the user cancels the
entry, there is a gap in the numbering. The other is to use error
handling
to warn the user, try a new number, and make sure any child records are
updated correctly.

This is certainly a confusing issue for someone who is not yet a
seasoned
database developer. So, my point is that although your concern is
valid
and
important, the primary goal at this time is to get a working model,
then
deal
with the other issues.

:

I expect you will pay no more attention to this than to my previous
posting,
but if you have a multi-user environment you will need to take steps
to
guard against errors if two users enter a record at the same time.

Thanks! That sounds like just what I want it to do. I'm confused
about
which values to enter into the field properties though. I'm kind of
new
at
this and not entirely familiar with codes or syntax or expressions
and
the
like. I've changed the field in the database from Autonumber to a
text
field. Can I assume that in order to have the Work_Order_Number
field
autopopulate that I should put an expression in the "default value"
property?
And if so would the expression then be =DMax[("table name","field
name")]+1
? (the table name is "2006 Work Orders" and the field name is
"Work
Order Number"
--
Ridnaway


:

If the Data Entry property of the form is set to Yes in design
view,
then
it
is a data entry form. Data entry forms are for entering new data.
They
always start off blank, but without acctually creating a record.

If you form opens up with the first record in the recordset
showing,
then
it
is not a data entry form. If this is the case and your control for
the
autonumber field is bound to the autonumber field, then when you
create a
new
record, the number should show up.

I am a little lost on a solution because I never use autonumbers as
data.
IMHO, users should never see them. They are intended to be used
for
establishing key values for table relationships. One thing to be
aware
of is
that you will experience gaps in the numbering sequence. If a user
begins to
create a record, the cancels it, the number assigned is not reused.

Here is a method I use if I need a sequentially numbered field. It
is
flexible enought it can be used even within a field that has other
repeating
values. For example, in once case, a user wanted a unique number
that
was
Year, Month, and an incrementing number as in 2006040001,
2006040002,
2006050001, etc
But for example purposes, I will show you the basic way to do this.
Use
the
form's current event:

If Me.NewRecord then
Me.txtOrderNumber = Max("[OrderNumber]", "tblOrderHeader") + 1
End If

This emulates auto numbering, but leaves no gaps unless you delete
a
record.
What happens is when you add a new record to your table, it looks
for
the
highest value in the field you identify and adds 1 to it and puts
it
in
the
text box for the order number.

:

It is a form. I'm not sure how to identify it as a data entry
form...didn't
know there was a difference. However, even if I begin entering
data
in
the
next field it will not populate the autonumber. Thanks for your
help.
Any
other thoughts?
--
Ridnaway


:

If your form is defined as a Data Entry form, then it is for
adding
new
records and comes up will all controls empty. That is because
a
record is
not actually created until you start entering data.

:

I am using a form to populate my database of work orders. I
have a
text box
on the form named "Work Order Number" and I want it to
automatically place
the next sequential number into this field upon opening the
form.
The field
is my table is named Work_Order_Number and is set at
autonumber
but
it isn't
placing the number onto the form when opened. What have I
not
done?

 

[BruceM]

I'm not quite sure I understand. Do you call the procedures? And when you
speak of keeping the form as free of code as is possible, are you referring
just to Form events or to events associated with controls on the form too?

perception = reality

This is also why lately I have taken to keeping forms as free of code as
possible. if there is any realy processing at all, I create a standard
module and do as much of the code there as possible. To keep it all
straight
for my 4K brain, I name the module the same as the form:
frmBuzFoo
modBuzFoo

Ah, perceptions! I'll have to keep that in mind.

Yes, the DMax will take the same length of time to execute regardless
of
where it is located in the form. The concept is user perception. If
it
take
a few seconds for a form to appear, the user preceives the applcation
to
be
slow. If the form pops right up, then starts looking in the database,
the
user preceives the application is fast. The user will take a second or
two
to react once the form is presented, so they don't notice the lag.

My using MAX was a typo. MAX is an SQL agregate function.... oops!.

As to moving to the Child record, you are correct.

:

Always good to see what you have to say, Klatuu. Wouldn't the main
record
be saved automatically when moving to a child record? In that case
there
couldn't be any child records if there is a duplicate number error in
the
main form. But I agree that I got ahead of things there.
One of the reasons I suggested Roger's example is that it not only
provides
a simple means of incrementing a number, it provides multi-user error
handling. I had not known that the Max function in a default property
would
bog down the database if there are a lot of records, so that's good to
know.
There isn't as big a performance hit with the function in an Event?
By
the
way, Roger's example uses DMax; as an aside I don't quite get the
distinction between Max and DMax in this case.
If the user does not need to see the number from the beginning it
could
be
assigned in the form's Before Update event. That should guard against
most
errors, but error handling is a good idea anyhow. But again, that's
running
out ahead of the immediate question.

Your point is valid, Bruce; however, the OP is probably not ready to
address
that issue, yet. I would suggest we get it working, than cover that
ground.

Basically, there are two approaches to dealing with this issue in a
multi
user environment. One is to create a record immediately to reserve
the
number. The problem with that approach is that if the user cancels
the
entry, there is a gap in the numbering. The other is to use error
handling
to warn the user, try a new number, and make sure any child records
are
updated correctly.

This is certainly a confusing issue for someone who is not yet a
seasoned
database developer. So, my point is that although your concern is
valid
and
important, the primary goal at this time is to get a working model,
then
deal
with the other issues.

:

I expect you will pay no more attention to this than to my previous
posting,
but if you have a multi-user environment you will need to take
steps
to
guard against errors if two users enter a record at the same time.

Thanks! That sounds like just what I want it to do. I'm
confused
about
which values to enter into the field properties though. I'm kind
of
new
at
this and not entirely familiar with codes or syntax or
expressions
and
the
like. I've changed the field in the database from Autonumber to
a
text
field. Can I assume that in order to have the Work_Order_Number
field
autopopulate that I should put an expression in the "default
value"
property?
And if so would the expression then be =DMax[("table
name","field
name")]+1
? (the table name is "2006 Work Orders" and the field name
is
"Work
Order Number"
--
Ridnaway


:

If the Data Entry property of the form is set to Yes in design
view,
then
it
is a data entry form. Data entry forms are for entering new
data.
They
always start off blank, but without acctually creating a record.

If you form opens up with the first record in the recordset
showing,
then
it
is not a data entry form. If this is the case and your control
for
the
autonumber field is bound to the autonumber field, then when you
create a
new
record, the number should show up.

I am a little lost on a solution because I never use autonumbers
as
data.
IMHO, users should never see them. They are intended to be used
for
establishing key values for table relationships. One thing to
be
aware
of is
that you will experience gaps in the numbering sequence. If a
user
begins to
create a record, the cancels it, the number assigned is not
reused.

Here is a method I use if I need a sequentially numbered field.
It
is
flexible enought it can be used even within a field that has
other
repeating
values. For example, in once case, a user wanted a unique number
that
was
Year, Month, and an incrementing number as in 2006040001,
2006040002,
2006050001, etc
But for example purposes, I will show you the basic way to do
this.
Use
the
form's current event:

If Me.NewRecord then
Me.txtOrderNumber = Max("[OrderNumber]", "tblOrderHeader") +
1
End If

This emulates auto numbering, but leaves no gaps unless you
delete
a
record.
What happens is when you add a new record to your table, it
looks
for
the
highest value in the field you identify and adds 1 to it and
puts
it
in
the
text box for the order number.

:

It is a form. I'm not sure how to identify it as a data entry
form...didn't
know there was a difference. However, even if I begin
entering
data
in
the
next field it will not populate the autonumber. Thanks for
your
help.
Any
other thoughts?
--
Ridnaway


:

If your form is defined as a Data Entry form, then it is for
adding
new
records and comes up will all controls empty. That is
because
a
record is
not actually created until you start entering data.

:

I am using a form to populate my database of work orders.
I
have a
text box
on the form named "Work Order Number" and I want it to
automatically place
the next sequential number into this field upon opening
the
form.
The field
is my table is named Work_Order_Number and is set at
autonumber
but
it isn't
placing the number onto the form when opened. What have I
not
done?

 

[Guest]

all of them. In fact, I have a standard module named modFormOperations where
I keep various functions that are common to most forms. Here is an example
of how I handle deletes. In the Click event of my delete button (that is
directly in the text box in the properties dialog):

=delcurrentrec([Forms]![frmAttrubute])

The argument is always the name of the current form

It calls this funtion in modFormOperations

Public Function DelCurrentRec(ByRef frmSomeForm As Form)
Dim rst As Recordset

On Error GoTo DelCurrentRec_Error

If MsgBox("Do You Want to Delete This Record", vbQuestion + vbYesNo, _
"Delete Requested") = vbYes Then
Application.Echo False
With frmSomeForm
Set rst = .RecordsetClone
rst.Bookmark = .Bookmark
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MoveNext
Else
.Recordset.MovePrevious
End If
rst.Delete
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MovePrevious
Else
.Recordset.MoveNext
End If
End With
Application.Echo True
End If

DelCurrentRec_Exit:

On Error Resume Next
rst.Close
Set rst = Nothing
Application.Echo True
Exit Function

DelCurrentRec_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure DelCurrentRec of Module modFormOperations"
GoTo DelCurrentRec_Exit

End Function

The advantages are, I only have to write the code once, form operations are
consistent, there is less code in the form. A form with no module loads the
fastest. The less code there is in the form, the faster it loads.

If you are wondering about moving back and forth in the recordset. I found
that when you delete a record, it stays on the form until you move away from
it. To me, that is like a dead body laying in the street. So I do the
moving so the next record is shown unless you are on the last record, then
the previous record is shown.

I'm not quite sure I understand. Do you call the procedures? And when you
speak of keeping the form as free of code as is possible, are you referring
just to Form events or to events associated with controls on the form too?

perception = reality

This is also why lately I have taken to keeping forms as free of code as
possible. if there is any realy processing at all, I create a standard
module and do as much of the code there as possible. To keep it all
straight
for my 4K brain, I name the module the same as the form:
frmBuzFoo
modBuzFoo

Ah, perceptions! I'll have to keep that in mind.

Yes, the DMax will take the same length of time to execute regardless
of
where it is located in the form. The concept is user perception. If
it
take
a few seconds for a form to appear, the user preceives the applcation
to
be
slow. If the form pops right up, then starts looking in the database,
the
user preceives the application is fast. The user will take a second or
two
to react once the form is presented, so they don't notice the lag.

My using MAX was a typo. MAX is an SQL agregate function.... oops!.

As to moving to the Child record, you are correct.

:

Always good to see what you have to say, Klatuu. Wouldn't the main
record
be saved automatically when moving to a child record? In that case
there
couldn't be any child records if there is a duplicate number error in
the
main form. But I agree that I got ahead of things there.
One of the reasons I suggested Roger's example is that it not only
provides
a simple means of incrementing a number, it provides multi-user error
handling. I had not known that the Max function in a default property
would
bog down the database if there are a lot of records, so that's good to
know.
There isn't as big a performance hit with the function in an Event?
By
the
way, Roger's example uses DMax; as an aside I don't quite get the
distinction between Max and DMax in this case.
If the user does not need to see the number from the beginning it
could
be
assigned in the form's Before Update event. That should guard against
most
errors, but error handling is a good idea anyhow. But again, that's
running
out ahead of the immediate question.

Your point is valid, Bruce; however, the OP is probably not ready to
address
that issue, yet. I would suggest we get it working, than cover that
ground.

Basically, there are two approaches to dealing with this issue in a
multi
user environment. One is to create a record immediately to reserve
the
number. The problem with that approach is that if the user cancels
the
entry, there is a gap in the numbering. The other is to use error
handling
to warn the user, try a new number, and make sure any child records
are
updated correctly.

This is certainly a confusing issue for someone who is not yet a
seasoned
database developer. So, my point is that although your concern is
valid
and
important, the primary goal at this time is to get a working model,
then
deal
with the other issues.

:

I expect you will pay no more attention to this than to my previous
posting,
but if you have a multi-user environment you will need to take
steps
to
guard against errors if two users enter a record at the same time.

Thanks! That sounds like just what I want it to do. I'm
confused
about
which values to enter into the field properties though. I'm kind
of
new
at
this and not entirely familiar with codes or syntax or
expressions
and
the
like. I've changed the field in the database from Autonumber to
a
text
field. Can I assume that in order to have the Work_Order_Number
field
autopopulate that I should put an expression in the "default
value"
property?
And if so would the expression then be =DMax[("table
name","field
name")]+1
? (the table name is "2006 Work Orders" and the field name
is
"Work
Order Number"
--
Ridnaway


:

If the Data Entry property of the form is set to Yes in design
view,
then
it
is a data entry form. Data entry forms are for entering new
data.
They
always start off blank, but without acctually creating a record.

If you form opens up with the first record in the recordset
showing,
then
it
is not a data entry form. If this is the case and your control
for
the
autonumber field is bound to the autonumber field, then when you
create a
new
record, the number should show up.

I am a little lost on a solution because I never use autonumbers
as
data.
IMHO, users should never see them. They are intended to be used
for
establishing key values for table relationships. One thing to
be
aware
of is
that you will experience gaps in the numbering sequence. If a
user
begins to
create a record, the cancels it, the number assigned is not
reused.

Here is a method I use if I need a sequentially numbered field.
It
is
flexible enought it can be used even within a field that has
other
repeating
values. For example, in once case, a user wanted a unique number
that
was
Year, Month, and an incrementing number as in 2006040001,
2006040002,
2006050001, etc
But for example purposes, I will show you the basic way to do
this.
Use
the
form's current event:

If Me.NewRecord then
Me.txtOrderNumber = Max("[OrderNumber]", "tblOrderHeader") +
1
End If

This emulates auto numbering, but leaves no gaps unless you
delete
a
record.
What happens is when you add a new record to your table, it
looks
for
the
highest value in the field you identify and adds 1 to it and
puts
it
in
the
text box for the order number.

:

It is a form. I'm not sure how to identify it as a data entry
form...didn't
know there was a difference. However, even if I begin
entering
data
in
the
next field it will not populate the autonumber. Thanks for
your
help.
Any
other thoughts?
--
Ridnaway


:

If your form is defined as a Data Entry form, then it is for
adding
new
records and comes up will all controls empty. That is
because
a
record is
not actually created until you start entering data.

:

I am using a form to populate my database of work orders.
I
have a
text box
on the form named "Work Order Number" and I want it to
automatically place
the next sequential number into this field upon opening
the
form.
The field
is my table is named Work_Order_Number and is set at
autonumber
but
it isn't
placing the number onto the form when opened. What have I
not
done?

 

[BruceM]

Thanks. I have done a limited version of this for navigation buttons (I
used called procedures, but the functions make a lot of sense), but I see
how this could be of value on an expanded scale. The navigating back and
forth makes perfect sense to me. I don't like to leave people wondering
what's going on. That just causes them to click randomly, then everybody is
confused.

all of them. In fact, I have a standard module named modFormOperations
where
I keep various functions that are common to most forms. Here is an
example
of how I handle deletes. In the Click event of my delete button (that is
directly in the text box in the properties dialog):

=delcurrentrec([Forms]![frmAttrubute])

The argument is always the name of the current form

It calls this funtion in modFormOperations

Public Function DelCurrentRec(ByRef frmSomeForm As Form)
Dim rst As Recordset

On Error GoTo DelCurrentRec_Error

If MsgBox("Do You Want to Delete This Record", vbQuestion + vbYesNo, _
"Delete Requested") = vbYes Then
Application.Echo False
With frmSomeForm
Set rst = .RecordsetClone
rst.Bookmark = .Bookmark
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MoveNext
Else
.Recordset.MovePrevious
End If
rst.Delete
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MovePrevious
Else
.Recordset.MoveNext
End If
End With
Application.Echo True
End If

DelCurrentRec_Exit:

On Error Resume Next
rst.Close
Set rst = Nothing
Application.Echo True
Exit Function

DelCurrentRec_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure DelCurrentRec of Module modFormOperations"
GoTo DelCurrentRec_Exit

End Function

The advantages are, I only have to write the code once, form operations
are
consistent, there is less code in the form. A form with no module loads
the
fastest. The less code there is in the form, the faster it loads.

If you are wondering about moving back and forth in the recordset. I
found
that when you delete a record, it stays on the form until you move away
from
it. To me, that is like a dead body laying in the street. So I do the
moving so the next record is shown unless you are on the last record, then
the previous record is shown.

I'm not quite sure I understand. Do you call the procedures? And when
you
speak of keeping the form as free of code as is possible, are you
referring
just to Form events or to events associated with controls on the form
too?

perception = reality

This is also why lately I have taken to keeping forms as free of code
as
possible. if there is any realy processing at all, I create a standard
module and do as much of the code there as possible. To keep it all
straight
for my 4K brain, I name the module the same as the form:
frmBuzFoo
modBuzFoo

:

Ah, perceptions! I'll have to keep that in mind.

Yes, the DMax will take the same length of time to execute
regardless
of
where it is located in the form. The concept is user perception.
If
it
take
a few seconds for a form to appear, the user preceives the
applcation
to
be
slow. If the form pops right up, then starts looking in the
database,
the
user preceives the application is fast. The user will take a second
or
two
to react once the form is presented, so they don't notice the lag.

My using MAX was a typo. MAX is an SQL agregate function.... oops!.

As to moving to the Child record, you are correct.

:

Always good to see what you have to say, Klatuu. Wouldn't the main
record
be saved automatically when moving to a child record? In that case
there
couldn't be any child records if there is a duplicate number error
in
the
main form. But I agree that I got ahead of things there.
One of the reasons I suggested Roger's example is that it not only
provides
a simple means of incrementing a number, it provides multi-user
error
handling. I had not known that the Max function in a default
property
would
bog down the database if there are a lot of records, so that's good
to
know.
There isn't as big a performance hit with the function in an Event?
By
the
way, Roger's example uses DMax; as an aside I don't quite get the
distinction between Max and DMax in this case.
If the user does not need to see the number from the beginning it
could
be
assigned in the form's Before Update event. That should guard
against
most
errors, but error handling is a good idea anyhow. But again,
that's
running
out ahead of the immediate question.

Your point is valid, Bruce; however, the OP is probably not ready
to
address
that issue, yet. I would suggest we get it working, than cover
that
ground.

Basically, there are two approaches to dealing with this issue in
a
multi
user environment. One is to create a record immediately to
reserve
the
number. The problem with that approach is that if the user
cancels
the
entry, there is a gap in the numbering. The other is to use
error
handling
to warn the user, try a new number, and make sure any child
records
are
updated correctly.

This is certainly a confusing issue for someone who is not yet a
seasoned
database developer. So, my point is that although your concern
is
valid
and
important, the primary goal at this time is to get a working
model,
then
deal
with the other issues.

:

I expect you will pay no more attention to this than to my
previous
posting,
but if you have a multi-user environment you will need to take
steps
to
guard against errors if two users enter a record at the same
time.

Thanks! That sounds like just what I want it to do. I'm
confused
about
which values to enter into the field properties though. I'm
kind
of
new
at
this and not entirely familiar with codes or syntax or
expressions
and
the
like. I've changed the field in the database from Autonumber
to
a
text
field. Can I assume that in order to have the
Work_Order_Number
field
autopopulate that I should put an expression in the "default
value"
property?
And if so would the expression then be =DMax[("table
name","field
name")]+1
? (the table name is "2006 Work Orders" and the field
name
is
"Work
Order Number"
--
Ridnaway


:

If the Data Entry property of the form is set to Yes in
design
view,
then
it
is a data entry form. Data entry forms are for entering new
data.
They
always start off blank, but without acctually creating a
record.

If you form opens up with the first record in the recordset
showing,
then
it
is not a data entry form. If this is the case and your
control
for
the
autonumber field is bound to the autonumber field, then when
you
create a
new
record, the number should show up.

I am a little lost on a solution because I never use
autonumbers
as
data.
IMHO, users should never see them. They are intended to be
used
for
establishing key values for table relationships. One thing
to
be
aware
of is
that you will experience gaps in the numbering sequence. If
a
user
begins to
create a record, the cancels it, the number assigned is not
reused.

Here is a method I use if I need a sequentially numbered
field.
It
is
flexible enought it can be used even within a field that has
other
repeating
values. For example, in once case, a user wanted a unique
number
that
was
Year, Month, and an incrementing number as in 2006040001,
2006040002,
2006050001, etc
But for example purposes, I will show you the basic way to do
this.
Use
the
form's current event:

If Me.NewRecord then
Me.txtOrderNumber = Max("[OrderNumber]",
"tblOrderHeader") +
1
End If

This emulates auto numbering, but leaves no gaps unless you
delete
a
record.
What happens is when you add a new record to your table, it
looks
for
the
highest value in the field you identify and adds 1 to it and
puts
it
in
the
text box for the order number.

:

It is a form. I'm not sure how to identify it as a data
entry
form...didn't
know there was a difference. However, even if I begin
entering
data
in
the
next field it will not populate the autonumber. Thanks for
your
help.
Any
other thoughts?
--
Ridnaway


:

If your form is defined as a Data Entry form, then it is
for
adding
new
records and comes up will all controls empty. That is
because
a
record is
not actually created until you start entering data.

:

I am using a form to populate my database of work
orders.
I
have a
text box
on the form named "Work Order Number" and I want it to
automatically place
the next sequential number into this field upon opening
the
form.
The field
is my table is named Work_Order_Number and is set at
autonumber
but
it isn't
placing the number onto the form when opened. What
have I
not
done?

 

[Guest]

I never get confuse..., oh wait, maybe.... once i did, or did I just think I
did, oh well, can't remember for sure

Thanks. I have done a limited version of this for navigation buttons (I
used called procedures, but the functions make a lot of sense), but I see
how this could be of value on an expanded scale. The navigating back and
forth makes perfect sense to me. I don't like to leave people wondering
what's going on. That just causes them to click randomly, then everybody is
confused.

all of them. In fact, I have a standard module named modFormOperations
where
I keep various functions that are common to most forms. Here is an
example
of how I handle deletes. In the Click event of my delete button (that is
directly in the text box in the properties dialog):

=delcurrentrec([Forms]![frmAttrubute])

The argument is always the name of the current form

It calls this funtion in modFormOperations

Public Function DelCurrentRec(ByRef frmSomeForm As Form)
Dim rst As Recordset

On Error GoTo DelCurrentRec_Error

If MsgBox("Do You Want to Delete This Record", vbQuestion + vbYesNo, _
"Delete Requested") = vbYes Then
Application.Echo False
With frmSomeForm
Set rst = .RecordsetClone
rst.Bookmark = .Bookmark
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MoveNext
Else
.Recordset.MovePrevious
End If
rst.Delete
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MovePrevious
Else
.Recordset.MoveNext
End If
End With
Application.Echo True
End If

DelCurrentRec_Exit:

On Error Resume Next
rst.Close
Set rst = Nothing
Application.Echo True
Exit Function

DelCurrentRec_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure DelCurrentRec of Module modFormOperations"
GoTo DelCurrentRec_Exit

End Function

The advantages are, I only have to write the code once, form operations
are
consistent, there is less code in the form. A form with no module loads
the
fastest. The less code there is in the form, the faster it loads.

If you are wondering about moving back and forth in the recordset. I
found
that when you delete a record, it stays on the form until you move away
from
it. To me, that is like a dead body laying in the street. So I do the
moving so the next record is shown unless you are on the last record, then
the previous record is shown.

I'm not quite sure I understand. Do you call the procedures? And when
you
speak of keeping the form as free of code as is possible, are you
referring
just to Form events or to events associated with controls on the form
too?

perception = reality

This is also why lately I have taken to keeping forms as free of code
as
possible. if there is any realy processing at all, I create a standard
module and do as much of the code there as possible. To keep it all
straight
for my 4K brain, I name the module the same as the form:
frmBuzFoo
modBuzFoo

:

Ah, perceptions! I'll have to keep that in mind.

Yes, the DMax will take the same length of time to execute
regardless
of
where it is located in the form. The concept is user perception.
If
it
take
a few seconds for a form to appear, the user preceives the
applcation
to
be
slow. If the form pops right up, then starts looking in the
database,
the
user preceives the application is fast. The user will take a second
or
two
to react once the form is presented, so they don't notice the lag.

My using MAX was a typo. MAX is an SQL agregate function.... oops!.

As to moving to the Child record, you are correct.

:

Always good to see what you have to say, Klatuu. Wouldn't the main
record
be saved automatically when moving to a child record? In that case
there
couldn't be any child records if there is a duplicate number error
in
the
main form. But I agree that I got ahead of things there.
One of the reasons I suggested Roger's example is that it not only
provides
a simple means of incrementing a number, it provides multi-user
error
handling. I had not known that the Max function in a default
property
would
bog down the database if there are a lot of records, so that's good
to
know.
There isn't as big a performance hit with the function in an Event?
By
the
way, Roger's example uses DMax; as an aside I don't quite get the
distinction between Max and DMax in this case.
If the user does not need to see the number from the beginning it
could
be
assigned in the form's Before Update event. That should guard
against
most
errors, but error handling is a good idea anyhow. But again,
that's
running
out ahead of the immediate question.

Your point is valid, Bruce; however, the OP is probably not ready
to
address
that issue, yet. I would suggest we get it working, than cover
that
ground.

Basically, there are two approaches to dealing with this issue in
a
multi
user environment. One is to create a record immediately to
reserve
the
number. The problem with that approach is that if the user
cancels
the
entry, there is a gap in the numbering. The other is to use
error
handling
to warn the user, try a new number, and make sure any child
records
are
updated correctly.

This is certainly a confusing issue for someone who is not yet a
seasoned
database developer. So, my point is that although your concern
is
valid
and
important, the primary goal at this time is to get a working
model,
then
deal
with the other issues.

:

I expect you will pay no more attention to this than to my
previous
posting,
but if you have a multi-user environment you will need to take
steps
to
guard against errors if two users enter a record at the same
time.

Thanks! That sounds like just what I want it to do. I'm
confused
about
which values to enter into the field properties though. I'm
kind
of
new
at
this and not entirely familiar with codes or syntax or
expressions
and
the
like. I've changed the field in the database from Autonumber
to
a
text
field. Can I assume that in order to have the
Work_Order_Number
field
autopopulate that I should put an expression in the "default
value"
property?
And if so would the expression then be =DMax[("table
name","field
name")]+1
? (the table name is "2006 Work Orders" and the field
name
is
"Work
Order Number"
--
Ridnaway


:

If the Data Entry property of the form is set to Yes in
design
view,
then
it
is a data entry form. Data entry forms are for entering new
data.
They
always start off blank, but without acctually creating a
record.

If you form opens up with the first record in the recordset
showing,
then
it
is not a data entry form. If this is the case and your
control
for
the
autonumber field is bound to the autonumber field, then when
you
create a
new
record, the number should show up.

I am a little lost on a solution because I never use
autonumbers
as
data.
IMHO, users should never see them. They are intended to be
used
for
establishing key values for table relationships. One thing
to
be
aware
of is
that you will experience gaps in the numbering sequence. If
a
user
begins to
create a record, the cancels it, the number assigned is not
reused.

Here is a method I use if I need a sequentially numbered
field.
It
is

 

[Guest]

I think my 1st reply to this got lost somehow so I'll try again

Thank you for your thoughts. I changed the form to a data entry form and
typed the following expression into the Defaul Value line of the
Work_Order_Number field......=Max("[Work_Order_Number]","2006 Work
Orders")+1. But when I tried to exit I got the following error
message......"The expression you entered has a function containing the wrong
number of arguments." What was my error this time?
--
Ridnaway

I can get a bit testy at times. It doesn't help anything.

As Klatuu observed, get it working first. Multi-user can happen later. I
expect you are going to test it first for a while, so multi-user
environments aren't the first concern.

I apologize if it seems that I paid no attention to your previous posting.
In fact I did follow your link and tried to apply it. I would have even
paid
the $1 donation if I could've remembered my PayPal user/password. The
problem was that I became confused by the code
language/strings/expressions
and was unable to make sense of how to apply it to my specific table names
and field names. Certainly, in our multiple user environment getting a
duplicate numbering error will occur sooner or later and it will be good
to
have the knowledge to overcome that. For now, being an inexperienced user
I
guess I'll just have to muddle through this all until I can discuss it in
terms the experts are familiar with.

Have a good day Sir
--
Ridnaway

I expect you will pay no more attention to this than to my previous
posting,
but if you have a multi-user environment you will need to take steps to
guard against errors if two users enter a record at the same time.

Thanks! That sounds like just what I want it to do. I'm confused
about
which values to enter into the field properties though. I'm kind of
new
at
this and not entirely familiar with codes or syntax or expressions and
the
like. I've changed the field in the database from Autonumber to a text
field. Can I assume that in order to have the Work_Order_Number field
autopopulate that I should put an expression in the "default value"
property?
And if so would the expression then be =DMax[("table name","field
name")]+1
? (the table name is "2006 Work Orders" and the field name is
"Work
Order Number"
--
Ridnaway


:

If the Data Entry property of the form is set to Yes in design view,
then
it
is a data entry form. Data entry forms are for entering new data.
They
always start off blank, but without acctually creating a record.

If you form opens up with the first record in the recordset showing,
then
it
is not a data entry form. If this is the case and your control for
the
autonumber field is bound to the autonumber field, then when you
create a
new
record, the number should show up.

I am a little lost on a solution because I never use autonumbers as
data.
IMHO, users should never see them. They are intended to be used for
establishing key values for table relationships. One thing to be
aware
of is
that you will experience gaps in the numbering sequence. If a user
begins to
create a record, the cancels it, the number assigned is not reused.

Here is a method I use if I need a sequentially numbered field. It is
flexible enought it can be used even within a field that has other
repeating
values. For example, in once case, a user wanted a unique number that
was
Year, Month, and an incrementing number as in 2006040001, 2006040002,
2006050001, etc
But for example purposes, I will show you the basic way to do this.
Use
the
form's current event:

If Me.NewRecord then
Me.txtOrderNumber = Max("[OrderNumber]", "tblOrderHeader") + 1
End If

This emulates auto numbering, but leaves no gaps unless you delete a
record.
What happens is when you add a new record to your table, it looks for
the
highest value in the field you identify and adds 1 to it and puts it
in
the
text box for the order number.

:

It is a form. I'm not sure how to identify it as a data entry
form...didn't
know there was a difference. However, even if I begin entering data
in
the
next field it will not populate the autonumber. Thanks for your
help.
Any
other thoughts?
--
Ridnaway


:

If your form is defined as a Data Entry form, then it is for
adding
new
records and comes up will all controls empty. That is because a
record is
not actually created until you start entering data.

:

I am using a form to populate my database of work orders. I
have a
text box
on the form named "Work Order Number" and I want it to
automatically place
the next sequential number into this field upon opening the
form.
The field
is my table is named Work_Order_Number and is set at autonumber
but
it isn't
placing the number onto the form when opened. What have I not
done?