Cascading Lists Need Help

[SteelFire via AccessMonster.com]

I am running a form, frmPartFinder, that has two combo boxes, cboCategory and
cboType. It also has a working OK and Cancel button. Right now, each of the
combo boxes gets its list from its own table, tblCategoryName and
tblCategoryType. When I hit OK, it opens the query, qryPartTable, closes it,
then opens frmPartList, then closes frmPartFinder. Query qryPartTable runs
off of table tblPartTable.

I am looking to make things run better and do a few more thing. The 2nd combo
box bring up the same list no matter what is in the first. I was thinking of
setting the list for the combo boxes to run off of the two relating columns
in qryPartTable. This way I don't have to keep two more tables up-to-date.
The big thing that I have been running into is when I do this, the combo
boxes list every part. I just need one of each. Is there a way to filter out
the list so it only displays one of each value, but when picked will bring up
all of the records that have that value? And for the 2nd combo box, give only
a list of the part types that fall into the selected Category? And also show
only one value to pick, but bring up all of the records?

Thanks for stopping to read and I hope you help.

 

[tina]

comments inline.

I am running a form, frmPartFinder, that has two combo boxes, cboCategory and
cboType. It also has a working OK and Cancel button. Right now, each of the
combo boxes gets its list from its own table, tblCategoryName and
tblCategoryType. When I hit OK, it opens the query, qryPartTable, closes it,
then opens frmPartList, then closes frmPartFinder. Query qryPartTable runs
off of table tblPartTable.

okay, so you're filtering the records in frmPartList from the values
selected in the combobox controls on frmPartFinder, correct?

why are you opening qryPartTable and then immediately closing it?

I am looking to make things run better and do a few more thing. The 2nd combo
box bring up the same list no matter what is in the first. I was thinking of
setting the list for the combo boxes to run off of the two relating columns
in qryPartTable. This way I don't have to keep two more tables up-to-date.

do you mean that you think you should delete tblCategoryName and
tblCategoryType? sounds like a bad idea to me. presumably those two tables
are "parent" tables, each with a one-to-many relationship with the
corresponding field in tblPartTable. if so, i probably wouldn't delete them
without giving it serious thought - presumably you created them for a reason
in the first place.

The big thing that I have been running into is when I do this, the combo
boxes list every part.

why do tblCategory and tblType have parts listed in them?

I just need one of each. Is there a way to filter out
the list so it only displays one of each value, but when picked will bring up
all of the records that have that value? And for the 2nd combo box, give only
a list of the part types that fall into the selected Category?

yes, certainly that's do-able. but is the combobox listing part types, or
parts (re your statement that "the combo boxes list every part")?

suggest you start at the beginning, and post your tables/relationships,
something like

tblCategory
CategoryID (primary key)
CategoryName

tblType
TypeID (pk)
TypeName

tblPartTable
PartID (pk)
PartNumber
PartDescription
PartCategory (foreign key from tblCategory)
PartType (fk from tblType)

and post the SQL from qryPartTable, please. you can do that by opening the
query in Design view, then changing the view to SQL View, copying *all* the
text in the SQL View pane, and pasting into your post.

also, post the RowSource property of each of the combobox controls on
frmPartFinder.

hth

 

[SteelFire via AccessMonster.com]

Okay, to Start.

tblCategoryName
CategoryName

tblCategoryType
CategoryType

tblContact
ContactID(pk)
LastName
FirstName
E-mail
Phone
Address
City
State
ZIP

tblCustomers
CustomerID(pk)
Company
Location Park
ContactID (fk from tblContact)

tblJob#
Job#(pk)
CustomerID (fk from tblCustomers)
ConveyorType
Years

tblPartTable
PartID(pk)
Part#
Job# (fk from tblJob#)
ProjectName
PartDescription
Rev
RevDate
CustomerID (fk from tblCustomers)
FilePath
CategoryName
CategoryType
PartDrawingAtt

I made tblCategoryType and tblCategoryName because I was following
instructions. They are not related to any table and I made them both by hand.
They are only there to be the lists for the combo boxes. tblPartTable is my
main table, and all the others are just used to store things related to the
parts.

As of this posting, my one query’s SQL is:
SELECT tblPartTable.CategoryType, tblPartTable.CategoryName, tblPartTable.ID,
tblPartTable.[Part#], tblPartTable.[Job#], tblPartTable.ProjectName,
tblPartTable.PartDescription, tblPartTable.Rev, tblPartTable.RevDate,
tblPartTable.CustomerID, tblPartTable.FilePath, tblPartTable.PartDrawingAtt,
tblPartTable.PartDrawingAtt.FileData, [PartDrawingAtt].[FileFlags] AS Expr1,
tblPartTable.PartDrawingAtt.FileName, [PartDrawingAtt].[FileTimeStamp] AS
Expr2, tblPartTable.PartDrawingAtt.FileType, [PartDrawingAtt].[FileURL] AS
Expr3
FROM tblPartTable
WHERE (((tblPartTable.CategoryName)=[Forms]![frmPartFinder]![cboCategory]))
OR (((([tblPartTable].[CategoryName]) Like [Forms]![frmPartFinder]!
[cboCategory]) Is Null));

The form frmPartFinder has the two Combo Boxes.
cboCategory with a row Source of: tblCategoryName
cboType with a row Source of: tblCategoryType

Right now I am working on a Event for cboCategory. It is After Update and so
far reads:
Private Sub cboCategory_AfterUpdate()
On Error Resume Next
cboType.RowSource = "SELECT qryPartTable.CategoryType FROM qryPartTable
WHERE qryPartTable.CategoryType = cboCategory.Value ORDER BY qryPartTable.
CategoryType"
End Sub

So now that I have everything listed, let me clear things up. I have close
to 2 thousand parts in my tblPartTable. Each part has a Category that is fits
into. And in each Category, there are many different Types. On my form, I am
trying to get it so that the cboCategory will list all of the different
Categories, but each one only once. I wanted to know if I could get the list
off of the main tblPartTable. This Way if a new part gets drafted I don’t
have to go back in and change tblCategoryName.CategoryName. Then find a way
to get a list of the Types that only apply to the Category that I picked. I
am trying to get the list off of the qryPartTable, but I get multiples of
each Type and I just need one distinct value in the list.

I hope that cleared things up.

 

[tina]

okay, good start. now a few more questions, and then some suggestions: one
category may apply to many parts, correct? but each part falls into only one
category, correct? and one type may apply to many parts, correct? but each
part is falls into only one type, correct?

now what about categories and types. from your explanation, i'd say that:
one category may contain many types, correct? but each type belongs to only
one category, correct?

if all of the above statements are correct, then your category table is
related to the category type table, and the category type table is related
to the parts table. based on that, i'd change the table structures to
support those relationships, as

tblCategories
CategoryID (pk)
CategoryName

tblCategoryTypes
TypeID (primary key)
CategoryID (foreign key from tblCategories)
TypeName

tblParts
PartID(pk)
PartNo (recommend you don't use a # sign in the fieldname.)
JobNo (fk from tblJob#. again, lose the # sign, in the tablename, too.)
ProjectName
PartDescription
Rev
RevDate
<CustomerID> (remove this field; customer is linked to the record via the
JobNo)
FilePath
TypeID (fk from tblCategoryTypes)
<CategoryName> (remove this field; category is linked to the record via
category type)
PartDrawingAtt

okay, on to your forms. based on the table relationships outlined above,
suggest: in frmPartFinder, use tblCategories as the RowSource of the first
combobox control, cboCategory. use a SQL statement as the RowSource of the
second combobox control, cboType, as

SELECT TypeID, TypeName FROM tblCategoryTypes WHERE CategoryID =
Forms!frmPartFinder!cboCategory;

add the following code to cboType's Enter event procedure, as

Me!cboType.Requery

so now when you choose a category in cboCategory, and then go to cboType,
the only types in the droplist will be those that are related to the chosen
category.

and on to the SQL statement: the SQL you posted will return all the parts
because of the part of the WHERE clause that says "OR
(((([tblPartTable].[CategoryName]) Like
[Forms]![frmPartFinder]![cboCategory]) Is Null))"

change the query to a multi-table query, so the returned fields are
user-friendly, as

SELECT tblCategoryTypes.TypeName, tblCategories.CategoryName,
tblParts.PartID, tblParts.PartNo, tblParts.JobNo, tblParts.ProjectName,
tblParts.PartDescription, tblParts.Rev, tblParts.RevDate,
tblCustomers.Company, tblParts.FilePath, tblParts.PartDwgAtt
FROM (((tblParts LEFT JOIN tblJobs ON tblParts.JobNo = tblJobs.JobNo) LEFT
JOIN tblCustomers ON tblJobs.CustomerID = tblCustomers.CustomerID) LEFT JOIN
tblCategoryTypes ON tblParts.TypeID = tblCategoryTypes.TypeID) LEFT JOIN
tblCategories ON tblCategoryTypes.CategoryID = tblCategories.CateogryID
WHERE (((tblParts.TypeID)=[Forms]![frmPartFinder]![cboType]));

note that i left off the query fields

"tblPartTable.PartDrawingAtt.FileData, [PartDrawingAtt].[FileFlags] AS
Expr1,
tblPartTable.PartDrawingAtt.FileName, [PartDrawingAtt].[FileTimeStamp] AS
Expr2, tblPartTable.PartDrawingAtt.FileType, [PartDrawingAtt].[FileURL] AS
Expr3"

because they're not listed in your table, i don't know where they're coming
from, and the syntax is strange to me. i'm wondering if you're using A2007,
and if PartDrawingAtt is a multi-value field. if so, i can't address that,
but i'm guessing that the solution i'm providing will work in A2007
basically the same as in earlier versions.

and, finally, you didn't say why your command button code opened and
immediately closed the query. it's a SELECT query, so there's nothing gained
by opening it separately from frmPartList. just set the code on the command
button to check for a value in cboType, and if one is found, to open
frmPartList and then close frmPartFinder. frmPartList should return all part
records where the type matches that selected in frmPartFinder.

recommend you read some more on relational design principles
(normalization), so you'll understand why i made the above table structure
suggestions, and can review any other tables you may have for possible
design improvements.

hth

Okay, to Start.

tblCategoryName
CategoryName

tblCategoryType
CategoryType

tblContact
ContactID(pk)
LastName
FirstName
E-mail
Phone
Address
City
State
ZIP

tblCustomers
CustomerID(pk)
Company
Location Park
ContactID (fk from tblContact)

tblJob#
Job#(pk)
CustomerID (fk from tblCustomers)
ConveyorType
Years

tblPartTable
PartID(pk)
Part#
Job# (fk from tblJob#)
ProjectName
PartDescription
Rev
RevDate
CustomerID (fk from tblCustomers)
FilePath
CategoryName
CategoryType
PartDrawingAtt

I made tblCategoryType and tblCategoryName because I was following
instructions. They are not related to any table and I made them both by hand.
They are only there to be the lists for the combo boxes. tblPartTable is my
main table, and all the others are just used to store things related to the
parts.

As of this posting, my one query's SQL is:
SELECT tblPartTable.CategoryType, tblPartTable.CategoryName, tblPartTable.ID,
tblPartTable.[Part#], tblPartTable.[Job#], tblPartTable.ProjectName,
tblPartTable.PartDescription, tblPartTable.Rev, tblPartTable.RevDate,
tblPartTable.CustomerID, tblPartTable.FilePath, tblPartTable.PartDrawingAtt,
tblPartTable.PartDrawingAtt.FileData, [PartDrawingAtt].[FileFlags] AS Expr1,
tblPartTable.PartDrawingAtt.FileName, [PartDrawingAtt].[FileTimeStamp] AS
Expr2, tblPartTable.PartDrawingAtt.FileType, [PartDrawingAtt].[FileURL] AS
Expr3
FROM tblPartTable
WHERE (((tblPartTable.CategoryName)=[Forms]![frmPartFinder]![cboCategory]))
OR (((([tblPartTable].[CategoryName]) Like [Forms]![frmPartFinder]!
[cboCategory]) Is Null));

The form frmPartFinder has the two Combo Boxes.
cboCategory with a row Source of: tblCategoryName
cboType with a row Source of: tblCategoryType

Right now I am working on a Event for cboCategory. It is After Update and so
far reads:
Private Sub cboCategory_AfterUpdate()
On Error Resume Next
cboType.RowSource = "SELECT qryPartTable.CategoryType FROM qryPartTable
WHERE qryPartTable.CategoryType = cboCategory.Value ORDER BY qryPartTable.
CategoryType"
End Sub

So now that I have everything listed, let me clear things up. I have close
to 2 thousand parts in my tblPartTable. Each part has a Category that is fits
into. And in each Category, there are many different Types. On my form, I am
trying to get it so that the cboCategory will list all of the different
Categories, but each one only once. I wanted to know if I could get the list
off of the main tblPartTable. This Way if a new part gets drafted I don't
have to go back in and change tblCategoryName.CategoryName. Then find a way
to get a list of the Types that only apply to the Category that I picked. I
am trying to get the list off of the qryPartTable, but I get multiples of
each Type and I just need one distinct value in the list.

I hope that cleared things up.