Results 1 to 8 of 8
  1. #1
    Join Date
    May 2012
    Posts
    64

    Unanswered: How to link associated fields on double click?

    I have a database tracking patient's procedures and diagnoses. The way my database is set up is every patient has a patient ID number and then each diagnosis has a patient ID and a diagnosis ID. Each diagnosis has a different table for it because if you had a stroke, then the details of that are completely different than if you had an aneurysm. So the title of the diagnosis is saved in a table with a diagnosis ID and patient ID. The stroke details are saved in another table with a diagnosis ID.

    My problem comes because two common diagnoses are aneurysms and AVMs. These will be entered separately, but each AVM could have an associated aneurysm. My AVM table has a column for aneurysm ID and when manually entered, the associated aneurysm will appear on the AVM form. However, I can't ask my user to remember an ID number of the aneurysm and then enter it into the AVM field. So I want to add a button to my AVM form that says add associated aneurysm which opens a continuous form that I want to show any of the aneurysms that have a diagnosis ID that matches any of the many diagnosis IDs that could be in the main form. Then the user needs to double click one of the aneurysms, the form will close, and the aneurysm ID from the record they chose will be saved in the correct field on the AVM form.

    So my question is, what should I put in the code on the double click event of the aneurysm form that both closes the form and saves the aneurysmID number in the AVM form in the associatedAneurysm field?

    Also, how do I filter the continuous aneurysm form to only show aneurysms from the current patient when my aneurysm table only has a diagnosis ID, not a patient ID. Each patient can have many diagosis IDs, all of which will be on the main form listed in a continuous form.

  2. #2
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Quote Originally Posted by klpe237 View Post
    The way my database is set up is every patient has a patient ID number and then each diagnosis has a patient ID and a diagnosis ID. Each diagnosis has a different table for it because if you had a stroke, then the details of that are completely different than if you had an aneurysm. So the title of the diagnosis is saved in a table with a diagnosis ID and patient ID. The stroke details are saved in another table with a diagnosis ID.
    How are you maintaining the relationships between these tables if you are trying to join a single field in your 'patient diagnosis' table with multiple diagnosis tables? You can't do this, a single field in your table can only join to one other table. You need to check the database design and find some commonality between all the diagnosis tables then create some kind of super table structure. You then join the patient with the top level diagnosis table.

    Quote Originally Posted by klpe237 View Post
    However, I can't ask my user to remember an ID number of the aneurysm and then enter it into the AVM field. So I want to add a button to my AVM form that says add associated aneurysm which opens a continuous form
    Why don't you just put a multiple field combo box on the form and let them choose?

    Quote Originally Posted by klpe237 View Post
    The way my database is set up is every patient has a patient ID number and then each diagnosis has a patient ID and a diagnosis ID
    Quote Originally Posted by klpe237 View Post
    Also, how do I filter the continuous aneurysm form to only show aneurysms from the current patient when my aneurysm table only has a diagnosis ID, not a patient ID. Each patient can have many diagosis IDs, all of which will be on the main form listed in a continuous form.
    These statements seem to contradict. Isn't an aneurysm a diagnosis? So according to the first statement it should have a patient ID.

    Either way, in order to filter the aneurysm form down to the current patient you will need some kind of link. How is a patient linked to a specific aneurysm? If you are using the aneurysm ID in the AVM table then you need to start there.

    You are letting the form dictate the design of the tables. This is backwards. Get the database designed properly and you can extract whatever you need from it for your forms, queries or reports. If the database structure is bad you will be fighting it the entire time you are maintaining this system.

    Steve

  3. #3
    Join Date
    May 2012
    Posts
    64
    I have a patient table that has a patient ID as the key and a diagnosis ID. I then have a diagnosis table that has a diagnosis ID as the key and also the patient ID to connect it back to each patient. Then I just have one more field: the actual diagnosis. This is just a text field where you type in the diagnosis. There are about 50 possible diagnoses that can go here. A patient can have any number of these diagnoses. Only 3 of the possible diagnoses in this list have additional information which are stroke, aneurysm, and AVM. I have given each of these their own table with a stroke ID, aneurysm ID, or AVM ID and then a diagnosis ID to connect it back to the diagnosis. This has all worked great so far until I needed to double click the aneurysm to save the ID in the field in the AVm form, which I would have trouble with no matter what.

    However, if you think I would be better off including all of the additional information for the diagnoses in the diagnoses table, then I could probably do that but having them in separate tables has made building the forms much much easier.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have what is common to a diagnosis in the same table, have what is unique to a specific diagnosis in other (sub) tables linked by diagnosisID
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2012
    Posts
    64
    Okay, that's how it's currently set up. So then, how would I achieve what my original question was:
    So my question is, what should I put in the code on the double click event of the aneurysm form that both closes the form and saves the aneurysmID number in the AVM form in the associatedAneurysm field?

    Also, how do I filter the continuous aneurysm form to only show aneurysms from the current patient when my aneurysm table only has a diagnosis ID, not a patient ID. Each patient can have many diagosis IDs, all of which will be on the main form listed in a continuous form.

  6. #6
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Quote Originally Posted by klpe237 View Post
    I have a patient table that has a patient ID as the key and a diagnosis ID. I then have a diagnosis table that has a diagnosis ID as the key and also the patient ID to connect it back to each patient.
    Why do you have the Patient ID and the Diagnosis ID in both tables?

    The Patient table should contain a single record per patient.
    The Diagnosis table should contain a single record per diagnosis.
    The PatientDiagnosis table has both the Patient ID and Diagnosis ID to join patients with one or many diagnosis

    You can then create the additional stroke, aneurysm, and AVM tables using the diagnosis ID to link them back to the main diagnosis table and since there are only three, create link tables between the PatientDiagnosis and each of these. By linking through the PatientDiagnosis table you know that patient has an assigned diagnosis.


    Quote Originally Posted by klpe237 View Post
    Then I just have one more field: the actual diagnosis. This is just a text field where you type in the diagnosis.
    You really shouldn't rely on letting the user type in a diagnosis. They will make typing errors, spell them differently or use abbreviations. You won't be able to rely on this field for anything. It's much better to create a table with all the possible diagnosis and provide the user with a combo or list box to choose from.

    Quote Originally Posted by klpe237 View Post
    So my question is, what should I put in the code on the double click event of the aneurysm form that both closes the form and saves the aneurysmID number in the AVM form in the associatedAneurysm field?.
    When the user makes their selection, you get the ID of the selected record and update the appropriate field with that value and close the form. Where's the specific trouble spot?

    Steve

  7. #7
    Join Date
    May 2012
    Posts
    64
    I'm putting this is the on click event of a button:
    Code:
    Private Sub Command44_Click()
    Me.aneurysmID = Forms!frm_avmNew.associatedAneurysm
    End Sub
    The button is on my aneurysm form that lists all aneurysms in a continuous form. So there are many of these buttons. When the button is clicked, it takes the number from aneurysmID and adds it to the associatedAneurysm field on the frm_avmNew form. But it isn't working! It is saying I can't assign a value to this object.....

  8. #8
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Maybe I'm missing something here but it looks like you have your assignment backwards. Aren't you trying to put the aneurysmID on frm_avmNew? If so, it needs to be reversed.

    Code:
    Forms!frm_avmNew.associatedAneurysm = Me.aneurysmID

    Steve

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •