Delete doesn't delete

[jpgoossen via AccessMonster.com]

Hi,

I have a table that combines the data of two underlying tables. The
underlying tables are EMPLOYEE and LOCATION. These are combined into a table
WORKPLACE. I have a form for entering new workplaces (through two combo boxes,
selecting records from EMPLOYEE and LOCATION on unique ID fields). I have a
separate form for changing the combination, based on selecting the name of
the employee and changing the associated location to create a new workplace.

What I cannot seem to be able to do, is delete a workplace combination. The
underlying record in WOKPLACE needs to be deleted in some cases, while the
EMPLOYEE and LOCATION tables and thus the associated records therein should
remain untouched. I tried a form based on a query WORKPLACE_PLUS that
combines the three tables so that the data professional can select on
employee name. A lot of employee and location details are of course stored in
the underlying tables EMPLOYEE and LOCATION, while only the unique ID's from
these tables are stored in the WORKPLACE table.

Placing a Delete button on the form, in Design View makes is easy, but
clicking it does NOT delete the record in WORKPLACE. Added Me.Requery and Me.
Form.Requery but that did not achieve anything either.

ANy Idea what I am doing wrong?

Thanks in advance,

Patrick

 

[Guest]

I almost made a mistake when reading your post. You aren't combining data in
the WORKPLACE table. Rather you are joining it there as WORKPLACE is a
linking or bridging table between the two.

To delete the link between the two tables, only have the WORKPLACE table as
record source of the form. If you include either of the other two tables as
part of the record source, you might have an unupdateable situation where
Access won't let you delete the records.

To see the data, such as a name, on the form, use unbound text boxes and
DLookup to show the data in them.

 

[jpgoossen via AccessMonster.com]

Jerry,

Sorry that it took me a while to respond, but wasn't able to get to a PC
where I could look at my program the last few days.

Anyway, I would say that the mistake is not yours, but mine by (almost) not
being able to explain what I meant. I guess being a relative newbie in this
does not help when explaining.

You were absolutely right in assuming that I meant joining (and adding some
extra data, of course) two tables into a new one.

I changed the form according to your suggestion and all is well in teh Work
sheet area again. Thanks a lot for your help.

Cheers,

Patrick

I almost made a mistake when reading your post. You aren't combining data in
the WORKPLACE table. Rather you are joining it there as WORKPLACE is a
linking or bridging table between the two.

To delete the link between the two tables, only have the WORKPLACE table as
record source of the form. If you include either of the other two tables as
part of the record source, you might have an unupdateable situation where
Access won't let you delete the records.

To see the data, such as a name, on the form, use unbound text boxes and
DLookup to show the data in them.

Hi,

[quoted text clipped - 23 lines]

Patrick

 

[jpgoossen via AccessMonster.com]

Chris,

Fortunately the problem has been solved by Jerry by reading between the lines
and figuring out what I was trying to do and what the possible mistakes were
that I could make. I had several source rows in teh form, which made
unambiguous deletion of data impossible. Thanks for your help, even if I did
not need it anymore.

The thinking along is very much appreciated!

Cheers,

Patrick