Open Specific Form Based on ID that May Be in One or More Tables (1 Viewer)

[moscgama]

Hi everyone,

I'm having a problem needing to pull out a record that may be in one of many, unrelated tables. I'm have a union query set up that lists all of the IDs in all of the tables and have DoCmd.OpenForm code that will check to see if the ID is in the table then open the form that belongs to the appropriate table. However, I'm having trouble opening the form to the correct record. As of now, the form opens to the record that was first entered.

Private Sub Combo4_AfterUpdate()
 
    If DCount("ID", "[Option1]", "[ID] = '" & Me.Combo4 & "'") > 0 Then
    DoCmd.OpenForm "Option1FRM", , , , acFormEdit, , " & Me.Combo4 & " '"
    
    ElseIf DCount("ID", "[Option2]", "[ID] = '" & Me.Combo4 & "'") > 0 Then
    DoCmd.OpenForm "Option2FRM", , , , acFormEdit, , " & Me.Combo4 & " '"
    
    ElseIf DCount("ID", "[Option3]", "[ID] = '" & Me.Combo4 & "'") > 0 Then
    DoCmd.OpenForm "Option3FRM", , , , acFormEdit, , " & Me.Combo4 & " '"
 
    End If
    
    DoCmd.Close acForm, "Contact_Edit_Select_Frm"
    
End Sub

Notes: ID is the ID that could be in any of the tables
Option# represents the possible tables
Optoin#FRM represents the form that is associated with the option# table

Do you think this is a good way of searching through all of the tables, or should I write another query? If this method works, what am I doing wrong in the argument statement for the DoCmd.Openform?

Let me know if you need more specifics or have any questions! Thanks in advance for the help!

 

[moke123]

Are the ID's unique across the three tables? Or might an ID appear in more than 1 table.

You need to post your table structure as it may be the root of your problem.
Just guessing but do you have three types of contacts stored in three tables?

 

[moscgama]

Hi Moke,

Thanks so much for your response! Sorry for being a little vague about my structure. This is not my exact case, but the following is an example of what I'm trying to do. Let's say I am running a clinical trial for a cancer drug. I have tables for the beginning cancer information, which is common among all patients. Then each cancer patient is assigned a drug, with each drug showing different side effects. There are some common elements to each drug (patient's blood pressure, etc.), however, there are variables that are unique to each drug that the other drugs do not share (side-effects, etc.). My current database separates out each patient into a separate table based on their drug, thus there is not an overlap between IDs in each of the tables, as you can only be a part of one treatment group. I originally was thinking it would be easier to have these treatment groups separated out so they could be easily examined individually and together if needed (no need to filter out all the other treatment groups if you're just looking at one and there is not a ton of variables common between the two). The tables aren't really linked together because there's not a common ID between the two. I am wondering now if I should have combined all the tables into one, but I feel as though everything's working so far EXCEPT finding the ID and opening the form!

Oh, and I forgot to specify in my previous post that Me.Combo4 is the name of the drop-down box that contains the IDs from all of the tables.

Let me know if there's any other information that would be helpful!

Thanks,

moscgama

 

[Minty]

Your form open isn't correct. Paul's web site will assist http://www.baldyweb.com/wherecondition.htm

Basically you aren't specifying the field to make the criteria work.

But...
If your combo has all the ID's, it could also have the table name it came from as a hidden column - and you could use that to open the correct form as you would know the table.

 

[moke123]

I'm far from an expert on normalization so hopefully someone who is will chime in. It usually takes me a bunch of tweaking before I'm happy with what i have.

without knowing your particular situation, It seems to me that you should be grouping all the patients in one table as well as the drugs, or treatment groups, side effects, diagnostics(blood pres., temp,etc). Then use junction tables to relate a patient to his drug or group, his side effects, and his diagnostics, etc. you may find that its easier to query and you may not need 3 forms depending on tables.

 

[plog]

My current database separates out each patient into a separate table based on their drug

That doesn't sound right. Can you work on the Relationship Tool then post a screenshot?