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
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
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?
On May 7, 1:35 pm, Ken Sheridan
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?
SELECT SubClass
FROM SubClasses <------------------------------------
WHERE Class = Forms!YourForm!SubClass
ORDER BY SubClass;
On May 6, 4:28 pm, Ken Sheridan
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:
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:
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
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?
