So I just learned about referential integrity and I got it working on my database. I deleted all my "test records" and did a compact and repair to reset the primary key back to 1 and now it's acting all strange and adding random values to the table.
Background:
I have a pretty complex database with multiple tables but there are two tables that I'm having a problem with.
Table 1: Incident - stores values dealing with a specific fire department call
table 2: incident_sub - stores values of what apparatus was taken out, the times logged for that apparatus (when it left the station, arrived to scene, etc...) and the people on board
Table 1 and table 2 primary key is just an autonumber
Table 2 has a field called [incident_num] that gets the value of the incident key
You may have multiple records in table 2 that relate to a single call in table 1 (more than 1 vehicle went on a call)
I wanted to make it so if you delete the call, it deletes all the records associated with that call in table 2 so I created a referential integrity relationship with cascade delete and update.
It was working great until i removed all the records in both tables, and did a compact and repair. Now when i open the form to enter the values (table 2 is a subform), it automatically populates with random data in the personnel field.
I tried to remove the relationship and re-establish it and it errors out now saying that it cannot create the relationship and enforce referential integrity. Both tables are empty and it worked before I deleted the records and did a compact and repair.
Lastly i'm typing this on my break at work so I didn't have time to re-read it to make sure everything makes complete sense. If i need to clarify anything, please let me know.
I'm really confused where it's populating these random values from. It's different each time. It's almost like it still remembers the records that were in the tables before I deleted them and did the compact and repair and now it's "remembering" the values and putting them back into the new records.
Any help or direction would be great!
Thanks!!
Background:
I have a pretty complex database with multiple tables but there are two tables that I'm having a problem with.
Table 1: Incident - stores values dealing with a specific fire department call
table 2: incident_sub - stores values of what apparatus was taken out, the times logged for that apparatus (when it left the station, arrived to scene, etc...) and the people on board
Table 1 and table 2 primary key is just an autonumber
Table 2 has a field called [incident_num] that gets the value of the incident key
You may have multiple records in table 2 that relate to a single call in table 1 (more than 1 vehicle went on a call)
I wanted to make it so if you delete the call, it deletes all the records associated with that call in table 2 so I created a referential integrity relationship with cascade delete and update.
It was working great until i removed all the records in both tables, and did a compact and repair. Now when i open the form to enter the values (table 2 is a subform), it automatically populates with random data in the personnel field.
I tried to remove the relationship and re-establish it and it errors out now saying that it cannot create the relationship and enforce referential integrity. Both tables are empty and it worked before I deleted the records and did a compact and repair.
Lastly i'm typing this on my break at work so I didn't have time to re-read it to make sure everything makes complete sense. If i need to clarify anything, please let me know.
I'm really confused where it's populating these random values from. It's different each time. It's almost like it still remembers the records that were in the tables before I deleted them and did the compact and repair and now it's "remembering" the values and putting them back into the new records.
Any help or direction would be great!
Thanks!!