Filter a group of items with a listbox

[Evi]

I have a main form (FormSubCategory) listing SubCategories
based on tblSubCategory
SubCatID
SubCategory

the subform lists SubSubCategories
It is based on tblSubCategoryToSubSubCategory
This table has
SubToSSCatID
SubCatID
SubSubCatID


SubSubCatID is linked to
TblSubSubCategory
SubSubCatID
SubSubCategory

I need this structure because a SubSubCategory can belong to more than one
SubCategory
Confused enough yet?
Now

TblSubCategory is linked to TblCategory via a joining table

TblCategoryToSubCategory
CatToSubCatID
CatID
SubCatID

TblCategory has simply
CatID
Category

I want to filter my MainForm (FrmSubCategory) with a listbox based on
TblCategory

How can I do this? My MainForm doesn't contain CatID (the value field of the
list box)

If I base the list box on the linking table (TblCategoryToSubCategory) then
each Category will be mentioned twice.
Likewise, if I base my main form on the linking table then the Subcategory's
will be listed more than once.

any suggestions?

Evi

 

[strive4peace]

Hi Evi,

if this is a single-user database, you can put a boolean field (Flag) in
TblCategory and use SQL to set it to true or false. Make sure Flag is
on your form (Visible = No) -- then apply a filter to the form for Flag=true

if this is a multi-user database, there is a different approach...

if you need clarification, please let us know

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

 

[Evi]

I've got the first bit. I've added a YesNo field to tblCategory - the table
on which the listbox is based. When i choose a Category in my list, I can
set that flag to true using an Update query Sql

But how do i add the flag to my form when my Main form is not based on
TblCategory but on tblSubcategory? My form isn't even based on the table
that links TblSubCategory to TblCategory (TblCategoryToSubCategory) because
a Subcategory can appear in more than one category

It is a single-user database.

Evi

 

[strive4peace]

Hi Evi,

"But how do i add the flag to my form when my Main form is not based on
TblCategory but on tblSubcategory?"

then add the flag to tblSubcategory ... sorry, I got a bit lost in your
structure and I answered quickly. You have answered questions for
others so I know you are knowledgeable, forgive me for skimping on the
details for your answer <smile>


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

 

[Evi]

Fantastic, I get it now! That's worked wonderfully Crystal. You are a star!

I think I got lost in my own explanation Crystal, rather than you getting
lost.

For the benefit of anyone insane enough to want to do this:

I've actually started off by doing it with the Main form based on
TblCategory and the listbox based on the next level up - TblGroup (PK is
GroupID) (each group can contain several categories and each category can be
in more than one group) so I've put the CatFlag in TblCategory and in my
Mainform.

The table that links TblGroup to TblCategory is TblCatToGroup which contains
both CatID and GroupID as foreign key fields.


Private Sub List7_AfterUpdate()
Dim MyGroup As Integer
Dim MySql As String
Dim MyRecs As Long

MyGroup = Me.List7
'value of GroupID in the list box
MyRecs = Nz(DCount("*", "QryGroupToCat", "[GroupID]=" & MyGroup), 0)
'Count if there are any records in that group

If MyRecs > 0 Then

'There are some records

'set all Flags to false
MySql = "UPDATE TblCategory SET TblCategory.CatFlag = False;"
CurrentDb.Execute MySql, dbFailOnError


MySql = "UPDATE TblCategory "
MySql = MySql & "INNER JOIN TblCatToGroup "
MySql = MySql & "ON TblCategory.CatID = TblCatToGroup.CatID "
MySql = MySql & "SET TblCategory.CatFlag = True "
MySql = MySql & "WHERE (((TblCatToGroup.GroupID)=" & MyGroup & "));"

CurrentDb.Execute MySql, dbFailOnError
'Update CatFlag to True
'for records where GroupID = MyGroup
Me.Filter = "CatFlag = True"
Me.FilterOn = True
'filter form

Else

MsgBox "No records for this Group"

End If

End Sub.



Thank you very much again, Crystal

Evi

 

[strive4peace]

you're welcome, Evi happy to help

thank you for clarifying things for others <smile>
(you should, however, give your controls logical names before you write
code for or with them ...List7)

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*

Fantastic, I get it now! That's worked wonderfully Crystal. You are a star!

I think I got lost in my own explanation Crystal, rather than you getting
lost.

For the benefit of anyone insane enough to want to do this:

I've actually started off by doing it with the Main form based on
TblCategory and the listbox based on the next level up - TblGroup (PK is
GroupID) (each group can contain several categories and each category can be
in more than one group) so I've put the CatFlag in TblCategory and in my
Mainform.

The table that links TblGroup to TblCategory is TblCatToGroup which contains
both CatID and GroupID as foreign key fields.


Private Sub List7_AfterUpdate()
Dim MyGroup As Integer
Dim MySql As String
Dim MyRecs As Long

MyGroup = Me.List7
'value of GroupID in the list box
MyRecs = Nz(DCount("*", "QryGroupToCat", "[GroupID]=" & MyGroup), 0)
'Count if there are any records in that group

If MyRecs > 0 Then

'There are some records

'set all Flags to false
MySql = "UPDATE TblCategory SET TblCategory.CatFlag = False;"
CurrentDb.Execute MySql, dbFailOnError


MySql = "UPDATE TblCategory "
MySql = MySql & "INNER JOIN TblCatToGroup "
MySql = MySql & "ON TblCategory.CatID = TblCatToGroup.CatID "
MySql = MySql & "SET TblCategory.CatFlag = True "
MySql = MySql & "WHERE (((TblCatToGroup.GroupID)=" & MyGroup & "));"

CurrentDb.Execute MySql, dbFailOnError
'Update CatFlag to True
'for records where GroupID = MyGroup
Me.Filter = "CatFlag = True"
Me.FilterOn = True
'filter form

Else

MsgBox "No records for this Group"

End If

End Sub.



Thank you very much again, Crystal

Evi

Hi Evi,

"But how do i add the flag to my form when my Main form is not based on
TblCategory but on tblSubcategory?"

then add the flag to tblSubcategory ... sorry, I got a bit lost in your
structure and I answered quickly. You have answered questions for
others so I know you are knowledgeable, forgive me for skimping on the
details for your answer <smile>


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*