Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms Programming / July 2005

Tip: Looking for answers? Try searching our database.

default entry in combo box

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Walter - 29 Jul 2005 14:39 GMT
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.
Signature

Thanks for your help,
Walter

John Griffiths - 30 Jul 2005 01:03 GMT
> 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
[quoted text clipped - 10 lines]
> 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.
-----------------------

Bit of a mouthful - John
Walter - 30 Jul 2005 23:41 GMT
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?
Signature

Thanks for your help,
Walter

> > I have a combo box on a form to display a list of drivers from the
> employees
[quoted text clipped - 53 lines]
>
> Bit of a mouthful - John
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.