Query for sub categories

[denelson]

I need to know how I can query for a particular item. Here is my
scenario... I see items... balloons, flowers, cards... etc... I have a
form which i fill out to track what i sell. There is a selection called
category which listed topics like balloons, cards, flowers etc. When I
submit the form it asks if i want to add sub categories... like if i
selected flowers in the cateogry and i hit select sub categories it
will bring up a window listing things like daisy, rose, tulip... etc...
I can do a query for category (balloons, flowers, cards)... but i now
want to do a query for sub categories. I want to create a form called
"Find Specifics". When it comes up there will be a list box containing
the categories (Cards balloons flowers etc)... I will click on one (or
more than one), and hit the "submit" button. When I do a new window
will appear we will call it "What to find" which will bring me only
those sub categories related to the items i previously selected (If I
only selected flowers it will only display rose tulip etc... not
mothersday which would be under cards). When I select the items to
query I hit submit and it goes and retrieves those records for me...
Whew... this is a lot i'm sure and may have a somewhat understandable
solution... can anyone help me? Thanks!

 

[Guest]

You should have three tables to handle this model. Categories, SubCategories
and Products. SubCategories will have a Category foreign key column and
Products a SubCategory foreign key column, each referencing the primary key
of the relevant referenced table. Products should not have a Category
foreign key column as the Category for each row in Products is implicit in
its SubCategory value. On that basis:

Firstly your list box on the first form has to be multi-select so you can
select multiple categories. The submit button will iterate through the list
box's ItemsSelected collection and build a value list which can be passed to
the second form as its OpenArgs property. So the code behind the Submit
button would be along these lines:

Dim varItem As Variant
Dim strCategoryList As String
Dim ctrl As Control

Set ctrl = Me.lstCategories

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strCategoryList = strCategoryList & ",""" &
ctrl.ItemData(varItem) & """"
Next varItem

' remove leading comma
strCategoryList = Mid(strCategoryList, 2)
' open second form
DoCmd.OpenForm "frmSubcategoriesDlg", OpenArgs:=strCategoryList
End If

The second form, frmSubcategoriesDlg, will have a similar multi-select list
box of sub categories and in the form's Load event procedure the list box's
RowSource property will be set and the control requeried:

Dim strSQL AS String

srSQL = "SELECT SubCategory FROM SubCategories " & _
"WHERE Category IN(" & Me.OpenArgs & ")"
Me.lstSubCategories.RowSource = strSQL
Me.lstSubCategories.Requery

The code for the button to display the products in the selected sub
categories is essentially a repeat of the code in the first form's button,
but this time opens a form based on the Products table, filtering it to the
delected items:

Dim varItem As Variant
Dim strSubCategoryList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.lstSubCategories

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSubCategoryList = strSubCategoryList & ",""" &
ctrl.ItemData(varItem) & """"
Next varItem

' remove leading comma
strSubCategoryList = Mid(strSubCategoryList, 2)
' build criterion for filtering products form
strCriteria = "SubCategory In(" & strSubCategoryList & ")"
' open Products form
DoCmd.OpenForm "frmProducts", WhereCondtion:=strCriteria
End If

Incidentally you can find a demo of various methods of entering data of this
nature using correlated combo boxes at:

http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps

The demo uses locations but the principle is the same.

Ken Sheridan
Stafford, England

 

[denelson]

I think I should be more specific, as I was confused with the three
tables bit... I have a table which stores everything on the general
form of cards such as customer information price etc. I have another
table which lists all the types of products cards balloons etc. I have
another table which lists the subtypes roses, tulips, round ballons,
disney balloons, etc... the relationships are established as well
between them. Now using this information does this alter the process of
what I want to try to do. I have a query already which will find all
the records which are ballooons and or flowers, but i want to break it
down so I find all the ones which deal with roses or tulips or round
balloons etc. I'm not great when it comes to this complex coding, have
done a little bit but not a lot. I just want to go to the switchboard
select the option "Query for Sub topics", a form pop up with a list box
called "Category"... I select the Category (Balloons, flowers, etc)...
hit submit button, the submit button will open another form which will
generate Subtypes in a listbox called "Sub types" with a submit button.
When that submit button is clicked the records matching the data
highlighted in the listbox will come up in a query and be displayed. I
got confused with the "Products" table and form in the previous
entry... Could you help me based on this information... Form 1 select
category, form 2 select sub types, hit submit the query results are
displayed. I went to the suggested site and it is nothing near to what
I want to be able to do, I was very confused with it. Thank you for
your help thus far though. It is appreciated.

 

[Guest]

There's no way you can avoid the code if you want to select multiple items
from the list box's I'm afraid. However, the process is much as I gave you.
In the first form's list box its RowSource would be a query on your table of
cards, balloons etc.

The second form's list box's RowSource, which is set by the code in its Load
event procedure would be based on the table of roses, tulips, round balloons,
Disney balloons, etc. I've called this table SubCategories and the foreign
key field in it which references the primary key of the first table I've
called Category. You'd need to change these to your own table and field
names of course.

To display the results the code opens a third form based on the general
table of products (presumably this is the first table you mention) which
should contain a foreign key field which references the primary key of the
table of subcategories ( the table of roses, tulips, round balloons, Disney
balloons, etc).

If you are still having problems post back with details of the three tables,
their names, and those there primary key and foreign key fields on which they
are related. I could then be more specific in what I send you.

Ken Sheridan
Stafford, England

 

[denelson]

Ok... I'm getting it now. However I'm having trouble understanding the
code... Let me clarify my DB.... I have the main table (Main) which
holds my customers names purchase totals, date of purchase, etc... Then
I have the following

tblCategory - Stores Categories connected to a purchase
CategoryID
Prim_Key
Category

tblSubCategory - Stores Sub Categories connected to a purchase
SubCategoryID
CategoryID
SubCategory

tblCategoryList - Stores possible choices for categories (Cards,
balloons, flowers, etc)
CategoryID
Category

tblSubCategoryList - Stores possible choices for sub categories (round,
tulip, rose,etc)
SubCategoryID
CategoryID
SubCategory

Forms as follows

FirstCategory - list box named lstCategory and Submit button
SecondCategory - list box named lstSubCategory and Submit button

I have the relationships set up as needed based off the example DB you
sent in the link.

I tried to figure out the code with the variables and the different
steps but still can't figure it out. Can you help me using the names I
have already created. Thanks! Your a HUGE help!

 

[Guest]

There are a few problems with your logical model:

1. tblcategory is redundant as the association of a subcategory with a
purchase automatically implies its association with the category to which the
subcategory belongs.

2. The subcategory column in tblSubCategory is redundant; you just need the
subcategoryID column which determines the subcategory value.

3. The tblSubCategory table actually models a many-to-many relationship
between purchases and subcategories, so it needs a foreign key column to
reference the primary key of the purchases table, which I take to be Prim_Key.

The model would thus look like this:

Main----<tblSubCategory>----tblSubCategoryList>----tblCategoryList

The open side of each < and > character indicates the many side of the
relationship (the referencing table in the jargon). The tables are now
properly normalized and hence allow for no possible update anomalies which
redundancy otherwise leaves the door open to.

You can update the tblSubCategory table once you've added a new Prim_Key
column with the following query

UPDATE tblSubCategory INNER JOIN tblCategory
ON tblSubCategory.CategoryID = tblCategory.CategoryID
SET tblSubCategory.Prim_Key = tblCategory.Prin_Key;

As always before making any changes like this you must back up the tables.

With the above model the list boxes would be as follows:

On form FirstCategory the RowSource property of the list box would be:

SELECT CategoryID, Category
FROM tblCategoryList
ORDER BY Category;

Its other relevant properties would be:

BoundColumn 1
ColumnCount 2
ColumnWidths 0cm;8cm (or rough equivalent in inches)
MultiSelect Either Extended or Simple according to preference.

The second dimension of the last property isn't crucial so long as its at
least as wide as the control. The first must be zero to hide the first
column.

The code behind the button on this form would be as follows. I'm assuming
CategoryID is a number, not text:

Dim varItem As Variant
Dim strCategoryList As String
Dim ctrl As Control

Set ctrl = Me.lstCategory

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strCategoryList = strCategoryList & "," & ctrl.ItemData(varItem)
Next varItem

' remove leading comma
strCategoryList = Mid(strCategoryList, 2)
' open second form
DoCmd.OpenForm "SecondCategory", OpenArgs:=strCategoryList
End If

In form SecondCategory the list box's RowSource by default can show all
subcategories:

SELECT SubCategoryID, SubCategory
FROM tblCategoryList
ORDER BY SubCategory;

Its other properties would be exactly the same as those for the list box on
the first form.

In the SecondCategory form's Load event procedure the RowSource of the list
box is changed to reflect the selected category(ies) in the first form, whose
values have been passed to this form by means of the OpenArgs mechanism:

Dim strSQL AS String

If Not IsNull(Me.OpenArgs) Then
srSQL = "SELECT SubCategoryID, SubCategory " & _
"FROM tblSubCategyList " & _
"WHERE CategoryID In(" & Me.OpenArgs & ") " & _
"ORDER BY SubCategory"
Me.lstSubCategories.RowSource = strSQL
Me.lstSubCategories.Requery
End If

The code behind the button on this form would open another form or a report
based on the Main table, which in the normalized model above, now includes
the SubCategoryID column. I've assumed it open a form called frmMain:

Dim varItem As Variant
Dim strSubCategoryList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.lstSubCategory

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSubCategoryList = strSubCategoryList & "," & ctrl.ItemData(varItem)
Next varItem

' remove leading comma
strSubCategoryList = Mid(strSubCategoryList, 2)
' build criterion for filtering products form
strCriteria = "SubCategoryID In(" & strSubCategoryList & ")"
' open Products form
DoCmd.OpenForm "frmMain", WhereCondtion:=strCriteria
End If

The above code is untested of course so I can't guarantee it won’t need some
debugging, but I think I've got it right on the whole.

Ken Sheridan
Stafford, England