midastouch1
New member
- Local time
- Today, 14:01
- Joined
- May 30, 2014
- Messages
- 6
So....new to Access here, but experienced in SQL. I work on a medical research team and have a database that contains a table for patient demographic info (name/age/SSN/race/etc) and a table for autopsy information (time/cause of death, etc). The PK for the demographic table is a random autonumber, and is the foreign key in the autopsy table. Some participants choose to participate in the "living" portion of the study only, some choose to be tissue donors only, and some choose both.
I created a form that contains demographic data on the top, and a subform on the bottom to capture autopsy data. If I can figure out how to clear the form after a record insert, this use of the form works perfectly to add a new patient that chose to donate tissue only, as the PK autopopulates in both the parent table (patient demographic) and the child table (autopsy information) as an FK. Any help on how to save the new record then clear the form in a single button click would be much appreciated. The hard problem arises when I need to add autopsy information for a patient that already has a demographic record (we already captured that data during the "living" study). I can't seem to figure out how to do a lookup within the form to check for an existing demographic record without immediately generating a new demographic record and PK. :banghead: Is there a way to insert a lookup function on say SSN to check for an existing record (and autopopulate the data into the top of the form if a record exists) without generating a new partial record as soon as I hit a keystroke in the demographic portion of the form?
I created a form that contains demographic data on the top, and a subform on the bottom to capture autopsy data. If I can figure out how to clear the form after a record insert, this use of the form works perfectly to add a new patient that chose to donate tissue only, as the PK autopopulates in both the parent table (patient demographic) and the child table (autopsy information) as an FK. Any help on how to save the new record then clear the form in a single button click would be much appreciated. The hard problem arises when I need to add autopsy information for a patient that already has a demographic record (we already captured that data during the "living" study). I can't seem to figure out how to do a lookup within the form to check for an existing demographic record without immediately generating a new demographic record and PK. :banghead: Is there a way to insert a lookup function on say SSN to check for an existing record (and autopopulate the data into the top of the form if a record exists) without generating a new partial record as soon as I hit a keystroke in the demographic portion of the form?
