I am trying to create a unique ID to join two tables for that will allow for cascading changes. The unique ID will take the FIN, Antibiotic, Frequency, Date Ordered, Time Ordered and put it into a string for an id. Or is there a better way to do this? TblPatientAdmissions can have a person (FIN) multiple times, because a person can have more than one anitibiotic order. The unique part would be putting together the FIN/Anitibotic/Frequency/D
ed (I orginally tried to make all of those fields primary since all the fields together would be unique but you could have a line with the sameFIN but different antibiotic. I hope this makes sense, because I need to link this unique field into the tblDose. I am not sure how to make that unique field autogenerate into the tblDose.
when database opens:
Main menu- click on Data entry
Search- use drop down on MRN field and choose the test > Click Find
View Patient Encounters- There is a button for new admission but no admission has been entered in so there will not be an option to double clik on doses.
This just gives you an idea of database, but it is the tables behind the scenes that I am trying to create the unique record.
The form that would create this is patient admission which in the database you can just go straight to the form or test it by clicking on the "new antibiotic" button.
Private Sub cmdAdd_Click()
On Error GoTo Err_Add
Dim rst As DAO.Recordset
Dim strOpenToPatient As String
Dim uniqueID As String 'hold the uniqueID for the record
Const cPatientForm = "frmViewPatientHistory"
strOpenToPatient = "MRN=""" & MRN & """"
'set uniqueID to the generated uniqueID
uniqueID = generateID(Antibotic.Value, Frequency.Value, Month(DateOrdered.Value), day(DateOrdered.Value), year(DateOrdered.Value), FIN.Value, AdmitDate.Value)
'set the recordset to the admission table
Set rst = CurrentDb.OpenRecordset("tblPatientAdmissions", dbOpenDynaset)
'loop through each of the admission and add them to the recordset
With rst '
.AddNew 'open the addnew event to add the record details
![Assessment_ID] = uniqueID
![FIN] = FIN.Value
![Antibiotic] = Antibiotic.Value
![Frequency] = Frequency.Value
![Date_Ordered] = DateOrdered.Value
![Time_Ordered] = TimeOrdered.Value
![AdmitDate] = AdmitDate.Value
![DischargeDate] = DischargeDate.Value
.Update 'update the record in the table
If PatientFieldsFilledIn() Then
MsgBox "Patient Changes Successfully Saved"