Problem with Multi Select List Box

D

DoveArrow

I'm trying to create a Multi Select List Box where the selections will
act as the parameters for a select query. The following website(http://
www.fontstuff.com/access/acctut11pfv.htm) has been invaluable in
helping me figure out how to do this, and I wrote the following code
based off of his suggestions.

Private Sub Command7_Click()
Set db = CurrentDb()
Set qdf = db.QueryDefs("qselCharacterSkills")
For Each varItem In Me!SkillSourceList.ItemsSelected
strCriteria = strCriteria & "," & Me!
SkillSourceList.ItemData(varItem)
Next varItem
If Len(strCriteria) = 0 Then
MsgBox "You must select at least one source." _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
strSQL = "SELECT * FROM [qlkpSkills] " & _
"WHERE [qlkpSkills].[Source ID] IN(" & strCriteria & ");"
qdf.SQL = strSQL
DoCmd.RunMacro "Macro1"
Forms![Character Sheet]![qfltCharacterSkills].Requery
Forms![Character Sheet]![qfltCharacterSkills].SetFocus
Set db = Nothing
Set qdf = Nothing
End Sub

Note: Macro1, in case you're wondering, opens an append query that
appends all of the non-duplicate records collected from my select
query to a table. It then closes the form. The settings for the macro
are as follows:

Set Warnings: No
OpenQuery: qappCharactersandSkillsTable
Close: Form, Skills by Source, Prompt
Set Warnings: Yes

Now this little program works great. However, I wanted to make one
little change so that my query would not only pull everything selected
from my form, but also one other record whose Source ID is 1. I
therefore added the line "strCriteria = 1 & strCriteria" right before
the SELECT statement. The new code is as follows:

Private Sub Command7_Click()
Set db = CurrentDb()
Set qdf = db.QueryDefs("qselCharacterSkills")
For Each varItem In Me!SkillSourceList.ItemsSelected
strCriteria = strCriteria & "," & Me!
SkillSourceList.ItemData(varItem)
Next varItem
If Len(strCriteria) = 0 Then
MsgBox "You must select at least one source." _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
strCriteria = 1 & strCriteria
strSQL = "SELECT * FROM [qlkpSkills] " & _
"WHERE [qlkpSkills].[Source ID] IN(" & strCriteria & ");"
qdf.SQL = strSQL
DoCmd.RunMacro "Macro1"
Forms![Character Sheet]![qfltCharacterSkills].Requery
Forms![Character Sheet]![qfltCharacterSkills].SetFocus
Set db = Nothing
Set qdf = Nothing
End Sub

Unfortunately, this program doesn't work the first time I run it. If I
run it a second time, however, or any time after that, it works fine.
What's going on?
 
D

DoveArrow

I'm trying to create a Multi Select List Box where the selections will
act as the parameters for a select query. The following website(http://www.fontstuff.com/access/acctut11pfv.htm) has been invaluable in
helping me figure out how to do this, and I wrote the following code
based off of his suggestions.

Private Sub Command7_Click()
Set db = CurrentDb()
Set qdf = db.QueryDefs("qselCharacterSkills")
For Each varItem In Me!SkillSourceList.ItemsSelected
strCriteria = strCriteria & "," & Me!
SkillSourceList.ItemData(varItem)
Next varItem
If Len(strCriteria) = 0 Then
MsgBox "You must select at least one source." _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
strSQL = "SELECT * FROM [qlkpSkills] " & _
"WHERE [qlkpSkills].[Source ID] IN(" & strCriteria & ");"
qdf.SQL = strSQL
DoCmd.RunMacro "Macro1"
Forms![Character Sheet]![qfltCharacterSkills].Requery
Forms![Character Sheet]![qfltCharacterSkills].SetFocus
Set db = Nothing
Set qdf = Nothing
End Sub

Note: Macro1, in case you're wondering, opens an append query that
appends all of the non-duplicate records collected from my select
query to a table. It then closes the form. The settings for the macro
are as follows:

Set Warnings: No
OpenQuery: qappCharactersandSkillsTable
Close: Form, Skills by Source, Prompt
Set Warnings: Yes

Now this little program works great. However, I wanted to make one
little change so that my query would not only pull everything selected
from my form, but also one other record whose Source ID is 1. I
therefore added the line "strCriteria = 1 & strCriteria" right before
the SELECT statement. The new code is as follows:

Private Sub Command7_Click()
Set db = CurrentDb()
Set qdf = db.QueryDefs("qselCharacterSkills")
For Each varItem In Me!SkillSourceList.ItemsSelected
strCriteria = strCriteria & "," & Me!
SkillSourceList.ItemData(varItem)
Next varItem
If Len(strCriteria) = 0 Then
MsgBox "You must select at least one source." _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
strCriteria = 1 & strCriteria
strSQL = "SELECT * FROM [qlkpSkills] " & _
"WHERE [qlkpSkills].[Source ID] IN(" & strCriteria & ");"
qdf.SQL = strSQL
DoCmd.RunMacro "Macro1"
Forms![Character Sheet]![qfltCharacterSkills].Requery
Forms![Character Sheet]![qfltCharacterSkills].SetFocus
Set db = Nothing
Set qdf = Nothing
End Sub

Unfortunately, this program doesn't work the first time I run it. If I
run it a second time, however, or any time after that, it works fine.
What's going on?

Update: The new line of code that I entered is not the problem.
Instead, it looks like it's a Data Type Conversion Failure that's
happening in my Append Query. Since that's not really a discussion for
this group, I'm going to move this topic to the queries group.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top