synchronize combo boxes problem

[cronept]

I am making a form for sales person input all the detailed info about
an elevator. There are maybe more than 100 data for one unit. i.e. :
elevator type,capacity, speed, safty codes, what kind of operator etc.

I want synchronize some combo boxes. For instance, when I choose
elevator type from the 1st combo box, say, I choose type A, then
I can only select 1000, 2000, 3000, 4000 lbs from the 2nd combo box
"capacity"; if i select type B, then I can only choose 1000,2000 lbs
from the capacity combo box; If I select "c", then capacity will be
1000, 2000,3000,4000,5000 lbs. As You can see, there are always some
same data in different catagories, i.e. 1000, 2000 lbs.

I saw the articles on Microsoft's website. MS's solutions are only for
different data in every catagory. Could you please give me some
suggestions? Thank you.

Jin

 

[Van T. Dinh]

How do you store the data in the sytem so that the system knows that type A
has 1K, 2K, 3K, 4K, type B has 1K, 2K and type C has 1K,2K, 3K, 4K, 5K?

Van T. Dinh
MVP (Access)

 

[cronept]

Hi, Van,

This is exactly what i want to know. Most data selections in my
database are entered by Value list, not from Table/Query.I left the
capacity item because I have no idea how to do with it. As I said, I
saw many articles from Microsoft's website talking about how to
synchronize 2 combo boxes. But most of these samples are not useful to
my case.

Acctually, not only "capacity" in my database has the problem. I have
other combo boxes which should synchronized with "elevator type". When
choosing "type A", the operator should be LVK or GAL, when "type B",
the operator should be GAL and MIT.

I am thinking of this for a long time and no solutions. I am an Access
Beginner and know just a little bit about VBA, Macro and SQL. If it
is convinient to you, please give me some detailed answers or
suggestions. Thank you very much.

Jin

 

[Van T. Dinh]

Most Access books explain ComboBox using a Table / Query as a RowSource
pretty well and there is no chance I can explain as much in newsgroup.

Look up any Access book and you shall find ... (I hope you got some Access
books since it hard to use Access without books!)

If you are not familiar with using Table / Query as the RowSource, try a
ComboBox without synchronising first to see what happens then try
synchronising.

 

[fflei]

Perhaps what you need to do is to fool Access into thinking that each
category does have different data.

I actually do have different data, but the method would work for you.

I have two tables, Category and Sub-Category.
Category has CATID (Primary Key) CategoryRef and CategoryName fields
Combo box 1 draws it's references from here.

SubCategory has SUBCATID (Primary Key), CatID, SubCatRef, Description
Combo box 2 draws from here, so you must list the info you want for each of
the categories,
So, supposing if CategoryA has an ID of 1, then you must list each amount
you want as a seperate record, but put 1 in the CatID field, and so on for
all the other types.

Then you can use the instructions given in the Microsoft Knowledge Base
Article - 209595

fflei

 

[fflei]

Hello Jin,

What I had was 3 tables, one of which is the one that the form stores the
information in, and the other two are there to 'feed' the combo boxes.

So...

Table 1 is tblCategories. This has three fields, CatID, which is an
AutoNumber field, CatRef, which is my unique reference no for each Category,
and CatName which is the text.

Table 2 is tbleSubCategories. This has four fields, SubCatID, which is an
AutoNumber field, CatID, which is links with the CatID filed in the other
table, SubCatRef which is my unique reference for each sub category, and
SubCatName which is the text.

You have now to enter the sub category data. Whilst each SubCat ID is
unique, the CatID, SubCatRef and SubCatName are not necessarily.... but each
sub Category is associated with a Category, eg

SubCatID CatID SubCatRef SubCatName
1 1 01 1000
2 1 02 2000
3 1 03 3000
4 2 01 1000
5 2 02 2500
6 2 03 3000
7 2 04 4000
8 3 01 2000
9 3 02 4000

Do you see what I mean? Even if a value is relevant to more than one
category, it has to have its own entry in order for it to be associated with
that category.

Table 3 is tblMain which is what the form writes to and has other related
information and this is bound to my Form.

fflei.