I have a database which records patient activity in a hospital. There is a table called SimActivityTbl which contains a record of each patient event. A patient can have several records in this table. The patient name is in a field PatientName and their Hospital ID # in a field PatientIDNumber. When new event is added for a patient, a combo box is used for patient selection. The control source for this is another table called PatientTbl which also contains the same field names as detailed above. In this way I can ensure a consistent entry of patient name and ID #. The problem is that the patient name or ID # may have been entered wrongly in the first place. What I want to do is to create a form that will allow me to search the SimActivityTbl for all records with the incorrect name or ID # and replace the entries with the correct information. What I was thinking of was a form that had a combo box to allow selection of the current name or ID # to correct and a text box in which to enter the correct name or ID #. What I am not clear about is how best to perform the required operation to correct the records. I have noticed the VBA 'Replace' function but am not sure if it is appropriate here and indeed how I would generate the code to make it work. I am more used to working in VBA than SQL but any help would be appreciated.
Sure, here's a gun. It's loaded. Aim it at your foot. The rest is up to you.
In other words, you need to go back to basics and get your table design right, not continue to patch a badly designed table structure. I refuse to help you dig yourself a deeper hole than the one you are already in.
Sorry to be so harsh, but sometimes, it's needed. Believe it or not I am actually trying to help ^^