Maintaining Data Integrity

**jlt199** · 02-27-2014, 02:20 PM

I'm going around and around in circles of ever increasing frustration

I have a form, on my form I have a combobox which contains the IDs of all the patients in my database. The form is for editing existing records and adding new records every time a patient fills in a form. It is possible for the patient to exist in the database, but have no form entries. It is also possible that the patient may not be in the database at all.

I would like to use the combobox purely for searching records, not for overwriting existing data. The process I am going for is

If the patient does not exist in the database ask user (via a dialog box) if they would like to add them. If yes, open the form for adding new patients. Set ID value in main form back to a value that exists.
If a patient exists but does yet have any records associated to them inform the user (via dialog box) and ask them if them would like to add a first record for this patient. If yes, go to new record and auto-fill ID box with the user entered ID. Otherwise, revert back to ID (and record) shown before user updated value.
If patient exists and has records, use a filter to find them all.

My current problem is with item 2. I can go to a new record and auto-fill the ID number. I enable all the other controls on the form so that the user can set their values for this record. However, I can't seem to be able to save this new record correctly though. When the user updates the value in the ID box again, to search and filter for the next patient, the new record is saved with this ID and not the ID intended.

The ID combo box should never allow existing data to be overwritten.

Can anyone help me with this please?

**jlt199** · 02-27-2014, 02:25 PM

Also the record I was looking previously to entering a patient ID that didn't yet have a record associated to it, and triggering event 2 above, gets overwritten with the new ID. This should not happen

**orange** · 02-27-2014, 02:39 PM

What is the rowsource of the combo?
What is the form for?
You should be able to query the database and determine if a patient exists or not.
If not, then new.
If already exists, then what? Why would a patient be in db but not on form? It's your program, please tell us about the database and the form.

**jlt199** · 02-27-2014, 02:52 PM

My rowsource for the combobox comes from a Table called "Patients" that contains all of the patient info, name, gender etc.

I have found a way to query the database to determine a) does the patient exist in the "Patient" table and b) does that patient have any existing records in the "PatientRecords" table.

If a) is false then I trigger the user to open the "AddPatient" form. If b) is false then I goto a new record in the current form (attached to the "PatientRecords" table) and insert the ID they gave in the ID box. If both queries are true then I search for the relevant records.

My problem is that in the process of going to a new record in the case of b) being false, I overwrite the ID of the previous record I was looking with this new ID. I also can't seem to save this new record with the intended ID. It always gets overwritten when I try and search for the next patient.

Can I prevent a combobox from overwriting data in existing records? Can I force the form to save a record when I want it to and not when it thinks I should?

**jlt199** · 02-27-2014, 03:11 PM

After several hours I think I may have the answer. Before doing anything in the "AfterUpdate" event I now save the entered ID into a LocalVar and UNDO the record before going down any of the update routes. This seems to work, but also seems clunky. Am I missing something? Thanks

**orange** · 02-27-2014, 03:15 PM

Try the before update event . Do the checking etc and you can cancel if needed.

I don't understand why there is an issue with overwriting the ID. Can you describe the set up? And show us (jpg) your table designs and relationships?