SubForm Combobox Query not producing proper results

[rhubarb]

Hi All,



I've added a subform onto my main form. In this subform is a combobox that uses a query to look up tblEmployees.FullName and is tied to the main form by a junction table tblEmployeestoTicket.
When using the drop-down it shows me the ID of the employee instead of the full name which makes total sense because there is no FullName field in the junction table. I'd like it so users can see the full name of the employee to assign it to the ticket.

The relationship looks like the image below.



The business logic states that an employee can be assigned to multiple ticket numbers so I use the junction tblEmployeestoTicket to map the many-to-many relationship between employee(s) and the ticket.

I'm not sure that's the best solution. Any ideas?

Thanks for any help!

[ranman256]

In the query that fills the combo, It needs 2 fields, ID, and name.
in combo properties, set
columns =2
col widths= 0;2

users don't care about the ID, just the name.

[rhubarb]

Thanks for the response.

Yes, I set this up but the junction table that the subform is based on doesn't know what the name is, only what the ID is. The ID maps to the name in the other table. So the query works fine, but if I load my main form it prompts for name.

[ranman256]

you need a query, qsNames
with ID , NAME
put this query in the join

[rhubarb]

I think I was over thinking this.

My combo box row source was a query for fullname against tblEmployees, while the Control Source was EmployeeID from the tblEmployeestoTicket table. Finally, my subform linked Ticket Number to EmployeeID.

Thanks for steering me in the right direction!