Solved

Access 2003- Need to create Unique ID to join two tables

Posted on 2009-07-14
7
387 Views
Last Modified: 2013-11-29
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/DateOrdered/TimeOrdered (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

        End With

        

            If PatientFieldsFilledIn() Then

                

        DoCmd.RunCommand acCmdSaveRecord

           

        MsgBox "Patient Changes Successfully Saved"

        

        DoCmd.Close

Open in new window

PD.zip
0
Comment
Question by:Namrac00
  • 4
  • 3
7 Comments
 
LVL 27

Assisted Solution

by:MikeToole
MikeToole earned 500 total points
ID: 24860441
It seems to me that you have three categories of data

   Patient - PK: FIN
   Admission - PK: AdmissionID (autonumber), FK: PIN
   Treatment -  PK: AntibioticID, FK: AdmissionID

So there should be three tables.
The Admission table could have a PK of (FIN, Admission date/time) but these would then need to be included as a Foreigh Key in the Treatment table, it's simpler to have an Autonumber-generated
ID
   
0
 

Author Comment

by:Namrac00
ID: 24860512
I was trying to do a autogenerator but I do not know how to have that same autonumber also update to the otherform so that I can link them
0
 
LVL 27

Assisted Solution

by:MikeToole
MikeToole earned 500 total points
ID: 24860976
I would use standard Access form/subform support to add the new details

Main form: Patient
Sub form: Admission
Nested subform: Treatment

That way Access automatically assigns the AdmissionID whenever you add a row to the Treatment subform.

Another option would be to have the Admissions as a subform of the Patient form, then have a button to open an Admissions form for the selected admission and have the Treatments as a subform on that. Same result, just different visual style.

0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:Namrac00
ID: 24861766
In my database, I have a form called viewPatientHistory that shows the patient info, then it has a button to add new admissions and then there is a subform on that page that has a query of the admission info and you double click on the doses to add a dose.
0
 
LVL 27

Assisted Solution

by:MikeToole
MikeToole earned 500 total points
ID: 24862088
Taking your 'doses' to be the same as 'Treatment' then if double-clicking on the doses navigates to a new record on the does subform, it should automatically inherit the AdmissionID (or your equivalent) from the parent form - Problem Solved!
0
 

Author Comment

by:Namrac00
ID: 24863228
no my subform is connecting the patient to the admission
0
 

Accepted Solution

by:
Namrac00 earned 0 total points
ID: 24865322
I was able to pull the uniqueID into the form as an open arg and then save it to the table using

assessment = txtassessment
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now