Cascading Update Problem

[DarcKnyt]

Hello, everyone!

I have an Access 2002 DB. My question involves table relationships and cascading update.

Here's the set-up I have:

I have three tables in use, and their structure is described below:

tblDevicesVC
ID (PK, AutoNumber)
LocationID
RoomID
(lots of other fields)

tblLocation
ID (PK, AutoNumber)
Region
Office
Address1
Address2
City
St
Zip
Phone

tblRoom
ID (PK, AutoNumber)
LocationID
Floor/Room
Seats
ReservationContact
TechnicalContact
RoomPhone
SupportVendor
SuppExpiration

I have the relationships established as shown in the attached screenshot.

Here's the issue:

I have a query that pulls all the records necessary to populate forms for editing existing data (no problem) and inputting a new record (a similar form opened in DATA ENTRY mode). When a new record is created, a location is selected from a drop-down list populated with the values from the tblLocation table captured by the query. This works fine; all the relevant location data (address, phone number, etc.) is automatically populated on the form correctly.

Once the Location is chosen a record is added to the Devices table by updating the field LocationID (FK) in the Devices table with the ID of the Location table. This works correctly.

The data for the Room table comes next, but a new record in the Room table isn't created until the Floor/Room field is manually input on the form. That is to say, the LocationID field (FK) in the Room table is NOT updated by the Location table ID field, as it is for the Devices table.



The relationships between tables allow the Location table ID field to update to the Devices table field LocationID (FK). However, the Room table LocationID (FK) field, which has the EXACT SAME properties (long integer type, no decimal places, no default value in both tables, indexed with dupes OK), does NOT get updated when a new record is created.

I'm completely stumped. The relationships between the Room Table and Location table is EXACTLY the same as the relationship between the Location and Devices tables. The update works going from Location to Devices, but does NOT work going from Location to Room. I have no idea why.

I've tried juggling the relationshihps around in the relationships window, but all I manage to do there is stop my query from permitting addition of records. I also tried to manipulate the MASSIVE query I use to gather all the requisite information into one place, and I either end up with too many records or not enough. When it's the right amount, and I'm able to do additions, the updating as listed above does not function.

It's interesting to note that when I delete the test records from my query, the records ARE removed from the Devices table, and are NOT removed from the Room table. So, cascading delete doesn't work either.

If I've not provided enough information, please let me know. Any insights are greatly appreciated. I apologize for the long post, but I wanted to be sure there was enough information for all you to help. I probably still missed something, though.

Thanks in advance, everyone.

-JDT-

PS - If I don't have an image uploaded to the web, can I embed it into a post directly from my computer? (Sorry, just wondering.)

 

[KeithG]

What part of IL are you from? I am in Springfield. There is no need for the LocationID in the Device table. Because a location is associate to a Room not an device. A device must be in a room. I would delete the relationship between the Device and Location Table. Are you using subforms for your data entry?

 

[KeithG]

Also I would take out the RoomID from the Device table and create another table to associate a Device to a room. This table would contain the RoomID and DeviceID.

 

[Pat Hartman]

Start by renaming the "ID" fields so that they are meaningful. They should be LocationID and RoomID so that there is no confusion when joining the tables. Then remove the special character from Floor/Room.

As Keith said, Remove LocationID from the Devices table. But you only need a junction table if you need to keep a history of when a device was in a particular room.

Cascade delete only deletes rows from the lowest level "child" table in the query. It never removes rows from more than one table. I also don't believe that you should have cascade delete turned on for ANY of the relationships in the diagram. Cascade delete is only used to ensure that there will be no orphan "children". For example, if you delete an order, it makes no sense to keep the order details so you would specify cascade delete on that relationship. However, just because you delete a device, doesn't mean that you would also want to delete the room it is in or vice versa.

 

[DarcKnyt]

What part of IL are you from? I am in Springfield.

I'm working up in Chicago, but live WAAAYYY north of there. Ugh.

There is no need for the LocationID in the Device table. Because a location is associate to a Room not an device. A device must be in a room. I would delete the relationship between the Device and Location Table.

Okay, but my thinking here was there could be many devices in a given location. I may need to pull the device and it's location information for reporting later on. Is this going to present a problem?

Are you using subforms for your data entry?

I'm trying to. However, when I do that, I have a really difficult time making the subforms and the main form synch. Once I get it to work, the main form doesn't accept input in it's fields anymore. I have no clue why, but I can address that later.

Just FYI, I did the things you suggested, and tried to implement what Pat said too. What happens now is the LocationID field in the Room table is updated, but the RoomID field of the DevicesVC table is NOT. Same situation, just a different combination. What the heck am I doing wrong??

Thanks for helping, Keith. I'm halfway there, but suspect I'll be back with more questions about why my query-based form doesn't let me enter data in it once I get the subform coordinated with the main form. *Sigh*

-JDT-

 

[DarcKnyt]

Start by renaming the "ID" fields so that they are meaningful. They should be LocationID and RoomID so that there is no confusion when joining the tables. Then remove the special character from Floor/Room.

Done! I actually named them DevID, RmID and LocID. I used the full names in the FK fields of the child tables.

As Keith said, Remove LocationID from the Devices table. But you only need a junction table if you need to keep a history of when a device was in a particular room.

Done. So, I removed it, but then I added it to the Room table? Did I understand this correctly? The Room table LocationID field is now getting updated by the DB, but the DeviceVC table (new name) RoomID field is NOT being updated. Any clues, 'cause I'm all kindsa stuck on this one.

Cascade delete only deletes rows from the lowest level "child" table in the query. It never removes rows from more than one table. I also don't believe that you should have cascade delete turned on for ANY of the relationships in the diagram. Cascade delete is only used to ensure that there will be no orphan "children". For example, if you delete an order, it makes no sense to keep the order details so you would specify cascade delete on that relationship. However, just because you delete a device, doesn't mean that you would also want to delete the room it is in or vice versa.

Okay, I see your point, and I've removed it. So deleting a device doesn't remove the room information along with it, but what about deleting a location? (It's feasible.) If that happens, don't I want EVERYTHING -- the device details and the details from EVERY ROOM in that location -- removed? Shouldn't I have cascading deletions in that relationship (Location - Room)? Just curious.

As noted to Keith above, I've had no success with both tables being updated automatically and still don't know why. I've included another shot of my relationships for perusal.

Thank you for your help, Pat. I appreciate it!

-JDT-

 

[Pat Hartman]

I wouldn't use cascade delete at all in your case. It is too easy to blow away a lot of data with it and you don't really have a hierarchial relationship. If you select to enforce RI (which should always be selected by the way) but not cascade delete, you would need to delete the related objects (or reassign them) before you deleted the location.

 

[DarcKnyt]

Thanks!

Thanks, guys. I appreciate the help. The new relationships between the tables seem to be facilitating the things I want to do, and I guess I'll just have to deal with the upating of ONE of the fields in another way. I've populated a combobox with the records from a table based on the selection of another combobox, and can store the value in the appropriate field.

I hope.



Thanks again!

 

[Pat Hartman]

You shouldn't need to "cascade" ANYTHING but the linking key field. Are you duplicating a non-key field?

 

[DarcKnyt]

No sir, I'm just trying to get the key field from one table to go into another through cascading update. It works for one, but not for the other. I assumed the explanation was the level of cascading Access will do, though. If this is incorrect, any assistance in getting this to work is greatly appreciated. This is the synopsis: The Location table key field is being updated into the Room FK field. The Room ID field, however, is NOT being updated into the FK field of the Devices table. Per the recommendation given prior, I am NOT linking the Location table to the Devices table. (I also discussed this with my boss who agrees it's not necessary. He also pointed out that we can use a combobox to select the appropriate Room ID on a form and have that information stored in the Devices table, which solves the problem. Unless I hear otherwise, I'll go with this method for now.) Thanks again for responding, and taking time on your weekend to do so.

 

[Pat Hartman]

Cascade update does not create new records or add data to new records. Cascade update only propagates key value changes to existing records. So if your PK value was AX347 and you changed it to AW347 all related child records would be updated with the new key value.

To get Access to automatically populate a foreign key field, the child record MUST be added via a subform of the parent and the master/child links must be properly set.

If you use popup forms to add child records, you will need to populate the foreign key for new records with VBA code. Do this in the BeforeInsert event so you won't dirty the record before the user does.

 

[DarcKnyt]

Outstanding. Thank you so much for that information. I'll look into the code needed to do this. I find it interesting, however, that the database DOES take the LocID field from the Location field and plug it into the RoomLocID field (FK) of the related table Room. It doesn't go on to add the RoomID field to the DevVCLocID (FK) field of the Devices table, though. If this isn't happening with cascading update, and from your explanation it's not, I'm not sure how/why it IS happening. It's a convenience, though, and I was hoping to replicate it in the subsequent tables. But VBA sounds fine. I'll put the code in the BeforeInsert event as you suggest, and once again, thank you so much for the information and guidance, and doubly so on a Sunday. Much, much appreciated.

 

[Pat Hartman]

If the FK is being automatically filled, it is because the FK is in a subform and the master/child links are properly set. It really isn't magic

In the BeforeInsert event of your popup form you need something like:

Me.YourFKname = Forms!frmYourOtherForm!YourPKname

 

[DarcKnyt]

If the FK is being automatically filled, it is because the FK is in a subform and the master/child links are properly set. It really isn't magic

Well ... no, it wasn't. I haven't built the form/subform yet, because I keep getting confused about which fields to link. The forms I have are all sort of pop-ups, but I can't figure out how to pass one record from form to form so that the various tables all have the new record updated/created and linked together.

I'm trying to do this now, but it's FAILING miserably. I'm really not sure if this should be done with the Access front end or if I should be researching VBA to make this happen. It's been a lot harder than I thought.

Not that you haven't helped, of course, you have, very much! I appreciate it!

In the BeforeInsert event of your popup form you need something like:

Me.YourFKname = Forms!frmYourOtherForm!YourPKname

Okay ... this is where it gets ugly. There are THREE forms, and thus, THREE PKs and THREE FKs.

Form one is the LocRoomInfo form. This contains information from the Location table and the FK from the Room table to link them together. The location is chosen from an unbound combobox (which is filled with the Location table locations and PK) and the rest of the location information is filled in. This choice also populates a second combobox which holds the Room information for all the rooms where devices reside IN THAT LOCATION ONLY. Once THIS choice is made, I need this form (LocRoomInfo) to launch a second form (RoomDevInfo) with the SAME record -- which will have the same LocID (Location table PK), RoomLocID (Room table FK to Location table) and RmID (Room table PK). Once the room details are filled out (thus populating the record for the room table), the second form (RoomDevID) will need to open the THIRD form (DevInfo), which will have the name of the Room, the LocID, the RmID and the RoomLocID fields all from the PREVIOUS forms. The entry to this form will populate the associated record in the DevicesVC table.

WHEW! That's a lot! Sorry. I think my monster has mutated. It's not really a question of updates anymore, it's really one of passing one record from one form to another to a third now.

Thanks again Pat. I'd love to hear more about how to use a form/subform and linking them together. No matter what I seem to try with this, they don't work correctly. The subform shows all the records in the query (52 right now) instead of just the ONE, OR, it shows NOTHING at all.

Take care and thanks for your help once again!

-JDT-

 

[Pat Hartman]

When you open each new form, use the where argument to control the records that the opening form will show.

 

[neileg]

Just a heads up. There is a subform wizard that will automatically synchronise the PK/FK fields between the form and the subform. If you build your subform and then insert it into the main form, the wizard should do the parent/child fields for you. For this to work efficiently, the names of the PK and FK should be indentical and the relationship defined in the relationships diagram.

I'll restate more clearly something that Pat has implied. Tables and relationships do not propogate the PK/FK values. You must use a form/subform for this unless you want to hack it in VBA code.

 

[DarcKnyt]

Just a heads up. There is a subform wizard that will automatically synchronise the PK/FK fields between the form and the subform. If you build your subform and then insert it into the main form, the wizard should do the parent/child fields for you. For this to work efficiently, the names of the PK and FK should be indentical and the relationship defined in the relationships diagram.

I'll restate more clearly something that Pat has implied. Tables and relationships do not propogate the PK/FK values. You must use a form/subform for this unless you want to hack it in VBA code.

Thanks, Neil. I guess I just can't figure out how the FK is being populated from the PK to the FK if it's not being propogated by Access.

Thanks for the information, I really appreciate.

 

[DarcKnyt]

When you open each new form, use the where argument to control the records that the opening form will show.

Pat -- I'll give it a try. It's not been working too well so far, but I started over with a new, clean DB, set up the relationships, and built some forms. So far, so good.

I really appreciate the help you've been providing.

 

[neileg]

Thanks, Neil. I guess I just can't figure out how the FK is being populated from the PK to the FK if it's not being propogated by Access.

Thanks for the information, I really appreciate.

It is propogated by Access providing you use a form/subform setup. Tables are more or less static and nothings happens in them by default (validation is about the only exception).

 

[DarcKnyt]

Neileg, I'm so sorry. I haven't signed on in some time. Thank you very much for your help with this. I'm still confused about how the PK is being put into the FK, because it happened BEFORE I constructed any forms, but I'll take the word of the experts and not plan on it.

Pat -- Thank you too! I really appreciate the input and guidance.