Listbox filtering on a form

[NYbills]

I have a form that the recordsource is called from query. On this form I
also have a multiple select listbox. this has the following sql statement as
the row source

SELECT qryproductfamilydescription.[Product Family Description] FROM
qryproductfamilydescription

with the following code

Private Sub List200_AfterUpdate()
Dim cnt, i As Integer
Dim strFilter As String

'On Error GoTo Problem

' Filter this form based on list selection
cnt = 0
For i = 0 To Me.List200.ListCount - 1
If Me.List200.Selected(i) Then
If cnt = 0 Then
strFilter = "[Product Family Description] = '" & Me.List200.ItemData(i) &
"'"
Else
strFilter = strFilter & " OR [Product Family Description] = '" &
Me.List200.ItemData(i) & "'"
End If
Me.Filter = strFilter
Me.FilterOn = True
cnt = cnt + 1
End If
Next i
Exit Sub

Problem:
'Code below shows the error message
'MsgBox Err.Description, , "Error n°" & Err.Number
Err.Clear
End Sub

What I am trying to accomplish is when selecting all the records in the
listbox have them all filter to give me the same amount of records. There is
a limitation and I would like to know how to workaround. I am very new at
this and a step by step would be greatly appreciated.

 

[Arvin Meyer [MVP]]

Let's assume that the list box is named lstWhatever, add a hidden textbox
named txtSelected, and in the click event of the multi-select list box, use
some code like:

Private Sub lstWhatever_Click()

Dim varItem As Variant
Dim strList As String
Dim strSQL As String

With Me!lstWhatever
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With

strSQL = "Select * From tblMyData Where MyField In (" & Me.txtSelected & ")

Me.RecordSource = strSQL

End Sub

 

[NYbills]

Add the statement but error.

Run-time error 3075. I think it is related to the following

strSQL = "Select * From tblproductfamilydescription Where Product Family
Description In (" & Me.txtSelected & ")"

Any ideas??

Let's assume that the list box is named lstWhatever, add a hidden textbox
named txtSelected, and in the click event of the multi-select list box, use
some code like:

Private Sub lstWhatever_Click()

Dim varItem As Variant
Dim strList As String
Dim strSQL As String

With Me!lstWhatever
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With

strSQL = "Select * From tblMyData Where MyField In (" & Me.txtSelected & ")

Me.RecordSource = strSQL

End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have a form that the recordsource is called from query. On this form I
also have a multiple select listbox. this has the following sql statement
as
the row source

SELECT qryproductfamilydescription.[Product Family Description] FROM
qryproductfamilydescription

with the following code

Private Sub List200_AfterUpdate()
Dim cnt, i As Integer
Dim strFilter As String

'On Error GoTo Problem

' Filter this form based on list selection
cnt = 0
For i = 0 To Me.List200.ListCount - 1
If Me.List200.Selected(i) Then
If cnt = 0 Then
strFilter = "[Product Family Description] = '" & Me.List200.ItemData(i) &
"'"
Else
strFilter = strFilter & " OR [Product Family Description] = '" &
Me.List200.ItemData(i) & "'"
End If
Me.Filter = strFilter
Me.FilterOn = True
cnt = cnt + 1
End If
Next i
Exit Sub

Problem:
'Code below shows the error message
'MsgBox Err.Description, , "Error n°" & Err.Number
Err.Clear
End Sub

What I am trying to accomplish is when selecting all the records in the
listbox have them all filter to give me the same amount of records. There
is
a limitation and I would like to know how to workaround. I am very new at
this and a step by step would be greatly appreciated.

 

[Douglas J. Steele]

Since your field Product Family Description has spaces in the name (never a
good idea!), you need to enclose the field name in square brackets:

strSQL = "Select * From tblproductfamilydescription " & _
"Where [Product Family Description] In (" & Me.txtSelected & ")"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Add the statement but error.

Run-time error 3075. I think it is related to the following

strSQL = "Select * From tblproductfamilydescription Where Product Family
Description In (" & Me.txtSelected & ")"

Any ideas??

Let's assume that the list box is named lstWhatever, add a hidden textbox
named txtSelected, and in the click event of the multi-select list box,
use
some code like:

Private Sub lstWhatever_Click()

Dim varItem As Variant
Dim strList As String
Dim strSQL As String

With Me!lstWhatever
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With

strSQL = "Select * From tblMyData Where MyField In (" & Me.txtSelected &
")

Me.RecordSource = strSQL

End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have a form that the recordsource is called from query. On this form
I
also have a multiple select listbox. this has the following sql
statement
as
the row source

SELECT qryproductfamilydescription.[Product Family Description] FROM
qryproductfamilydescription

with the following code

Private Sub List200_AfterUpdate()
Dim cnt, i As Integer
Dim strFilter As String

'On Error GoTo Problem

' Filter this form based on list selection
cnt = 0
For i = 0 To Me.List200.ListCount - 1
If Me.List200.Selected(i) Then
If cnt = 0 Then
strFilter = "[Product Family Description] = '" &
Me.List200.ItemData(i) &
"'"
Else
strFilter = strFilter & " OR [Product Family Description] = '" &
Me.List200.ItemData(i) & "'"
End If
Me.Filter = strFilter
Me.FilterOn = True
cnt = cnt + 1
End If
Next i
Exit Sub

Problem:
'Code below shows the error message
'MsgBox Err.Description, , "Error n°" & Err.Number
Err.Clear
End Sub

What I am trying to accomplish is when selecting all the records in the
listbox have them all filter to give me the same amount of records.
There
is
a limitation and I would like to know how to workaround. I am very new
at
this and a step by step would be greatly appreciated.

 

[Arvin Meyer [MVP]]

Besides Doug's suggestion, I should point our that the multi-select code is
for numeric values, specifically, long integers. Product Family Description
sounds like a text field. You will need to either use the Key from that
table or rewrite the multi-select code to use text. In case the description
field really is a long integer and you've used a lookup field, I suggest
that you fix it by using a query instead of a lookup field:

http://www.mvps.org/access/lookupfields.htm
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Add the statement but error.

Run-time error 3075. I think it is related to the following

strSQL = "Select * From tblproductfamilydescription Where Product Family
Description In (" & Me.txtSelected & ")"

Any ideas??

Let's assume that the list box is named lstWhatever, add a hidden textbox
named txtSelected, and in the click event of the multi-select list box,
use
some code like:

Private Sub lstWhatever_Click()

Dim varItem As Variant
Dim strList As String
Dim strSQL As String

With Me!lstWhatever
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With

strSQL = "Select * From tblMyData Where MyField In (" & Me.txtSelected &
")

Me.RecordSource = strSQL

End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have a form that the recordsource is called from query. On this form
I
also have a multiple select listbox. this has the following sql
statement
as
the row source

SELECT qryproductfamilydescription.[Product Family Description] FROM
qryproductfamilydescription

with the following code

Private Sub List200_AfterUpdate()
Dim cnt, i As Integer
Dim strFilter As String

'On Error GoTo Problem

' Filter this form based on list selection
cnt = 0
For i = 0 To Me.List200.ListCount - 1
If Me.List200.Selected(i) Then
If cnt = 0 Then
strFilter = "[Product Family Description] = '" &
Me.List200.ItemData(i) &
"'"
Else
strFilter = strFilter & " OR [Product Family Description] = '" &
Me.List200.ItemData(i) & "'"
End If
Me.Filter = strFilter
Me.FilterOn = True
cnt = cnt + 1
End If
Next i
Exit Sub

Problem:
'Code below shows the error message
'MsgBox Err.Description, , "Error n°" & Err.Number
Err.Clear
End Sub

What I am trying to accomplish is when selecting all the records in the
listbox have them all filter to give me the same amount of records.
There
is
a limitation and I would like to know how to workaround. I am very new
at
this and a step by step would be greatly appreciated.

 

[NYbills]

How do I rewrite the code to text? In my strSQL can I have this based of a
query instead of a table? This is my first experience in writing code.

Thanks.

Besides Doug's suggestion, I should point our that the multi-select code is
for numeric values, specifically, long integers. Product Family Description
sounds like a text field. You will need to either use the Key from that
table or rewrite the multi-select code to use text. In case the description
field really is a long integer and you've used a lookup field, I suggest
that you fix it by using a query instead of a lookup field:

http://www.mvps.org/access/lookupfields.htm
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Add the statement but error.

Run-time error 3075. I think it is related to the following

strSQL = "Select * From tblproductfamilydescription Where Product Family
Description In (" & Me.txtSelected & ")"

Any ideas??

Let's assume that the list box is named lstWhatever, add a hidden textbox
named txtSelected, and in the click event of the multi-select list box,
use
some code like:

Private Sub lstWhatever_Click()

Dim varItem As Variant
Dim strList As String
Dim strSQL As String

With Me!lstWhatever
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With

strSQL = "Select * From tblMyData Where MyField In (" & Me.txtSelected &
")

Me.RecordSource = strSQL

End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have a form that the recordsource is called from query. On this form
I
also have a multiple select listbox. this has the following sql
statement
as
the row source

SELECT qryproductfamilydescription.[Product Family Description] FROM
qryproductfamilydescription

with the following code

Private Sub List200_AfterUpdate()
Dim cnt, i As Integer
Dim strFilter As String

'On Error GoTo Problem

' Filter this form based on list selection
cnt = 0
For i = 0 To Me.List200.ListCount - 1
If Me.List200.Selected(i) Then
If cnt = 0 Then
strFilter = "[Product Family Description] = '" &
Me.List200.ItemData(i) &
"'"
Else
strFilter = strFilter & " OR [Product Family Description] = '" &
Me.List200.ItemData(i) & "'"
End If
Me.Filter = strFilter
Me.FilterOn = True
cnt = cnt + 1
End If
Next i
Exit Sub

Problem:
'Code below shows the error message
'MsgBox Err.Description, , "Error n°" & Err.Number
Err.Clear
End Sub

What I am trying to accomplish is when selecting all the records in the
listbox have them all filter to give me the same amount of records.
There
is
a limitation and I would like to know how to workaround. I am very new
at
this and a step by step would be greatly appreciated.

 

[Arvin Meyer [MVP]]

Yes, you can select from a query instead of a table. Changing the code for
text will take some time, and if you haven't written code before, it may
take quite a bit of time to play with it. Before I try to do it for you, let
me first suggest that you do not need to try and find records based on a
text description field. The index, if used is slower than a numeric index,
and if you've used a lookup field, your design isn't correct anyway.

If you have a Product Family Description field, it should be in a table with
a Primary Key of type autonumber, something like ProductFamilyID. Good
design also suggests that you do not use spaces in your fieldnames because
it makes them unscalable.

Microsoft programmers are not database developers and as a result, they have
made many decisions that appear to make it easier for users, when in fact,
quite the opposite is true, and they succeed in allowing new users to build
databases which are poor design. Doing it correctly greatly increases
performance and scalability, and reduces any chance of corruption.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

How do I rewrite the code to text? In my strSQL can I have this based of
a
query instead of a table? This is my first experience in writing code.

Thanks.

Besides Doug's suggestion, I should point our that the multi-select code
is
for numeric values, specifically, long integers. Product Family
Description
sounds like a text field. You will need to either use the Key from that
table or rewrite the multi-select code to use text. In case the
description
field really is a long integer and you've used a lookup field, I suggest
that you fix it by using a query instead of a lookup field:

http://www.mvps.org/access/lookupfields.htm
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Add the statement but error.

Run-time error 3075. I think it is related to the following

strSQL = "Select * From tblproductfamilydescription Where Product
Family
Description In (" & Me.txtSelected & ")"

Any ideas??

:

Let's assume that the list box is named lstWhatever, add a hidden
textbox
named txtSelected, and in the click event of the multi-select list
box,
use
some code like:

Private Sub lstWhatever_Click()

Dim varItem As Variant
Dim strList As String
Dim strSQL As String

With Me!lstWhatever
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With

strSQL = "Select * From tblMyData Where MyField In (" & Me.txtSelected
&
")

Me.RecordSource = strSQL

End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have a form that the recordsource is called from query. On this
form
I
also have a multiple select listbox. this has the following sql
statement
as
the row source

SELECT qryproductfamilydescription.[Product Family Description] FROM
qryproductfamilydescription

with the following code

Private Sub List200_AfterUpdate()
Dim cnt, i As Integer
Dim strFilter As String

'On Error GoTo Problem

' Filter this form based on list selection
cnt = 0
For i = 0 To Me.List200.ListCount - 1
If Me.List200.Selected(i) Then
If cnt = 0 Then
strFilter = "[Product Family Description] = '" &
Me.List200.ItemData(i) &
"'"
Else
strFilter = strFilter & " OR [Product Family Description] = '" &
Me.List200.ItemData(i) & "'"
End If
Me.Filter = strFilter
Me.FilterOn = True
cnt = cnt + 1
End If
Next i
Exit Sub

Problem:
'Code below shows the error message
'MsgBox Err.Description, , "Error n°" & Err.Number
Err.Clear
End Sub

What I am trying to accomplish is when selecting all the records in
the
listbox have them all filter to give me the same amount of records.
There
is
a limitation and I would like to know how to workaround. I am very
new
at
this and a step by step would be greatly appreciated.

 

[NYbills]

Thank you for your response. I took your advise. No need to write the code.
I have linked the query back to the primary key and used the following code.
I have also gone thru the database and labeled all forms, tables, and querys
"frm", "tbl", "qry". And the spaces between names. I am in the finance/it
department and the database portion interests me at bit. This was someone
else database but I think I am making it easier for the next person to
understand if they have experience.

Thanks again for your help.

Yes, you can select from a query instead of a table. Changing the code for
text will take some time, and if you haven't written code before, it may
take quite a bit of time to play with it. Before I try to do it for you, let
me first suggest that you do not need to try and find records based on a
text description field. The index, if used is slower than a numeric index,
and if you've used a lookup field, your design isn't correct anyway.

If you have a Product Family Description field, it should be in a table with
a Primary Key of type autonumber, something like ProductFamilyID. Good
design also suggests that you do not use spaces in your fieldnames because
it makes them unscalable.

Microsoft programmers are not database developers and as a result, they have
made many decisions that appear to make it easier for users, when in fact,
quite the opposite is true, and they succeed in allowing new users to build
databases which are poor design. Doing it correctly greatly increases
performance and scalability, and reduces any chance of corruption.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

How do I rewrite the code to text? In my strSQL can I have this based of
a
query instead of a table? This is my first experience in writing code.

Thanks.

Besides Doug's suggestion, I should point our that the multi-select code
is
for numeric values, specifically, long integers. Product Family
Description
sounds like a text field. You will need to either use the Key from that
table or rewrite the multi-select code to use text. In case the
description
field really is a long integer and you've used a lookup field, I suggest
that you fix it by using a query instead of a lookup field:

http://www.mvps.org/access/lookupfields.htm
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Add the statement but error.

Run-time error 3075. I think it is related to the following

strSQL = "Select * From tblproductfamilydescription Where Product
Family
Description In (" & Me.txtSelected & ")"

Any ideas??

:

Let's assume that the list box is named lstWhatever, add a hidden
textbox
named txtSelected, and in the click event of the multi-select list
box,
use
some code like:

Private Sub lstWhatever_Click()

Dim varItem As Variant
Dim strList As String
Dim strSQL As String

With Me!lstWhatever
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With

strSQL = "Select * From tblMyData Where MyField In (" & Me.txtSelected
&
")

Me.RecordSource = strSQL

End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have a form that the recordsource is called from query. On this
form
I
also have a multiple select listbox. this has the following sql
statement
as
the row source

SELECT qryproductfamilydescription.[Product Family Description] FROM
qryproductfamilydescription

with the following code

Private Sub List200_AfterUpdate()
Dim cnt, i As Integer
Dim strFilter As String

'On Error GoTo Problem

' Filter this form based on list selection
cnt = 0
For i = 0 To Me.List200.ListCount - 1
If Me.List200.Selected(i) Then
If cnt = 0 Then
strFilter = "[Product Family Description] = '" &
Me.List200.ItemData(i) &
"'"
Else
strFilter = strFilter & " OR [Product Family Description] = '" &
Me.List200.ItemData(i) & "'"
End If
Me.Filter = strFilter
Me.FilterOn = True
cnt = cnt + 1
End If
Next i
Exit Sub

Problem:
'Code below shows the error message
'MsgBox Err.Description, , "Error n°" & Err.Number
Err.Clear
End Sub

What I am trying to accomplish is when selecting all the records in
the
listbox have them all filter to give me the same amount of records.
There
is
a limitation and I would like to know how to workaround. I am very
new
at
this and a step by step would be greatly appreciated.