dependant combo boxes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

davidhull - 14 May 2007 13:47 GMT

Hi could someone please help me with a small problem i have encountered
i have 2 combo boxes on a form

combo1 - software,hardware,servers,misc
combo2 - Office, capture, navision,monitor, laptop,desktop,exchange,dca,dcb

what i want to do is if i select software then office,capture,navision will
show on the other combo box, the other options will not be visible

what is the easiest way to perform this action (go easy im still learning)
i have typed in the values i want to display in both options
would a table be better??

Reply to this Message

Arvin Meyer [MVP] - 14 May 2007 14:05 GMT

Tables are always better for storing values which can change. In the future,
you may want to enter Power Supplies to combo1.

The table for combo1 could easily have a column for Type, so that it could
then be based on a query instead of a table. The query will easily limit the
values in combo2:

Select * From tblDevices Where Type = Forms!FormName!combo1;

Signature

Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

> Hi could someone please help me with a small problem i have encountered
> i have 2 combo boxes on a form
[quoted text clipped - 10 lines]
> i have typed in the values i want to display in both options
> would a table be better??

Reply to this Message

davidhull - 14 May 2007 15:34 GMT

Please be patient but dont quite get what you are saying

should i ctreate a table with a field called type containing software,
hardware, servers etc then another field beside this called desc containing
navision, office, capture etc

then create a combo box 1 based on the sql query you have given me??
what do i base the combo box 2 on ?? the query? values from desc?
find record based on the value i select in combo?

which option

> Tables are always better for storing values which can change. In the future,
> you may want to enter Power Supplies to combo1.
[quoted text clipped - 18 lines]
> > i have typed in the values i want to display in both options
> > would a table be better??

Reply to this Message

scubadiver - 14 May 2007 16:17 GMT

On my database I have "dept" and "subdept".

The first combo is based on the "dept" table
The second combo is based on a query with a filter that is linked to the
first combo.

Field1: "Subdept" (shown)
Field2: "Dept" (not shown)

In the after update event of the first combo you will need

me.[second combo].requery

And do a search for "cascading combo". There are plenty of threads.

> Please be patient but dont quite get what you are saying
>
[quoted text clipped - 34 lines]
> > > i have typed in the values i want to display in both options
> > > would a table be better??

Reply to this Message

Arvin Meyer [MVP] - 15 May 2007 15:04 GMT

Create 2 tables:

tblTypes
TypeID - autonumber Primary Key
Type - Text

in this table, you'll have 1 record for each type:

TypeID Type
1 Software
2 Hardware

tblItems
ItemID - autonumber Primary Key
Item - Text
TypeID - Number - Long Integer - Foreign Key to tblTypes
... other fields, as required

combo1 is based on tblTypes, combo2 based on tblItems.

Signature

Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

> Please be patient but dont quite get what you are saying
>
[quoted text clipped - 35 lines]
>> > i have typed in the values i want to display in both options
>> > would a table be better??

Reply to this Message