Combobox question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ryan.fitzpatrick3@safeway.com - 06 May 2008 23:06 GMT

I have 2 combo boxes where in the first one I have a choice of
selections. Then in the 2nd combo box i'd like the filter selections
that pertain to the first choice. I have the tables linked in the
relationship.

combobox1 is called Class
combobox2 is called SubClass

I've done this from combobox to listbox and this has worked fine. But
it's not working for me from combo to combo. Any help? Do you need
more information from me?

Ryan

Reply to this Message

Ken Sheridan - 07 May 2008 00:28 GMT

Ryan:

For the sub-class combo box's RowSource property use a query which
references the Class combo box, e.g.

SELECT SubClass
FROM SubClasses
WHERE Class = Forms!YourForm!SubClass
ORDER BY SubClass;

And requery the SubClass control in the Class control's AfterUpdate event
procedure with:

Me.SubClass.Requery

Two things to note:

1. In a single form this will work fine, but in a continuous form it will
not do so if you are using 'surrogate' keys rather than the 'natural' keys,
i.e. if the bound column of the SubClass control was a hidden SubClassID
value rather than the visible SubClass value. In a continuous form when you
select a class in one row the sub-classes in other rows with a different
class selected will be blanked out. The values will still be in the
underlying SubClassID field, but you won't see the corresponding Class values.

2. If you have columns for both Class and SubClass in the form's underlying
table the table is not properly normalized. Class is implied by SubClass so
is redundant, and leaves the table open to inconsistent data being entered.
Even though your use of correlated combo boxes is designed to prevent this,
it is still possible for an invalid class/sub-class combination to be entered
by other means.

You'll find a demo database which addresses both of these issues at:

http://community.netscape.com/n/pfx/forum.aspx?nav=libraryMessages&tsn=1&tid=236
26&webtag=ws-msdevapps

Ken Sheridan
Stafford, England

> I have 2 combo boxes where in the first one I have a choice of
> selections. Then in the 2nd combo box i'd like the filter selections
[quoted text clipped - 9 lines]
>
> Ryan

Reply to this Message

ryan.fitzpatrick3@safeway.com - 07 May 2008 16:34 GMT

Thanks for the reply. Where does the subclasses come from?

SELECT SubClass
FROM SubClasses <------------------------------------
WHERE Class = Forms!YourForm!SubClass
ORDER BY SubClass;

On May 6, 4:28 pm, Ken Sheridan
<KenSheri...@discussions.microsoft.com> wrote:

> Ryan:
>
[quoted text clipped - 49 lines]
>
> > Ryan

Reply to this Message

Ken Sheridan - 07 May 2008 21:35 GMT

Ryan:

You should, if your tables are properly normalized have a table Classes,
with a primary key column Class, and a table SubClasses, with a primary key
column SubClass and a foreign key column Class referencing the primary key of
Classes. The combo box's RowSource property is a query on the SubClasses
table.

The form will be based on another table which contains, if properly
normalized a column SubClass referencing the primary key of SubClasses, and
this column will be the one to which the SubClass combo box is bound. If not
properly normalized the table will also contain a column Class to which the
Class combo box will be bound. With a properly normalized table this combo
box would be unbound, and set up in the way the file for which I gave you the
link, demonstrates.

Ken Sheridan
Stafford, England

> Thanks for the reply. Where does the subclasses come from?
>
[quoted text clipped - 58 lines]
> >
> > > Ryan

Reply to this Message

ryan.fitzpatrick3@safeway.com - 07 May 2008 22:10 GMT

I have three tables 1st tblClass, 2nd tblCategories, 3rd tblGroup

In tblClass theres the autonumber and theres the class(of item) it
looks like

ClassID(autonum) class (text field)
1 flour
2 Oil
3 Rice

the 2nd table is tblCategories. All of these tables are linked in
relationships. It looks like

CategorieID(autonum) CategoryName(text) ClassID (Numeric field)
4 RETAIL 1 (FLOUR)
5 ISB 1 (FLOUR)
6 RETAIL 2 (OIL)
7 ISB 2 (OIL)
8 RETAIL 3 (RICE)

The 3rd table is laid out the same way

GroupID(autonum) GroupName(text) CategorieID(Numeric field)
9 ORGANIC 1 (RETAIL)
10 WHITE 1 (ISB)
11 CANOLA 2 (RETAIL)
12 SOYBEAN 2 (RETAIL)
13 CORN 2 (RETAIL)
14 CALROSE 3 (RICE)

The links are numeric to numeric and I think correct. Is there
anything else you need?

On May 7, 1:35 pm, Ken Sheridan
<KenSheri...@discussions.microsoft.com> wrote:

> Ryan:
>
[quoted text clipped - 78 lines]
>
> > > > Ryan

Reply to this Message

ryan.fitzpatrick3@safeway.com - 07 May 2008 22:27 GMT

I got it to work! Since the ClassID, CategoryID and GroupID are all
numeric (autonumber) in a query under the class, categorie, group
fields in the criteria I link each respectively to the combobox of the
same name. In Categorie for example it'll put in autonumber instead of
the text "RETAIL". How do I get "RETAIL" to go in the criteria and not
the autonumber number?

Ryan

On May 7, 1:35 pm, Ken Sheridan
<KenSheri...@discussions.microsoft.com> wrote:

> Ryan:
>
[quoted text clipped - 78 lines]
>
> > > > Ryan

Reply to this Message

Ken Sheridan - 07 May 2008 23:35 GMT

Ryan:

You actually do want the numeric values to be the criteria as these are the
keys which relate the tables to each other. You never need see these values,
however, only the text values, for which you need to set up your combo boxes
to hide the bound numeric columns. I'm assuming the form's underlying table
is based on a table which is not fully normalized and thus includes columns
ClassID, CategorieID and GroupID, with the combo boxes, which I'll call
cboClass, cboCategory and cboGroup below so change the names where necessary
to your actual ones, bound to these columns. Also change YourForm to the
actual name of your form, remembering to enclose it in brackets [like this]
if the name includes spaces or other special characters.

So for the cboClass combo box:

RowSource: SELECT ClassID, Class FROM tblClass ORDER BY Class;

ControlSource: ClassID
BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

For the cboCategory combo box:

RowSource: SELECT CategorieID, CategoryName FROM tblCategories WHERE
ClassID = Forms!YourForm!cboClass ORDER BY CategoryName;

ControlSource: CategorieID
BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

And for the cboGroup combo box:

RowSource: SELECT GroupID, GroupName FROM tblGroups WHERE CategorieID =
Forms!YourForm!cboCategory ORDER BY GroupName;

ControlSource: GroupID
BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

In cboClass's After Update event procedure put:

Me.cboCategory = Null
Me.cboCategory.Requery

and in cboCategory's AfterUpdate event procedure put:

Me.cboGroup = Null
Me.cboGroup.Requery

And in the form's Current event procedure requery both with:

Me.cboCategory.Requery
Me.cboGroup.Requery

In each case by setting the first dimension of the ColumnWidths property to
zero the numeric ID column is hidden so you just see the second column with
the text values. The numeric values are meaningless to the user, so there is
no point in exposing them, but they need to be the values of the combo boxes,
which is determined by the BoundColumn property, in each case 1, so the
values are in fact the hidden numeric values, which is as it should be.

These numeric columns are what I was referring to when I mentioned
'surrogate' keys, so while the above will work fine in single form view, if
you try to do it in continuous form view you'll find it won't work. The
values in one or both of the category and group combo boxes in non-current
rows will disappear if the class or category is different from the current
row. The data won't be lost; you just won't see it in the form. If you do
need to do this in continuous form view you'll need to change the keys to
'natural' ones, i.e. the text columns and dump the numeric primary and
foreign key columns, or use a 'hybrid' control as demonstrated in my file at
the link I gave you. That also uses a 3-tier hierarchy, so is directly
applicable to your scenario. It also uses normalized tables, so avoids the
possible pitfalls from which a non-normalized design suffers.

Ken Sheridan
Stafford,

> I got it to work! Since the ClassID, CategoryID and GroupID are all
> numeric (autonumber) in a query under the class, categorie, group
[quoted text clipped - 89 lines]
> >
> > > > > Ryan

Reply to this Message

ryan.fitzpatrick3@safeway.com - 08 May 2008 16:52 GMT

I got the combo boxes to work, i found out that I had the row source
in the cboxclass wrong and that was the whole issue. Now that this
works I used get-let statements to go into a query, where the
selection in the combo boxes goes right into a query I have set up.
Here is the get-let code. Maybe it's not correct. I have one for each
of the combo boxes but I'll list one.

Option Compare Database
Option Explicit
Private mstrcboxClass As String
Public Function GetcboxClass() As String
On Error GoTo HandleErr
Const cstrProcName As String = "modLetGet - GetcboxClass"
GetcboxClass = mstrcboxClass
ExitHere:
Exit Function
HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbCritical, cstrProcName
End Select
End Function

Public Sub LetcboxClass(ByVal pdteVData As String)
On Error GoTo HandleErr
Const cstrProcName As String = "modLetGet - LetcboxClass"
mstrcboxClass = pdteVData
ExitHere:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbCritical, cstrProcName
End Select
End Sub

here is the SQL of the query.

SELECT QrySSIMSAllitemswithAllVendors.CORP,
QrySSIMSAllitemswithAllVendors.DIVISION,
QrySSIMSAllitemswithAllVendors.FACILITY,
QrySSIMSAllitemswithAllVendors.DST_CNTR,
tblSSIMSItemNumbers.ItemNumber,
QrySSIMSAllitemswithAllVendors.CORP_ITEM_CD,
tblSSIMSItemNumbers.Class, tblSSIMSItemNumbers.Categorie,
tblSSIMSItemNumbers.Group, QrySSIMSAllitemswithAllVendors.DESC_ITEM,
QrySSIMSAllitemswithAllVendors.VEND_NUM,
QrySSIMSAllitemswithAllVendors.VEND_SUB_ACNT,
QrySSIMSAllitemswithAllVendors.NAME, [Qry(1c)POReceivings].AMT_RECV,
QrySSIMSAllitemswithAllVendors.SIZE_NUM,
QrySSIMSAllitemswithAllVendors.SIZE_UOM, IIf([SIZE_UOM]="OZ",
[SIZE_NUM]/16,[SIZE_NUM]) AS OZTOLBCONV,
QrySSIMSAllitemswithAllVendors.PACK_WHSE,
[AMT_RECV]*[PACK_WHSE]*[OZTOLBCONV] AS TOTALVOL,
QrySSIMSAllitemswithAllVendors.UNITCOST,
QrySSIMSAllitemswithAllVendors.PERUNITCOST, [PERUNITCOST]/[OZTOLBCONV]
AS LBPRICE, QrySSIMSAllitemswithAllVendors.COST_IB,
[LBPRICE]*[TOTALVOL] AS SPEND, [Qry(1c)POReceivings].LAST_FM_DATE
FROM (tblSSIMSItemNumbers INNER JOIN QrySSIMSAllitemswithAllVendors ON
tblSSIMSItemNumbers.ItemNumber =
QrySSIMSAllitemswithAllVendors.CORP_ITEM_CD) INNER JOIN
[Qry(1c)POReceivings] ON (QrySSIMSAllitemswithAllVendors.CORP =
[Qry(1c)POReceivings].CORP) AND
(QrySSIMSAllitemswithAllVendors.DIVISION =
[Qry(1c)POReceivings].DIVISION) AND
(QrySSIMSAllitemswithAllVendors.FACILITY =
[Qry(1c)POReceivings].FACILITY) AND
(QrySSIMSAllitemswithAllVendors.CORP_ITEM_CD =
[Qry(1c)POReceivings].CORP_ITEM_CD)
WHERE (((QrySSIMSAllitemswithAllVendors.CORP)="001") AND
((QrySSIMSAllitemswithAllVendors.DIVISION)=getcboxdivision()) AND
((tblSSIMSItemNumbers.Class)=getcboxclass()) AND
((tblSSIMSItemNumbers.Categorie)=getcboxcategories()) AND
((tblSSIMSItemNumbers.Group)=getcboxgroup()) AND
(([Qry(1c)POReceivings].LAST_FM_DATE) Between getDateBeg() And
getDateend()));

The issue I think I'm having is when I run the query it comes up as an
error#. I think the combo boxes reflect text i.e. "FLOUR", "RETAIL",
"ORGANIC" but what goes into the query from the get-let is the numeric
value of each the autonumber. If it's not that then I have no clue why
it's not working properly. I just need to get the values of those 3
combo boxes in the query, so when I run it, it'll pull the correct
information. Thanks for your help.

Ryan

On May 7, 3:35 pm, Ken Sheridan
<KenSheri...@discussions.microsoft.com> wrote:

> Ryan:
>
[quoted text clipped - 175 lines]
>
> > > > > > Ryan

Reply to this Message

Ken Sheridan - 08 May 2008 19:11 GMT

Ryan:

It looks to me like the problem fundamentally stems from your storing the
text values of Class, Categorie and Group in the tblSSIMSItemNumbers table.
As the primary keys of the referenced tables are autonumber columns, however,
foreign key columns in a referencing table should be long integer numbers
which correspond to the numeric values of the keys of the referenced tables.
You'll then be able to reference the combo boxes directly in the query as
their values are the hidden first columns' values if they are set up in the
way I described.

If you wish you can assign the values to module level variables as you have
done and examine them via functions, but the only advantage I'd see in this
id if the form is being closed before opening the query and thus making the
values of the combo boxes no longer directly available to the query.

Ken Sheridan
Stafford, England

> I got the combo boxes to work, i found out that I had the row source
> in the cboxclass wrong and that was the whole issue. Now that this
[quoted text clipped - 266 lines]
> >
> > > > > > > Ryan

Reply to this Message

ryan.fitzpatrick3@safeway.com - 08 May 2008 19:45 GMT

They referencing column is a long integer. I put a textbox on a form
and linked it the first combo box. When I selected 'FLOUR' in the
textbox "1" came up, the autonumb. Any other way to get the text which
would be column 2 in my example above in the query so the query reads
'FLOUR' and not '1'?

On May 8, 11:11 am, Ken Sheridan
<KenSheri...@discussions.microsoft.com> wrote:

> Ryan:
>
[quoted text clipped - 232 lines]
>
> read more »

Reply to this Message

Ken Sheridan - 08 May 2008 21:22 GMT

Ryan:

To return the text values in the query join the three referenced tables
either to the to the QrySSIMSAllitemswithAllVendors in the final query, or
within QrySSIMSAllitemswithAllVendors and in the final query return the text
columns from the referenced tables.

Ken Sheridan
Stafford, England

> They referencing column is a long integer. I put a textbox on a form
> and linked it the first combo box. When I selected 'FLOUR' in the
[quoted text clipped - 240 lines]
> >
> > read more »

Reply to this Message