Lets say I have two linked (SQL Server) tables, one for medical cases, one for patients. The med_cases table has case_no, intake_date, patient_id, etc etc etc. The patients table has patient_id, patient_fname, patient_lname, etc etc etc. There is a foreign key relationship between MED_CASES.PATIENT_ID and PATIENTS.PATIENT_ID where (obviously) patients is the parent table.
Ok so now I have an Access form for each of those two tables. In order to pick a case, I have a combo box on the MED_CASES forms that is populated by a query which has patient first and last name, case number and intake date. So obviously this query is linking the MED_CASES table and the PATIENTS table in order to return the patient's name rather than just the ID. Also on the MED_CASES form there is a command button called "Edit Patient" which will open the patient form so I can add address information, correct a misspelling, etc.
Here's the problem: Because the combo box on the MED_CASES form is still open, it is locking the PATIENTS table so I cannot update my patient data (it comes back with a "cannot update a linked table" error after about 90 seconds. Sometimes it also says timeout expired sometimes it does not). The ugly way around this is to scroll all the way to the bottom of the combo box, returning all rows and therefore releasing all locks. But asking users to do this is a little silly.
So, is there any way (besides denormalizing the data and placing the patient name field in the MED_CASES table) in Access to keep the data unlocked or have it ignore the locks?
Thanks!
Ok so now I have an Access form for each of those two tables. In order to pick a case, I have a combo box on the MED_CASES forms that is populated by a query which has patient first and last name, case number and intake date. So obviously this query is linking the MED_CASES table and the PATIENTS table in order to return the patient's name rather than just the ID. Also on the MED_CASES form there is a command button called "Edit Patient" which will open the patient form so I can add address information, correct a misspelling, etc.
Here's the problem: Because the combo box on the MED_CASES form is still open, it is locking the PATIENTS table so I cannot update my patient data (it comes back with a "cannot update a linked table" error after about 90 seconds. Sometimes it also says timeout expired sometimes it does not). The ugly way around this is to scroll all the way to the bottom of the combo box, returning all rows and therefore releasing all locks. But asking users to do this is a little silly.
So, is there any way (besides denormalizing the data and placing the patient name field in the MED_CASES table) in Access to keep the data unlocked or have it ignore the locks?
Thanks!
