2 Combo Boxes

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

Guest

Hi - I have 2 combo boxes at the start of a form. The first one allows the
user to selct one of 10 different machines on our factory. How I can set up
the second combo box such that it will display a different list for each
value that may be selected in the first combo box. The second combox box will
have a list of different parts of a machine and since each part is different
the values displayed in the second combo box must be updated when the first
combo box is selected and is it possible to prevent the user from accessing
the second box before they have selected a value from the first box. HELP.

PS. I am a novice to SQL so please be gentle . . .
 
Richard said:
Hi - I have 2 combo boxes at the start of a form. The first one allows the
user to selct one of 10 different machines on our factory. How I can set up
the second combo box such that it will display a different list for each
value that may be selected in the first combo box. The second combox box will
have a list of different parts of a machine and since each part is different
the values displayed in the second combo box must be updated when the first
combo box is selected and is it possible to prevent the user from accessing
the second box before they have selected a value from the first box. HELP.
Click to expand...


See http://www.mvps.org/access/forms/frm0028.htm
 
I understand the logic but I cannot get to grips with the programming. Could
you send me the code using the following template so I can figure it out.

Combo box 1 allows the user to select 10 machines.machine1 -> machine 10.
Once the user has selected the machine in combo box 1 they will go to combox
box 2 which will now have a list of machine parts in the list. ie. If they
select machine1 in combo box 1 then combo box 2 will have the following
choices eg. machine1_part1 -> machine2_part2 -> machine3_partZ. If they have
selected machineX in the first combox box then the "code" will list all the
parts from machinex table.

All of the different machines are in one table on their own
All the parts of machine1 are in a different table on their own
All the parts of machineX are in a different table on their own.

I know this sounds dum but it would be very nice if you could send me an
example of the code

Thanks Marshall
 
Richard said:
I understand the logic but I cannot get to grips with the programming. Could
you send me the code using the following template so I can figure it out.

Combo box 1 allows the user to select 10 machines.machine1 -> machine 10.
Once the user has selected the machine in combo box 1 they will go to combox
box 2 which will now have a list of machine parts in the list. ie. If they
select machine1 in combo box 1 then combo box 2 will have the following
choices eg. machine1_part1 -> machine2_part2 -> machine3_partZ. If they have
selected machineX in the first combox box then the "code" will list all the
parts from machinex table.

All of the different machines are in one table on their own
All the parts of machine1 are in a different table on their own
All the parts of machineX are in a different table on their own.

I know this sounds dum but it would be very nice if you could send me an
example of the code
Click to expand...


Well, to be blunt, Yes, that does sound dumb ;-)

If you really have a separate table for each machine's
parts, then you're in for a lot more trouble than this combo
box issue. You would be much better off taking time out to
understand the rudiments of normalization and then fix your
table structure before proceding any further with this
spreadsheet like structure. This is not a personal
criticism, it's the considered advice of someone with a lot
of experience in making mistakes and how much work it takes
to dig out from under them.

If you're absolutely stuck with this structure, I couldn't
begin to attempt writing code without more details about the
tables and their important fields.
 
Marshall,
I am new to Access so I have probably started wrong. I am not tied to any
defined structure. I will explain the scenario and then you might be able to
point me in the right direction

I am part of a Maintenance team in a 24/7 production facility. We have
numerous and varied pieces of equipment that we maintain. This Database is
going to be a means of recording changes made to any one machine. Each
machine is unique and can vary from an Air Handling Unit to a high speed
Pouching machine. I want to allow up to 10 different people create an entry
in the database and these 2 dependant combo boxes were meant to make life
easier for the technicians entering the record and easier pinpoint
troublesome parts in machines.

If in 2 years time I cannot get a certain part of a certain machine to work
then I can query this database to see if anyone else had similar problems to
me and had entered a solution in the database.

If you can give me a starting point to be working with which would prevent
more hassle down the line for me I would appreciate it. . .

Anything would help.
 
Richard said:
I am new to Access so I have probably started wrong. I am not tied to any
defined structure. I will explain the scenario and then you might be able to
point me in the right direction

I am part of a Maintenance team in a 24/7 production facility. We have
numerous and varied pieces of equipment that we maintain. This Database is
going to be a means of recording changes made to any one machine. Each
machine is unique and can vary from an Air Handling Unit to a high speed
Pouching machine. I want to allow up to 10 different people create an entry
in the database and these 2 dependant combo boxes were meant to make life
easier for the technicians entering the record and easier pinpoint
troublesome parts in machines.

If in 2 years time I cannot get a certain part of a certain machine to work
then I can query this database to see if anyone else had similar problems to
me and had entered a solution in the database.
Click to expand...


I strongly suggest that you get a book on Access. Almost
any of them will cover the basics of data normalization,
which is the core of how to determine which tables you need
and what fields belong in which table. The first 3 Normal
Forms are essential to designing a useful database (the
others can be interesting but are a little beyond everyday
Access programs). A summary generalization of those rules
that I find useful is:

Whenever any value needs to be changed, it can
be done by editing a single field in a single table.

To get you started, think of each table as a model of
whatever you have to deal with as an entity. Obviously, you
have a machines entity, a Parts entity, and a repairs
entity. If a part might be used in more than one machine,
you will also need another entity to represent a machine's
parts.

These tables will be related in one-to-many realtionships.
This means that the many side table must have a field
(called a foreign key) that contains the value of its parent
record's primary key. For example, each part is identified
by a unique identifyer (probably a part number) and each
repair record would also have the part number that was
repaired. OK, I know, that sounds like the same data in
multiple places, but that rule can be ignored for foreign
keys or you can use an AutoNumber field as a surrogate key
in the parts table and refer to that in the foreign key.
Since AutoNumber fields should never be exposed to your
users, they will never be a need to change it and the rule
will still hold.

The thing I was objecting to earlier is that you had a
separate table for the parts in each machine. Instead you
should have a single table for parts with a foreign key
field to tell you which machine it is used in. This will
greatly simplify your question about the dependent combo box
by allowing the parts combo box to just filter its records
by matching its machine foreign key to the machine ID
selected in the first combo box.

Newsgroups are not an appropriate means to conduct tutorials
like this, but when you get a handle on this stuff come on
back with specific questions. I suspect that parts can be
used in multiple machines so I think we'll expect a question
or two on how to deal with that scenario.
 
Back
Top