Combo Box Help

[Lynxboy252]

Hi there,

I need help with combo boxes.

I'm currently designing a database to keep records of individual vehicles
operated by particular companies and the depot that they're allocated to.

For example. Bus 154 is operated by Big Bus Company and is allocated to
their depot in, lets say London.

I have a table with the operator information in, another table with depot
names in which are linked to the operator. So one operator can have many
depots. The vehicle info is kept in a vehicles table. I input the vehicle
information which is fine but what i want to do with my form is for each
vehicle I enter, I'd like to select an operator from a combo box. Then, in
the depot combo box, I want it to show me only the depots relating to the
operator I'd previously selected and not every depot I have in my depot table.

I do apologise for the poor explanation and I do hope it makes some sense.
This is driving me crazy and I anticipate it to be something very simple,
though have to admit i'm not terribly great with SQL etc.

Can someone advise me on how I go about doing this?

Thanks in anticipation.

Adam.

 

[Golfinray]

I would create a form for the operators and a subform for the depots. Forms
are the one side, subforms are the many side. When you select the operator on
the form it would show all possible depots for that operator. Use the form
wizard to create the form and the subform wizard to create the subform. The
wizard should link them for you.

 

[Lynxboy252]

Many thanks for your reply Golfinray.

This I have done which shows which depots related to a particular operator.

However, I would like to be able to select an individual depot to be saved
into the vehicles table.

In the vehicles table, registration details and body/chassis details are
entered. I would like to select an operator from a combo box, which then
filters depots for that operator and displays them in another combo box so
that I can select the one depot to which that particular vehicle is allocated.

Thanks.

Adam

 

[Golfinray]

See if i understand. You want certain vehicles assigned to certain depots and
not all depots? And you want certain operators assigned to certain vehicles
and also assigned to certain depots but not other vehicles or depots? Give us
a little better description please? When you say you want an individual depot
saved into the vehicle table, is this so you can view that information later?
If not, you could just go into the vehicles table and assign each to a depot.

 

[Pete D.]

You are describing a many to many relationship for the operator, vehile and
depot. I would assume that each operator may not be able to operate all
types of vehicles. You first need to write down the relationships of each
entity, vehicle, operator, depot. Then you may want to look at each driver
qualifications, hazmat license, passenger endorsement... Each operator could
be at many depots and each vehicle could also work out of a few depots.
Operator 1 might be available for one depot but not the end of the line
depot but he could do the round robin delivery. I would draw it out on
paper and then build the tables with the correct relationships. Once you
figure out the data you need your tables will help dictate your forms. Look
at the paper based system your orginization uses also so you don't miss
something and have to go backwards. This isn't hard if you take it one
table at a time and then relate them to each other. Once you decide on the
table data you can post back for folks to help you with the relationships.

Suggested tables to start
Vehicles
Depots
Operators
Qualifications ie, trac trailer, passenger, haz mat...
Type vehicles to match up operator and qualifications

junction tables maybe more, maybe combine
operators/depots
vehicles/depots
operator/qualifications
vehicle/req qualifications
vehicle makes/models (save you from typing same info for a fleet of 2004
ford F-750 automatics)
service facilities/depots that can mantain that type vehicle