instead of autonumber?

[Liz Hansen]

Hi,

I have created a table which uses a custom autonumber and a form that I'm
using to add records to the table.

The problem occurs when I cancel (or undo) a record. Even though the record
is not saved, the autonumber is considered used, I guess, and it jumps to
the next autonumber when I add the next new record. I want to use the
cancelled autonumbers record so that I don't have gaps in my series. (I'm
using the autonumber to number drawings).

So, it looks like I have to come up with something else but autonumbering my
drawing number field. Could I maybe use some kind of default value instead
where the field is populated by looking at the table and finding the last
number in the series and then adding one number?

For instance, if the last drawing number was 400100 (now, I'm not sure this
necessarily would be the last record in the table) then the next would be
400101...

Any suggestions?

Thanks!

Liz

 

[tina]

So, it looks like I have to come up with something else but autonumbering
my

drawing number field. Could I maybe use some kind of default value instead
where the field is populated by looking at the table and finding the last
number in the series and then adding one number?

yes, that's a better solution. as a general rule, you should not use an
autonumber data type in a field that will have meaning to the user (in this
case, you).
first, you'll need to go to the table and change the number field's Data
Type from Autonumber to Number. *Note*: after changing to Number, look at
the Field Size. if it is Double or Single, and if you are only using whole
numbers (not decimals, like 400100.77), then change the Field Size to Long
Integer.
next, open the form in design view, and click on the control for the number
field. in the Properties box, click on the Data tab, and add the following
to the Default Value line, as

=Max("NumberFieldName","NameOfTableUnderlyingTheForm") + 1

substitute the correct table and field names, of course.

hth

 

[Liz Hansen]

Hi Tina,

I must be doing something wrong. I followed your instructions but instead
of the last number in the series it will display the number 1.

The default value I entered is: =Max([tblParts]![DrawingNumber])+1 (I tried
to use your format, but it didn't like it. Access 97)

Thanks,

 

[tina]

oops, sorry - left out the D. here's the correct line. change the field and
table names, but *not* the format.

=DMax("NumberFieldName","NameOfTableUnderlyingTheForm") + 1

Hi Tina,

I must be doing something wrong. I followed your instructions but instead
of the last number in the series it will display the number 1.

The default value I entered is: =Max([tblParts]![DrawingNumber])+1 (I tried
to use your format, but it didn't like it. Access 97)

Thanks,

autonumbering

yes, that's a better solution. as a general rule, you should not use an
autonumber data type in a field that will have meaning to the user (in this
case, you).
first, you'll need to go to the table and change the number field's Data
Type from Autonumber to Number. *Note*: after changing to Number, look at
the Field Size. if it is Double or Single, and if you are only using whole
numbers (not decimals, like 400100.77), then change the Field Size to Long
Integer.
next, open the form in design view, and click on the control for the number
field. in the Properties box, click on the Data tab, and add the following
to the Default Value line, as

=Max("NumberFieldName","NameOfTableUnderlyingTheForm") + 1

substitute the correct table and field names, of course.

hth


autonumbering

 

[Lynn Trapp]

Actually, I don't think you can do that at the table level. You will
probably get an error message about the DMax function being unknown.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

oops, sorry - left out the D. here's the correct line. change the field and
table names, but *not* the format.

=DMax("NumberFieldName","NameOfTableUnderlyingTheForm") + 1

Hi Tina,

I must be doing something wrong. I followed your instructions but instead
of the last number in the series it will display the number 1.

The default value I entered is: =Max([tblParts]![DrawingNumber])+1 (I tried
to use your format, but it didn't like it. Access 97)

Thanks,

So, it looks like I have to come up with something else but autonumbering
my
drawing number field. Could I maybe use some kind of default value
instead
where the field is populated by looking at the table and finding the last
number in the series and then adding one number?

yes, that's a better solution. as a general rule, you should not use an
autonumber data type in a field that will have meaning to the user (in this
case, you).
first, you'll need to go to the table and change the number field's Data
Type from Autonumber to Number. *Note*: after changing to Number, look at
the Field Size. if it is Double or Single, and if you are only using whole
numbers (not decimals, like 400100.77), then change the Field Size to Long
Integer.
next, open the form in design view, and click on the control for the number
field. in the Properties box, click on the Data tab, and add the following
to the Default Value line, as

=Max("NumberFieldName","NameOfTableUnderlyingTheForm") + 1

substitute the correct table and field names, of course.

hth


Hi,

I have created a table which uses a custom autonumber and a form

that
I'm

using to add records to the table.

The problem occurs when I cancel (or undo) a record. Even though the
record
is not saved, the autonumber is considered used, I guess, and it

jumps
to

the next autonumber when I add the next new record. I want to use the
cancelled autonumbers record so that I don't have gaps in my series. (I'm
using the autonumber to number drawings).

So, it looks like I have to come up with something else but autonumbering
my
drawing number field. Could I maybe use some kind of default value
instead
where the field is populated by looking at the table and finding the last
number in the series and then adding one number?

For instance, if the last drawing number was 400100 (now, I'm not sure
this
necessarily would be the last record in the table) then the next

would
be

400101...

Any suggestions?

Thanks!

Liz

 

[Lynn Trapp]

Liz,
Check out the following website and look for a database called
AutoNumberProblem.mdb

http://www.rogersaccesslibrary.com/TableOfContents3.asp

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Hi Tina,

I must be doing something wrong. I followed your instructions but instead
of the last number in the series it will display the number 1.

The default value I entered is: =Max([tblParts]![DrawingNumber])+1 (I tried
to use your format, but it didn't like it. Access 97)

Thanks,

autonumbering

yes, that's a better solution. as a general rule, you should not use an
autonumber data type in a field that will have meaning to the user (in this
case, you).
first, you'll need to go to the table and change the number field's Data
Type from Autonumber to Number. *Note*: after changing to Number, look at
the Field Size. if it is Double or Single, and if you are only using whole
numbers (not decimals, like 400100.77), then change the Field Size to Long
Integer.
next, open the form in design view, and click on the control for the number
field. in the Properties box, click on the Data tab, and add the following
to the Default Value line, as

=Max("NumberFieldName","NameOfTableUnderlyingTheForm") + 1

substitute the correct table and field names, of course.

hth


autonumbering

 

[Liz Hansen]

Works like a charm.

Thanks!

oops, sorry - left out the D. here's the correct line. change the field and
table names, but *not* the format.

=DMax("NumberFieldName","NameOfTableUnderlyingTheForm") + 1

Hi Tina,

I must be doing something wrong. I followed your instructions but instead
of the last number in the series it will display the number 1.

The default value I entered is: =Max([tblParts]![DrawingNumber])+1 (I tried
to use your format, but it didn't like it. Access 97)

Thanks,

So, it looks like I have to come up with something else but autonumbering
my
drawing number field. Could I maybe use some kind of default value
instead
where the field is populated by looking at the table and finding the last
number in the series and then adding one number?

yes, that's a better solution. as a general rule, you should not use an
autonumber data type in a field that will have meaning to the user (in this
case, you).
first, you'll need to go to the table and change the number field's Data
Type from Autonumber to Number. *Note*: after changing to Number, look at
the Field Size. if it is Double or Single, and if you are only using whole
numbers (not decimals, like 400100.77), then change the Field Size to Long
Integer.
next, open the form in design view, and click on the control for the number
field. in the Properties box, click on the Data tab, and add the following
to the Default Value line, as

=Max("NumberFieldName","NameOfTableUnderlyingTheForm") + 1

substitute the correct table and field names, of course.

hth


Hi,

I have created a table which uses a custom autonumber and a form

that
I'm

using to add records to the table.

The problem occurs when I cancel (or undo) a record. Even though the
record
is not saved, the autonumber is considered used, I guess, and it

jumps
to

the next autonumber when I add the next new record. I want to use the
cancelled autonumbers record so that I don't have gaps in my series. (I'm
using the autonumber to number drawings).

So, it looks like I have to come up with something else but autonumbering
my
drawing number field. Could I maybe use some kind of default value
instead
where the field is populated by looking at the table and finding the last
number in the series and then adding one number?

For instance, if the last drawing number was 400100 (now, I'm not sure
this
necessarily would be the last record in the table) then the next

would
be

400101...

Any suggestions?

Thanks!

Liz

 

[Liz Hansen]

No, it worked great.

Thanks,

Actually, I don't think you can do that at the table level. You will
probably get an error message about the DMax function being unknown.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

oops, sorry - left out the D. here's the correct line. change the field and
table names, but *not* the format.

=DMax("NumberFieldName","NameOfTableUnderlyingTheForm") + 1

Hi Tina,

I must be doing something wrong. I followed your instructions but instead
of the last number in the series it will display the number 1.

The default value I entered is: =Max([tblParts]![DrawingNumber])+1 (I tried
to use your format, but it didn't like it. Access 97)

Thanks,


So, it looks like I have to come up with something else but
autonumbering
my
drawing number field. Could I maybe use some kind of default value
instead
where the field is populated by looking at the table and finding the
last
number in the series and then adding one number?

yes, that's a better solution. as a general rule, you should not use an
autonumber data type in a field that will have meaning to the user (in
this
case, you).
first, you'll need to go to the table and change the number field's Data
Type from Autonumber to Number. *Note*: after changing to Number,

look
at

the Field Size. if it is Double or Single, and if you are only using whole
numbers (not decimals, like 400100.77), then change the Field Size

to
Long

Integer.
next, open the form in design view, and click on the control for the
number
field. in the Properties box, click on the Data tab, and add the following
to the Default Value line, as

=Max("NumberFieldName","NameOfTableUnderlyingTheForm") + 1

substitute the correct table and field names, of course.

hth


Hi,

I have created a table which uses a custom autonumber and a form that
I'm
using to add records to the table.

The problem occurs when I cancel (or undo) a record. Even though the
record
is not saved, the autonumber is considered used, I guess, and it jumps
to
the next autonumber when I add the next new record. I want to use the
cancelled autonumbers record so that I don't have gaps in my series.
(I'm
using the autonumber to number drawings).

So, it looks like I have to come up with something else but
autonumbering
my
drawing number field. Could I maybe use some kind of default value
instead
where the field is populated by looking at the table and finding the
last
number in the series and then adding one number?

For instance, if the last drawing number was 400100 (now, I'm not sure
this
necessarily would be the last record in the table) then the next would
be
400101...

Any suggestions?

Thanks!

Liz

 

[Lynn Trapp]

Good to hear. I kept getting an error. I may have a problem with references
on my computer.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

No, it worked great.

Thanks,

Actually, I don't think you can do that at the table level. You will
probably get an error message about the DMax function being unknown.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

oops, sorry - left out the D. here's the correct line. change the

field
and

table names, but *not* the format.

=DMax("NumberFieldName","NameOfTableUnderlyingTheForm") + 1


Hi Tina,

I must be doing something wrong. I followed your instructions but instead
of the last number in the series it will display the number 1.

The default value I entered is: =Max([tblParts]![DrawingNumber])+1 (I
tried
to use your format, but it didn't like it. Access 97)

Thanks,


So, it looks like I have to come up with something else but
autonumbering
my
drawing number field. Could I maybe use some kind of default value
instead
where the field is populated by looking at the table and finding the
last
number in the series and then adding one number?

yes, that's a better solution. as a general rule, you should not

use
an

autonumber data type in a field that will have meaning to the user (in
this
case, you).
first, you'll need to go to the table and change the number

field's
Data

Type from Autonumber to Number. *Note*: after changing to Number, look
at
the Field Size. if it is Double or Single, and if you are only using
whole
numbers (not decimals, like 400100.77), then change the Field Size to
Long
Integer.
next, open the form in design view, and click on the control for the
number
field. in the Properties box, click on the Data tab, and add the
following
to the Default Value line, as

=Max("NumberFieldName","NameOfTableUnderlyingTheForm") + 1

substitute the correct table and field names, of course.

hth


Hi,

I have created a table which uses a custom autonumber and a form that
I'm
using to add records to the table.

The problem occurs when I cancel (or undo) a record. Even

though
the

record
is not saved, the autonumber is considered used, I guess, and it jumps
to
the next autonumber when I add the next new record. I want to

use
the

cancelled autonumbers record so that I don't have gaps in my series.
(I'm
using the autonumber to number drawings).

So, it looks like I have to come up with something else but
autonumbering
my
drawing number field. Could I maybe use some kind of default value
instead
where the field is populated by looking at the table and finding the
last
number in the series and then adding one number?

For instance, if the last drawing number was 400100 (now, I'm

not
sure

this
necessarily would be the last record in the table) then the next would
be
400101...

Any suggestions?

Thanks!

Liz

 

[tina]

i think your references are probably fine, Lynn. my instructions to Liz in
my first post told her to add the expression to the control's Default Value
property in the form, so i'm sure that's where she did it. my second post to
her was just to correct my typing error in the expression itself.

Good to hear. I kept getting an error. I may have a problem with references
on my computer.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

No, it worked great.

Thanks,

Actually, I don't think you can do that at the table level. You will
probably get an error message about the DMax function being unknown.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


oops, sorry - left out the D. here's the correct line. change the field
and
table names, but *not* the format.

=DMax("NumberFieldName","NameOfTableUnderlyingTheForm") + 1


Hi Tina,

I must be doing something wrong. I followed your instructions but
instead
of the last number in the series it will display the number 1.

The default value I entered is: =Max([tblParts]![DrawingNumber])+1 (I
tried
to use your format, but it didn't like it. Access 97)

Thanks,


So, it looks like I have to come up with something else but
autonumbering
my
drawing number field. Could I maybe use some kind of default value
instead
where the field is populated by looking at the table and

finding
the

last
number in the series and then adding one number?

yes, that's a better solution. as a general rule, you should not use
an
autonumber data type in a field that will have meaning to the

user
(in

this
case, you).
first, you'll need to go to the table and change the number field's
Data
Type from Autonumber to Number. *Note*: after changing to

Number,
look

at
the Field Size. if it is Double or Single, and if you are only using
whole
numbers (not decimals, like 400100.77), then change the Field

Size
to

Long
Integer.
next, open the form in design view, and click on the control for the
number
field. in the Properties box, click on the Data tab, and add the
following
to the Default Value line, as

=Max("NumberFieldName","NameOfTableUnderlyingTheForm") + 1

substitute the correct table and field names, of course.

hth


Hi,

I have created a table which uses a custom autonumber and a form
that
I'm
using to add records to the table.

The problem occurs when I cancel (or undo) a record. Even though
the
record
is not saved, the autonumber is considered used, I guess, and it
jumps
to
the next autonumber when I add the next new record. I want to use
the
cancelled autonumbers record so that I don't have gaps in my series.
(I'm
using the autonumber to number drawings).

So, it looks like I have to come up with something else but
autonumbering
my
drawing number field. Could I maybe use some kind of default value
instead
where the field is populated by looking at the table and

finding
the

last
number in the series and then adding one number?

For instance, if the last drawing number was 400100 (now, I'm not
sure
this
necessarily would be the last record in the table) then the next
would
be
400101...

Any suggestions?

Thanks!

Liz

 

[Lynn Trapp]

Tina,
I see that now. I had misread your post and thought you were talking about
the table instead of the form.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

i think your references are probably fine, Lynn. my instructions to Liz in
my first post told her to add the expression to the control's Default Value
property in the form, so i'm sure that's where she did it. my second post to
her was just to correct my typing error in the expression itself.

Good to hear. I kept getting an error. I may have a problem with references
on my computer.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

No, it worked great.

Thanks,


Actually, I don't think you can do that at the table level. You will
probably get an error message about the DMax function being unknown.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


oops, sorry - left out the D. here's the correct line. change the field
and
table names, but *not* the format.

=DMax("NumberFieldName","NameOfTableUnderlyingTheForm") + 1


Hi Tina,

I must be doing something wrong. I followed your instructions but
instead
of the last number in the series it will display the number 1.

The default value I entered is:

=Max([tblParts]![DrawingNumber])+1
(I

tried
to use your format, but it didn't like it. Access 97)

Thanks,


So, it looks like I have to come up with something else but
autonumbering
my
drawing number field. Could I maybe use some kind of default
value
instead
where the field is populated by looking at the table and finding
the
last
number in the series and then adding one number?

yes, that's a better solution. as a general rule, you should

not
use

an
autonumber data type in a field that will have meaning to the user
(in
this
case, you).
first, you'll need to go to the table and change the number field's
Data
Type from Autonumber to Number. *Note*: after changing to Number,
look
at
the Field Size. if it is Double or Single, and if you are only using
whole
numbers (not decimals, like 400100.77), then change the Field Size
to
Long
Integer.
next, open the form in design view, and click on the control

for
the

number
field. in the Properties box, click on the Data tab, and add the
following
to the Default Value line, as

=Max("NumberFieldName","NameOfTableUnderlyingTheForm") + 1

substitute the correct table and field names, of course.

hth


Hi,

I have created a table which uses a custom autonumber and a form
that
I'm
using to add records to the table.

The problem occurs when I cancel (or undo) a record. Even though
the
record
is not saved, the autonumber is considered used, I guess,

and

it to
use I'm
not

 

[tina]

but thanks for pointing out the "error". i always hope that if i post a bad
solution or miss/forget something, that somebody else will notice and
correct me - it helps me, and the person who asked the question.

Tina,
I see that now. I had misread your post and thought you were talking about
the table instead of the form.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

i think your references are probably fine, Lynn. my instructions to Liz in
my first post told her to add the expression to the control's Default Value
property in the form, so i'm sure that's where she did it. my second

post

to
her was just to correct my typing error in the expression itself.

Good to hear. I kept getting an error. I may have a problem with references
on my computer.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


No, it worked great.

Thanks,


Actually, I don't think you can do that at the table level. You will
probably get an error message about the DMax function being unknown.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


oops, sorry - left out the D. here's the correct line. change the
field
and
table names, but *not* the format.

=DMax("NumberFieldName","NameOfTableUnderlyingTheForm") + 1


Hi Tina,

I must be doing something wrong. I followed your instructions but
instead
of the last number in the series it will display the number 1.

The default value I entered is: =Max([tblParts]![DrawingNumber])+1
(I
tried
to use your format, but it didn't like it. Access 97)

Thanks,


So, it looks like I have to come up with something else but
autonumbering
my
drawing number field. Could I maybe use some kind of default
value
instead
where the field is populated by looking at the table and finding
the
last
number in the series and then adding one number?

yes, that's a better solution. as a general rule, you should not
use
an
autonumber data type in a field that will have meaning to

the
user

(in
this
case, you).
first, you'll need to go to the table and change the number
field's
Data
Type from Autonumber to Number. *Note*: after changing to Number,
look
at
the Field Size. if it is Double or Single, and if you are only
using
whole
numbers (not decimals, like 400100.77), then change the

Field
Size

to
Long
Integer.
next, open the form in design view, and click on the control for
the
number
field. in the Properties box, click on the Data tab, and add the
following
to the Default Value line, as

=Max("NumberFieldName","NameOfTableUnderlyingTheForm") + 1

substitute the correct table and field names, of course.

hth


Hi,

I have created a table which uses a custom autonumber and

a
form

that
I'm
using to add records to the table.

The problem occurs when I cancel (or undo) a record. Even
though
the
record
is not saved, the autonumber is considered used, I guess,

and

it
jumps
to
the next autonumber when I add the next new record. I

want

to the
next