Creating lookup table based on form control

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm designing a database with a component for dispatching drivers.

Tables:
TblTrips (with combo box to choose volunteer driver and a ServiceDate
field)
TblVolDrivers (current list of volunteer drivers)
TblDriverAvail (simple autonumber/voldriver lookup/date unavailable
table linked to driverid in TblVolDrivers)

Form: FrmTrips (with corresponding combo box to lookup available drivers)

What would be the slickest approach to design a means for the form and
TblTrips to lookup only those drivers available on the service date specified
on that corresponding record in TblTrips via the entry form?

And an approach that will eventually lend itself to being modified to also
programatically consider whether or not a particular driver is available for
a particular time period.

Thanks! Sometimes I think I've got it...but then it all just slips away :)
 
If TblDriverAvail had a field [Date Available], this would be easy:

SELECT [voldriver] FROM [TblDriverAvail] WHERE [Date Available]=#[Forms]!
[MainForm]![ServiceDate]#;

However, you're keeping track of when drivers are unavailable so try a
subquery:

SELECT [voldriver] FROM [TblDriverAvail] WHERE [voldriver] NOT IN (SELECT
[voldriver] FROM [TblDriverAvail] WHERE [date unavailable]=#[Forms]![MainForm]
![ServiceDate]#;);

The same principle can be used for time slots, you'd just have to check for
two criteria (date and time).
 
Back
Top