Unanswered: Deleting record in Form does not delete record from underlying table
Oooooo, I can't figure this out.
Here's the basics:
Access 2000 database, being worked on in Access 2002.
I have a Main Form (frmClientDataEntry) which contains a tab control.
The record source for the Main Form is a SQL statement left joining a primary table (tblClient) with a child table (tlbProgramData) on the appropriate key fields.
On two of the pages of the tab control are subforms with simple query recordsources left joining tblClient to tblPhone and to tblEmployment respectively.
In the relationships window all relationships are set to cascade update/delete.
If I delete a client record using the Main Form's select/delete ribbon, the record and its child records in the subforms *appear* to get deleted (and I get a warning that I am about to delete the record)...yet it is not: when I re-open the form, there it is. And when I open the underlying tables, the parent and child records are still there.
If I *update* an exisiting client record, or add a *new* client record using the form, all changes/additions to parent and child take affect and are not undone by closing/reopening the form.
If I delete a *child* record on one of tab control's Phone or Employment subforms, that takes affect and is not undone by closing/reopening the form.
It is only in attempting to delete a parent record using the form that I am encountering this problem.
Any theories out there?
There was a similar post relating to access 2007 and deletions from a subform not taking affect in this same way...but I've checked many of the settings suggested by users responding to that post and everything seems to be set correctly.
Why is the frmClientDataEntry form's record source linked to a child table? The child records should be displayed in your subform(s), so there should be no reason to link it. If you find you don't need that link, remove the child table from the underlying query and try it again.
Strange that you get no error message about the deletion not working though :/
Actually, now that I think about it, the relationship between tblClient and tblProgramData is 1:1---they are split into two tables for neatness sake because Program Data has a lot of fields, but there is only one set of these fields per client. So I guess it's not a parent/child relationship, but rather a co-parent relationship.
So maybe the way I've joined them in the form is the problem. I'll try a couple different things and post back.
So, I realized that in the relationships window the relationship between tblClient and tblProgramData was set as 1:8 instead of 1:1, because I'd set the keys incorrectly. I fixed that and re-established the relationship as 1:1, then re-set my form data source to be just tblClient.
What happened then is that if I was only able to enter some client data and not get to any of the program data fields for that client, then the whole record would disappear from the form the next time it was opened (it would still appear in the underlying table, though).
So, I changed the join in the form's data source to be a left join on tblClient and tblProgramData, and that problem was fixed----AND, the original delete problem was fixed, too! All parts of all records show up when I want them to, and all are deleted when deleted.
So, I think that's that...for now...unless I find I've inadvertently created additional problems that haven't shown up yet.