Combo Box Row Source Union Query

Question

Hello,

I'm trying to use a union query as the row source for a combo box wherein the user can select data in a field from two tables but I'm running into problems and don't know if it lies in the query itself or my setup.  The two tables involving the union query are tblAthletes and tblContacts.  I also have another table which is called tblRoster with primary key fields of AthleteID and EventID.  The situation is that a roster can consist of people from tblContacts and/or people from tblAthletes.

The SQL for the form is:

SELECT tblRosters.AthleteID, tblRosters.EventID

FROM tblRosters;

The SQL for the combo box AthleteID is:

SELECT tblContacts.ContactID AS ID, [FirstName] & " " & [LastName] AS FullName, tblContacts.Active

FROM tblContacts

WHERE (((tblContacts.Active)=False))

UNION ALL SELECT tblAthletes.AthleteID AS ID, [FirstName] & " " & [LastName] AS [Full Name], tblAthletes.Active

FROM tblAthletes

WHERE (((tblAthletes.Active)=False))

ORDER BY FullName;

All the names from both tables appear in the combo box, but I'm only able to select names from the tblContacts.  I get an error "You cannot add or change a record because a related record is required in table 'tblContacts'" if I try to select a name from the tblAthletes.

First is what I'm trying to do possible, if so I'm hoping someone can help resolve my problem.

Thanks,

Mike

Answer 1

> tblContacts, tblAthletes

If you would like to have a discussion on the merits of this db design, open a new topic. Post the relevant portion of the Relationships diagram, and some more background info on the goals of the db design / application.

Answer 2

Currently the control source of the combo is AthleteID which data type is number in the tblRosters.

Actually I'm trying to pick an ID from tblContacts and/or tblAthletes and store the selected value in the AthleteID field of tblRosters.  I want the ID, whether it comes from tblContacts (ContactID) or tblAthletes (AthleteID) to be stored in tblRosters (AthleteID).

Hope that makes sense.  Is this possible?

Answer 3

Evidently the Control Source of the combo box is at fault; it appears that you're trying to pick an ID from either tblRosters or tblContacts, and store the selected value in some table with a relationship to tblContacts. No can do! 

What is the Control Source of the combo? What do you want to happen when you select a Contact from the list? What do you want to happen when you select an Athlete?