SubTables

[Emma]

I have a table TBLClient and it has a subdatasheet based on a table called
TblCaseNote Unfortunately when I delete a Client from TBLClient the subtable
doesn't delete the corresponding CaseNote. I tried changing the relationship,
and adding a new row called NewID but it is still doing the same thing. Any
ideas?

 

[Emma]

I guess what I'm asking is how do I delete the corresponding CaseNote as well?

 

[John W. Vinson]

I have a table TBLClient and it has a subdatasheet based on a table called
TblCaseNote Unfortunately when I delete a Client from TBLClient the subtable
doesn't delete the corresponding CaseNote. I tried changing the relationship,
and adding a new row called NewID but it is still doing the same thing. Any
ideas?

First off... subdatasheets are a MAJOR drag on performance, are never
necessary, and should be avoided. You should *not* be looking at table
datasheets in any case - Forms with Subforms are a much more powerful and
flexible option!

That said, open the Relationships window (it looks like three little
datasheets with lines between them). The subdatasheet misfeature will have
created a relationship between TBLClient and TBLCaseNote. You may need to
select View... Show All Relationships to see it.

Click on the join line between these two tables and view the relationship's
Properties. Check the "Cascade Deletes" check box. This will cause all case
notes for a client to be permanently and irrevokably destroyed, losing all
your history of the case forever, if you delete a client - if that's what you
want to happen, then go for it!

You might instead want to consider adding a yes/no field Active to the Clients
table, defaulting to True; set it to False to take a client off the active
list, and base your forms and reports on a query selecting only active
clients.

 

[Emma]

There is a major problem with my relationship, because I have ID #
(Autonumber)and Client ID (Number) in TBLClient. I had joined the two Client
ID's but they're not unique so I can't Enforce Referential Integrity to
Cascade Delete Related Records. How do I make a NewID in the Case Note Table
to relate to the ID #? Sorry if this makes it more confusing.

 

[Emma]

I only want the Admin to be able to delete, so this function won't be used
much, and not at all by regular users. On my next database I will avoid using
sub datasheets, this is my first time, so I'm really happy about how it's
progressing, even though it's not perfect or optimal.

 

[John W. Vinson]

There is a major problem with my relationship, because I have ID #
(Autonumber)and Client ID (Number) in TBLClient.

Which - if either - is the Primary Key?

I had joined the two Client
ID's but they're not unique so I can't Enforce Referential Integrity to
Cascade Delete Related Records.

So... you have more than one client each with the same ID!? What is the point
of that? Who is Client 312 if there are three records in the table all with
ClientID 312?

What two tables did you join on ClientID? What is the datatype of ClientID in
each table: Number/Long Integer? Can you view the properties of your
subdatasheet and determine which fields are the master/child link field?

How do I make a NewID in the Case Note Table
to relate to the ID #? Sorry if this makes it more confusing.

You CAN'T, if you already have existing notes; and you needn't, if the
subdatasheet is in fact showing the correct notes for each client. You HAVE a
relationship - Access creates one for you automatically when you create a
subdatasheet. We just need to figure out how to fix it.

 

[Emma]

Hi John, The ID # is the primary key. Client ID is the year, month and a
unique number. Actually I was wrong Client ID only repeats itself if I delete
a record then add a new record right away. So maybe we should be discussing
how to stop the client ID from repeating? Since Client ID isn't the primary
key I can't Enforce Referential Integrity.

I want Client ID to only appear once in the TBLClient and several times in
the TblCaseNotes, as it is a 1 to many realtionship and there can be many
casenotes on one client.

 

[John W. Vinson]

Hi John, The ID # is the primary key. Client ID is the year, month and a
unique number. Actually I was wrong Client ID only repeats itself if I delete
a record then add a new record right away. So maybe we should be discussing
how to stop the client ID from repeating? Since Client ID isn't the primary
key I can't Enforce Referential Integrity.

I want Client ID to only appear once in the TBLClient and several times in
the TblCaseNotes, as it is a 1 to many realtionship and there can be many
casenotes on one client.

Then what I don't understand is why you have an autonumber primary key *that
you're not using*. If the ClientID is unique (and yes, it can easily be made
unique) then it should be the PK.

However, storing data - a date and a sequential number - jammed into one field
is Bad Design. Your inability to delete and add a record is a case in point.
How is the "unique number" being assigned? If it's unique, how is it
duplicating?

Note that you CAN enforce RI even from a field that is not the PK if you add a
unique Index to it.