Multi Select Lists

I found this code in a text book that works for selecting multiple items in a
list box. I can't get the bottom part to work, the part that shows how to
open the query once the parameters have been estblished. The book says to use
CreateQueryDef to open the query. My parameters are getting in correctly, but
I can't get the query to open. Has any one does this before?

Dim varItem As Variant
Dim strInClause As String
If Me!lstCities.ItemsSelected.Count = 0 Then
MsgBox("Please select at least one city.")
Else
strInClause = "[city] IN("
For Each varItem In Me!lstCities.ItemsSelected
strInClause = strInClause & """" & _
Me!lstCities.Column(0,varItem) & """" & ", "
Next varItem
'Remove the trailing comma and space from the last item
strInClause=left(strInClause,len(strInClause)-2) & ")"
End If

Dim qdf As QueryDef
Set qdf = CurrentDb.CreateQueryDef("qrySales", _
"Select * From tblSales Where " & strInClause & ";")
DoCmd.OpenQuery "qrySales"

"djf44" ha scritto nel messaggio
...
I found this code in a text book that works for selecting multiple items in
a
list box. I can't get the bottom part to work, the part that shows how to
open the query once the parameters have been estblished. The book says to
use
CreateQueryDef to open the query. My parameters are getting in correctly,
but
I can't get the query to open. Has any one does this before?

Dim varItem As Variant
Dim strInClause As String
If Me!lstCities.ItemsSelected.Count = 0 Then
MsgBox("Please select at least one city.")
Else
strInClause = "[city] IN("
For Each varItem In Me!lstCities.ItemsSelected
strInClause = strInClause & """" & _
Me!lstCities.Column(0,varItem) & """" & ", "
Next varItem
'Remove the trailing comma and space from the last item
strInClause=left(strInClause,len(strInClause)-2) & ")"
End If

Dim qdf As QueryDef
Set qdf = CurrentDb.CreateQueryDef("qrySales", _
"Select * From tblSales Where " & strInClause & ";")
DoCmd.OpenQuery "qrySales"


Hi djf44,
what kind of error do you have?
If the error is "query not found " or something similar try
Application.RefreshDatabaseWindow
before doing DoCmd.OpenQuery "qrySales"
Bye
--
Cinzia [Access MVP]
_______________________
www.riolab.org
----------------------------------------

The error says "Run-Time error 3012 Object 'qsTest' already exists." It runs
OK one time and then it says "Object already exists"

"Cinzia" wrote:


"djf44" ha scritto nel messaggio
...
I found this code in a text book that works for selecting multiple items in
a
list box. I can't get the bottom part to work, the part that shows how to
open the query once the parameters have been estblished. The book says to
use
CreateQueryDef to open the query. My parameters are getting in correctly,
but
I can't get the query to open. Has any one does this before?

Dim varItem As Variant
Dim strInClause As String
If Me!lstCities.ItemsSelected.Count = 0 Then
MsgBox("Please select at least one city.")
Else
strInClause = "[city] IN("
For Each varItem In Me!lstCities.ItemsSelected
strInClause = strInClause & """" & _
Me!lstCities.Column(0,varItem) & """" & ", "
Next varItem
'Remove the trailing comma and space from the last item
strInClause=left(strInClause,len(strInClause)-2) & ")"
End If

Dim qdf As QueryDef
Set qdf = CurrentDb.CreateQueryDef("qrySales", _
"Select * From tblSales Where " & strInClause & ";")
DoCmd.OpenQuery "qrySales"


Hi djf44,
what kind of error do you have?
If the error is "query not found " or something similar try
Application.RefreshDatabaseWindow
before doing DoCmd.OpenQuery "qrySales"
Bye
--
Cinzia [Access MVP]
_______________________
www.riolab.org
----------------------------------------

"djf44" wrote:
I found this code in a text book that works for selecting multiple items in
a
list box. I can't get the bottom part to work, the part that shows how to
open the query once the parameters have been estblished. The book says to
use
CreateQueryDef to open the query. My parameters are getting in correctly,
but
I can't get the query to open. Has any one does this before?

Dim varItem As Variant
Dim strInClause As String
If Me!lstCities.ItemsSelected.Count = 0 Then
MsgBox("Please select at least one city.")
Else
strInClause = "[city] IN("
For Each varItem In Me!lstCities.ItemsSelected
strInClause = strInClause & """" & _
Me!lstCities.Column(0,varItem) & """" & ", "
Next varItem
'Remove the trailing comma and space from the last item
strInClause=left(strInClause,len(strInClause)-2) & ")"
End If

Dim qdf As QueryDef
Set qdf = CurrentDb.CreateQueryDef("qrySales", _
"Select * From tblSales Where " & strInClause & ";")
DoCmd.OpenQuery "qrySales"


I wonder if you are not getting an error because the
query already exists?

save following in a code module:

Public Function fQueryExists(strQuery) As Boolean
On Error GoTo ErrorHandler
'http://www.woodyswatch.com/access/archtemplate.asp?4-06
Dim qdf As DAO.QueryDef

Set qdf = CurrentDb.QueryDefs(strQuery)
fQueryExists = True

ErrorHandlerExit:

Exit Function

ErrorHandler:

If Err = 3265 Then
fQueryExists = False
Resume ErrorHandlerExit
Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End If

End Function

then, try changing

Dim qdf As QueryDef
Set qdf = CurrentDb.CreateQueryDef("qrySales", _
"Select * From tblSales Where " & strInClause & ";")
DoCmd.OpenQuery "qrySales"
to

Dim qdf As DAO.QueryDef
Dim strSQL As String

strSQL = "Select * From tblSales Where " & strInClause & ";"
Debug.Print strSQL
If fQueryExists("qrySales")=True Then
CurrentDb.QueryDefs("qrySales") = strSQL
Else
Set qdf = CurrentDb.CreateQueryDef("qrySales",strSQL)
End If
DoCmd.OpenQuery "qrySales"

Dim qdf As DAO.QueryDef
Dim strSQL As String

strSQL = "Select * From tblSales Where " & strInClause & ";"
Debug.Print strSQL
If fQueryExists("qrySales")=True Then
CurrentDb.QueryDefs("qrySales").SQL = strSQL
Else
Set qdf = CurrentDb.CreateQueryDef("qrySales",strSQL)
End If
DoCmd.OpenQuery "qrySales"


"Gary Walter" wrote erroneously:
"djf44" wrote:
I found this code in a text book that works for selecting multiple items
in a
list box. I can't get the bottom part to work, the part that shows how
to
open the query once the parameters have been estblished. The book says to
use
CreateQueryDef to open the query. My parameters are getting in correctly,
but
I can't get the query to open. Has any one does this before?

Dim varItem As Variant
Dim strInClause As String
If Me!lstCities.ItemsSelected.Count = 0 Then
MsgBox("Please select at least one city.")
Else
strInClause = "[city] IN("
For Each varItem In Me!lstCities.ItemsSelected
strInClause = strInClause & """" & _
Me!lstCities.Column(0,varItem) & """" & ", "
Next varItem
'Remove the trailing comma and space from the last item
strInClause=left(strInClause,len(strInClause)-2) & ")"
End If

Dim qdf As QueryDef
Set qdf = CurrentDb.CreateQueryDef("qrySales", _
"Select * From tblSales Where " & strInClause & ";")
DoCmd.OpenQuery "qrySales"


I wonder if you are not getting an error because the
query already exists?

save following in a code module:

Public Function fQueryExists(strQuery) As Boolean
On Error GoTo ErrorHandler
'http://www.woodyswatch.com/access/archtemplate.asp?4-06
Dim qdf As DAO.QueryDef

Set qdf = CurrentDb.QueryDefs(strQuery)
fQueryExists = True

ErrorHandlerExit:

Exit Function

ErrorHandler:

If Err = 3265 Then
fQueryExists = False
Resume ErrorHandlerExit
Else
MsgBox "Error No: " & Err.Number & "; Description: " &
Err.Description
Resume ErrorHandlerExit
End If

End Function

then, try changing

Dim qdf As QueryDef
Set qdf = CurrentDb.CreateQueryDef("qrySales", _
"Select * From tblSales Where " & strInClause & ";")
DoCmd.OpenQuery "qrySales"
to

Dim qdf As DAO.QueryDef
Dim strSQL As String

strSQL = "Select * From tblSales Where " & strInClause & ";"
Debug.Print strSQL
If fQueryExists("qrySales")=True Then
CurrentDb.QueryDefs("qrySales") = strSQL
Else
Set qdf = CurrentDb.CreateQueryDef("qrySales",strSQL)
End If
DoCmd.OpenQuery "qrySales"

"Cinzia" wrote:


"djf44" ha scritto nel messaggio
...
I found this code in a text book that works for selecting multiple items
in
a
list box. I can't get the bottom part to work, the part that shows how
to
open the query once the parameters have been estblished. The book says
to
use
CreateQueryDef to open the query. My parameters are getting in
correctly,
but
I can't get the query to open. Has any one does this before?

Dim varItem As Variant
Dim strInClause As String
If Me!lstCities.ItemsSelected.Count = 0 Then
MsgBox("Please select at least one city.")
Else
strInClause = "[city] IN("
For Each varItem In Me!lstCities.ItemsSelected
strInClause = strInClause & """" & _
Me!lstCities.Column(0,varItem) & """" & ", "
Next varItem
'Remove the trailing comma and space from the last item
strInClause=left(strInClause,len(strInClause)-2) & ")"
End If

Dim qdf As QueryDef
Set qdf = CurrentDb.CreateQueryDef("qrySales", _
"Select * From tblSales Where " & strInClause & ";")
DoCmd.OpenQuery "qrySales"


Hi djf44,
what kind of error do you have?
If the error is "query not found " or something similar try
Application.RefreshDatabaseWindow
before doing DoCmd.OpenQuery "qrySales"
Bye
--


"djf44" ha scritto nel messaggio
...
The error says "Run-Time error 3012 Object 'qsTest' already exists." It
runs
OK one time and then it says "Object already exists"
Hi djf44,
it's right the first time the oject doesn't exist so all works fine, bu the
second time the object is already there so you have to delete it before
recreating:

DoCmd.DeleteObject acQuery, "qsTest"
Set qdf = CurrentDb.CreateQueryDef("qrySales", _
"Select * From tblSales Where " & strInClause & ";")
DoCmd.OpenQuery "qrySales"

bye

--
Cinzia [Office Access MVP]
_______________________
www.riolab.org
----------------------------------------