Creating Forms with dependent lookup fields (controls)

  • Thread starter Thread starter Gar44
  • Start date Start date
G

Gar44

Hi guys I’m wondering can anyone help me out. I’m trying to create a form for
a database that tracks cars in different locations around my country. I have
a lot to track and want to make the forms easy to fill out using lookup
fields (controls). I was wondering if there is a way to make the values in
one lookup field (control) depend on another. What I mean by this is that on
the form when they select a location in the country I want the form to then
only show the vehicles in that location. Thanks a lot for any replies in
advance, hopefully someone can help me out :)
 
Gar44 said:
Hi guys I’m wondering can anyone help me out. I’m trying to create a form
for
a database that tracks cars in different locations around my country. I
have
a lot to track and want to make the forms easy to fill out using lookup
fields (controls). I was wondering if there is a way to make the values in
one lookup field (control) depend on another. What I mean by this is that
on
the form when they select a location in the country I want the form to
then
only show the vehicles in that location. Thanks a lot for any replies in
advance, hopefully someone can help me out :)
Click to expand...

http://www.fontstuff.com/access/acctut10.htm

Keith.
www.keithwilby.co.uk
 
Hi Keith thanks for the reply. I tried out what you suggested and it worked.
The problem I’m having is that I want to use the form to allow the users to
enter data into a table to fill it. Any ideas how I would do this?

Would I create the table first and then create the form to fill it. I am use
to allowing access create forms for me based on the table I currently have
selected so when the form is filled out the table is filled too.

Could I start it this way by allowing access to create the form and then
going into design view and editing the way the controls work? Sorry for the
long reply I just wanted to try give a better picture of what I’m trying to
do. Thanks again Keith I really do appreciate
 
Gar44 said:
Hi Keith thanks for the reply. I tried out what you suggested and it
worked.
The problem I’m having is that I want to use the form to allow the users
to
enter data into a table to fill it. Any ideas how I would do this?

Would I create the table first and then create the form to fill it. I am
use
to allowing access create forms for me based on the table I currently have
selected so when the form is filled out the table is filled too.

Could I start it this way by allowing access to create the form and then
going into design view and editing the way the controls work? Sorry for
the
long reply I just wanted to try give a better picture of what I’m trying
to
do. Thanks again Keith I really do appreciate
Click to expand...

I'm not sure that I understand the problem since you seem to have answered
your own question. The usual approach for a database application is to
provide users with forms to manipulate their data but you already seem to be
aware of that. Such forms should be "bound" to the table that you want to
edit. Is that the concept you're struggling with?

Here are some useful links that might help:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal
 
Hi Keith, yes I think that’s where my problem is. I understand the concept of
what needs to be done but I’m new to access so I lack experience. The idea of
the form being bound to the tables I use to create it is also new so I assume
that’s what happens automatically when I allow access to create a form for
me. I just wasn’t sure if I allowed access to create the form if I would be
able to add cascading combo boxes etc.

What I mean by this is that I thought I would have to do this on the table
design before I allowed access to create the form. So that when the form was
created the control boxes access assigns would already cascade. Can I just
use Blank standard boxes in the table and they will be filled out correctly
once the controls on the form cascade.

Hopefully you get me, thanks again and thanks for the links I’ll go look at
them now.
 
Gar44 said:
Hi Keith, yes I think that’s where my problem is. I understand the concept
of
what needs to be done but I’m new to access so I lack experience. The idea
of
the form being bound to the tables I use to create it is also new so I
assume
that’s what happens automatically when I allow access to create a form for
me. I just wasn’t sure if I allowed access to create the form if I would
be
able to add cascading combo boxes etc.

What I mean by this is that I thought I would have to do this on the table
design before I allowed access to create the form. So that when the form
was
created the control boxes access assigns would already cascade. Can I just
use Blank standard boxes in the table and they will be filled out
correctly
once the controls on the form cascade.

Hopefully you get me, thanks again and thanks for the links I’ll go look
at
them now.
Click to expand...

If you use the form wizard then, yes, it will bind your form to a data
source, be it a table or query. Anything the wizards do for you you can do
manually.

Please don't be offended by this but I get the impression that you're trying
to run before learning to walk. Have a good look around those sites I've
offered and also have a look at the sample database that ships with Access.
It's called "Northwind" and, whilst not ideal, it will give you a good
insight into how things hang together and how you can use forms for
different purposes.

Access has a steep learning curve and cascading combo boxes is quite an
advanced topic. Keep in mind also that Access is a tool kit to allow you to
*build* an application and is unlike Excel and Word in that respect.

Regards,
Keith.
www.keithwilby.co.uk
 
Hi Keith, I actually think you’re completely right about that I’m definitely
trying to run before I can walk. I think I’ll have to take it back to basics.
To be honest I’m not even sure if I started the database in the correct way
with planning etc. So I'll go back and hopefully understand it a little bit
better this time around.

Can I ask one question if you had nine braches (as I do) and each branch had
fields such as location, registration, vehicle type, function etc. Would you
have these 9 branch tables built as independent tables all containing these
fields or would you use smaller tables to create them.

As of yet I still have no relationships defined in this database and I’m
wondering should I have started smaller and thus each of these 9 tables would
have relationships with other tables already such as vehicle type tables or
location tables. Sorry for being a pain I just want to try figure out if I’m
going about this all wrong. Thanks again
 
Gar44 said:
Can I ask one question if you had nine braches (as I do) and each branch
had
fields such as location, registration, vehicle type, function etc. Would
you
have these 9 branch tables built as independent tables all containing
these
fields or would you use smaller tables to create them.

As of yet I still have no relationships defined in this database and I’m
wondering should I have started smaller and thus each of these 9 tables
would
have relationships with other tables already such as vehicle type tables
or
location tables. Sorry for being a pain I just want to try figure out if
I’m
going about this all wrong. Thanks again
Click to expand...

You need to think of things as entities with attributes to decide on your
table structure. A branch is an entity with attributes such as location
(address) so consider having a table called "tblBranch" with fields for
location etc which never change.

A branch can have many vehicles so consider having a table called
"tblVehicle" with all vehicle attributes (reg no, make & model etc). There
would be a one-to-many relationship between tblBranch and tblVehicle.

You'll start to notice that you're storing each piece of data once only in
the entire database and this process is known as "normalisation".

Keith.
www.keithwilby.co.uk
 
That’s brilliant Keith thanks a lot for that makes it clear I was approaching
it wrong. The whole purpose of the database is to track problems on vehicles
around the branches.

So if I have a table branch with fields such as (location and vehicle). And
then a Vehicles table with fields such as (reg, type, function, weight,
license needed, year of reg.) These would share the one to many relationship
as one branch has many vehicles, as you said.

If I made two more tables called Technicians and jobs. Technicians would
have fields such as (first name and last name) And jobs would have fields
such as (received date, start date, branch, reported fault, date completed,
tech name, status, vehicle reg)

Would my relationships then be that techs would have a 1 to many
relationship with
jobs as each tech can have many jobs? And also each job would have a branch
and a vehicle reg so what would the relationships be there. Would it be that
branch would have a one to many relationship with job as each job can only
have one branch but each branch can have many jobs?

And job would have to have a relationship to vehicle I assume because it has
stores a vehicle reg but I can’t figure that one out

Thanks Keith you’re a huge help :)
 
Gar44 said:
That’s brilliant Keith thanks a lot for that makes it clear I was
approaching
it wrong. The whole purpose of the database is to track problems on
vehicles
around the branches.

So if I have a table branch with fields such as (location and vehicle).
Click to expand...

I think that your "vehicle" table would be your central one. Fields might
be along these lines:

tblVehicle:
ID (primary key)
RegNo
Make
Model
EngineSize
BranchID (foreign key to contain the ID number of the branch from the branch
table)
And
then a Vehicles table with fields such as (reg, type, function, weight,
license needed, year of reg.) These would share the one to many
relationship
as one branch has many vehicles, as you said.
Click to expand...

tblVehicle joins 1:M on BranchID to ID in the branch table (each branch can
have many vehicles):

tblBranch:
ID (primary key, also stored in tblVehicle.BranchID)
BranchName
Address
PhoneNo

With this setup, if a vehicle transfers to another branch all you need to
change is the BranchID field.
If I made two more tables called Technicians and jobs. Technicians would
have fields such as (first name and last name)
Click to expand...

I would say that technician is an entity related to branch in the same way
as vehicle is since a branch can have many technicians.
And jobs would have fields
such as (received date, start date, branch, reported fault, date
completed,
tech name, status, vehicle reg)

Would my relationships then be that techs would have a 1 to many
relationship with
jobs as each tech can have many jobs? And also each job would have a
branch
and a vehicle reg so what would the relationships be there. Would it be
that
branch would have a one to many relationship with job as each job can only
have one branch but each branch can have many jobs?
Click to expand...


I would say that a job is an entity related to a vehicle since a vehicle can
have many jobs done on it. The branch isn't directly related to the job.

I keep saying "I would say" because there is often more than one way to do
this kind of thing. :)
And job would have to have a relationship to vehicle I assume because it
has
stores a vehicle reg but I can’t figure that one out

Thanks Keith you’re a huge help :)
Click to expand...

Glad to help if I can :)
 
Back
Top