data has disappeared

[Knowlton]

I have a database for tracking state miles for IFTA. To avoid errors, I have a lookup table of bordering states to the state presently in. For state line crossings, I have a combo box that limits the choices to the states that border the current state. I have been using this for a couple years with no problem but in the last couple months have experience issues where a border state is no longer in the table and therefore not an available choice although it has been numerous times. The first few times I just added it back to the table and went on but it is continuing to happen so I'm trying to find out what's going on. It is not the same state each time.
Thanks in advance for your help!

[jforbes]

In the past, when I've run into this situation there is a Form based on the table with the disappearing records that allows deletes. A user comes along and clicks the Red-X to exit the Form but the Red-X is really to delete the record. Usually this is an honest mistake and removing the ability to delete records from the Form stops the problem.

[Knowlton]

I thought of that possibility. The form is a details form in continuous view. Since the records must be entered in order there are times when a record needs to be deleted if they are out of order on the trip sheet and it's not caught in advance. I entered a dummy trip and then deleted a couple state line records to check this but they weren't deleted from the lookup table. I suppose it's possible that could happen at times. The lookup table is only for the available choices. The data is saved in the details table.
Thanks!

[zmbd]

Have you made any modifications to the working database?

Which version of Access

The two controls, do these two controls share any records between them for the control-source property?

Would you mind listing the table names and any relationships to each other.

If there are relationships, do you have cascade-delete enabled? (I never enable cascade-delete, all too often vital data has been deleted by the uncaring/knowing user!)


On a side note, any reason you have to enter the events in order? The reason I ask is that I have a database that tracks historical events for instrumentation and I often enter legacy events as I find them and it is the date/time field that handles the ordering.

[Knowlton]

I have not made any changes to this part of the DB. I also track maintenance and have been working on that some but different forms and tables.

I am using Access within Office 365.

There is only 1 control, a combo named State whose control source is State which is a field in the tblTripDetails which is the record source for the form.

The row source for the combo is:

SELECT tlkpBorderStates.BorderState
FROM tlkpBorderStates
WHERE (((tlkpBorderStates.State)="varState"))
ORDER BY tlkpBorderStates.BorderState;

I also have a table tlkpStates which I just realized that I probably no longer need. It was the original states table for the purpose of verifying proper abbreviations. I later added the tlkpBorderStates to limit the choices to actual bordering states to be sure there were no state line crossings missed.

There is a one to many relationship between the tlkpStates and tblTripDetails. Enforce referential integrity, cascade update and delete.

There is a one to many relationship between the tlkpStates and tlkpBorderStetes. Enforce referential integrity, cascade update and delete.

I'm beginning to think I should get rid of the tlkpStates table and have no relationship between tblBorderStates and tblTripDetails since the state is stored in tblTripDetails and tlkpBorderStates only serves to assure accurate data entry.

The events do have to be entered in order since I'm also tracking loaded and empty miles and there are calculations and load status changes going on in the background as data is entered.

I know it's a bit long but I wanted to try to fully explain.
Thanks!

[zmbd]

I would remove the cascade delete option in the table relationships. I've had this given me problems with "missing" data in the related tables used for row_sources. This may not be the solution to your particular situation; however, it shouldn't hurt.

You'd be surprised how much damage that cascade delete can cause to the data records.

Before you go removing the tables have a read thru:
home > topics > microsoft access / vba > insights > database normalization and table structures

[jforbes]

I would also agree that the Cascade Delete is the most likely suspect. They need to be handled with care. Not only that, Cascading Deletes don't generally fit in with mostly static lists like States, so you probably will never need it. It's not like States destroyed very often. =)

Personally, I would remove the Cascading Update as well, but that would just be a personal preference, and I could see why you would want to keep it.