Stacked unbound combo boxes

L

Len B

I have tblEquip including fields EquipNum, ProdCode, Location etc.

I have tblProdCode with some sample data as follows (PK omitted here)-

ProdCode|ProdType |Class1|Class2 |Class3 |Class4 |
--------|---------|------|---------|----------|--------|
82111 |Furniture|Beds |Electric |Hi-lo tilt|Invacare|
82120 |Furniture|Beds |Electric |Hi-lo | |
82210 |Furniture|Beds |Hydraulic|Hi-lo tilt| |
82220 |Furniture|Beds |Hydraulic|Hi-lo | |
82300 |Furniture|Beds |Mobile | | |
81110 |Furniture|Cots |Hospital |High sides| |
81120 |Furniture|Cots |Hospital |Low sides | |
--------------------------------------------------------

On a form, RecordSource is tblEquip, I have a stack of 5 unbound combos.
ProdType whose RecordSource is a qry selecting all distinct ProdTypes
Class1 whose RS qry selects distinct Class1s matching ProdType choice.
::
Class4 whose RS qry selects distinct Class4s to match cboClass3 choice.

The user can select (say) Furniture then Cots then Hospital ... and then
81110 is plugged into the form's txtProdCode.

Q1 Is there a way that I can populate the combos in OnCurrent?
Or should I superimpose some matching text boxes and populate those,
alternately making combos and text boxes visible as appropriate?
Q2 Should I extend the WHERE clauses for the Classes to limit rows to
data matching ALL preceding choices or maybe 2 preceding choices.

TIA
 
M

Maha Arupputhan Pappan

Hi Len,

Good day.

I did the following and worked.

1. Inserted 4 combo boxes:
1.A) cboProdType
- Control Source: none
- Row Source:
SELECT tblProdCode.ProdType
FROM tblProdCode
GROUP BY tblProdCode.ProdType;
1.B) cboClass1
- Control Source: none
- Row Source:
SELECT tblProdCode.Class1
FROM tblProdCode
GROUP BY tblProdCode.Class1;
1.C) cboClass2
- Control Source: none
- Row Source:
SELECT tblProdCode.Class2
FROM tblProdCode
GROUP BY tblProdCode.Class2;
- In the "On Change" event, insert: Me!cboClass3.Requery
1.D) cboClass3
- Control Source: none
- Row Source:
SELECT tblProdCode.Class3, tblProdCode.ProdCode
FROM tblProdCode
WHERE
(((tblProdCode.ProdType)=[Forms]![Form1]![cboProdType]) AND
((tblProdCode.Class1)=[Forms]![Form1]![cboClass1]))
GROUP BY tblProdCode.Class3, tblProdCode.ProdCode;

- In the "On Click" event, insert: Me!txtProdCode =
me!cboClass3.Column(2)
- Column Count: 2
- Column Widths: 1";0
- List Width: 1"

2. Inserted 1 text box and call it txtProdCode

Save the form and try. Should work. Hope this helps you.
 
L

Len B

Thank you so much Maha. I really appreciate the work that you have put into
this.
I created a new Form1 with the attributes you said to test it. However it
doesn't
work as desired. I hope I haven't made a typo somewhere although I fixed
all of
the errors it complained of.1.A works fine but 1.B and later display every
item in
that column rather than restrict the list to those that correspond to the
choice(s) in
the combo(s) above it. Apart from that, when I click the record selector to
go to the
next record the combos do not update but continue to display the previous
data.

I have figured Row Sources for the 5 combos and they seem to work. In my
first
post I mistakenly called the table tblProdCode when it really is Products.
Here
are the Row Sources I have and they seem to work. However, moving from
record
to record does not cause the combos to update (because they are unbound I
think).

SELECT DISTINCT Products.ProdType
FROM Products
ORDER BY Products.ProdType;

SELECT DISTINCT Products.Class1
FROM Products
WHERE ((Products.ProdType=[cboProdType]) AND
(Products.Class1 Is Not Null) )
ORDER BY Products.Class1;

SELECT DISTINCT Products.Class2
FROM Products
WHERE ((Products.ProdType=[cboProdType]) AND
(Products.Class1=[cboClass1]) AND
(Products.Class2 Is Not Null) )
ORDER BY Products.Class2;

SELECT DISTINCT Products.Class3
FROM Products
WHERE ((Products.ProdType=[cboProdType]) AND
(Products.Class1=[cboClass1]) AND
(Products.Class2=[cboClass2]) AND
(Products.Class3 Is Not Null) )
ORDER BY Products.Class3;

SELECT DISTINCT Products.Class4
FROM Products
WHERE ((Products.ProdType=[cboProdType]) AND
(Products.Class1=[cboClass1]) AND
(Products.Class2=[cboClass2]) AND
(Products.Class3=[cboClass3]) AND
(Products.Class4 Is Not Null) )
ORDER BY Products.Class4;

I have other procedures attached to OnEnter and AfterUpdate of each combo.
The OnEnter clears all lower combos and makes them visible.
The AfterUpdate checks for Null in next column and if so makes all lower
combos invisible and jumps to the next input field.

None of this has any effect when moving from record to record. Combos do
not
reflect the descriptions attached to each ProdCode. I have unsuccessfully
tried
a few things.

On Form1 I added some text boxes and used DLookup to populate them.
I used DLookup("Class2","Products","ProdCode = '" & [txtProdCode] & "' ")
That worked. I guess I'll just superimpose them over the combos.

Thanks again Maha for your effort.
--
Len
______________________________________________________
remove nothing for valid email address.
| Hi Len,
|
| Good day.
|
| I did the following and worked.
|
| 1. Inserted 4 combo boxes:
| 1.A) cboProdType
| - Control Source: none
| - Row Source:
| SELECT tblProdCode.ProdType
| FROM tblProdCode
| GROUP BY tblProdCode.ProdType;
| 1.B) cboClass1
| - Control Source: none
| - Row Source:
| SELECT tblProdCode.Class1
| FROM tblProdCode
| GROUP BY tblProdCode.Class1;
| 1.C) cboClass2
| - Control Source: none
| - Row Source:
| SELECT tblProdCode.Class2
| FROM tblProdCode
| GROUP BY tblProdCode.Class2;
| - In the "On Change" event, insert: Me!cboClass3.Requery
| 1.D) cboClass3
| - Control Source: none
| - Row Source:
| SELECT tblProdCode.Class3, tblProdCode.ProdCode
| FROM tblProdCode
| WHERE
| (((tblProdCode.ProdType)=[Forms]![Form1]![cboProdType]) AND
| ((tblProdCode.Class1)=[Forms]![Form1]![cboClass1]))
| GROUP BY tblProdCode.Class3, tblProdCode.ProdCode;
|
| - In the "On Click" event, insert: Me!txtProdCode =
| me!cboClass3.Column(2)
| - Column Count: 2
| - Column Widths: 1";0
| - List Width: 1"
|
| 2. Inserted 1 text box and call it txtProdCode
|
| Save the form and try. Should work. Hope this helps you.
|
| --
| Maha Aruppthan Pappan
| Nacap Asia Pacific (Thailand) Co., Ltd.
|
|
| "Len B" wrote:
|
| > I have tblEquip including fields EquipNum, ProdCode, Location etc.
| >
| > I have tblProdCode with some sample data as follows (PK omitted here)-
| >
| > ProdCode|ProdType |Class1|Class2 |Class3 |Class4 |
| > --------|---------|------|---------|----------|--------|
| > 82111 |Furniture|Beds |Electric |Hi-lo tilt|Invacare|
| > 82120 |Furniture|Beds |Electric |Hi-lo | |
| > 82210 |Furniture|Beds |Hydraulic|Hi-lo tilt| |
| > 82220 |Furniture|Beds |Hydraulic|Hi-lo | |
| > 82300 |Furniture|Beds |Mobile | | |
| > 81110 |Furniture|Cots |Hospital |High sides| |
| > 81120 |Furniture|Cots |Hospital |Low sides | |
| > --------------------------------------------------------
| >
| > On a form, RecordSource is tblEquip, I have a stack of 5 unbound
combos.
| > ProdType whose RecordSource is a qry selecting all distinct ProdTypes
| > Class1 whose RS qry selects distinct Class1s matching ProdType
choice.
| > ::
| > Class4 whose RS qry selects distinct Class4s to match cboClass3
choice.
| >
| > The user can select (say) Furniture then Cots then Hospital ... and
then
| > 81110 is plugged into the form's txtProdCode.
| >
| > Q1 Is there a way that I can populate the combos in OnCurrent?
| > Or should I superimpose some matching text boxes and populate
those,
| > alternately making combos and text boxes visible as appropriate?
| > Q2 Should I extend the WHERE clauses for the Classes to limit rows to
| > data matching ALL preceding choices or maybe 2 preceding choices.
| >
| > TIA
| >
| > --
| > Len
| > ______________________________________________________
| > remove nothing for valid email address.
| >
| >
| >
 
M

Maha Arupputhan Pappan

Glad that you made it work. A piece of advise. DLookup will slow your system
in the long run. Just be cautious. Try not to use to many DLookup in your
coding.

--
Maha Aruppthan Pappan
Nacap Asia Pacific


Len B said:
Thank you so much Maha. I really appreciate the work that you have put into
this.
I created a new Form1 with the attributes you said to test it. However it
doesn't
work as desired. I hope I haven't made a typo somewhere although I fixed
all of
the errors it complained of.1.A works fine but 1.B and later display every
item in
that column rather than restrict the list to those that correspond to the
choice(s) in
the combo(s) above it. Apart from that, when I click the record selector to
go to the
next record the combos do not update but continue to display the previous
data.

I have figured Row Sources for the 5 combos and they seem to work. In my
first
post I mistakenly called the table tblProdCode when it really is Products.
Here
are the Row Sources I have and they seem to work. However, moving from
record
to record does not cause the combos to update (because they are unbound I
think).

SELECT DISTINCT Products.ProdType
FROM Products
ORDER BY Products.ProdType;

SELECT DISTINCT Products.Class1
FROM Products
WHERE ((Products.ProdType=[cboProdType]) AND
(Products.Class1 Is Not Null) )
ORDER BY Products.Class1;

SELECT DISTINCT Products.Class2
FROM Products
WHERE ((Products.ProdType=[cboProdType]) AND
(Products.Class1=[cboClass1]) AND
(Products.Class2 Is Not Null) )
ORDER BY Products.Class2;

SELECT DISTINCT Products.Class3
FROM Products
WHERE ((Products.ProdType=[cboProdType]) AND
(Products.Class1=[cboClass1]) AND
(Products.Class2=[cboClass2]) AND
(Products.Class3 Is Not Null) )
ORDER BY Products.Class3;

SELECT DISTINCT Products.Class4
FROM Products
WHERE ((Products.ProdType=[cboProdType]) AND
(Products.Class1=[cboClass1]) AND
(Products.Class2=[cboClass2]) AND
(Products.Class3=[cboClass3]) AND
(Products.Class4 Is Not Null) )
ORDER BY Products.Class4;

I have other procedures attached to OnEnter and AfterUpdate of each combo.
The OnEnter clears all lower combos and makes them visible.
The AfterUpdate checks for Null in next column and if so makes all lower
combos invisible and jumps to the next input field.

None of this has any effect when moving from record to record. Combos do
not
reflect the descriptions attached to each ProdCode. I have unsuccessfully
tried
a few things.

On Form1 I added some text boxes and used DLookup to populate them.
I used DLookup("Class2","Products","ProdCode = '" & [txtProdCode] & "' ")
That worked. I guess I'll just superimpose them over the combos.

Thanks again Maha for your effort.
--
Len
______________________________________________________
remove nothing for valid email address.
| Hi Len,
|
| Good day.
|
| I did the following and worked.
|
| 1. Inserted 4 combo boxes:
| 1.A) cboProdType
| - Control Source: none
| - Row Source:
| SELECT tblProdCode.ProdType
| FROM tblProdCode
| GROUP BY tblProdCode.ProdType;
| 1.B) cboClass1
| - Control Source: none
| - Row Source:
| SELECT tblProdCode.Class1
| FROM tblProdCode
| GROUP BY tblProdCode.Class1;
| 1.C) cboClass2
| - Control Source: none
| - Row Source:
| SELECT tblProdCode.Class2
| FROM tblProdCode
| GROUP BY tblProdCode.Class2;
| - In the "On Change" event, insert: Me!cboClass3.Requery
| 1.D) cboClass3
| - Control Source: none
| - Row Source:
| SELECT tblProdCode.Class3, tblProdCode.ProdCode
| FROM tblProdCode
| WHERE
| (((tblProdCode.ProdType)=[Forms]![Form1]![cboProdType]) AND
| ((tblProdCode.Class1)=[Forms]![Form1]![cboClass1]))
| GROUP BY tblProdCode.Class3, tblProdCode.ProdCode;
|
| - In the "On Click" event, insert: Me!txtProdCode =
| me!cboClass3.Column(2)
| - Column Count: 2
| - Column Widths: 1";0
| - List Width: 1"
|
| 2. Inserted 1 text box and call it txtProdCode
|
| Save the form and try. Should work. Hope this helps you.
|
| --
| Maha Aruppthan Pappan
| Nacap Asia Pacific (Thailand) Co., Ltd.
|
|
| "Len B" wrote:
|
| > I have tblEquip including fields EquipNum, ProdCode, Location etc.
| >
| > I have tblProdCode with some sample data as follows (PK omitted here)-
| >
| > ProdCode|ProdType |Class1|Class2 |Class3 |Class4 |
| > --------|---------|------|---------|----------|--------|
| > 82111 |Furniture|Beds |Electric |Hi-lo tilt|Invacare|
| > 82120 |Furniture|Beds |Electric |Hi-lo | |
| > 82210 |Furniture|Beds |Hydraulic|Hi-lo tilt| |
| > 82220 |Furniture|Beds |Hydraulic|Hi-lo | |
| > 82300 |Furniture|Beds |Mobile | | |
| > 81110 |Furniture|Cots |Hospital |High sides| |
| > 81120 |Furniture|Cots |Hospital |Low sides | |
| > --------------------------------------------------------
| >
| > On a form, RecordSource is tblEquip, I have a stack of 5 unbound
combos.
| > ProdType whose RecordSource is a qry selecting all distinct ProdTypes
| > Class1 whose RS qry selects distinct Class1s matching ProdType
choice.
| > ::
| > Class4 whose RS qry selects distinct Class4s to match cboClass3
choice.
| >
| > The user can select (say) Furniture then Cots then Hospital ... and
then
| > 81110 is plugged into the form's txtProdCode.
| >
| > Q1 Is there a way that I can populate the combos in OnCurrent?
| > Or should I superimpose some matching text boxes and populate
those,
| > alternately making combos and text boxes visible as appropriate?
| > Q2 Should I extend the WHERE clauses for the Classes to limit rows to
| > data matching ALL preceding choices or maybe 2 preceding choices.
| >
| > TIA
| >
| > --
| > Len
| > ______________________________________________________
| > remove nothing for valid email address.
| >
| >
| >
Click to expand...
 
Top