Open form for record selected in combo

[CW]

When our orders are booked, they may come from Agents or Corporates.
I have an Agents table and an Agent Data Form, and a Corporates table and a
Corporate Data Form.
On the main Orders page I have a combo called Booker and the data cource is
a Union query which combines all the records from the Agents and Corporates
tables, and the user can select whichever applies. All good so far.
Now, we want to be able to double-click on the record selected in the combo
and open the relevant Data Form, be it an Agent or a Corporate. (In order to
quickly gather a detail such a phone nbr or whatever else resides on the form)
I have no problem doing it for a single data source, but cannot work out how
to code it in this instance - how to tell Access to find the respective form,
because it could be either an Agent form or a Corporate form.
I have fiddled around with some If syntax but nothing even came close... can
somebody assist, please?
Many thanks
CW

 

[Allen Browne]

Design the UNION query to it tells you which table the record came from. You
can then read that Column of the combo to know which form to open.

Example:
SELECT AgentID AS ID, AgentName AS TheName, "Agent" AS TheSource
FROM tblAgent
UNION ALL
SELECT CorpID AS ID, CorpName AS TheName, "Corp" AS TheSource
FROM tblCorp;

Then this kind of thing:

Dim strForm As Form
Dim strWhere As String
With Me.Combo1
If Not IsNull(.Value) Then
If .Column(2) = "Agent" Then
strForm = "frmAgent"
strWhere = "AgentID = " & .Value
Else
strForm = "frmCorp
strWhere = "CorpID = " & .Value
End If
DoCmd.OpenForm strForm, WhereCondition:=strWhere
End If
End With

A better solution might be to have the agents and corporates all in the one
table of clients. If you are sure no one will ever be both, you can add an
extra field to indicate the client type (Agent or Corporate.) If somebody
could be both, add a related Role table that records the roles your clients
have.

The advantage of this approach is that is makes it much easier to build your
foreign keys. For example, if you have to track bookings or payments, it
doesn't matter whether you have an appointment with/payment to an agent or a
corporate.

 

[CW]

Thanks, Allen - I'll have a go at that. You've hit the nail on the head with
your second point - some companies are indeed both Agent and Corporate. That
was why I created separate tables, but I do see your thinking in combining
them and having a "role" or similar. I'll play around with that when (if) I
get a spare mo.
Thanks again
CW