lookup query and display in combo box?

[Jackie]

Hi All



I was looking for a little help with one of my forms which assigns an engineer to a job using a combo box which works fine - what I'm looking for it to do is to when you select a date I want it to lookup a query which list all the days and engineer has booked off and if any engineers are booked off for that date I would like when you look at the combo box the engineers that are not available to turn a different colour and can not be selected

I have had a look through the forum and on google but can't see anything that looks like this - I would really appricate if someone could point me in the right direction

cheers jackie

[jzwp11]

I don't know of a way to change colors within a combo box, but you could exclude the engineers that are not available from even showing up in the combo box by using a query for the combo box that excludes those engineers that are already booked.

[Jackie]

Hi there

Thanks for the reply - that would be ok how would this work? I have a query that shows what staff and what date they are off but how would I build a query to show when they are available?

Jackie

[jzwp11]

You would most likely need a subquery

SELECT engineerID, engineername
FROM tableofengineers
WHERE engineerID NOT IN (select engineerID from tableofdaysoff where dateoff= forms!formname!datecontrol)

[Jackie]

Hi

OK this is what I have

SELECT 0 as JobID, "<all>" as Initials FROM NullTable UNION ALL SELECT Staff.StaffID, Staff.Initials FROM Staff
ORDER BY Initials WHERE StaffID NOT IN (SELECT StaffID FROM StaffHolidays WHERE HolDate = forms!SearchJobSheet!BookedDate);

Syntax Error (Missing Operator) in query expression

Not sure where I am going wrong? any ideas

Jackie

[jzwp11]

I would probably create the query with the subquery and save that, then reference the saved query in the union query.

[jzwp11]

I had a thought, why would you include the option for ALL in the combo box if your intent was to limit it to only those who were available. There appears to be a logical issue there.