Why a list box on a form was not filter out?

[Ally]

Would you please kindly help me to see why the list box on a form was not
filter out?

I have a form (form#1) with a list box called "lstCName" displayed on the
top of the page. The list box includes a list of capital items with Project#
(column 1), Cost center number (column 2), Item description (column 3),
Estimated cost (column 4) and FiscalYear (column 5) displayed on the top of
the page. The column 1 (Project#) is the BoundColumn used for command buttons
(such as Edit, New, Add) in the form. It works fine itself with all command
buttons
funtioning correctly. The only issue is that form#1 displays capital items
of all fiscal year (FY) starting FY 05 to FY 09 and the list is just simply
too long to look through one capital item in one specific fiscal year. (I do
have a search function in the form#1, but I still think I need to filter the
form#1 out by Fiscal Year so that I can just look at the capital items for
one or two fiscal year as I would like).

Therefore, I am thinking about adding form#2 to select FiscalYear and
display all capital items in the form#1 for the fiscalyear that I choose from
form#2. Form#2 has a list box called "FiscalYear" (BoundColumn is 1 which is
the field FiscalYear) and by selecting "Edit" buttion on form#2, the form#1
with all capital items for that fiscalyear should be braught up. My probelm
is that it does not work correctly... No matter which year I selected in
form#2, the form#1 still displays all capital items for every fiscal year. It
does not filter out the correct fiscalyear I choose
from form#2. I am attaching the related code below for your reveiw and hope
you can help me out!

Here is the code for form#2:

Private Sub cmdEdit_Click()
Dim strWhere, str As String, varItem As Variant

If Me!FiscalYear.ItemsSelected.Count > 0 Then
For Each varItem In Me!FiscalYear.ItemsSelected
str = str & Chr$(34) & Me!FiscalYear.Column(0, varItem) &
Chr$(34) & ","
Next varItem
str = Left$(str, Len(str) - 1)
If IsNothing(strWhere) Then
strWhere = "[FiscalYear] IN (" & str & ")"
Dim MyVar
MyVar = strWhere
Debug.Print MyVar
Else
strWhere = strWhere & " AND [FiscalYear] IN (" & str & ")"
End If

End If

DoCmd.OpenForm FormName:="frmApprOver50KSelect", WhereCondition:=strWhere
DoCmd.Close acForm, Me.Name
End Sub

And here is the code for form#1:

Private Sub Edit_Click()
Dim strWhere As String, varItem As Variant
If Me!lstCName.ItemsSelected.Count = 0 Then Exit Sub
' Loop through the items selected collection
For Each varItem In Me!lstCName.ItemsSelected
' Grab the Project# column for each selected item
strWhere = strWhere & Chr$(34) & Me!lstCName.Column(0, varItem) &
Chr$(34) & ","
Next varItem
' Throw away the extra comma on the "IN" string
strWhere = Left$(strWhere, Len(strWhere) - 1)
' Open the project form filtered on the selected projects
gstrWhereClub = "[Project#] IN (" & strWhere & ")"
DoCmd.OpenForm FormName:="frmApprOver50KProjects",
WhereCondition:=gstrWhereClub
Dim MyVar
MyVar = gstrWhereClub
Debug.Print MyVar
' Hide the New button, but show the Show All button
Forms!frmApprOver50KProjects!cmdAddNew.Visible = False
Forms!frmApprOver50KProjects!cmdShowAll.Visible = True
DoCmd.Close acForm, Me.Name
End Sub

Would you please let me know why it happens this way? really appreciate it!!!

Many Thanks!

Ally

 

[Klatuu]

If you want to select multiple years in a list box, you will need to make it
a multi select list box. The issue with a multi select list box is that it
will return no value on its own. You have to evaluate the ItemsSelected
collection of the list box and build a filtering string to filter form1.
This takes a bit of VBA. I would suggest a command button on form2 that
will set the filter on form1 based on the years selected in the list box then
close form2. In the following example, if you select no years, the
assumption is you want all years. You may need to modify it some to suit
your needs and use correct names. It also assumes the field in your table
that defines the year is a date/time data type.

Private Sub cmdFilterByYears_Click()
Dim strFilter As String

strFilter = BuildWhereCondition("lstYears")

If Len(strFilter) > 0 Then
strFilter = "Year([SomeDateField]) " & strFilter
Forms!Form1.Filter = strFilter
Forms!Form1.FilterOn = True
End If

Docmd.Close acForm, Me.Name, acSaveNo

End Sub


This function will build a string based on the items selected. Pass it the
name of the list box control.

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= " & _
ctl.ItemData(ctl.ItemsSelected(0))
Case Else 'Multiple Selection
strWhere = " IN (

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & .ItemData(varItem) & ", "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function


--
Dave Hargis, Microsoft Access MVP

Would you please kindly help me to see why the list box on a form was not
filter out?

I have a form (form#1) with a list box called "lstCName" displayed on the
top of the page. The list box includes a list of capital items with Project#
(column 1), Cost center number (column 2), Item description (column 3),
Estimated cost (column 4) and FiscalYear (column 5) displayed on the top of
the page. The column 1 (Project#) is the BoundColumn used for command buttons
(such as Edit, New, Add) in the form. It works fine itself with all command
buttons
funtioning correctly. The only issue is that form#1 displays capital items
of all fiscal year (FY) starting FY 05 to FY 09 and the list is just simply
too long to look through one capital item in one specific fiscal year. (I do
have a search function in the form#1, but I still think I need to filter the
form#1 out by Fiscal Year so that I can just look at the capital items for
one or two fiscal year as I would like).

Therefore, I am thinking about adding form#2 to select FiscalYear and
display all capital items in the form#1 for the fiscalyear that I choose from
form#2. Form#2 has a list box called "FiscalYear" (BoundColumn is 1 which is
the field FiscalYear) and by selecting "Edit" buttion on form#2, the form#1
with all capital items for that fiscalyear should be braught up. My probelm
is that it does not work correctly... No matter which year I selected in
form#2, the form#1 still displays all capital items for every fiscal year. It
does not filter out the correct fiscalyear I choose
from form#2. I am attaching the related code below for your reveiw and hope
you can help me out!

Here is the code for form#2:

Private Sub cmdEdit_Click()
Dim strWhere, str As String, varItem As Variant

If Me!FiscalYear.ItemsSelected.Count > 0 Then
For Each varItem In Me!FiscalYear.ItemsSelected
str = str & Chr$(34) & Me!FiscalYear.Column(0, varItem) &
Chr$(34) & ","
Next varItem
str = Left$(str, Len(str) - 1)
If IsNothing(strWhere) Then
strWhere = "[FiscalYear] IN (" & str & ")"
Dim MyVar
MyVar = strWhere
Debug.Print MyVar
Else
strWhere = strWhere & " AND [FiscalYear] IN (" & str & ")"
End If

End If

DoCmd.OpenForm FormName:="frmApprOver50KSelect", WhereCondition:=strWhere
DoCmd.Close acForm, Me.Name
End Sub

And here is the code for form#1:

Private Sub Edit_Click()
Dim strWhere As String, varItem As Variant
If Me!lstCName.ItemsSelected.Count = 0 Then Exit Sub
' Loop through the items selected collection
For Each varItem In Me!lstCName.ItemsSelected
' Grab the Project# column for each selected item
strWhere = strWhere & Chr$(34) & Me!lstCName.Column(0, varItem) &
Chr$(34) & ","
Next varItem
' Throw away the extra comma on the "IN" string
strWhere = Left$(strWhere, Len(strWhere) - 1)
' Open the project form filtered on the selected projects
gstrWhereClub = "[Project#] IN (" & strWhere & ")"
DoCmd.OpenForm FormName:="frmApprOver50KProjects",
WhereCondition:=gstrWhereClub
Dim MyVar
MyVar = gstrWhereClub
Debug.Print MyVar
' Hide the New button, but show the Show All button
Forms!frmApprOver50KProjects!cmdAddNew.Visible = False
Forms!frmApprOver50KProjects!cmdShowAll.Visible = True
DoCmd.Close acForm, Me.Name
End Sub

Would you please let me know why it happens this way? really appreciate it!!!

Many Thanks!

Ally