Hello all, looking for some help from the pros on this one:
I have tblBooking with only a few fields: BookingID (autonumber, primary key), LName, FName, RecDate, StudyDone (Yes/No)
I made a simple form which allows our booking staff to enter the data each time they receive a requisition for a study to be performed. This is saved as a front end db on the booking staff computer.
I also have tblMain which contains LName, FName, RecDate, and StudyDone as well as other fields.
First I made qryBooking to filter out any records where StudyDone is "Yes".
Then I made frmMain, which will be used by the technologist who performs the study. I made LName a combo box which looks up data from qryBooking (therefore shows only those records where StudyDone is "No"). When the user selects the record, it automatically fills in LName, FName, and RecDate. I did this using the me.fieldname = combo.column(#) trick - works well. Now here's the part I need help on:
Once the record is selected (and therefore a study has been done), I'd like to have tblBooking.StudyDone checked "Yes". This serves 2 purposes. First, the user won't be able to select the same record twice. Secondly, I can query tblBooking periodically to see if there are any outstanding records which haven't been studied and may have fell through the cracks.
One problem I can foresee with a simplistic solution is if the frmMain user accidently selects the wrong record and then changes it to the correct one later. If the wrongly selected record had StudyDone checked as "Yes", then it should get reversed back to "No" when the error gets corrected.
I hope this makes sense. I realize that this db structure is not ideal but I've inherited the data from a Word table and have to make due with what I got.
Any suggestions?
I have tblBooking with only a few fields: BookingID (autonumber, primary key), LName, FName, RecDate, StudyDone (Yes/No)
I made a simple form which allows our booking staff to enter the data each time they receive a requisition for a study to be performed. This is saved as a front end db on the booking staff computer.
I also have tblMain which contains LName, FName, RecDate, and StudyDone as well as other fields.
First I made qryBooking to filter out any records where StudyDone is "Yes".
Then I made frmMain, which will be used by the technologist who performs the study. I made LName a combo box which looks up data from qryBooking (therefore shows only those records where StudyDone is "No"). When the user selects the record, it automatically fills in LName, FName, and RecDate. I did this using the me.fieldname = combo.column(#) trick - works well. Now here's the part I need help on:
Once the record is selected (and therefore a study has been done), I'd like to have tblBooking.StudyDone checked "Yes". This serves 2 purposes. First, the user won't be able to select the same record twice. Secondly, I can query tblBooking periodically to see if there are any outstanding records which haven't been studied and may have fell through the cracks.
One problem I can foresee with a simplistic solution is if the frmMain user accidently selects the wrong record and then changes it to the correct one later. If the wrongly selected record had StudyDone checked as "Yes", then it should get reversed back to "No" when the error gets corrected.
I hope this makes sense. I realize that this db structure is not ideal but I've inherited the data from a Word table and have to make due with what I got.
Any suggestions?
