Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / New Users / July 2006

Tip: Looking for answers? Try searching our database.

Multiple dependent combo boxes

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
vroedie@gmail.com - 31 Jul 2006 09:58 GMT
Hi,

I'm currently building an Inventory DB for entering HW and users. In
the DB I have created a form to define hardware. For this form I've
created several tables where I've defined Type, Brand, Line, Model, CPU
and RAM. On the form I've made combo boxen and linked them to these
tables. Now I would like to make the choices of these boxes dependent
of each other. I think I'll manage to create a requery statement, but
I'm a bit confused on how I should build the tables.

What they look like now:

Tbl_Type:

ID
Type

Tbl_Brand
ID
Brand

Tbl_Line
ID
Line

Tbl_Model
ID
Model

Tbl_CPU
ID
CPU

Tbl_Ram
ID
Ram

What I would like to do is:

When I select a value from Type

Laptop

I want to display hardware brands of laptops

Dell
IBM

When I select a brand

Dell

I want to display only the laptop lines of this brand

Latitude
Inspiron

Then I want to display the different models of this line

D600
D610

Then follows the CPU speed and amount of RAM in the same manner.

I hope someone can enlighten me. ;)

Regards,

Jeroen
Douglas J. Steele - 31 Jul 2006 12:06 GMT
See whether http://office.microsoft.com/en-ca/assistance/HA011730581033.aspx 
is enough to get you going.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Hi,
>
[quoted text clipped - 65 lines]
>
> Jeroen
vroedie@gmail.com - 31 Jul 2006 12:28 GMT
Hi,

This helps some, but I still cannot figure out how to make a
many-to-many link.

I thought about doing it like this:

tbl_Type:

ID
Type

tbl_Brand:

ID
Brand
Type

tbl_Line
ID
Line
Brand
Type

tbl_Model
ID
Model
Line

but how can I put multiple entries for (for example) brands?

Should it be like this:

ID               Brand               Type
1                 Dell                Laptop
2                 Dell                Computer
3                 Dell                Printer
4                 Dell                Monitor

and then put the SQL query to DISTINCT so it only shows Dell once?

regards,

Jeroen

Douglas J. Steele schreef:

> See whether http://office.microsoft.com/en-ca/assistance/HA011730581033.aspx
> is enough to get you going.
[quoted text clipped - 73 lines]
> >
> > Jeroen
Douglas J. Steele - 31 Jul 2006 15:46 GMT
Strikes me that tbl_Brand should only contain ID and Brand, so that Dell and
IBM each only appear once in that table.

You'd then have a tbl_BrandType that contains what your current tbl_Brand
contains.

tbl_Line would then have:

ID               Brand               Type                   Line
1                 Dell                Laptop                 Inspiron
2                 Dell                Laptop                 Latitude
3                 Dell                Computer            Optiplex
4                 Dell                Computer            Precision

and tbl_Model would have

ID               Brand               Type                   Line
Model
1                 Dell                Laptop                 Inspiron
1300
2                 Dell                Laptop                 Inspiron
6400
3                 Dell                Laptop                 Latitude
D610
4                 Dell                Laptop                 Latitude
D620

Of course, you wouldn't actually have Brand nor Type as text fields in
tbl_Line: you'd store the Id from tbl_Brand and tbl_Type:

ID               Brand               Type                   Line
1                 1                       1
Inspiron
2                 1                       1
Latitude
3                 1                       2
Optiplex
4                 1                       2
Precision

Similarly, Brand, Type and Line wouldn't be text fields in tbl_Model. In
fact, you don't even need to store Brand and Type in that table, since you
can derive those from the tbl_Line ID:

ID               Line              Model
1                 1                  1300
2                 1                  6400
3                 2                  D610
4                 2                  D620

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Hi,
>
[quoted text clipped - 121 lines]
>> >
>> > Jeroen
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.