default entry in combo box

[Guest]

I have a combo box on a form to display a list of drivers from the employees
table. Since one person is the primary driver of each truck, I would like it
to default to the driver whom this truck is assigned to(tblEmployees,Truck)
however be able to choose a different driver if needed.
The row source is qryDriver.
The SQL is:
SELECT DISTINCT tblEmployees.EmployeeID, tblEmployees.Truck
FROM tblEmployees INNER JOIN tblTrips ON tblEmployees.EmployeeID =
tblTrips.EmployeeID
WHERE (((tblEmployees.Truck)=[forms]![frmTrips]![TruckID]));

I've tried using DLookup in the Default Value line but it doesn't seem to
accept the criteria portion. I'm sure this is possible but I've been
unsuccessful in accomplishing it so far.

 

[John Griffiths]

I have a combo box on a form to display a list of drivers from the employees
table. Since one person is the primary driver of each truck, I would like it
to default to the driver whom this truck is assigned to(tblEmployees,Truck)
however be able to choose a different driver if needed.
The row source is qryDriver.
The SQL is:
SELECT DISTINCT tblEmployees.EmployeeID, tblEmployees.Truck
FROM tblEmployees INNER JOIN tblTrips ON tblEmployees.EmployeeID =
tblTrips.EmployeeID
WHERE (((tblEmployees.Truck)=[forms]![frmTrips]![TruckID]));

I've tried using DLookup in the Default Value line but it doesn't seem to
accept the criteria portion. I'm sure this is possible but I've been
unsuccessful in accomplishing it so far.

If you are using table Trips (an employee of type driver
makes between 0 and MANY trips using a company Truck);
then when you have a new driver the list will be empty.
Should LimitToList = True then a new driver will never get her first trip.

-----------------------
Combo
ControlSource=tblTrips.EmployeeID
BoundColumn=1
RowSource=

SELECT e1.EmployeeID, e1.Truck, 1
FROM tblEmployees e1
WHERE e1.Truck = [forms]![frmTrips]![TruckID]
UNION
SELECT e2.EmployeeID, e2.Truck, 2
FROM tblEmployees e2
WHERE e2.Truck IS NOT NULL
And e2.Truck <> [forms]![frmTrips]![TruckID]
UNION
SELECT e3.EmployeeID, "", 3
FROM tblEmployees e3
WHERE e2.Truck IS NULL
ORDER BY 3, 1
-----------------------
Select e1 employees who are set as the preferred driver for the truck,
this can be no rows if no employees have the preferred the
truck (ie new employee)
this can be one row if only 1 employee has the truck
preferred (normal)
this can be more than one row when more than one
driver has the same proffered truck.
UNION puts the selects together
Select e2 employees who are not set as the preferred driver for the truck
but have a preferred truck.
Select e3 employees who are do not have a preferred truck.

 

[Guest]

Thanks John for your reply.
I noticed I posted the wrong query SQL last time. It was to select the
assigned driver for a truck. The row source for my combo box is qryDrivers
which selects all drivers. Here is the SQL for this query:
SELECT tblEmployees.EmployeeID, tblEmployees.FirstName & ' ' &
tblEmployees.LastName AS Expr1, tblEmployees.Driver
FROM tblEmployees
WHERE (((tblEmployees.Driver)=Yes));

All drivers are stored in tblEmployees with a FK field in tblTrips and Limit
To List is set to true so a driver must be setup before a trip can be entered.
I've looked at your code and as I understand it, you select first the driver
assigned to the truck the any drivers assigned to different trucks and
finally any drivers not assigned to a truck.
I don't understand the e1,e2,e3 before EmployeeID and Truck. Where does
this come from or what does it represent?
I pasted your code in the row source and changed the control source as you
suggested and the combo list was blank.
Is it possible to join the two queries I have, which return the correct
results, in the same way as you did to show first the assigned driver but
also have all drivers listed?
--
Thanks for your help,
Walter

I have a combo box on a form to display a list of drivers from the employees
table. Since one person is the primary driver of each truck, I would like it
to default to the driver whom this truck is assigned to(tblEmployees,Truck)
however be able to choose a different driver if needed.
The row source is qryDriver.
The SQL is:
SELECT DISTINCT tblEmployees.EmployeeID, tblEmployees.Truck
FROM tblEmployees INNER JOIN tblTrips ON tblEmployees.EmployeeID =
tblTrips.EmployeeID
WHERE (((tblEmployees.Truck)=[forms]![frmTrips]![TruckID]));

I've tried using DLookup in the Default Value line but it doesn't seem to
accept the criteria portion. I'm sure this is possible but I've been
unsuccessful in accomplishing it so far.

If you are using table Trips (an employee of type driver
makes between 0 and MANY trips using a company Truck);
then when you have a new driver the list will be empty.
Should LimitToList = True then a new driver will never get her first trip.

-----------------------
Combo
ControlSource=tblTrips.EmployeeID
BoundColumn=1
RowSource=

SELECT e1.EmployeeID, e1.Truck, 1
FROM tblEmployees e1
WHERE e1.Truck = [forms]![frmTrips]![TruckID]
UNION
SELECT e2.EmployeeID, e2.Truck, 2
FROM tblEmployees e2
WHERE e2.Truck IS NOT NULL
And e2.Truck <> [forms]![frmTrips]![TruckID]
UNION
SELECT e3.EmployeeID, "", 3
FROM tblEmployees e3
WHERE e2.Truck IS NULL
ORDER BY 3, 1
-----------------------
Select e1 employees who are set as the preferred driver for the truck,
this can be no rows if no employees have the preferred the
truck (ie new employee)
this can be one row if only 1 employee has the truck
preferred (normal)
this can be more than one row when more than one
driver has the same proffered truck.
UNION puts the selects together
Select e2 employees who are not set as the preferred driver for the truck
but have a preferred truck.
Select e3 employees who are do not have a preferred truck.