Modification of Relationships

[Guest]

I have a form that records work orders for maintenance performed for
customers on vehicles. When i was first given the project I was told that
there would be no need for duplicate vehicle numbers (Unit Numbers) and so I
used this for the key in my tblUnit table. I have now been made aware now
that the customer has been added to the mix. That there may be duplicate Unit
Numbers under different customers. When I modify my tblUnit Table to include
an autonumber as a key my form becomes uneditable.. and furthermore I can not
add new records to the form.

I can however, add new records to the tblWorkOrder table in datasheet view.
Is it possible to complete this relationship transition, have everything
work as it did and not have to rebuild the form?

thanks in advance

Pip'n

 

[Roger Carlson]

I'm assuming your form is based on a Join of tblUnit and tblWorkOrder and
the tables were Joined on the field Unit Numbers which was the primary key
for tblUnit. Correct? A query with a Join will only be updateable if there
is a Unique Index on the Join field on the One side of the relationship (in
this case tblUnit). Once you remove that unique index (by making the
autonumber field the primary key), then you no longer have an updateable
recordset.

Now, you CAN fix this. What you have to do is add a new field to
tblWorkOrder that is of type Long Integer, that will store the value of your
primary key in tblUnit. Then create your relationship on that. Here's the
steps:

1) Make UnitNumbers the primary key of tblUnit again (just temporarily)
2) I assume you already have an autonumber field in tblUnit -- if not create
it -- I'll call it UnitID since you didn't name it.
3) Create a field in tblWorkOrder that is type Long Integer and call it
UnitID as well
4) Make an Update query that Joins the two tables and writes the value of
UnitID from tblUnit into UnitID of tblWorkOrder
ex. UPDATE tblUnit INNER JOIN tblWorkOrder
ON tblUnit.[Unit Numbers] = tblWorkOrder.[Unit Numbers]
SET tblWorkOrder.UnitID = [tblUnit].[UnitID];
5) Make UnitID the primary key field of tblUnit
6) Delete the relationship based on Unit Numbers and base it on UnitID
7) Modify any and all queries that join these two tables and use UnitID as
the join field.

That's about it.
--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Roger Carlson]

Minor modification: Step 5 should go inbetween the two halves of step 6, so
it would be:

5) Delete the relationship based on Unit Numbers
6) Make UnitID the primary key field of tblUnit
7) Recreate relationship based on UnitID
8) Modify any and all queries that join these two tables and use UnitID as
the join field.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I'm assuming your form is based on a Join of tblUnit and tblWorkOrder and
the tables were Joined on the field Unit Numbers which was the primary key
for tblUnit. Correct? A query with a Join will only be updateable if there
is a Unique Index on the Join field on the One side of the relationship (in
this case tblUnit). Once you remove that unique index (by making the
autonumber field the primary key), then you no longer have an updateable
recordset.

Now, you CAN fix this. What you have to do is add a new field to
tblWorkOrder that is of type Long Integer, that will store the value of your
primary key in tblUnit. Then create your relationship on that. Here's the
steps:

1) Make UnitNumbers the primary key of tblUnit again (just temporarily)
2) I assume you already have an autonumber field in tblUnit -- if not create
it -- I'll call it UnitID since you didn't name it.
3) Create a field in tblWorkOrder that is type Long Integer and call it
UnitID as well
4) Make an Update query that Joins the two tables and writes the value of
UnitID from tblUnit into UnitID of tblWorkOrder
ex. UPDATE tblUnit INNER JOIN tblWorkOrder
ON tblUnit.[Unit Numbers] = tblWorkOrder.[Unit Numbers]
SET tblWorkOrder.UnitID = [tblUnit].[UnitID];
5) Make UnitID the primary key field of tblUnit
6) Delete the relationship based on Unit Numbers and base it on UnitID
7) Modify any and all queries that join these two tables and use UnitID as
the join field.

That's about it.
--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have a form that records work orders for maintenance performed for
customers on vehicles. When i was first given the project I was told that
there would be no need for duplicate vehicle numbers (Unit Numbers) and

so

I
used this for the key in my tblUnit table. I have now been made aware now
that the customer has been added to the mix. That there may be duplicate Unit
Numbers under different customers. When I modify my tblUnit Table to include
an autonumber as a key my form becomes uneditable.. and furthermore I

can

not
add new records to the form.

I can however, add new records to the tblWorkOrder table in datasheet view.
Is it possible to complete this relationship transition, have everything
work as it did and not have to rebuild the form?

thanks in advance

Pip'n

 

[Guest]

Thanks for your help so far Roger..

I am now able to create the duplicate Unit Numbers and edit existing records
in the form however i can't create new records.

Here's what you guys usually mention to checik on the form... and I believe
I did everything in the order that was mentioned from your previous posts..

Allow Edits: Yes
Allow Deletions: Yes
Allow Additions: Yes
Data Entry: No

Thanks in Advance,

Pip'n

Minor modification: Step 5 should go inbetween the two halves of step 6, so
it would be:

5) Delete the relationship based on Unit Numbers
6) Make UnitID the primary key field of tblUnit
7) Recreate relationship based on UnitID
8) Modify any and all queries that join these two tables and use UnitID as
the join field.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I'm assuming your form is based on a Join of tblUnit and tblWorkOrder and
the tables were Joined on the field Unit Numbers which was the primary key
for tblUnit. Correct? A query with a Join will only be updateable if there
is a Unique Index on the Join field on the One side of the relationship (in
this case tblUnit). Once you remove that unique index (by making the
autonumber field the primary key), then you no longer have an updateable
recordset.

Now, you CAN fix this. What you have to do is add a new field to
tblWorkOrder that is of type Long Integer, that will store the value of your
primary key in tblUnit. Then create your relationship on that. Here's the
steps:

1) Make UnitNumbers the primary key of tblUnit again (just temporarily)
2) I assume you already have an autonumber field in tblUnit -- if not create
it -- I'll call it UnitID since you didn't name it.
3) Create a field in tblWorkOrder that is type Long Integer and call it
UnitID as well
4) Make an Update query that Joins the two tables and writes the value of
UnitID from tblUnit into UnitID of tblWorkOrder
ex. UPDATE tblUnit INNER JOIN tblWorkOrder
ON tblUnit.[Unit Numbers] = tblWorkOrder.[Unit Numbers]
SET tblWorkOrder.UnitID = [tblUnit].[UnitID];
5) Make UnitID the primary key field of tblUnit
6) Delete the relationship based on Unit Numbers and base it on UnitID
7) Modify any and all queries that join these two tables and use UnitID as
the join field.

That's about it.
--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have a form that records work orders for maintenance performed for
customers on vehicles. When i was first given the project I was told that
there would be no need for duplicate vehicle numbers (Unit Numbers) and

so

I
used this for the key in my tblUnit table. I have now been made aware now
that the customer has been added to the mix. That there may be duplicate Unit
Numbers under different customers. When I modify my tblUnit Table to include
an autonumber as a key my form becomes uneditable.. and furthermore I

can

not
add new records to the form.

I can however, add new records to the tblWorkOrder table in datasheet view.
Is it possible to complete this relationship transition, have everything
work as it did and not have to rebuild the form?

thanks in advance

Pip'n

 

[Guest]

Just a quick correction...

The form allows the creation of a new record. However the controls can not
be edited in this new record. The controls can now be edited in the existing
records.

Thanks for your help so far Roger..
I am now able to create the duplicate Unit Numbers and edit existing records
in the form however i can't create new records.

Here's what you guys usually mention to checik on the form... and I believe
I did everything in the order that was mentioned from your previous posts..

Allow Edits: Yes
Allow Deletions: Yes
Allow Additions: Yes
Data Entry: No

Thanks in Advance,

Pip'n

Minor modification: Step 5 should go inbetween the two halves of step 6, so
it would be:

5) Delete the relationship based on Unit Numbers
6) Make UnitID the primary key field of tblUnit
7) Recreate relationship based on UnitID
8) Modify any and all queries that join these two tables and use UnitID as
the join field.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I'm assuming your form is based on a Join of tblUnit and tblWorkOrder and
the tables were Joined on the field Unit Numbers which was the primary key
for tblUnit. Correct? A query with a Join will only be updateable if there
is a Unique Index on the Join field on the One side of the relationship (in
this case tblUnit). Once you remove that unique index (by making the
autonumber field the primary key), then you no longer have an updateable
recordset.

Now, you CAN fix this. What you have to do is add a new field to
tblWorkOrder that is of type Long Integer, that will store the value of your
primary key in tblUnit. Then create your relationship on that. Here's the
steps:

1) Make UnitNumbers the primary key of tblUnit again (just temporarily)
2) I assume you already have an autonumber field in tblUnit -- if not create
it -- I'll call it UnitID since you didn't name it.
3) Create a field in tblWorkOrder that is type Long Integer and call it
UnitID as well
4) Make an Update query that Joins the two tables and writes the value of
UnitID from tblUnit into UnitID of tblWorkOrder
ex. UPDATE tblUnit INNER JOIN tblWorkOrder
ON tblUnit.[Unit Numbers] = tblWorkOrder.[Unit Numbers]
SET tblWorkOrder.UnitID = [tblUnit].[UnitID];
5) Make UnitID the primary key field of tblUnit
6) Delete the relationship based on Unit Numbers and base it on UnitID
7) Modify any and all queries that join these two tables and use UnitID as
the join field.

That's about it.
--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




I have a form that records work orders for maintenance performed for
customers on vehicles. When i was first given the project I was told that
there would be no need for duplicate vehicle numbers (Unit Numbers) and so
I
used this for the key in my tblUnit table. I have now been made aware now
that the customer has been added to the mix. That there may be duplicate
Unit
Numbers under different customers. When I modify my tblUnit Table to
include
an autonumber as a key my form becomes uneditable.. and furthermore I can
not
add new records to the form.

I can however, add new records to the tblWorkOrder table in datasheet
view.
Is it possible to complete this relationship transition, have everything
work as it did and not have to rebuild the form?

thanks in advance

Pip'n

 

[Roger Carlson]

Without knowing more about your form, it's impossible to say. However, make
sure you have a control bound to the foreign key (ie UnitID from
tblWorkOrder).
--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Just a quick correction...

The form allows the creation of a new record. However the controls can not
be edited in this new record. The controls can now be edited in the existing
records.

Thanks for your help so far Roger..
I am now able to create the duplicate Unit Numbers and edit existing records
in the form however i can't create new records.

Here's what you guys usually mention to checik on the form... and I believe
I did everything in the order that was mentioned from your previous posts..

Allow Edits: Yes
Allow Deletions: Yes
Allow Additions: Yes
Data Entry: No

Thanks in Advance,

Pip'n

Minor modification: Step 5 should go inbetween the two halves of step 6, so
it would be:

5) Delete the relationship based on Unit Numbers
6) Make UnitID the primary key field of tblUnit
7) Recreate relationship based on UnitID
8) Modify any and all queries that join these two tables and use UnitID as
the join field.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



I'm assuming your form is based on a Join of tblUnit and tblWorkOrder and
the tables were Joined on the field Unit Numbers which was the primary key
for tblUnit. Correct? A query with a Join will only be updateable if
there
is a Unique Index on the Join field on the One side of the relationship
(in
this case tblUnit). Once you remove that unique index (by making the
autonumber field the primary key), then you no longer have an updateable
recordset.

Now, you CAN fix this. What you have to do is add a new field to
tblWorkOrder that is of type Long Integer, that will store the value of
your
primary key in tblUnit. Then create your relationship on that. Here's
the
steps:

1) Make UnitNumbers the primary key of tblUnit again (just temporarily)
2) I assume you already have an autonumber field in tblUnit -- if not
create
it -- I'll call it UnitID since you didn't name it.
3) Create a field in tblWorkOrder that is type Long Integer and call it
UnitID as well
4) Make an Update query that Joins the two tables and writes the value of
UnitID from tblUnit into UnitID of tblWorkOrder
ex. UPDATE tblUnit INNER JOIN tblWorkOrder
ON tblUnit.[Unit Numbers] = tblWorkOrder.[Unit Numbers]
SET tblWorkOrder.UnitID = [tblUnit].[UnitID];
5) Make UnitID the primary key field of tblUnit
6) Delete the relationship based on Unit Numbers and base it on UnitID
7) Modify any and all queries that join these two tables and use UnitID as
the join field.

That's about it.
--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




I have a form that records work orders for maintenance performed for
customers on vehicles. When i was first given the project I was told
that
there would be no need for duplicate vehicle numbers (Unit Numbers) and
so
I
used this for the key in my tblUnit table. I have now been made aware
now
that the customer has been added to the mix. That there may be duplicate
Unit
Numbers under different customers. When I modify my tblUnit Table to
include
an autonumber as a key my form becomes uneditable.. and furthermore I
can
not
add new records to the form.

I can however, add new records to the tblWorkOrder table in datasheet
view.
Is it possible to complete this relationship transition, have everything
work as it did and not have to rebuild the form?

thanks in advance

Pip'n

 

[Guest]

Thanks a lot Roger...

That was what I was missing. I added UnitID to the Record Source of the form
and everything works perfectly.

Thanks again,
Pip'n