updating foreign key value of another table

[Han]

I have two tables, A and B.

The two tables are related with a "case_num" column.

Each table has a form counterpart. form A and form B. Form A is a subform of
form B. Subform A contains a text box of "case_num" which is updatable.

Question. I need to update the corresponding "case_num" record in table B
when the value in "case_num" text box of subform A is changed.

What's the most efficient way to do this? Creating a sub-procedure
containing an UPDATE query using the "on change" event is one way. Is there
another?

Thanks.

 

[Jeff Boyce]

Han

You don't explicitly state what the relationship is between TableA and
TableB. I'm inferring, from which table belongs to which form/subform, that
TableB is the "parent" (one) table, and TableA is the "child" (many)
table -- why else would the form based on TableA be a "subform of formB?!

If your subformA has an updateable [CaseNumber] field, you may be going at
it backwards. If TableB (and FormB) is the parent, you don't WANT to be
able to change a child's "parent" ID.

Or have I misunderstood?

Jeff Boyce
<Access MVP>

 

[Han]

Jeff,

I simply need to keep the values of the two relative fields the same.

Any suggestions?

 

[Scott McDaniel]

I believe Jeff was trying to make the point that your data scheme may be
flawed ... a child record should not update or change the foreign key value
of it's parent ... there would be no way to guarantee data integrity.

However, if you insist, then just write a simple Update procedure in the
AfterUpdate event of your textbox in the subform. You are almost certain to
hose the data by doing this, but it's your data!!

CurrentProject.Connection.Execute "UPDATE TableA SET case_num = '" &
Me.NameOfYourTextbox & "' WHERE NameOfIDField="
Forms!NameOfYourParentForm.NameOfIDField


--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

Jeff,

I simply need to keep the values of the two relative fields the same.

Any suggestions?

Han

You don't explicitly state what the relationship is between TableA and
TableB. I'm inferring, from which table belongs to which form/subform, that
TableB is the "parent" (one) table, and TableA is the "child" (many)
table -- why else would the form based on TableA be a "subform of formB?!

If your subformA has an updateable [CaseNumber] field, you may be going at
it backwards. If TableB (and FormB) is the parent, you don't WANT to be
able to change a child's "parent" ID.

Or have I misunderstood?

Jeff Boyce
<Access MVP>

 

[Jeff Boyce]

Han

Scott has correctly seen through the subtlety of my reply -- I was
suggesting that your data design is flawed, and doing what you asked will,
as Scott points out, HOSE YOUR DATA! Doing what you asked will cause child
records to become "orphaned" from their parent.

Reconsider your data design.

Good luck

Jeff Boyce
<Access MVP>

 

[Han]

Scott & Jeff,

Thank you for your replies.

I understand fundamentally what you both are saying. Perhaps my original
example wasn't the greatest, but in reality the integrity of the data is
fine. In terms of database schema, the parent is updating the child. The
flaw in my design is possibly with the forms, not the table relationships
themselves. At the form level, the parent form is a subform of the child.

In any event, I was interested in learning the proper and most efficient
syntax to update the child foreign key value.

I sincerely appreciate your help.

Thanks.

I believe Jeff was trying to make the point that your data scheme may be
flawed ... a child record should not update or change the foreign key value
of it's parent ... there would be no way to guarantee data integrity.

However, if you insist, then just write a simple Update procedure in the
AfterUpdate event of your textbox in the subform. You are almost certain to
hose the data by doing this, but it's your data!!

CurrentProject.Connection.Execute "UPDATE TableA SET case_num = '" &
Me.NameOfYourTextbox & "' WHERE NameOfIDField="
Forms!NameOfYourParentForm.NameOfIDField


--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

Jeff,

I simply need to keep the values of the two relative fields the same.

Any suggestions?

Han

You don't explicitly state what the relationship is between TableA and
TableB. I'm inferring, from which table belongs to which

form/subform,
that

TableB is the "parent" (one) table, and TableA is the "child" (many)
table -- why else would the form based on TableA be a "subform of formB?!

If your subformA has an updateable [CaseNumber] field, you may be

going

at

 

[Scott McDaniel]

I believe you said that you have a form/subform design ... if your parent
form (the "one" side of the relationship) is correctly linked to your child
form (the "many" of your relationship), then Access will handle the udpate
for you.

--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

Scott & Jeff,

Thank you for your replies.

I understand fundamentally what you both are saying. Perhaps my original
example wasn't the greatest, but in reality the integrity of the data is
fine. In terms of database schema, the parent is updating the child. The
flaw in my design is possibly with the forms, not the table relationships
themselves. At the form level, the parent form is a subform of the child.

In any event, I was interested in learning the proper and most efficient
syntax to update the child foreign key value.

I sincerely appreciate your help.

Thanks.

I believe Jeff was trying to make the point that your data scheme may be
flawed ... a child record should not update or change the foreign key value
of it's parent ... there would be no way to guarantee data integrity.

However, if you insist, then just write a simple Update procedure in the
AfterUpdate event of your textbox in the subform. You are almost certain to
hose the data by doing this, but it's your data!!

CurrentProject.Connection.Execute "UPDATE TableA SET case_num = '" &
Me.NameOfYourTextbox & "' WHERE NameOfIDField="
Forms!NameOfYourParentForm.NameOfIDField


--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

Jeff,

I simply need to keep the values of the two relative fields the same.

Any suggestions?

Han

You don't explicitly state what the relationship is between TableA and
TableB. I'm inferring, from which table belongs to which form/subform,
that
TableB is the "parent" (one) table, and TableA is the "child" (many)
table -- why else would the form based on TableA be a "subform of formB?!

If your subformA has an updateable [CaseNumber] field, you may be

going

at
it backwards. If TableB (and FormB) is the parent, you don't WANT

to

be

 

[Han]

I thought this was the case, but it doesn't appear to be working. I will
recheck this.

Incidentally, these record numbers are NOT primary key integers, but are
text.

Thanks.

I believe you said that you have a form/subform design ... if your parent
form (the "one" side of the relationship) is correctly linked to your child
form (the "many" of your relationship), then Access will handle the udpate
for you.

--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

Scott & Jeff,

Thank you for your replies.

I understand fundamentally what you both are saying. Perhaps my original
example wasn't the greatest, but in reality the integrity of the data is
fine. In terms of database schema, the parent is updating the child. The
flaw in my design is possibly with the forms, not the table relationships
themselves. At the form level, the parent form is a subform of the child.

In any event, I was interested in learning the proper and most efficient
syntax to update the child foreign key value.

I sincerely appreciate your help.

Thanks.

I believe Jeff was trying to make the point that your data scheme may be
flawed ... a child record should not update or change the foreign key value
of it's parent ... there would be no way to guarantee data integrity.

However, if you insist, then just write a simple Update procedure in the
AfterUpdate event of your textbox in the subform. You are almost

certain
to

hose the data by doing this, but it's your data!!

CurrentProject.Connection.Execute "UPDATE TableA SET case_num = '" &
Me.NameOfYourTextbox & "' WHERE NameOfIDField="
Forms!NameOfYourParentForm.NameOfIDField


--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

Jeff,

I simply need to keep the values of the two relative fields the same.

Any suggestions?

Han

You don't explicitly state what the relationship is between TableA and
TableB. I'm inferring, from which table belongs to which form/subform,
that
TableB is the "parent" (one) table, and TableA is the "child" (many)
table -- why else would the form based on TableA be a "subform of
formB?!

If your subformA has an updateable [CaseNumber] field, you may be going
at
it backwards. If TableB (and FormB) is the parent, you don't WANT

to

be
able to change a child's "parent" ID.

Or have I misunderstood?

Jeff Boyce
<Access MVP>

 

[Han]

Ok, I changed my form structure so the record number is in the parent form.
Now, the update happens automatically without all the procedure nonsense.

Thanks!

I believe you said that you have a form/subform design ... if your parent
form (the "one" side of the relationship) is correctly linked to your child
form (the "many" of your relationship), then Access will handle the udpate
for you.

--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

Scott & Jeff,

Thank you for your replies.

I understand fundamentally what you both are saying. Perhaps my original
example wasn't the greatest, but in reality the integrity of the data is
fine. In terms of database schema, the parent is updating the child. The
flaw in my design is possibly with the forms, not the table relationships
themselves. At the form level, the parent form is a subform of the child.

In any event, I was interested in learning the proper and most efficient
syntax to update the child foreign key value.

I sincerely appreciate your help.

Thanks.

I believe Jeff was trying to make the point that your data scheme may be
flawed ... a child record should not update or change the foreign key value
of it's parent ... there would be no way to guarantee data integrity.

However, if you insist, then just write a simple Update procedure in the
AfterUpdate event of your textbox in the subform. You are almost

certain
to

hose the data by doing this, but it's your data!!

CurrentProject.Connection.Execute "UPDATE TableA SET case_num = '" &
Me.NameOfYourTextbox & "' WHERE NameOfIDField="
Forms!NameOfYourParentForm.NameOfIDField


--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

Jeff,

I simply need to keep the values of the two relative fields the same.

Any suggestions?

Han

You don't explicitly state what the relationship is between TableA and
TableB. I'm inferring, from which table belongs to which form/subform,
that
TableB is the "parent" (one) table, and TableA is the "child" (many)
table -- why else would the form based on TableA be a "subform of
formB?!

If your subformA has an updateable [CaseNumber] field, you may be going
at
it backwards. If TableB (and FormB) is the parent, you don't WANT

to

be
able to change a child's "parent" ID.

Or have I misunderstood?

Jeff Boyce
<Access MVP>

 

[Han]

I spoke too soon.

I switched my forms around and now the relationship is correct. That is, the
parent form will update the child form. The forms are related using the
previously defined case number.

Unfortunately this did not solve the problem. When the case number is
changed in the parent form, all the data in the child form disappears. When
the case number is changed back to its original value, all the data in the
subform reappears.

The link relationship is obviously working because initially all the data in
both forms is correct. This tells me the automatic update is not working the
way it's suppose to when the case number value is changed.

Any suggestions on how to remedy this problem?

Thanks in advance.

I believe you said that you have a form/subform design ... if your parent
form (the "one" side of the relationship) is correctly linked to your child
form (the "many" of your relationship), then Access will handle the udpate
for you.

--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

Scott & Jeff,

Thank you for your replies.

I understand fundamentally what you both are saying. Perhaps my original
example wasn't the greatest, but in reality the integrity of the data is
fine. In terms of database schema, the parent is updating the child. The
flaw in my design is possibly with the forms, not the table relationships
themselves. At the form level, the parent form is a subform of the child.

In any event, I was interested in learning the proper and most efficient
syntax to update the child foreign key value.

I sincerely appreciate your help.

Thanks.

I believe Jeff was trying to make the point that your data scheme may be
flawed ... a child record should not update or change the foreign key value
of it's parent ... there would be no way to guarantee data integrity.

However, if you insist, then just write a simple Update procedure in the
AfterUpdate event of your textbox in the subform. You are almost

certain
to

hose the data by doing this, but it's your data!!

CurrentProject.Connection.Execute "UPDATE TableA SET case_num = '" &
Me.NameOfYourTextbox & "' WHERE NameOfIDField="
Forms!NameOfYourParentForm.NameOfIDField


--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

Jeff,

I simply need to keep the values of the two relative fields the same.

Any suggestions?

Han

You don't explicitly state what the relationship is between TableA and
TableB. I'm inferring, from which table belongs to which form/subform,
that
TableB is the "parent" (one) table, and TableA is the "child" (many)
table -- why else would the form based on TableA be a "subform of
formB?!

If your subformA has an updateable [CaseNumber] field, you may be going
at
it backwards. If TableB (and FormB) is the parent, you don't WANT

to

be
able to change a child's "parent" ID.

Or have I misunderstood?

Jeff Boyce
<Access MVP>

 

[Jeff Boyce]

Sorry to butt back in, but I guess I need to ask why you would need to
change a case number? Once a case number's been assigned, and "child"
records created, what situation causes you to need to change the case
number?

Another approach would be to use a different primary key, so that you could
keep the parent and child records associated, and would only have to store
the case number in the parent record. An Autonumber data type primary key
(in the parent table) comes to mind. If you do this, your child table will
need a Long Integer type field for the foreign key.

I suspect your forms are working correctly -- when you add a new case
number in the main form, you should have NO related records in the subform.
That sounds like what you're seeing.

Good luck

Jeff Boyce
<Access MVP>

 

[Han]

Jeff, you are absolutely correct--using a primary key to relate the two
tables is much more efficient.

The parent table already has a primary key so making the switch will be
easy. I simply need to add the parent table primary key to the child table
and remove the case number.

I now realize why relating the two tables with a dynamic field is a bad
idea.

Thanks very much for your input!