querying based on a multiselect list box

[Guest]

I am trying to build a form in Access 2003 that allows users to select from a
list and then run a query based on those selections. I need to take the
names from a list box with the multiselect property set to simple and use
them as the criteria in a query. What is the easiest way for me to do this?
I am not very skilled or familiar with using VBA.

 

[Guest]

You can try that code

Private Sub Button_Click()
Dim prm_MyCtl As Control
Dim VarItm
Dim FilterStr As String
Dim DBS As Database
Dim rst As Recordset, SqlStr As String

Set prm_MyCtl = Me![MyList]

If prm_MyCtl.ItemsSelected.Count = 0 Then
Beep
MsgBox "No Item Selected", 48
Exit Sub
End If
' run through all selected items
For Each VarItm In prm_MyCtl.ItemsSelected
FilterStr = FilterStr & prm_MyCtl.Column(2) & ","
Next
' create the selection
FilterStr = Left(FilterStr, Len(FilterStr) - 1)


Set DBS = CodeDb
' create an empty query name GlobalQuery
SqlStr = "SELECT * FROM MyTable Where MyField in(" & FilterStr & ")"
DBS.QueryDefs("GlobalQuery").SQL = SqlStr
' the new string will be entered to the query, now you can use that query
End sub

 

[Douglas J. Steele]

Just thought I'd point out that Ofer's code assumes that the field is
numeric. If it's text, you'd need to change the line

FilterStr = FilterStr & prm_MyCtl.Column(2) & ","

to

FilterStr = FilterStr & "'" & prm_MyCtl.Column(2) & "',"

where, exagerated for effect, that's

FilterStr = FilterStr & " ' " & prm_MyCtl.Column(2) & " ' ,"

Of course, THAT won't work if any of the strings include an apostrophe. In
that case, you'd need:

FilterStr = FilterStr & Chr$(34) & prm_MyCtl.Column(2) & Chr$(34) & ","

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You can try that code

Private Sub Button_Click()
Dim prm_MyCtl As Control
Dim VarItm
Dim FilterStr As String
Dim DBS As Database
Dim rst As Recordset, SqlStr As String

Set prm_MyCtl = Me![MyList]

If prm_MyCtl.ItemsSelected.Count = 0 Then
Beep
MsgBox "No Item Selected", 48
Exit Sub
End If
' run through all selected items
For Each VarItm In prm_MyCtl.ItemsSelected
FilterStr = FilterStr & prm_MyCtl.Column(2) & ","
Next
' create the selection
FilterStr = Left(FilterStr, Len(FilterStr) - 1)


Set DBS = CodeDb
' create an empty query name GlobalQuery
SqlStr = "SELECT * FROM MyTable Where MyField in(" & FilterStr & ")"
DBS.QueryDefs("GlobalQuery").SQL = SqlStr
' the new string will be entered to the query, now you can use that query
End sub

I am trying to build a form in Access 2003 that allows users to select
from a
list and then run a query based on those selections. I need to take the
names from a list box with the multiselect property set to simple and use
them as the criteria in a query. What is the easiest way for me to do
this?
I am not very skilled or familiar with using VBA.

 

[Guest]

Thanks
and in that note if the list has a date field then you should change the '
char With # char
FilterStr = FilterStr & "#" & prm_MyCtl.Column(2) & "#,"

Just thought I'd point out that Ofer's code assumes that the field is
numeric. If it's text, you'd need to change the line

FilterStr = FilterStr & prm_MyCtl.Column(2) & ","

to

FilterStr = FilterStr & "'" & prm_MyCtl.Column(2) & "',"

where, exagerated for effect, that's

FilterStr = FilterStr & " ' " & prm_MyCtl.Column(2) & " ' ,"

Of course, THAT won't work if any of the strings include an apostrophe. In
that case, you'd need:

FilterStr = FilterStr & Chr$(34) & prm_MyCtl.Column(2) & Chr$(34) & ","

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You can try that code

Private Sub Button_Click()
Dim prm_MyCtl As Control
Dim VarItm
Dim FilterStr As String
Dim DBS As Database
Dim rst As Recordset, SqlStr As String

Set prm_MyCtl = Me![MyList]

If prm_MyCtl.ItemsSelected.Count = 0 Then
Beep
MsgBox "No Item Selected", 48
Exit Sub
End If
' run through all selected items
For Each VarItm In prm_MyCtl.ItemsSelected
FilterStr = FilterStr & prm_MyCtl.Column(2) & ","
Next
' create the selection
FilterStr = Left(FilterStr, Len(FilterStr) - 1)


Set DBS = CodeDb
' create an empty query name GlobalQuery
SqlStr = "SELECT * FROM MyTable Where MyField in(" & FilterStr & ")"
DBS.QueryDefs("GlobalQuery").SQL = SqlStr
' the new string will be entered to the query, now you can use that query
End sub

I am trying to build a form in Access 2003 that allows users to select
from a
list and then run a query based on those selections. I need to take the
names from a list box with the multiselect property set to simple and use
them as the criteria in a query. What is the easiest way for me to do
this?
I am not very skilled or familiar with using VBA.

 

[Guest]

Thank you for you help.
I'm having a problem with this line in your code:
Dim DBS As Database

Also, I have 5 listboxes on the form. Can this code work with all 5?

Thanks again.

You can try that code

Private Sub Button_Click()
Dim prm_MyCtl As Control
Dim VarItm
Dim FilterStr As String
Dim DBS As Database
Dim rst As Recordset, SqlStr As String

Set prm_MyCtl = Me![MyList]

If prm_MyCtl.ItemsSelected.Count = 0 Then
Beep
MsgBox "No Item Selected", 48
Exit Sub
End If
' run through all selected items
For Each VarItm In prm_MyCtl.ItemsSelected
FilterStr = FilterStr & prm_MyCtl.Column(2) & ","
Next
' create the selection
FilterStr = Left(FilterStr, Len(FilterStr) - 1)


Set DBS = CodeDb
' create an empty query name GlobalQuery
SqlStr = "SELECT * FROM MyTable Where MyField in(" & FilterStr & ")"
DBS.QueryDefs("GlobalQuery").SQL = SqlStr
' the new string will be entered to the query, now you can use that query
End sub

I am trying to build a form in Access 2003 that allows users to select from a
list and then run a query based on those selections. I need to take the
names from a list box with the multiselect property set to simple and use
them as the criteria in a query. What is the easiest way for me to do this?
I am not very skilled or familiar with using VBA.

 

[Guest]

Yes it can work with how many list box you want, you just have to change the
name of the list you refering to

about the error you getting
check in the reference if you have reference to Access 11, if not then you
should add reference to the file msacc.olb.
to get to the reference file go into the code section, go to tools, reference.

Thank you for you help.
I'm having a problem with this line in your code:
Dim DBS As Database

Also, I have 5 listboxes on the form. Can this code work with all 5?

Thanks again.

You can try that code

Private Sub Button_Click()
Dim prm_MyCtl As Control
Dim VarItm
Dim FilterStr As String
Dim DBS As Database
Dim rst As Recordset, SqlStr As String

Set prm_MyCtl = Me![MyList]

If prm_MyCtl.ItemsSelected.Count = 0 Then
Beep
MsgBox "No Item Selected", 48
Exit Sub
End If
' run through all selected items
For Each VarItm In prm_MyCtl.ItemsSelected
FilterStr = FilterStr & prm_MyCtl.Column(2) & ","
Next
' create the selection
FilterStr = Left(FilterStr, Len(FilterStr) - 1)


Set DBS = CodeDb
' create an empty query name GlobalQuery
SqlStr = "SELECT * FROM MyTable Where MyField in(" & FilterStr & ")"
DBS.QueryDefs("GlobalQuery").SQL = SqlStr
' the new string will be entered to the query, now you can use that query
End sub

I am trying to build a form in Access 2003 that allows users to select from a
list and then run a query based on those selections. I need to take the
names from a list box with the multiselect property set to simple and use
them as the criteria in a query. What is the easiest way for me to do this?
I am not very skilled or familiar with using VBA.

 

[Douglas J Steele]

If the code is failing on Dim DBS As Database then the missing reference
would be DAO 3.6, not Access 11.

Since that's DAO code and presumably the ADO reference is still in place,
you'll probably also need to change Dim rst As Recordset to Dim rst As
DAO.Recordset (although I don't see where that object is being used in the
code snippet)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes it can work with how many list box you want, you just have to change the
name of the list you refering to

about the error you getting
check in the reference if you have reference to Access 11, if not then you
should add reference to the file msacc.olb.
to get to the reference file go into the code section, go to tools, reference.

Thank you for you help.
I'm having a problem with this line in your code:
Dim DBS As Database

Also, I have 5 listboxes on the form. Can this code work with all 5?

Thanks again.

You can try that code

Private Sub Button_Click()
Dim prm_MyCtl As Control
Dim VarItm
Dim FilterStr As String
Dim DBS As Database
Dim rst As Recordset, SqlStr As String

Set prm_MyCtl = Me![MyList]

If prm_MyCtl.ItemsSelected.Count = 0 Then
Beep
MsgBox "No Item Selected", 48
Exit Sub
End If
' run through all selected items
For Each VarItm In prm_MyCtl.ItemsSelected
FilterStr = FilterStr & prm_MyCtl.Column(2) & ","
Next
' create the selection
FilterStr = Left(FilterStr, Len(FilterStr) - 1)


Set DBS = CodeDb
' create an empty query name GlobalQuery
SqlStr = "SELECT * FROM MyTable Where MyField in(" & FilterStr & ")"
DBS.QueryDefs("GlobalQuery").SQL = SqlStr
' the new string will be entered to the query, now you can use that query
End sub

:

I am trying to build a form in Access 2003 that allows users to select from a
list and then run a query based on those selections. I need to take the
names from a list box with the multiselect property set to simple and use
them as the criteria in a query. What is the easiest way for me to do this?
I am not very skilled or familiar with using VBA.

 

[Guest]

You right, thank you

If the code is failing on Dim DBS As Database then the missing reference
would be DAO 3.6, not Access 11.

Since that's DAO code and presumably the ADO reference is still in place,
you'll probably also need to change Dim rst As Recordset to Dim rst As
DAO.Recordset (although I don't see where that object is being used in the
code snippet)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes it can work with how many list box you want, you just have to change the
name of the list you refering to

about the error you getting
check in the reference if you have reference to Access 11, if not then you
should add reference to the file msacc.olb.
to get to the reference file go into the code section, go to tools, reference.

Thank you for you help.
I'm having a problem with this line in your code:
Dim DBS As Database

Also, I have 5 listboxes on the form. Can this code work with all 5?

Thanks again.

:

You can try that code

Private Sub Button_Click()
Dim prm_MyCtl As Control
Dim VarItm
Dim FilterStr As String
Dim DBS As Database
Dim rst As Recordset, SqlStr As String

Set prm_MyCtl = Me![MyList]

If prm_MyCtl.ItemsSelected.Count = 0 Then
Beep
MsgBox "No Item Selected", 48
Exit Sub
End If
' run through all selected items
For Each VarItm In prm_MyCtl.ItemsSelected
FilterStr = FilterStr & prm_MyCtl.Column(2) & ","
Next
' create the selection
FilterStr = Left(FilterStr, Len(FilterStr) - 1)


Set DBS = CodeDb
' create an empty query name GlobalQuery
SqlStr = "SELECT * FROM MyTable Where MyField in(" & FilterStr & ")"
DBS.QueryDefs("GlobalQuery").SQL = SqlStr
' the new string will be entered to the query, now you can use that query
End sub

:

I am trying to build a form in Access 2003 that allows users to select from a
list and then run a query based on those selections. I need to take the
names from a list box with the multiselect property set to simple and use
them as the criteria in a query. What is the easiest way for me to do this?
I am not very skilled or familiar with using VBA.