How to "turn-off" auto-increment between two text columns

[CapinMorgan]

Hi Everyone,

I have a client at a lawfirm who has run across something I've never
seen before. I've tested this in Access 97 throught Access 2003 and
have been able to reproduce it everytime. However, I cannot find any
mention of it on Microsoft's knowledgbase or by searching the news
group's articles here.

Here's how to reproduce the problem:

Open any version of Access from the list above. Create a new table in
design view. Create four new fields using the following data types:
[field1]:Autonumber, [field2]:Text, [field3]:Text, [field4]choose
from Date, Memo, or Text).

Open the table and type something like 00513 in [field2] (the first
text field).
Then type either 00514 or 00515 in [field3] (the second text field).
Now hit the [TAB] key.

As long as the difference between the numerical value in [field2] and
[field3] is in the set {1,2} you should notice the next progressive
increment listed in [field4]. If [field4] is not a Date field then you
just have an automatically generated increment value in there which is
a minor annoyance. However, if it is a Date field and you try to move
to the next field/record you will get the error:

"The value you entered isn't valid for this field",

which I consider to be much more than a minor annoyance. This even
works if [field2] is 513 and [field3] is 00000514 it will put 515 in
[field4]. It always drops off the leading zeros.

Has anyone ever encountered this before and if so have you found a way
to turn off this "feature."

I can't do anything tricky like just moving the fields around because
the fields are BegNo and EndNo and the table is tracking documents for
a court case and as anyone in the legal field would understand it would
be a big 'ol no-no to not have the fields right next to each other as
well as the docDate right after for data entry. The client is also
adamant that they want to do the data entry in "table" form as they
call it as opposed to on a form where this issue does not occur.

All in all it is a very strange result from just having two text fields
with numbers in them next to each other. I also can't convert the
BegNo and EndNo fields to long int because some of the document numbers
have an alpha pre-fix. I just need to find a way to turn this
automation off for the client.

Any help would be greatly appreciated, thank you.

Best Regards,

Dave Morgan
Business Analyst/Independent Consultant

 

[Jeff Boyce]

Dave

This is a documented "feature" of doing data entry directly in the tables.
Your customer may wish to work directly in the tables because it reminds
them of spreadsheets.

Access is a relational database. Tables store data, forms are used to
view/add/edit data. You've encountered one of the reasons the consensus in
these access-related newsgroups strongly recommend never using tables for
data entry.

If your users insist, point out that this is a fact of life and that they'll
have to learn to live with it... or you can offer them a data entry form
that looks a LOT like a spreadsheet, but has some safeguards and
user-proofing built in, as well as not triggering this issue.

Pay me now or pay me later!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Everyone,

I have a client at a lawfirm who has run across something I've never
seen before. I've tested this in Access 97 throught Access 2003 and
have been able to reproduce it everytime. However, I cannot find any
mention of it on Microsoft's knowledgbase or by searching the news
group's articles here.

Here's how to reproduce the problem:

Open any version of Access from the list above. Create a new table in
design view. Create four new fields using the following data types:
[field1]:Autonumber, [field2]:Text, [field3]:Text, [field4]choose
from Date, Memo, or Text).

Open the table and type something like 00513 in [field2] (the first
text field).
Then type either 00514 or 00515 in [field3] (the second text field).
Now hit the [TAB] key.

As long as the difference between the numerical value in [field2] and
[field3] is in the set {1,2} you should notice the next progressive
increment listed in [field4]. If [field4] is not a Date field then you
just have an automatically generated increment value in there which is
a minor annoyance. However, if it is a Date field and you try to move
to the next field/record you will get the error:

"The value you entered isn't valid for this field",

which I consider to be much more than a minor annoyance. This even
works if [field2] is 513 and [field3] is 00000514 it will put 515 in
[field4]. It always drops off the leading zeros.

Has anyone ever encountered this before and if so have you found a way
to turn off this "feature."

I can't do anything tricky like just moving the fields around because
the fields are BegNo and EndNo and the table is tracking documents for
a court case and as anyone in the legal field would understand it would
be a big 'ol no-no to not have the fields right next to each other as
well as the docDate right after for data entry. The client is also
adamant that they want to do the data entry in "table" form as they
call it as opposed to on a form where this issue does not occur.

All in all it is a very strange result from just having two text fields
with numbers in them next to each other. I also can't convert the
BegNo and EndNo fields to long int because some of the document numbers
have an alpha pre-fix. I just need to find a way to turn this
automation off for the client.

Any help would be greatly appreciated, thank you.

Best Regards,

Dave Morgan
Business Analyst/Independent Consultant

 

[CapinMorgan]

Dear Jeff,

Thanks for the reply. Do you happen to know off-hand where this is
documented? I searched everywhere on Microsoft's website and using
general search engines such as MSN, Yahoo!, and Google to name a few
and couldn't find any references to this behavior.

If you don't have a reference for me could you point me in the right
direction and I'll do my best to locate the documentation. Even just
the technical term for this behavior would be a big help.

I have a very opinionated, read stubborn, client who won't just take my
word for it and will want to hear it from Microsoft before letting me
say there isn't anything I can do. Normally I wouldn't even worry
about disappointing a client over such a seemingly trivial matter but
they pay very well.

I'm thinking I might just make a form and set it to table view and then
just trap the error and clear the Date field.

Thanks in advance for any help on the documentation.

Sincerely,

-Dave Morgan

Dave

This is a documented "feature" of doing data entry directly in the tables.
Your customer may wish to work directly in the tables because it reminds
them of spreadsheets.

Access is a relational database. Tables store data, forms are used to
view/add/edit data. You've encountered one of the reasons the consensus in
these access-related newsgroups strongly recommend never using tables for
data entry.

If your users insist, point out that this is a fact of life and that they'll
have to learn to live with it... or you can offer them a data entry form
that looks a LOT like a spreadsheet, but has some safeguards and
user-proofing built in, as well as not triggering this issue.

Pay me now or pay me later!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Everyone,

I have a client at a lawfirm who has run across something I've never
seen before. I've tested this in Access 97 throught Access 2003 and
have been able to reproduce it everytime. However, I cannot find any
mention of it on Microsoft's knowledgbase or by searching the news
group's articles here.

Here's how to reproduce the problem:

Open any version of Access from the list above. Create a new table in
design view. Create four new fields using the following data types:
[field1]:Autonumber, [field2]:Text, [field3]:Text, [field4]choose
from Date, Memo, or Text).

Open the table and type something like 00513 in [field2] (the first
text field).
Then type either 00514 or 00515 in [field3] (the second text field).
Now hit the [TAB] key.

As long as the difference between the numerical value in [field2] and
[field3] is in the set {1,2} you should notice the next progressive
increment listed in [field4]. If [field4] is not a Date field then you
just have an automatically generated increment value in there which is
a minor annoyance. However, if it is a Date field and you try to move
to the next field/record you will get the error:

"The value you entered isn't valid for this field",

which I consider to be much more than a minor annoyance. This even
works if [field2] is 513 and [field3] is 00000514 it will put 515 in
[field4]. It always drops off the leading zeros.

Has anyone ever encountered this before and if so have you found a way
to turn off this "feature."

I can't do anything tricky like just moving the fields around because
the fields are BegNo and EndNo and the table is tracking documents for
a court case and as anyone in the legal field would understand it would
be a big 'ol no-no to not have the fields right next to each other as
well as the docDate right after for data entry. The client is also
adamant that they want to do the data entry in "table" form as they
call it as opposed to on a form where this issue does not occur.

All in all it is a very strange result from just having two text fields
with numbers in them next to each other. I also can't convert the
BegNo and EndNo fields to long int because some of the document numbers
have an alpha pre-fix. I just need to find a way to turn this
automation off for the client.

Any help would be greatly appreciated, thank you.

Best Regards,

Dave Morgan
Business Analyst/Independent Consultant

 

[Jeff Boyce]

I know I put that here somewhere... pat, pat, shuffle, shuffle ... (but I'm
losing it!).

Perhaps one of the other 'group readers can recall where they've seen an
explanation of the issue you described?

I wonder if Access HELP on features/specifications might have something?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Dear Jeff,

Thanks for the reply. Do you happen to know off-hand where this is
documented? I searched everywhere on Microsoft's website and using
general search engines such as MSN, Yahoo!, and Google to name a few
and couldn't find any references to this behavior.

If you don't have a reference for me could you point me in the right
direction and I'll do my best to locate the documentation. Even just
the technical term for this behavior would be a big help.

I have a very opinionated, read stubborn, client who won't just take my
word for it and will want to hear it from Microsoft before letting me
say there isn't anything I can do. Normally I wouldn't even worry
about disappointing a client over such a seemingly trivial matter but
they pay very well.

I'm thinking I might just make a form and set it to table view and then
just trap the error and clear the Date field.

Thanks in advance for any help on the documentation.

Sincerely,

-Dave Morgan

Dave

This is a documented "feature" of doing data entry directly in the
tables.
Your customer may wish to work directly in the tables because it reminds
them of spreadsheets.

Access is a relational database. Tables store data, forms are used to
view/add/edit data. You've encountered one of the reasons the consensus
in
these access-related newsgroups strongly recommend never using tables for
data entry.

If your users insist, point out that this is a fact of life and that
they'll
have to learn to live with it... or you can offer them a data entry form
that looks a LOT like a spreadsheet, but has some safeguards and
user-proofing built in, as well as not triggering this issue.

Pay me now or pay me later!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Everyone,

I have a client at a lawfirm who has run across something I've never
seen before. I've tested this in Access 97 throught Access 2003 and
have been able to reproduce it everytime. However, I cannot find any
mention of it on Microsoft's knowledgbase or by searching the news
group's articles here.

Here's how to reproduce the problem:

Open any version of Access from the list above. Create a new table in
design view. Create four new fields using the following data types:
[field1]:Autonumber, [field2]:Text, [field3]:Text, [field4]choose
from Date, Memo, or Text).

Open the table and type something like 00513 in [field2] (the first
text field).
Then type either 00514 or 00515 in [field3] (the second text field).
Now hit the [TAB] key.

As long as the difference between the numerical value in [field2] and
[field3] is in the set {1,2} you should notice the next progressive
increment listed in [field4]. If [field4] is not a Date field then you
just have an automatically generated increment value in there which is
a minor annoyance. However, if it is a Date field and you try to move
to the next field/record you will get the error:

"The value you entered isn't valid for this field",

which I consider to be much more than a minor annoyance. This even
works if [field2] is 513 and [field3] is 00000514 it will put 515 in
[field4]. It always drops off the leading zeros.

Has anyone ever encountered this before and if so have you found a way
to turn off this "feature."

I can't do anything tricky like just moving the fields around because
the fields are BegNo and EndNo and the table is tracking documents for
a court case and as anyone in the legal field would understand it would
be a big 'ol no-no to not have the fields right next to each other as
well as the docDate right after for data entry. The client is also
adamant that they want to do the data entry in "table" form as they
call it as opposed to on a form where this issue does not occur.

All in all it is a very strange result from just having two text fields
with numbers in them next to each other. I also can't convert the
BegNo and EndNo fields to long int because some of the document numbers
have an alpha pre-fix. I just need to find a way to turn this
automation off for the client.

Any help would be greatly appreciated, thank you.

Best Regards,

Dave Morgan
Business Analyst/Independent Consultant

 

[Van T. Dinh]

See The Microsoft Knowledge Base article:

http://support.microsoft.com/kb/271607

--
HTH
Van T. Dinh
MVP (Access)

Dear Jeff,

Thanks for the reply. Do you happen to know off-hand where this is
documented? I searched everywhere on Microsoft's website and using
general search engines such as MSN, Yahoo!, and Google to name a few
and couldn't find any references to this behavior.

If you don't have a reference for me could you point me in the right
direction and I'll do my best to locate the documentation. Even just
the technical term for this behavior would be a big help.

I have a very opinionated, read stubborn, client who won't just take my
word for it and will want to hear it from Microsoft before letting me
say there isn't anything I can do. Normally I wouldn't even worry
about disappointing a client over such a seemingly trivial matter but
they pay very well.

I'm thinking I might just make a form and set it to table view and then
just trap the error and clear the Date field.

Thanks in advance for any help on the documentation.

Sincerely,

-Dave Morgan

Dave

This is a documented "feature" of doing data entry directly in the
tables.
Your customer may wish to work directly in the tables because it reminds
them of spreadsheets.

Access is a relational database. Tables store data, forms are used to
view/add/edit data. You've encountered one of the reasons the consensus
in
these access-related newsgroups strongly recommend never using tables for
data entry.

If your users insist, point out that this is a fact of life and that
they'll
have to learn to live with it... or you can offer them a data entry form
that looks a LOT like a spreadsheet, but has some safeguards and
user-proofing built in, as well as not triggering this issue.

Pay me now or pay me later!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Everyone,

I have a client at a lawfirm who has run across something I've never
seen before. I've tested this in Access 97 throught Access 2003 and
have been able to reproduce it everytime. However, I cannot find any
mention of it on Microsoft's knowledgbase or by searching the news
group's articles here.

Here's how to reproduce the problem:

Open any version of Access from the list above. Create a new table in
design view. Create four new fields using the following data types:
[field1]:Autonumber, [field2]:Text, [field3]:Text, [field4]choose
from Date, Memo, or Text).

Open the table and type something like 00513 in [field2] (the first
text field).
Then type either 00514 or 00515 in [field3] (the second text field).
Now hit the [TAB] key.

As long as the difference between the numerical value in [field2] and
[field3] is in the set {1,2} you should notice the next progressive
increment listed in [field4]. If [field4] is not a Date field then you
just have an automatically generated increment value in there which is
a minor annoyance. However, if it is a Date field and you try to move
to the next field/record you will get the error:

"The value you entered isn't valid for this field",

which I consider to be much more than a minor annoyance. This even
works if [field2] is 513 and [field3] is 00000514 it will put 515 in
[field4]. It always drops off the leading zeros.

Has anyone ever encountered this before and if so have you found a way
to turn off this "feature."

I can't do anything tricky like just moving the fields around because
the fields are BegNo and EndNo and the table is tracking documents for
a court case and as anyone in the legal field would understand it would
be a big 'ol no-no to not have the fields right next to each other as
well as the docDate right after for data entry. The client is also
adamant that they want to do the data entry in "table" form as they
call it as opposed to on a form where this issue does not occur.

All in all it is a very strange result from just having two text fields
with numbers in them next to each other. I also can't convert the
BegNo and EndNo fields to long int because some of the document numbers
have an alpha pre-fix. I just need to find a way to turn this
automation off for the client.

Any help would be greatly appreciated, thank you.

Best Regards,

Dave Morgan
Business Analyst/Independent Consultant

 

[Jeff Boyce]

Thanks, Van. I knew someone would remember where that was (or what it was
called)!

Jeff

See The Microsoft Knowledge Base article:

http://support.microsoft.com/kb/271607

--
HTH
Van T. Dinh
MVP (Access)

Dear Jeff,

Thanks for the reply. Do you happen to know off-hand where this is
documented? I searched everywhere on Microsoft's website and using
general search engines such as MSN, Yahoo!, and Google to name a few
and couldn't find any references to this behavior.

If you don't have a reference for me could you point me in the right
direction and I'll do my best to locate the documentation. Even just
the technical term for this behavior would be a big help.

I have a very opinionated, read stubborn, client who won't just take my
word for it and will want to hear it from Microsoft before letting me
say there isn't anything I can do. Normally I wouldn't even worry
about disappointing a client over such a seemingly trivial matter but
they pay very well.

I'm thinking I might just make a form and set it to table view and then
just trap the error and clear the Date field.

Thanks in advance for any help on the documentation.

Sincerely,

-Dave Morgan

Dave

This is a documented "feature" of doing data entry directly in the
tables.
Your customer may wish to work directly in the tables because it reminds
them of spreadsheets.

Access is a relational database. Tables store data, forms are used to
view/add/edit data. You've encountered one of the reasons the consensus
in
these access-related newsgroups strongly recommend never using tables
for
data entry.

If your users insist, point out that this is a fact of life and that
they'll
have to learn to live with it... or you can offer them a data entry form
that looks a LOT like a spreadsheet, but has some safeguards and
user-proofing built in, as well as not triggering this issue.

Pay me now or pay me later!

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hi Everyone,

I have a client at a lawfirm who has run across something I've never
seen before. I've tested this in Access 97 throught Access 2003 and
have been able to reproduce it everytime. However, I cannot find any
mention of it on Microsoft's knowledgbase or by searching the news
group's articles here.

Here's how to reproduce the problem:

Open any version of Access from the list above. Create a new table in
design view. Create four new fields using the following data types:
[field1]:Autonumber, [field2]:Text, [field3]:Text, [field4]choose
from Date, Memo, or Text).

Open the table and type something like 00513 in [field2] (the first
text field).
Then type either 00514 or 00515 in [field3] (the second text field).
Now hit the [TAB] key.

As long as the difference between the numerical value in [field2] and
[field3] is in the set {1,2} you should notice the next progressive
increment listed in [field4]. If [field4] is not a Date field then
you
just have an automatically generated increment value in there which is
a minor annoyance. However, if it is a Date field and you try to move
to the next field/record you will get the error:

"The value you entered isn't valid for this field",

which I consider to be much more than a minor annoyance. This even
works if [field2] is 513 and [field3] is 00000514 it will put 515 in
[field4]. It always drops off the leading zeros.

Has anyone ever encountered this before and if so have you found a way
to turn off this "feature."

I can't do anything tricky like just moving the fields around because
the fields are BegNo and EndNo and the table is tracking documents for
a court case and as anyone in the legal field would understand it
would
be a big 'ol no-no to not have the fields right next to each other as
well as the docDate right after for data entry. The client is also
adamant that they want to do the data entry in "table" form as they
call it as opposed to on a form where this issue does not occur.

All in all it is a very strange result from just having two text
fields
with numbers in them next to each other. I also can't convert the
BegNo and EndNo fields to long int because some of the document
numbers
have an alpha pre-fix. I just need to find a way to turn this
automation off for the client.

Any help would be greatly appreciated, thank you.

Best Regards,

Dave Morgan
Business Analyst/Independent Consultant

 

[CapinMorgan]

Thanks Van that is exactly what I needed. Now lets hope the client
finally believes me and lets me make them a custom form.

Thanks again.

Sincerely,

Dave Morgan

See The Microsoft Knowledge Base article:

http://support.microsoft.com/kb/271607

--
HTH
Van T. Dinh
MVP (Access)

Dear Jeff,

Thanks for the reply. Do you happen to know off-hand where this is
documented? I searched everywhere on Microsoft's website and using
general search engines such as MSN, Yahoo!, and Google to name a few
and couldn't find any references to this behavior.

If you don't have a reference for me could you point me in the right
direction and I'll do my best to locate the documentation. Even just
the technical term for this behavior would be a big help.

I have a very opinionated, read stubborn, client who won't just take my
word for it and will want to hear it from Microsoft before letting me
say there isn't anything I can do. Normally I wouldn't even worry
about disappointing a client over such a seemingly trivial matter but
they pay very well.

I'm thinking I might just make a form and set it to table view and then
just trap the error and clear the Date field.

Thanks in advance for any help on the documentation.

Sincerely,

-Dave Morgan

Dave

This is a documented "feature" of doing data entry directly in the
tables.
Your customer may wish to work directly in the tables because it reminds
them of spreadsheets.

Access is a relational database. Tables store data, forms are used to
view/add/edit data. You've encountered one of the reasons the consensus
in
these access-related newsgroups strongly recommend never using tables for
data entry.

If your users insist, point out that this is a fact of life and that
they'll
have to learn to live with it... or you can offer them a data entry form
that looks a LOT like a spreadsheet, but has some safeguards and
user-proofing built in, as well as not triggering this issue.

Pay me now or pay me later!

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hi Everyone,

I have a client at a lawfirm who has run across something I've never
seen before. I've tested this in Access 97 throught Access 2003 and
have been able to reproduce it everytime. However, I cannot find any
mention of it on Microsoft's knowledgbase or by searching the news
group's articles here.

Here's how to reproduce the problem:

Open any version of Access from the list above. Create a new table in
design view. Create four new fields using the following data types:
[field1]:Autonumber, [field2]:Text, [field3]:Text, [field4]choose
from Date, Memo, or Text).

Open the table and type something like 00513 in [field2] (the first
text field).
Then type either 00514 or 00515 in [field3] (the second text field).
Now hit the [TAB] key.

As long as the difference between the numerical value in [field2] and
[field3] is in the set {1,2} you should notice the next progressive
increment listed in [field4]. If [field4] is not a Date field then you
just have an automatically generated increment value in there which is
a minor annoyance. However, if it is a Date field and you try to move
to the next field/record you will get the error:

"The value you entered isn't valid for this field",

which I consider to be much more than a minor annoyance. This even
works if [field2] is 513 and [field3] is 00000514 it will put 515 in
[field4]. It always drops off the leading zeros.

Has anyone ever encountered this before and if so have you found a way
to turn off this "feature."

I can't do anything tricky like just moving the fields around because
the fields are BegNo and EndNo and the table is tracking documents for
a court case and as anyone in the legal field would understand it would
be a big 'ol no-no to not have the fields right next to each other as
well as the docDate right after for data entry. The client is also
adamant that they want to do the data entry in "table" form as they
call it as opposed to on a form where this issue does not occur.

All in all it is a very strange result from just having two text fields
with numbers in them next to each other. I also can't convert the
BegNo and EndNo fields to long int because some of the document numbers
have an alpha pre-fix. I just need to find a way to turn this
automation off for the client.

Any help would be greatly appreciated, thank you.

Best Regards,

Dave Morgan
Business Analyst/Independent Consultant