need to update record with autonumber as primary key

[MES via AccessMonster.com]

I am trying to run an update query to update records in a table (from another
table). The primary key for the table is an autonumber, so it is not letting
me do so. The source of the update is another table (a temp table that I
created) whose primary key is the same as the table that I am trying to
update. I am not trying to change the primary key field - I only need to
update other fields in the record. How do I do this?

 

[Jeff Boyce]

I may not be understanding your description, but it sounds like you expect
an Autonumber field in table #2 to have exactly the same value(s) as the
Autonumber field in table #1. Won't happen!

Autonumber is independent. If you need to preserve the value in a table you
are appending to, use a long integer data type.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[MES via AccessMonster.com]

Thanks for your response.

No, table #2 is not an autonumber field, but table #1 is. I am trying to
update table #1 with the edited record from table #2. The field in my
example is 'Course_Num' . For example, Course_Num 1 has been edited in table
#2 (i.e. - name, description, etc.), and I am trying to use an update query
to update Course_Num 1 record in table #1 with the Course_Num 1 record in
table #2.

Is this do-able? Any suggestions on how I could make this happen otherwise?

I may not be understanding your description, but it sounds like you expect
an Autonumber field in table #2 to have exactly the same value(s) as the
Autonumber field in table #1. Won't happen!

Autonumber is independent. If you need to preserve the value in a table you
are appending to, use a long integer data type.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am trying to run an update query to update records in a table (from
another

[quoted text clipped - 4 lines]

update. I am not trying to change the primary key field - I only need to
update other fields in the record. How do I do this?

 

[Jeff Boyce]

I guess I'm still not tracking on this one.

How do you get Course_Num = 1 entered into your table#2 in the first place?
(I assume that =1 means that the Autonumber created in Table#1 was =1.)

What about the record in table#2 are you trying to update?

For that matter, why do you have the same course number in more than one
table?!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for your response.

No, table #2 is not an autonumber field, but table #1 is. I am trying to
update table #1 with the edited record from table #2. The field in my
example is 'Course_Num' . For example, Course_Num 1 has been edited in
table
#2 (i.e. - name, description, etc.), and I am trying to use an update
query
to update Course_Num 1 record in table #1 with the Course_Num 1 record in
table #2.

Is this do-able? Any suggestions on how I could make this happen
otherwise?

I may not be understanding your description, but it sounds like you expect
an Autonumber field in table #2 to have exactly the same value(s) as the
Autonumber field in table #1. Won't happen!

Autonumber is independent. If you need to preserve the value in a table
you
are appending to, use a long integer data type.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am trying to run an update query to update records in a table (from
another

[quoted text clipped - 4 lines]

update. I am not trying to change the primary key field - I only need
to
update other fields in the record. How do I do this?