Creating Forms with dependent lookup fields (controls)
Gar44
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
Keith Wilby
news:80F82F01-9A6A-468B...@microsoft.com...
http://www.fontstuff.com/access/acctut10.htm
Keith.
www.keithwilby.co.uk
Gar44
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
Keith Wilby
> 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
>
>
>
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
Gar44
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.
Keith Wilby
> 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.
>
>
>
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
Gar44
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
Keith Wilby
>
> 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
>
>
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
Gar44
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 :)
Keith Wilby
> 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).
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.
>
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)
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?
>
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 :)
>
Glad to help if I can :)