Trying to open a form based on a SQL Query pointing to tables

[stimp]

I have a database with 3 forms with 3 separate tables. All the forms are linked to their individual tables and have a Part field. So, I want to open the specific form that has this specific Part.
Easiest way I felt is to have an SQL statement that compares all the tables Part field with my Switchboard Part field. And that is where I am stuck. Once the SQL determines the table with the right Part#, how do I get it to open the Form linked and continue with my data input?

My PartQuery:
Select Distinct Table1.Part from Table1 where ((([Table1].Part)=(Forms![Switchboard].Part)));
UNION ALL
Select Distinct Table2.Part from Table2 where ((([Table2].Part)=(Forms![Switchboard].Part)));
UNION ALL
Select Distinct Table3.Part from Table3 where ((([Table3].Part)=(Forms![Switchboard].Part)));

The Query works and finds the right Part#. Now what I would like to put is a DoCmd.Openform "Form(1,2or3)" before the UNION ALL step once it finds the table with the Part#.

Is there someway to open the corresponding form once the Part# is found?

[Rabbit]

Is there a reason why you have them in separate tables? It's usually better design to have similar data in one table.

[stimp]

Different equipment with different setups. It's a very old database with type of equipment getting their own table and form. Over the years this equipment has gotten various part numbers.

[jforbes]

You could create a Method like the following to open the correct Form and show the Part Number:

Public Sub showPart(ByRef sPart As String)
    If Len(sPart) > 0 Then
        If DCount("Part", "Table1", "Part='" & sPart & "'") > 0 Then
            DoCmd.OpenForm "Form1", , , "Part='" & sPart & "'"
        ElseIf DCount("Part", "Table2", "Part='" & sPart & "'") > 0 Then
            DoCmd.OpenForm "Form2", , , "Part='" & sPart & "'"
        ElseIf DCount("Part", "Table3", "Part='" & sPart & "'") > 0 Then
            DoCmd.OpenForm "Form3", , , "Part='" & sPart & "'"
        End If
    End If
End Sub

Then just call the Sub:

Call showPart(Forms![Switchboard].Part)

[stimp]

That would go on my switchboard I presume. I've been trying to rework it from that angle, but being Friday and close to quitting time my brain just didn't want to work.

Thanks for this, I'll give it a try.

[jforbes]

You can put it in your Switchboard and it will work just fine. I typically put Methods like that in a Module, then they can be called from anywhere in the project where there is a Part Number, which can be really handy. But that is completely up to you.

[stimp]

Thanks jforbes that worked great.

I'd rather run this through VBA as I am more familiar with it than SQL. Not sure why I didn't think of this way to begin with. If we ever duplicate that part in multiple tables I will have to think of a fix. In the meantime it runs great, and this database has been going strong for almost 20 years with no duplication except accidental entries in the wrong form.