Loosing Data from tables (1 Viewer)

Dear friends, (HELP . . SOS),

The problem which is happening is totally paradoxical and I am really desperate because my database runs on a number of users and the problem is so paradox that I have reached the limits of despair. The problem is the following:

The database is for doctors usage and I admit that has a lot of complexity in some of procedures. However, I have done debug and compile to the code and everything looks fine (without any errors).The Back-end is 5MB and the Front-end 35MB for the moment.

The Database
There is a master table (tblPatients) with includes one Autonumber field and may other Text Fields. Also there is another field [PatientID] wich is number field. The PatientID field takes the ID using vba code (+1 to the last ID).

The tblPatients is related with several other tables and is the master table (One to Many). All the other related tables has also Autonumber field.

The problem
The problem is that sometimes (not each time) some records may not appears because they didn't stored for completely unexplained reason into the Child Tables!!

When does the problem happen?
I have a main form (Patients) with Record Source direct to tblPatients. Using a command button I open any other Child form (Popup) with Record Source direct to one of the related table (Many - Relationship)

When this related form is opened (Popup), goes directly to a new record and I can see the Autonumber field showing (New).

By entering new data into any other field I can see the Autonumber field displaying the new increment serial number automatically which means that the new record has already been entered in the table.

Keep in mind that, there is a data validation process when the SAVE command button selected. On this point I make it clear that I am 100% sure that the data has been saved.

But... sometimes (after many testings) the data not saved into the related table and are totally lost from the table.!!

Keep in mind that when the problem occurs in order to check what's going on, i tried the following:

1. I create new Backed / Frontend and test it again and works fine. But, after many times of testing again the problem persist without any reason..!! Then,

2. When the paradox problem comes again, in order to check if there is any problem with my office pack (ProPlus 2016 - 32Bit) i tried to remove the office and install it again. After the new installation of the office everything works OK until the next time .. !!

Can anyone give me some explanation?...

Is database split design? Each user runs their own copy of frontend?

Good evening June7,

Yes the database is split and each user has their own front end.

Also, I comment to Microsoft and I'm waiting for their reply..

Thanks

gstylianou said:

By entering new data into any other field I can see the Autonumber field displaying the new increment serial number automatically which means that the new record has already been entered in the table.

Click to expand...

Not quite. I use the Access default method mostly for bound forms. I start a new record, then realise for some reason I need to do something else first.

Pressing Esc key gets me out of committing to that record. The next time I enter a new record I get the same ID.?

Would have to see your code in the form(s) that do this. It could be that you are accidentally saving a blank record then NOT saving the update.

You are saying that you have a SAVE routine to verify that the data gets saved, it fires, but then you find that it has not been saved after all. Is that correct?

In this save routine, do you have an error handler? Do you have error notifications disabled? Do you have a logging table that records the fact of some action? How do you verify that the SAVE routine has done its job (i.e. what is being measured or tested to come to that conclusion?)

When does this happen? (This is a broad-scope question...) For instance, is it the last record a person saves before closing the session? Is it the second (or later) record saved by the same doctor or for the same patient or referring to the same procedure code?

I understand your concern and it is quite legitimate. However, we need a lot of information for a problem as elusive as this one. You will need patience and vigilance to find this. If you do NOT have a logging system, you might need to implement one to see what your users are doing at the time of the failure. At least, to see which forms and what milestones in processing are passing during these save operations. You can turn off the logging when you get it fixed, but I have found event-log style processing totally invaluable when tracing down nasty problems like this.

This may not be solvable without seeing a copy of the db because it seems to be hit and miss.

I can see the Autonumber field displaying the new increment serial number automatically which means that the new record has already been entered in the table.

Click to expand...

Not necessarily true. If you show record selectors and see pencil icon at left, this record is in edit or create mode and is not saved until committed. However, your process may be causing this creation to fail sometimes, either because of improper code, unbound forms or who knows what. The fact that you are opening another form to add related records may be the root cause. IMHO, too many variables to consider for what appears to be a random failure.

I realize posting a db copy would be an issue unless the data could be easily obscured, so if it comes down to that, I have code that would alter text values in any field it is run on in an update query on a random basis after the nth character that you specify. If need be, you could strip out a lot of records to reduce the count, or just randomize as many fields as are required to attain anonymity. The alterations cannot be reverse-engineered.

I suspect you would prefer to try to solve this via guesses from forum members, and who knows, you may succeed. Just letting you know of another option

Something to check. See if there are any orphan records in the related tables. Maybe the parent ID is not being stored in the related table.

from your description, tblPatients is your master table connecting to a number of child tables. However you appear to be saying this table has an autonumber field and a PatientID field based on a calculation along the lines of DMax+1 or DCount+1. why both?

You have not explained how you are relating your master table to these child tables - i.e. what is the primary key? the autonumber field or the patientID field? and what is the the foreign key in the child tables? and how is it populated?

You have described how you can see the autonumber field in the child tables being populated when a new child record is being created. You have not described whether you can see the foreign key field being populated.

it would be helpful if you can post an image of your relationships screen and clarify which child tables are not always getting populated.

When you use a subform and set the master/child links correctly, Access takes care of adding the foreign key so that the child records contain the PK of the parent record. When you use a popup form, YOU must write the code to do this yourself.

Also, if your validation code is not in the Form's BeforeUpdate event, you may not actually be properly controlling whether or not a record may be saved.

It may be helpful if you could post the actual database. Please compact and zip prior to posting. We need both the FE and the BE.

I think it would be helpful to get others here to look at the code/database execution. So a copy of FE and BE with some anonymized data would be a best case, if practical. A picture of your tables and relationships may be useful to readers.
As Doc mentioned, a logfile to record which events were entered and completed with relevant data values is a great debugging tool.
I often use a boolean variable eg ShowDebug that if true will log various activities, if false just bypass the Debug.Print or logging routine.
Sure sounds a very elusive situation.
Good luck.

Good morning,

Because I understand that the problem can be extremely complex or even rare, I want to make clear the following:

1. There is absolutely no problem with the way of the storing data as because its simply confirmation that the user has actually added the following: Patient Name - Contact telephones - Date of birth - Country of residence and the City. All of those information are added properly when the problem occurs.

2. The data storage command (SAVE) contains a simple vba verification code of the above, and if everything are correct then it also gives the new Patient ID and stores the record. Regarding the verification process i wanted to make it clear that the problem is not the way on how can i have the new Patient ID as well as if it gives it or not, in this case everything looks fine.!

Therefore, I would like to focus on the essence which is:
How its possible when the problem arises, then i must to remove the Office package and re-install it again in order to solve the problem? Please note that, after the new installation of the office package (ProPlus 32bit VL) the software works fine but after some data input (for testings) the problem comes again..absolutely the same..!!

I also want to make it clear that:
The application is split (backend / frondend) and runs into 1PC (one doctor). In a few cases I have more than one user in the same place (doctor / secretary)

In order to see what is happening, we have come to the point of doing the following:
Suspecting that there may be a problem after deleting the Office 365 package and may staying somewhere in the computer (eg. registry) some files, then we proceed to Removed the office package using the Microsoft Office Removal Tool, and then again new installation of MS Office 2016 Pro-Plus 32Bit VL .. !! Unfortunately all the above was without any result in order to solve the problem

Please,i would like to ask you to be focused on the essence of the problem and not on the way on how I give PatinetID etc which is basically not related.

Thanks once again for your time.

you haven't given us anything to go on except words and your assurances that your verification code works every time - except it doesn't.

it is very unusual that the temporary cure to the problem is to reinstall access/office. Invariably the issue is with table design/data/code. Particularly as you have identified re-installation is only a temporary fix.

It is more likely that you have a constraint issue in one or more tables - perhaps a data type, perhaps indexing, perhaps a relationship, all of which your verification code is not addressing or perhaps handling incorrectly.

You have not answered a single question asked (except split) but are convinced that it is the access installation is the problem - how have you come to that conclusion?

when you reinstall - is the BE cleared of data so you start again? If so, do you enter data in the same order or a random order? How many records are you talking about before the error occurs again? How does the record that does not get appended compare with other records that have been successfully appended? If not do you 'start where you left off' and try to insert the missing record first?

How are you appending? could the data contain illegal characters which causes the code to stop? (can happen when importing from other sources)

In terms of uninstalling/reinstalling you have done everything I would expect to do. You may just have discovered a bug that no-one else has encountered - windows/office updates sometimes send things sideways - and there has to be a first person. But others would follow so I would look on the MS forums for others experiencing the same issue.

gstylianou said:

..
By entering new data into any other field I can see the Autonumber field displaying the new increment serial number automatically which means that the new record has already been entered in the table.
..

Click to expand...

You are wrong about that, so ... some of your other statements could be wrong to.
Excuse it sounds negative, it's not really the meaning, it should only make you think about it an extra time, if there may be other places where you might be wrong.

JUB: That was covered in post 7. I don't think I have anything else to offer.

Keep in mind that, there is a data validation process when the SAVE command button selected. On this point I make it clear that I am 100% sure that the data has been saved.

Click to expand...

Validation on a button click event does not mean a record has been saved. What is the check that determines for 100% certainty that data has been saved - an examination of the table itself, or the fact that validation passed (which is not the way to make this conclusion)?


I wonder if this is all because warnings are turned off?