Table w/ Autonumber AND Cascade update

[Lee C.]

I'm finding this to be extremely difficult to set up. I understand
that Access won't manage the primary key and the cascade updates for a
table. Fine. I tried changing the PK type to number and setting
default value to a UDF that manages the auto-numbering. Access won't
take a UDF as a default value. Okay, I'll use SQL WITHOUT any
aggregate functions, for the default value. Access won't do that
either. Okay, I create a second column, make it autonumber and PK,
then set the default value of my old PK column to the field value of
the new autonumber PK field. Wow! Access won't do that either. Am I
crazy or should this be easier? Any insight will be greatly
appreciated.

MS Access 2002
Windows XP Pro

Thanks,
Lee

[Mike Storr]

On 7 Feb 2004 14:55:45 -0800, Lee C. wrote:

I'm finding this to be extremely difficult to set up. I understand
that Access won't manage the primary key and the cascade updates for a
table. Fine. I tried changing the PK type to number and setting
default value to a UDF that manages the auto-numbering. Access won't
take a UDF as a default value. Okay, I'll use SQL WITHOUT any
aggregate functions, for the default value. Access won't do that
either. Okay, I create a second column, make it autonumber and PK,
then set the default value of my old PK column to the field value of
the new autonumber PK field. Wow! Access won't do that either. Am I
crazy or should this be easier? Any insight will be greatly
appreciated.

MS Access 2002
Windows XP Pro

Thanks,
Lee

What is it you are trying to do?
Autonumbers are not meant to be "managed". They're a one time use, unique
value that can be automatically assigned without fear of it being repeated.
Access manages cascade updates just fine. If a record is changed, it's
related record will be adjusted as long as the relationships are set, and
the new values do not violate the rules that have been defined.

If you are trying to use a numbering system that never breaks sequence,
then you'll need to create update queries or a function that renumbers the
desired records. If relationships are defined, then these changes will
cascade.
--
Mike Storr
veraccess.com

[Lee C.]

What I am trying to do is have the capability of changing a record's
primary key value and have it cascade updated throughout related
tables.

Autonumbers certainly are managed. Access assigns them and ensures
they are unique. That is management, my friend. <g>

I know Access manages cascade updates just fine--but it refuses to
take on the management task if the PK is an autonumber! Yeah, yeah,
you can't change an autonumber. <g> That's what I am trying to work
around. (If someone paid me, I could write an RDBMS where you could
change an autonumber, and I'd still ensure uniqueness. Access already
lets me change any other PK--that's not autonumber--and Access still
ensures uniqueness, or prevents the change. Why is autonumber so
different? Because Microsoft chose to have it that way; not because
it's impossible or bad design.)

This is an existing database, which I didn't design: Requisitions have
line items. Line items can be reassigned to other requisitions. All
I want is to be able to change the ReqID (currently an autonumber) of
an existing line item and have Access cascade update the related
tables.

This really doesn't seem like to much to ask from a version 10 RDBMS.
Presently, the line item is copied to a temp table, and then VBA is
used to delete the record from tblLineItems, then VBA copies the rec
from the temp table back to tblLineItems with the new ReqID, and
deletes the rec from the temp table. There are so many related tables
that this nightmare is cumbersome to manage in VBA. Hence, my idea to
have the Relation Database MANAGEMENT System do some managing.

Thanks again,
Lee

Mike Storr <st******@sympa tico.ca> wrote in message news:<1w******* *************** ********@40tude .net>...

What is it you are trying to do?
Autonumbers are not meant to be "managed". They're a one time use, unique
value that can be automatically assigned without fear of it being repeated.
Access manages cascade updates just fine. If a record is changed, it's
related record will be adjusted as long as the relationships are set, and
the new values do not violate the rules that have been defined.

If you are trying to use a numbering system that never breaks sequence,
then you'll need to create update queries or a function that renumbers the
desired records. If relationships are defined, then these changes will
cascade. On 7 Feb 2004 14:55:45 -0800, Lee C. wrote:

I'm finding this to be extremely difficult to set up. I understand
that Access won't manage the primary key and the cascade updates for a
table. Fine. I tried changing the PK type to number and setting
default value to a UDF that manages the auto-numbering. Access won't
take a UDF as a default value. Okay, I'll use SQL WITHOUT any
aggregate functions, for the default value. Access won't do that
either. Okay, I create a second column, make it autonumber and PK,
then set the default value of my old PK column to the field value of
the new autonumber PK field. Wow! Access won't do that either. Am I
crazy or should this be easier? Any insight will be greatly
appreciated.

MS Access 2002
Windows XP Pro

Thanks,
Lee

[Douglas J. Steele]

I don't believe any RDMS that has the equivalent of AutoNumbers (say, for
instance, SQL Server's Identity type) allows you to change the value of the
field. There is no legitimate reason I can think of why you'd need to.

As Mike alludes to, AutoNumbers are intended for one purpose: to create an
(almost guaranteed) unique value that can be used as a primary key. Seldom,
if ever, should the value of the AutoNumber field even be shown to the user.
If you care about the value of the AutoNumber field, then it's almost
certain that you should be using a different data type.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Lee C." <ci***********@ hotmail.com> wrote in message
news:c4******** *************** **@posting.goog le.com...

What I am trying to do is have the capability of changing a record's
primary key value and have it cascade updated throughout related
tables.

Autonumbers certainly are managed. Access assigns them and ensures
they are unique. That is management, my friend. <g>

I know Access manages cascade updates just fine--but it refuses to
take on the management task if the PK is an autonumber! Yeah, yeah,
you can't change an autonumber. <g> That's what I am trying to work
around. (If someone paid me, I could write an RDBMS where you could
change an autonumber, and I'd still ensure uniqueness. Access already
lets me change any other PK--that's not autonumber--and Access still
ensures uniqueness, or prevents the change. Why is autonumber so
different? Because Microsoft chose to have it that way; not because
it's impossible or bad design.)

This is an existing database, which I didn't design: Requisitions have
line items. Line items can be reassigned to other requisitions. All
I want is to be able to change the ReqID (currently an autonumber) of
an existing line item and have Access cascade update the related
tables.

This really doesn't seem like to much to ask from a version 10 RDBMS.
Presently, the line item is copied to a temp table, and then VBA is
used to delete the record from tblLineItems, then VBA copies the rec
from the temp table back to tblLineItems with the new ReqID, and
deletes the rec from the temp table. There are so many related tables
that this nightmare is cumbersome to manage in VBA. Hence, my idea to
have the Relation Database MANAGEMENT System do some managing.

Thanks again,
Lee

Mike Storr <st******@sympa tico.ca> wrote in message

news:<1w******* *************** ********@40tude .net>...

What is it you are trying to do?
Autonumbers are not meant to be "managed". They're a one time use, unique value that can be automatically assigned without fear of it being repeated. Access manages cascade updates just fine. If a record is changed, it's
related record will be adjusted as long as the relationships are set, and the new values do not violate the rules that have been defined.

If you are trying to use a numbering system that never breaks sequence,
then you'll need to create update queries or a function that renumbers the desired records. If relationships are defined, then these changes will
cascade.

[Lee Cichanowicz]

I appreciate the replies. I'm not looking to redesign Accesss. I'm trying
to work around it's limitations--I'll concede that they are wonderful
product features, if it will get us back on track.. <g> That is what I
want help with, the workaround. We can all agree or disagree on how things
should be, but that doesn't help me right now. How do I get around this, is
my question.

I am stuck with a table that has an autonumber PK, because it has no good
candidate key fields or field combinations. I need to convert it to a
number so that I can change the field value when necessary. Once I do
change the field type, how do I manage the PK field values for new records
if Access won't let me: (1) use SQL in the default value, (2) use a UDF in
the default value, or (3) reference another field in the default value?

I abhor the idea of doing this in the front-end mdb.

Sincere thanks,
Lee
"Douglas J. Steele" <NOSPAM_djsteel e@NOSPAM_canada .com> wrote in message
news:XX******** **********@twis ter01.bloor.is. net.cable.roger s.com...

I don't believe any RDMS that has the equivalent of AutoNumbers (say, for
instance, SQL Server's Identity type) allows you to change the value of the field. There is no legitimate reason I can think of why you'd need to.

As Mike alludes to, AutoNumbers are intended for one purpose: to create an
(almost guaranteed) unique value that can be used as a primary key. Seldom, if ever, should the value of the AutoNumber field even be shown to the user. If you care about the value of the AutoNumber field, then it's almost
certain that you should be using a different data type.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Lee C." <ci***********@ hotmail.com> wrote in message
news:c4******** *************** **@posting.goog le.com...

What I am trying to do is have the capability of changing a record's
primary key value and have it cascade updated throughout related
tables.

Autonumbers certainly are managed. Access assigns them and ensures
they are unique. That is management, my friend. <g>

I know Access manages cascade updates just fine--but it refuses to
take on the management task if the PK is an autonumber! Yeah, yeah,
you can't change an autonumber. <g> That's what I am trying to work
around. (If someone paid me, I could write an RDBMS where you could
change an autonumber, and I'd still ensure uniqueness. Access already
lets me change any other PK--that's not autonumber--and Access still
ensures uniqueness, or prevents the change. Why is autonumber so
different? Because Microsoft chose to have it that way; not because
it's impossible or bad design.)

This is an existing database, which I didn't design: Requisitions have
line items. Line items can be reassigned to other requisitions. All
I want is to be able to change the ReqID (currently an autonumber) of
an existing line item and have Access cascade update the related
tables.

This really doesn't seem like to much to ask from a version 10 RDBMS.
Presently, the line item is copied to a temp table, and then VBA is
used to delete the record from tblLineItems, then VBA copies the rec
from the temp table back to tblLineItems with the new ReqID, and
deletes the rec from the temp table. There are so many related tables
that this nightmare is cumbersome to manage in VBA. Hence, my idea to
have the Relation Database MANAGEMENT System do some managing.

Thanks again,
Lee

Mike Storr <st******@sympa tico.ca> wrote in message

news:<1w******* *************** ********@40tude .net>...

What is it you are trying to do?
Autonumbers are not meant to be "managed". They're a one time use, unique value that can be automatically assigned without fear of it being repeated. Access manages cascade updates just fine. If a record is changed, it's
related record will be adjusted as long as the relationships are set, and the new values do not violate the rules that have been defined.

If you are trying to use a numbering system that never breaks sequence, then you'll need to create update queries or a function that renumbers the desired records. If relationships are defined, then these changes will
cascade.

[Bas Cost Budde]

Lee Cichanowicz wrote:

I appreciate the replies. I'm not looking to redesign Accesss. I'm trying
to work around it's limitations--I'll concede that they are wonderful
product features, if it will get us back on track.. <g> That is what I
want help with, the workaround. We can all agree or disagree on how things
should be, but that doesn't help me right now. How do I get around this, is
my question.

I am stuck with a table that has an autonumber PK, because it has no good
candidate key fields or field combinations. I need to convert it to a
number so that I can change the field value when necessary. Once I do
change the field type, how do I manage the PK field values for new records
if Access won't let me: (1) use SQL in the default value, (2) use a UDF in
the default value, or (3) reference another field in the default value?

I abhor the idea of doing this in the front-end mdb.

You definately have a no-go there. There are no user defined triggers in
Access (Jet engine). So, nothing inbetween numbers and autonumber.

As long as your users do not touch the tables, is there a real objection
to handling new numbers from the front end?
--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

[Lee Cichanowicz]

You have clearly identified problem, Bas: No triggers in Access! What a
tremendous shortcoming...I mean, product feature, that is.

My real objection to managing record IDs in the front end is that I upsize a
lot of Access databases to SQL Server. I also put Web front-ends on them.
I know that the more back-end management that I do in the front-end, the
greater the nightmare upsizing or Web-enabling becomes. So, it's just a
matter of principle, but it seems I'm screwed here.

Thanks,
Lee

"Bas Cost Budde" <ba*@heuveltop. org> wrote in message
news:c0******** *@news2.solcon. nl...

You definately have a no-go there. There are no user defined triggers in
Access (Jet engine). So, nothing inbetween numbers and autonumber.

As long as your users do not touch the tables, is there a real objection
to handling new numbers from the front end?
--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl
Lee Cichanowicz wrote:

I appreciate the replies. I'm not looking to redesign Accesss. I'm trying to work around it's limitations--I'll concede that they are wonderful
product features, if it will get us back on track.. <g> That is what I
want help with, the workaround. We can all agree or disagree on how things should be, but that doesn't help me right now. How do I get around this, is my question.

I am stuck with a table that has an autonumber PK, because it has no good candidate key fields or field combinations. I need to convert it to a
number so that I can change the field value when necessary. Once I do
change the field type, how do I manage the PK field values for new records if Access won't let me: (1) use SQL in the default value, (2) use a UDF in the default value, or (3) reference another field in the default value?

I abhor the idea of doing this in the front-end mdb.

[Tony Toews]

ci***********@h otmail.com (Lee C.) wrote:

What I am trying to do is have the capability of changing a record's
primary key value and have it cascade updated throughout related
tables.
But why?
This is an existing database, which I didn't design: Requisitions have
line items. Line items can be reassigned to other requisitions. All
I want is to be able to change the ReqID (currently an autonumber) of
an existing line item and have Access cascade update the related
tables.
But this sounds like ReqID is a foreign key and not the primary key. Surely the
ReqID is a primary key in the Requesition Header table. But it would be a foreign
key in the Line Item table.
This really doesn't seem like to much to ask from a version 10 RDBMS.
Presently, the line item is copied to a temp table, and then VBA is
used to delete the record from tblLineItems, then VBA copies the rec
from the temp table back to tblLineItems with the new ReqID, and
deletes the rec from the temp table. There are so many related tables
that this nightmare is cumbersome to manage in VBA. Hence, my idea to
have the Relation Database MANAGEMENT System do some managing.

Just curious. What kind of related tables do you have from the Line Items table?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Lee Cichanowicz]

> >What I am trying to do is have the capability of changing a record's

primary key value and have it cascade updated throughout related
tables.
But why?

As it says below: Line items can be reassigned to other requisitions. All
I want is to be able to change the ReqID (currently an autonumber) of
an existing line item and have Access cascade update the related
tables.

This is an existing database, which I didn't design: Requisitions have
line items. Line items can be reassigned to other requisitions. All
I want is to be able to change the ReqID (currently an autonumber) of
an existing line item and have Access cascade update the related
tables. But this sounds like ReqID is a foreign key and not the primary key. Surely the ReqID is a primary key in the Requesition Header table. But it would be a foreign key in the Line Item table.

Yep. Precisely.
This really doesn't seem like to much to ask from a version 10 RDBMS.
Presently, the line item is copied to a temp table, and then VBA is
used to delete the record from tblLineItems, then VBA copies the rec
from the temp table back to tblLineItems with the new ReqID, and
deletes the rec from the temp table. There are so many related tables
that this nightmare is cumbersome to manage in VBA. Hence, my idea to
have the Relation Database MANAGEMENT System do some managing.

Just curious. What kind of related tables do you have from the Line Items

table?

tblRequisitions --> tblLineItems --> tblLineItemFund ing -->
tblUnitAcceptan ce ...

[Mike Storr]

On 7 Feb 2004 14:55:45 -0800, Lee C. wrote:

I'm finding this to be extremely difficult to set up. I understand
that Access won't manage the primary key and the cascade updates for a
table. Fine. I tried changing the PK type to number and setting
default value to a UDF that manages the auto-numbering. Access won't
take a UDF as a default value. Okay, I'll use SQL WITHOUT any
aggregate functions, for the default value. Access won't do that
either. Okay, I create a second column, make it autonumber and PK,
then set the default value of my old PK column to the field value of
the new autonumber PK field. Wow! Access won't do that either. Am I
crazy or should this be easier? Any insight will be greatly
appreciated.

MS Access 2002
Windows XP Pro

Thanks,
Lee

I think the only work around for this is to change the data type on the
field from AutoNumber to Long. You'll need to create you own function to
generate new numbers, but your update queries should then be capable of
changing your IDs

--
Mike Storr
veraccess.com