Hello everyone, long time lurker, first time poster!
I am re-building a database due to the fact that I took a few shortcuts around normalization the first go around. I've just about gotten my database and FE application going, but have run into a problem that I can't seem to get around.
I have a Form (Vancomycin_monitoring). The form has the main demographic information in two tables, Patient_Demographics (PD) and Vanco_Demographics (VD). The PK for PD is Patient_ID, which is the foreign key for VD.
When entering data on this form I get an error message, "You must enter a value in the 'Vanco_Demographics.Patient ID' field." Pretty easy to figure out what's going on here, so I built an append query to add the Patient ID from the PD table into the VD table after it is input. Problem solved....if only...:banghead:
This works, as long as I close the form, then re-open it to the same record. I am then able to update the rest of the information on the form as expected. I've built a pop-up message into the form advising the user to do this, but obviously that is not a user friendly way to do this. I know there is probably an easy solution, but I've worn out my internets trying to find a way! I ran through about a billion different macros to get this to work (okay, more like 30), but have totally struck out!
Thank you in advance for any advice you can provide,
Kyle
I am re-building a database due to the fact that I took a few shortcuts around normalization the first go around. I've just about gotten my database and FE application going, but have run into a problem that I can't seem to get around.
I have a Form (Vancomycin_monitoring). The form has the main demographic information in two tables, Patient_Demographics (PD) and Vanco_Demographics (VD). The PK for PD is Patient_ID, which is the foreign key for VD.
When entering data on this form I get an error message, "You must enter a value in the 'Vanco_Demographics.Patient ID' field." Pretty easy to figure out what's going on here, so I built an append query to add the Patient ID from the PD table into the VD table after it is input. Problem solved....if only...:banghead:
This works, as long as I close the form, then re-open it to the same record. I am then able to update the rest of the information on the form as expected. I've built a pop-up message into the form advising the user to do this, but obviously that is not a user friendly way to do this. I know there is probably an easy solution, but I've worn out my internets trying to find a way! I ran through about a billion different macros to get this to work (okay, more like 30), but have totally struck out!
Thank you in advance for any advice you can provide,
Kyle