Prev: Pen Mode in Access 2007
Next: Can we put two or three sets of data into one combobox?
From: oldblindpew on 15 Feb 2010 09:54
I have a table with many existing records, and it uses an autonumber key. I
recently added another table for additional information that only applies to
some, not all, of the records in the first table. These tables have a
one-to-one relationship, and the second table's primary key is supposed to
match the first table's primary key.

I add records to the second table by selecting from the first table via a
combo box.

My question is: What could cause Access to delete the record from the first
table just because I delete the record in the second table?

Directional: I have heard the term "directional" applied to relationships,
but none of my reference books breathe a mumbling word on this topic.

Referential Integrity: At first I had NOT enforced referential integrity, so
in desperation I tried enforcing it, but it made no difference.

Thanks,
OldBlindPew
From: Jerry Whittle on 15 Feb 2010 11:22
Do you have the tables joined in the Relationships Window? If so do you have
Referential Integrity enabled? If that answer is Yes, look just below the RI
check box and there are Cascade Update and ((shudder)) Cascade Delete. If
Cascade Delete is enabled and you delete a record, all related records are
also toast.

However it seems by your description that Cascade Delete wasn't on as you
didn't have RI enabled at first.

Are you sure that the record is actually missing? If you are joining the two
tables in a query with an inner join, a record won't be returned unless there
is a record in both tables. Also you are using autonumbers. It's possible to
'burn' an autonumber very easily. If you are just seeing gaps in the
autonumber sequence, that means nothing.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"oldblindpew" wrote:

> I have a table with many existing records, and it uses an autonumber key. I
> recently added another table for additional information that only applies to
> some, not all, of the records in the first table. These tables have a
> one-to-one relationship, and the second table's primary key is supposed to
> match the first table's primary key.
>
> I add records to the second table by selecting from the first table via a
> combo box.
>
> My question is: What could cause Access to delete the record from the first
> table just because I delete the record in the second table?
>
> Directional: I have heard the term "directional" applied to relationships,
> but none of my reference books breathe a mumbling word on this topic.
>
> Referential Integrity: At first I had NOT enforced referential integrity, so
> in desperation I tried enforcing it, but it made no difference.
>
> Thanks,
> OldBlindPew
From: oldblindpew on 15 Feb 2010 12:53
Thanks, Jerry.

Per prior post, RI was not set at first.
I tried setting RI because I couldn't think of anything else to try.
Cascading updates or deletes have never been enabled.
The record in the main table definitely gets deleted. I have to manally
re-add it.
I have compacted and repaired.
This is a split database, but that shouldn't make any difference.

There is another wierd thing going on: when I try to add a new record to the
second table, using a subform, I get "Field Cannot Be Updated". When I click
Okay, then close and reopen the form, the new record is there.

At this point all I can think do is to scrap the two-table approach and just
put the additional fields in the first table.

Thanks,
Pew

"Jerry Whittle" wrote:

> Do you have the tables joined in the Relationships Window? If so do you have
> Referential Integrity enabled? If that answer is Yes, look just below the RI
> check box and there are Cascade Update and ((shudder)) Cascade Delete. If
> Cascade Delete is enabled and you delete a record, all related records are
> also toast.
>
> However it seems by your description that Cascade Delete wasn't on as you
> didn't have RI enabled at first.
>
> Are you sure that the record is actually missing? If you are joining the two
> tables in a query with an inner join, a record won't be returned unless there
> is a record in both tables. Also you are using autonumbers. It's possible to
> 'burn' an autonumber very easily. If you are just seeing gaps in the
> autonumber sequence, that means nothing.
> --
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
> "oldblindpew" wrote:
>
> > I have a table with many existing records, and it uses an autonumber key. I
> > recently added another table for additional information that only applies to
> > some, not all, of the records in the first table. These tables have a
> > one-to-one relationship, and the second table's primary key is supposed to
> > match the first table's primary key.
> >
> > I add records to the second table by selecting from the first table via a
> > combo box.
> >
> > My question is: What could cause Access to delete the record from the first
> > table just because I delete the record in the second table?
> >
> > Directional: I have heard the term "directional" applied to relationships,
> > but none of my reference books breathe a mumbling word on this topic.
> >
> > Referential Integrity: At first I had NOT enforced referential integrity, so
> > in desperation I tried enforcing it, but it made no difference.
> >
> > Thanks,
> > OldBlindPew
From: Daryl S on 15 Feb 2010 13:15
Pew -

One possibility - does the second form possibly have the bound key value
from the original table instead of the new table? Check the record source
for this form and make sure you don't have the incorrect field...

--
Daryl S


"oldblindpew" wrote:

> Thanks, Jerry.
>
> Per prior post, RI was not set at first.
> I tried setting RI because I couldn't think of anything else to try.
> Cascading updates or deletes have never been enabled.
> The record in the main table definitely gets deleted. I have to manally
> re-add it.
> I have compacted and repaired.
> This is a split database, but that shouldn't make any difference.
>
> There is another wierd thing going on: when I try to add a new record to the
> second table, using a subform, I get "Field Cannot Be Updated". When I click
> Okay, then close and reopen the form, the new record is there.
>
> At this point all I can think do is to scrap the two-table approach and just
> put the additional fields in the first table.
>
> Thanks,
> Pew
>
> "Jerry Whittle" wrote:
>
> > Do you have the tables joined in the Relationships Window? If so do you have
> > Referential Integrity enabled? If that answer is Yes, look just below the RI
> > check box and there are Cascade Update and ((shudder)) Cascade Delete. If
> > Cascade Delete is enabled and you delete a record, all related records are
> > also toast.
> >
> > However it seems by your description that Cascade Delete wasn't on as you
> > didn't have RI enabled at first.
> >
> > Are you sure that the record is actually missing? If you are joining the two
> > tables in a query with an inner join, a record won't be returned unless there
> > is a record in both tables. Also you are using autonumbers. It's possible to
> > 'burn' an autonumber very easily. If you are just seeing gaps in the
> > autonumber sequence, that means nothing.
> > --
> > Jerry Whittle, Microsoft Access MVP
> > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> >
> >
> > "oldblindpew" wrote:
> >
> > > I have a table with many existing records, and it uses an autonumber key. I
> > > recently added another table for additional information that only applies to
> > > some, not all, of the records in the first table. These tables have a
> > > one-to-one relationship, and the second table's primary key is supposed to
> > > match the first table's primary key.
> > >
> > > I add records to the second table by selecting from the first table via a
> > > combo box.
> > >
> > > My question is: What could cause Access to delete the record from the first
> > > table just because I delete the record in the second table?
> > >
> > > Directional: I have heard the term "directional" applied to relationships,
> > > but none of my reference books breathe a mumbling word on this topic.
> > >
> > > Referential Integrity: At first I had NOT enforced referential integrity, so
> > > in desperation I tried enforcing it, but it made no difference.
> > >
> > > Thanks,
> > > OldBlindPew
From: oldblindpew on 15 Feb 2010 15:13
Thanks Daryl S,

I must have some wires crossed here somehow. I'm trying to add records to
the second table by using a combo box to find the key value in the first
table and place it in the second table.

Meanwhile, in a backup copy of the database, I abandoned the second table
and brought its fields into the first table, with promising results. I'm not
all that pleased with this approach, but may have to settle for a crude
solution that works rather than an elegant one that doesn't.

When creating my row source in the query builder I apparently ran out of
space! I was forced to specify "all fields" in the first table rather than
just the ones I needed. I guess I ran into a limit on string length for the
query. It's not very many fields and it seems a shame to fetch the whole
table when just a portion would do. Is there a performance gain or loss at
stake? Would this argue for using shorter field names?

Thanks,
Pew

"Daryl S" wrote:

> Pew -
>
> One possibility - does the second form possibly have the bound key value
> from the original table instead of the new table? Check the record source
> for this form and make sure you don't have the incorrect field...
>
> --
> Daryl S
>
>
> "oldblindpew" wrote:
>
> > Thanks, Jerry.
> >
> > Per prior post, RI was not set at first.
> > I tried setting RI because I couldn't think of anything else to try.
> > Cascading updates or deletes have never been enabled.
> > The record in the main table definitely gets deleted. I have to manally
> > re-add it.
> > I have compacted and repaired.
> > This is a split database, but that shouldn't make any difference.
> >
> > There is another wierd thing going on: when I try to add a new record to the
> > second table, using a subform, I get "Field Cannot Be Updated". When I click
> > Okay, then close and reopen the form, the new record is there.
> >
> > At this point all I can think do is to scrap the two-table approach and just
> > put the additional fields in the first table.
> >
> > Thanks,
> > Pew
> >
> > "Jerry Whittle" wrote:
> >
> > > Do you have the tables joined in the Relationships Window? If so do you have
> > > Referential Integrity enabled? If that answer is Yes, look just below the RI
> > > check box and there are Cascade Update and ((shudder)) Cascade Delete. If
> > > Cascade Delete is enabled and you delete a record, all related records are
> > > also toast.
> > >
> > > However it seems by your description that Cascade Delete wasn't on as you
> > > didn't have RI enabled at first.
> > >
> > > Are you sure that the record is actually missing? If you are joining the two
> > > tables in a query with an inner join, a record won't be returned unless there
> > > is a record in both tables. Also you are using autonumbers. It's possible to
> > > 'burn' an autonumber very easily. If you are just seeing gaps in the
> > > autonumber sequence, that means nothing.
> > > --
> > > Jerry Whittle, Microsoft Access MVP
> > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > >
> > >
> > > "oldblindpew" wrote:
> > >
> > > > I have a table with many existing records, and it uses an autonumber key. I
> > > > recently added another table for additional information that only applies to
> > > > some, not all, of the records in the first table. These tables have a
> > > > one-to-one relationship, and the second table's primary key is supposed to
> > > > match the first table's primary key.
> > > >
> > > > I add records to the second table by selecting from the first table via a
> > > > combo box.
> > > >
> > > > My question is: What could cause Access to delete the record from the first
> > > > table just because I delete the record in the second table?
> > > >
> > > > Directional: I have heard the term "directional" applied to relationships,
> > > > but none of my reference books breathe a mumbling word on this topic.
> > > >
> > > > Referential Integrity: At first I had NOT enforced referential integrity, so
> > > > in desperation I tried enforcing it, but it made no difference.
> > > >
> > > > Thanks,
> > > > OldBlindPew
 |  Next  |  Last
Pages: 1 2
Prev: Pen Mode in Access 2007
Next: Can we put two or three sets of data into one combobox?