Multi Select Lists

D

djf44

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"
 
C

Cinzia

djf44 said:
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"
Click to expand...

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
 
D

djf44

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

Cinzia said:
djf44 said:
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"
Click to expand...

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
Click to expand...
 
G

Gary Walter

djf44 said:
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"
Click to expand...

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"
Click to expand...
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"
 
G

Gary Walter

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"


djf44 said:
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"
Click to expand...

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"
Click to expand...
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"
Click to expand...
 
C

Cinzia

Cinzia said:
djf44 said:
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"
Click to expand...

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
--
Click to expand...
Click to expand...


djf44 said:
The error says "Run-Time error 3012 Object 'qsTest' already exists." It
runs
OK one time and then it says "Object already exists"
Click to expand...
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
 

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

Similar Threads

Multi Select List 4
Multi Select List Box 6
Requery after you modify the SQL statement in VBA Access? 3
Query Inside an SQL statement 3
IN in query 8
Building WHERE string; Passing Combo Box Value to a Query 1
Multi Selection List Box Query 6
Problems with SQL in vba code 2

Top