union query docmd elseif code help

[nomax]

I realize that is a terrible subject line, not sure how else to word it. Here is my issue and hope someone can help me with it

I have (3) forms - CONTACTS, CRUISEBOOKING and FITBOOKING. I have a union query that provides me with both cruisebooking and fitbooking for that contact. This works fine and it gives me exactly what I need. The union query is now a subform on my contacts form so I can see all of the bookings made for that contact.

From that subform I would like to be able to open that specific booking. Is this possible or am I wasting my time banging my head against the wall. When I had the reports seperate - one for CRUISE and one for FIT I could open that specific record, I just can't figure out how to do this when they are both on the same subform. Maybe a docmd statement with elseif?

I hope I explained this well enough for someone to help me.

 

[Ranman256]

you can open a specific record,
in your union query ,each query should have an extra field, form name.
(contacts query has "fContacts", cruise query has "fCruise")
then you can open THAT form with THAT ID
docmd.openform txtForm ,,,"[id]=" & txtID

 

[nomax]

sorry, not getting it - maybe this will help - here is my union query

SELECT Contacts.ID, Contacts.[Full Legal Name], [FIT BOOKING].TRIPID, [FIT BOOKING].Confirmation, [FIT BOOKING].[Departure Date], [FIT BOOKING].Description
FROM Contacts INNER JOIN [FIT BOOKING] ON Contacts.ID = [FIT BOOKING].ID
UNION
SELECT Contacts.ID, Contacts.[Full Legal Name], CRUISE.CRUISEID, CRUISE.Confirmation, CRUISE.[Departure Date], CRUISE.Description
FROM Contacts INNER JOIN CRUISE ON Contacts.ID = CRUISE.ID;

 

[MarkK]

Check out the 'Type' field added to the SQL...

SELECT c.ID, c.[Full Legal Name], fb.TRIPID, 'FitBook' As Type, fb.Confirmation, fb.[Departure Date], fb.Description
FROM Contacts As c INNER JOIN [FIT BOOKING] As fb ON c.ID = fb.ID
UNION
SELECT c1.ID, c1.[Full Legal Name], c2.CRUISEID, 'Cruise', c2.Confirmation, c2.[Departure Date], c2.Description
FROM Contacts INNER JOIN CRUISE ON c1.ID = c2.ID;

That way you can distinguish the origin of the records in your UNION query.

 

[nomax]

Thank you I will give that a try

 

[nomax]

Okay, great that worked, now I just need to figure out how to open that record