Multi Select List Box as Source for Parameter Query

[DoveArrow]

Let me try to explain exactly what I'm trying to do and see if anyone
can help me with this.

I have a table in my database, called Skills Table. It's a little
complicated to explain what 'skills' are, but suffice it to say each
skill in my table has a record source. I therefore created a second
table, called Source Table, listing the names of the record sources
that each skill could come from. I then linked this table to the
Skills Table by Source ID.

What I want to do now is create a form with a Multi Select List box,
where I can select which sources I want to use for skills, and have
that information be used as the criteria for a parameter query. I went
to the Microsoft Help and Support website (http://
support.microsoft.com/kb/135546) to see if they had any information
about how to do this, and they had two options listed.

Now I don't really understand how either of these options are supposed
to work (Example: How is the database supposed to know which query I
want to use these criteria for?). However, I could at least understand
some of what the first option was saying, so I decided to try creating
my form based on its method. Here's what I did.

1) I created a form, using the Skills Table as my Record Source.

2) I dragged the Skill ID and the Skill Name fields to the detail
section of my form (I don't know if that's what I was supposed to do,
but those sounded like the equivalent fields described on the
Microsoft Help & Support website. Later, I also tried dragging the
Source ID to the form as well, just to see if that made any
difference. It didn't.)

3) I created an unbound List Box that uses the Skills Table as the row
source, made all the appropriate adjustments, and entitled it
"SkillSourceList."

4) I then tried rewriting the code that Microsoft listed in the
following manner.

Option Compare Database
OptionExplicit

Private Sub Command7_Click()
Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![SkillSourceList].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[Source ID]='" _
& Me![SkillSourceList].ItemData(i) & "'"
Next i

' Filter the form using selected items in the list box.
Me.Filter = Criteria
Me.FilterOn = True
End Sub

5) I then went into form view and clicked my Command button and got
this error message:

"The expression On Click you entered as the event property setting
produced the following error:
Invalid outside procedure.

*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or
macro."

So what did I do wrong?

 

[DoveArrow]

Let me try to explain exactly what I'm trying to do and see if anyone
can help me with this.

I have a table in my database, called Skills Table. It's a little
complicated to explain what 'skills' are, but suffice it to say each
skill in my table has a record source. I therefore created a second
table, called Source Table, listing the names of the record sources
that each skill could come from. I then linked this table to the
Skills Table by Source ID.

What I want to do now is create a form with a Multi Select List box,
where I can select which sources I want to use for skills, and have
that information be used as the criteria for a parameter query. I went
to the Microsoft Help and Support website (http://
support.microsoft.com/kb/135546) to see if they had any information
about how to do this, and they had two options listed.

Now I don't really understand how either of these options are supposed
to work (Example: How is the database supposed to know which query I
want to use these criteria for?). However, I could at least understand
some of what the first option was saying, so I decided to try creating
my form based on its method. Here's what I did.

1) I created a form, using the Skills Table as my Record Source.

2) I dragged the Skill ID and the Skill Name fields to the detail
section of my form (I don't know if that's what I was supposed to do,
but those sounded like the equivalent fields described on the
Microsoft Help & Support website. Later, I also tried dragging the
Source ID to the form as well, just to see if that made any
difference. It didn't.)

3) I created an unbound List Box that uses the Skills Table as the row
source, made all the appropriate adjustments, and entitled it
"SkillSourceList."

4) I then tried rewriting the code that Microsoft listed in the
following manner.

Option Compare Database
OptionExplicit

Private Sub Command7_Click()
Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![SkillSourceList].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[Source ID]='" _
& Me![SkillSourceList].ItemData(i) & "'"
Next i

' Filter the form using selected items in the list box.
Me.Filter = Criteria
Me.FilterOn = True
End Sub

5) I then went into form view and clicked my Command button and got
this error message:

"The expression On Click you entered as the event property setting
produced the following error:
Invalid outside procedure.

*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or
macro."

So what did I do wrong?

Update:

I've abandoned the idea above and have been tinkering with the
following:

Private Sub Command7_Click()
On Error GoTo Error_Msg
Dim i As Integer
Criteria.SetFocus
Criteria.Text = " "
SkillSourceList.SetFocus
For i = 0 To SkillSourceList.ListCount - 1
' loop through listbox adding selected values and Or.
If SkillSourceList.Selected(i) Then
Criteria.SetFocus
Criteria.Text = Criteria.Text & " " &
SkillSourceList.Column(0, i) & " " & "Or"
SkillSourceList.SetFocus
End If
Next
' get rid of trailing Or
Criteria.SetFocus
Criteria.Text = Left(Criteria.Text, Len(Criteria.Text) - 2)
Criteria.Text = Right(Criteria.Text, Len(Criteria.Text) - 1)
SkillSourceList.SetFocus

Exit_Command7_Click:
Exit Sub

Error_Msg:
MsgBox "You must select at least one source."
Resume Exit_Command7_Click
End Sub

This fills a text box with the Source IDs separated by an or statement
(Example: If I select Record 1 and Record 4, the form fills a text box
with the statement "1 Or 4")

The problem is, the query isn't reading 1 Or 4 as if it's an
expression. I think it's reading it as if it's just text Is there a
way that I can tell the query to read the data in this text box as an
expression, rather than as text? Let me know. Thanks.

 

[pietlinden]

Let me try to explain exactly what I'm trying to do and see if anyone
can help me with this.

I have a table in my database, called Skills Table. It's a little
complicated to explain what 'skills' are, but suffice it to say each
skill in my table has a record source. I therefore created a second
table, called Source Table, listing the names of the record sources
that each skill could come from. I then linked this table to the
Skills Table by Source ID.

What I want to do now is create a form with a Multi Select List box,
where I can select which sources I want to use for skills, and have
that information be used as the criteria for a parameter query. I went
to the Microsoft Help and Support website (http://
support.microsoft.com/kb/135546) to see if they had any information
about how to do this, and they had two options listed.

Now I don't really understand how either of these options are supposed
to work (Example: How is the database supposed to know which query I
want to use these criteria for?). However, I could at least understand
some of what the first option was saying, so I decided to try creating
my form based on its method. Here's what I did.

1) I created a form, using the Skills Table as my Record Source.

2) I dragged the Skill ID and the Skill Name fields to the detail
section of my form (I don't know if that's what I was supposed to do,
but those sounded like the equivalent fields described on the
Microsoft Help & Support website. Later, I also tried dragging the
Source ID to the form as well, just to see if that made any
difference. It didn't.)

3) I created an unbound List Box that uses the Skills Table as the row
source, made all the appropriate adjustments, and entitled it
"SkillSourceList."

4) I then tried rewriting the code that Microsoft listed in the
following manner.

Option Compare Database
OptionExplicit

Private Sub Command7_Click()
Dim Criteria As String
Dim i As Variant

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![SkillSourceList].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[Source ID]='" _
& Me![SkillSourceList].ItemData(i) & "'"
Next i

' Filter the form using selected items in the list box.
Me.Filter = Criteria
Me.FilterOn = True
End Sub

5) I then went into form view and clicked my Command button and got
this error message:

"The expression On Click you entered as the event property setting
produced the following error:
Invalid outside procedure.

*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or
macro."

So what did I do wrong?

Update:

I've abandoned the idea above and have been tinkering with the
following:

Private Sub Command7_Click()
On Error GoTo Error_Msg
Dim i As Integer
Criteria.SetFocus
Criteria.Text = " "
SkillSourceList.SetFocus
For i = 0 To SkillSourceList.ListCount - 1
' loop through listbox adding selected values and Or.
If SkillSourceList.Selected(i) Then
Criteria.SetFocus
Criteria.Text = Criteria.Text & " " &
SkillSourceList.Column(0, i) & " " & "Or"
SkillSourceList.SetFocus
End If
Next
' get rid of trailing Or
Criteria.SetFocus
Criteria.Text = Left(Criteria.Text, Len(Criteria.Text) - 2)
Criteria.Text = Right(Criteria.Text, Len(Criteria.Text) - 1)
SkillSourceList.SetFocus

Exit_Command7_Click:
Exit Sub

Error_Msg:
MsgBox "You must select at least one source."
Resume Exit_Command7_Click
End Sub

This fills a text box with the Source IDs separated by an or statement
(Example: If I select Record 1 and Record 4, the form fills a text box
with the statement "1 Or 4")

The problem is, the query isn't reading 1 Or 4 as if it's an
expression. I think it's reading it as if it's just text Is there a
way that I can tell the query to read the data in this text box as an
expression, rather than as text? Let me know. Thanks.

save the query without the filter. Then add the filter at runtime
using the querydef's SQL property. Then open the query. If you're
just filtering a recordset, you could either create a report and
filter it or filter a form showing only the records you wanted.

 

[DoveArrow]

Let me try to explain exactly what I'm trying to do and see if anyone
can help me with this.
I have a table in my database, called Skills Table. It's a little
complicated to explain what 'skills' are, but suffice it to say each
skill in my table has a record source. I therefore created a second
table, called Source Table, listing the names of the record sources
that each skill could come from. I then linked this table to the
Skills Table by Source ID.
What I want to do now is create a form with a Multi Select List box,
where I can select which sources I want to use for skills, and have
that information be used as the criteria for a parameter query. I went
to the Microsoft Help and Support website (http://
support.microsoft.com/kb/135546) to see if they had any information
about how to do this, and they had two options listed.
Now I don't really understand how either of these options are supposed
to work (Example: How is the database supposed to know which query I
want to use these criteria for?). However, I could at least understand
some of what the first option was saying, so I decided to try creating
my form based on its method. Here's what I did.
1) I created a form, using the Skills Table as my Record Source.
2) I dragged the Skill ID and the Skill Name fields to the detail
section of my form (I don't know if that's what I was supposed to do,
but those sounded like the equivalent fields described on the
Microsoft Help & Support website. Later, I also tried dragging the
Source ID to the form as well, just to see if that made any
difference. It didn't.)
3) I created an unbound List Box that uses the Skills Table as the row
source, made all the appropriate adjustments, and entitled it
"SkillSourceList."
4) I then tried rewriting the code that Microsoft listed in the
following manner.
Option Compare Database
OptionExplicit
Private Sub Command7_Click()
Dim Criteria As String
Dim i As Variant
' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![SkillSourceList].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[Source ID]='" _
& Me![SkillSourceList].ItemData(i) & "'"
Next i
' Filter the form using selected items in the list box.
Me.Filter = Criteria
Me.FilterOn = True
End Sub
5) I then went into form view and clicked my Command button and got
this error message:
"The expression On Click you entered as the event property setting
produced the following error:
Invalid outside procedure.
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or
macro."
So what did I do wrong?

Update:

I've abandoned the idea above and have been tinkering with the
following:

Private Sub Command7_Click()
On Error GoTo Error_Msg
Dim i As Integer
Criteria.SetFocus
Criteria.Text = " "
SkillSourceList.SetFocus
For i = 0 To SkillSourceList.ListCount - 1
' loop through listbox adding selected values and Or.
If SkillSourceList.Selected(i) Then
Criteria.SetFocus
Criteria.Text = Criteria.Text & " " &
SkillSourceList.Column(0, i) & " " & "Or"
SkillSourceList.SetFocus
End If
Next
' get rid of trailing Or
Criteria.SetFocus
Criteria.Text = Left(Criteria.Text, Len(Criteria.Text) - 2)
Criteria.Text = Right(Criteria.Text, Len(Criteria.Text) - 1)
SkillSourceList.SetFocus

Exit_Command7_Click:
Exit Sub

Error_Msg:
MsgBox "You must select at least one source."
Resume Exit_Command7_Click
End Sub

This fills a text box with the Source IDs separated by an or statement
(Example: If I select Record 1 and Record 4, the form fills a text box
with the statement "1 Or 4")

The problem is, the query isn't reading 1 Or 4 as if it's an
expression. I think it's reading it as if it's just text Is there a
way that I can tell the query to read the data in this text box as an
expression, rather than as text? Let me know. Thanks.

save the query without the filter. Then add the filter at runtime
using the querydef's SQL property. Then open the query. If you're
just filtering a recordset, you could either create a report and
filter it or filter a form showing only the records you wanted.- Hide quoted text -

- Show quoted text -

I only have a vague understanding of what you just said, but I'm going
to try scouring the internet first and I'll get back to you if I run
into any problems.

 

[DoveArrow]

Let me try to explain exactly what I'm trying to do and see if anyone
can help me with this.
I have a table in my database, called Skills Table. It's a little
complicated to explain what 'skills' are, but suffice it to say each
skill in my table has a record source. I therefore created a second
table, called Source Table, listing the names of the record sources
that each skill could come from. I then linked this table to the
Skills Table by Source ID.
What I want to do now is create a form with a Multi Select List box,
where I can select which sources I want to use for skills, and have
that information be used as the criteria for a parameter query. I went
to the Microsoft Help and Support website (http://
support.microsoft.com/kb/135546) to see if they had any information
about how to do this, and they had two options listed.
Now I don't really understand how either of these options are supposed
to work (Example: How is the database supposed to know which query I
want to use these criteria for?). However, I could at least understand
some of what the first option was saying, so I decided to try creating
my form based on its method. Here's what I did.
1) I created a form, using the Skills Table as my Record Source.
2) I dragged the Skill ID and the Skill Name fields to the detail
section of my form (I don't know if that's what I was supposed to do,
but those sounded like the equivalent fields described on the
Microsoft Help & Support website. Later, I also tried dragging the
Source ID to the form as well, just to see if that made any
difference. It didn't.)
3) I created an unbound List Box that uses the Skills Table as the row
source, made all the appropriate adjustments, and entitled it
"SkillSourceList."
4) I then tried rewriting the code that Microsoft listed in the
following manner.
Option Compare Database
OptionExplicit
Private Sub Command7_Click()
Dim Criteria As String
Dim i As Variant
' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![SkillSourceList].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[Source ID]='" _
& Me![SkillSourceList].ItemData(i) & "'"
Next i
' Filter the form using selected items in the list box.
Me.Filter = Criteria
Me.FilterOn = True
End Sub
5) I then went into form view and clicked my Command button and got
this error message:
"The expression On Click you entered as the event property setting
produced the following error:
Invalid outside procedure.
*The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or
macro."
So what did I do wrong?
Update:
I've abandoned the idea above and have been tinkering with the
following:
Private Sub Command7_Click()
On Error GoTo Error_Msg
Dim i As Integer
Criteria.SetFocus
Criteria.Text = " "
SkillSourceList.SetFocus
For i = 0 To SkillSourceList.ListCount - 1
' loop through listbox adding selected values and Or.
If SkillSourceList.Selected(i) Then
Criteria.SetFocus
Criteria.Text = Criteria.Text & " " &
SkillSourceList.Column(0, i) & " " & "Or"
SkillSourceList.SetFocus
End If
Next
' get rid of trailing Or
Criteria.SetFocus
Criteria.Text = Left(Criteria.Text, Len(Criteria.Text) - 2)
Criteria.Text = Right(Criteria.Text, Len(Criteria.Text) - 1)
SkillSourceList.SetFocus
Exit_Command7_Click:
Exit Sub
Error_Msg:
MsgBox "You must select at least one source."
Resume Exit_Command7_Click
End Sub
This fills a text box with the Source IDs separated by an or statement
(Example: If I select Record 1 and Record 4, the form fills a text box
with the statement "1 Or 4")
The problem is, the query isn't reading 1 Or 4 as if it's an
expression. I think it's reading it as if it's just text Is there a
way that I can tell the query to read the data in this text box as an
expression, rather than as text? Let me know. Thanks.

save the query without the filter. Then add the filter at runtime
using the querydef's SQL property. Then open the query. If you're
just filtering a recordset, you could either create a report and
filter it or filter a form showing only the records you wanted.- Hide quoted text -

- Show quoted text -

I only have a vague understanding of what you just said, but I'm going
to try scouring the internet first and I'll get back to you if I run
into any problems.- Hide quoted text -

- Show quoted text -

Update:

Well, after scouring the internet, I decided to abandon my second idea
and go with this:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("SkillQuery")
For Each varItem In Me!List0.ItemsSelected
strCriteria = strCriteria & "," & Me!List0.ItemData(varItem)
Next varItem
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
strSQL = "SELECT * FROM SkillsTable " & _
"WHERE SkillsTable.SourceID IN(" & strCriteria & ");"
qdf.SQL = strSQL
DoCmd.OpenQuery "SkillQuery"
Set db = Nothing
Set qdf = Nothing

Note: You may notice that the names of some of my tables and queries
are a little different here than they were in my previous posts. This
is because I decided to create a test database to try and test for
some things like whether or not it mattered if there were spaces
between the words in the titles of my tables and queries.

In any case, this one works! The only thing that I found weird is
that it only looks at the first column in my table. This really blew
my mind, because I was under the impression that the "WHERE
SkillsTable.SourceID IN(" &strCriteria & ");" line was telling the
database to pull Skills from the Skills Table where the number in the
Column labeled 'SourceID' equals a number selected from my list box. I
guess I'm wrong, though, since that's not what it's doing. It's not a
big deal (I can just move the columns around, or have it point to a
query with the columns set up the way I want them), but I had a
dickens of a time trying to figure that part out. Out of curiosity,
what is it in this code that's telling it pull from the first column
in SkillsTable?

Regardless, I want to say thanks for pointing me in the right
direction. I wouldn't have figured it out without your suggestion.