Running query with input from list boxes

[Marie via AccessMonster.com]

I have a form with multiple list boxes that the user can use to select
multiple entries, e.g States, Counties, etc.
The user selects a state and the associated counties display. The user can
then select a county, or counties and then click the Run Query button.
The following code is attached to the Run Query button.
The problem is when I click the Run Query button the hourglass briefly
displays on the screen, but nothing returns.
I must be missing something to run the query, but don't know what it is.
Any help would be greatly appreciated!

*****************************
Private Sub cmdRunQuery_Click()
On Error GoTo Err_cmdRunQuery_Click

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSQL As String
Dim StrTemp As String
Dim qryDefname As String
Dim SQLWHERE As String
Dim SQLWHERECounty As String
Dim SQLWHEREState As String
Dim SQLWHEREStoreStatus As String
Dim SQLWHERECLAUSE As String
Dim varItem As Variant

SQLWHERE = ""
SQLWHEREState = ""
SQLWHEREStoreStatus = ""
SQLWHERECounty = ""
SQLWHERECLAUSE = ""

Set db = CurrentDb

On Error Resume Next
'remove existing querydef if exists
db.QueryDefs.Delete "qryActiveStores"


strSQL = "SELECT tblStoreInfo.Store, tblStoreInfo.Area, tblStoreInfo.Region,
tblStoreInfo.District, tblStoreInfo.[24 Hour Flag],"
strSQL = strSQL & "tblStoreInfo.DC, tblStoreInfo.Address, tblStoreInfo.City,
tblStoreInfo.State, tblStoreInfo.Zip,"
strSQL = strSQL & "tblStoreInfo.County, tblStoreInfo.[Area Mgr Name],
tblStoreInfo.[Region Mgr Name], tblStoreInfo.[District Mgr Name], "
strSQL = strSQL & "tblStoreInfo.[Regional Healthcare Mgr Name_1],
tblStoreInfo.[Regional Healthcare Mgr Name_2],"
strSQL = strSQL & "tblStoreInfo.[Store Status], tblStoreInfo.FID,
tblStoreInfo.Open, tblStoreInfo.Closed,"
strSQL = strSQL & "FROM tblStoreInfo "


If State.ItemsSelected.Count > 0 And State.ItemsSelected.Count < State.
ListCount Then
For Each varItem In State.ItemsSelected
If Len(SQLWHEREState) > 0 Then
SQLWHEREState = SQLWHEREState & " OR tblStoreInfo.State = '" &
CStr(State.ItemData(varItem)) & "'"
Else
SQLWHEREState = " ( tblStoreInfo.State = " & "'" & CStr(State.
ItemData(varItem)) & "'"
End If
Next varItem
SQLWHEREState = SQLWHEREState & " )"
End If


If County.ItemsSelected.Count > 0 And County.ItemsSelected.Count < County.
ListCount Then
For Each varItem In County.ItemsSelected
If Len(SQLWHERECounty) > 0 Then
SQLWHERECounty = SQLWHERECounty & " OR tblStoreInfo.County = '"
& CStr(County.ItemData(varItem)) & "'"
Else
SQLWHERECounty = " ( tblStoreInfo.County = " & "'" & CStr(County.
ItemData(varItem)) & "'"
End If
Next varItem
SQLWHERECounty = SQLWHERECounty & " )"
End If


If Len(SQLWHEREState) = 0 Then
Else
SQLWHERECLAUSE = SQLWHERECLAUSE & SQLWHEREState
End If


If Len(SQLWHERECounty) = 0 Then
Else
If Len(SQLWHERECLAUSE) > 0 Then
SQLWHERECLAUSE = SQLWHERECLAUSE & " AND " & SQLWHERECounty
Else
SQLWHERECLAUSE = SQLWHERECLAUSE & SQLWHERECounty
End If
End If


'SQLWHERECLAUSE = SQLWHERECLAUSE & SQLWHEREStoreStatus
If Len(SQLWHERECLAUSE) > 0 Then
strSQL = strSQL & "WHERE tblStoreInfo.[Store Status] = 'open' AND " &
SQLWHERECLAUSE
Else
strSQL = strSQL & "WHERE tblStoreInfo.[Store Status] = 'open'"
End If


'Build query def name
qryDefname = "qryActiveStores"

Set qd = db.CreateQueryDef(qryDefname, strSQL)
DoCmd.OpenQuery qryDefname

Exit_cmdRunQuery_Click:
Exit Sub

Err_cmdRunQuery_Click:
MsgBox Err.Description
Resume Exit_cmdRunQuery_Click

End Sub
**********************************

 

[Duane Hookom]

You don't need to delete the querydef. Just update the SQL property of the
existing query.
What have you done to trouble-shoot? Have you tried to open
"qryActiveStores" in design view to see if there is an issue?
Does the behavior depend on the user selections in the list boxes?

--
Duane Hookom
MS Access MVP

I have a form with multiple list boxes that the user can use to select
multiple entries, e.g States, Counties, etc.
The user selects a state and the associated counties display. The user can
then select a county, or counties and then click the Run Query button.
The following code is attached to the Run Query button.
The problem is when I click the Run Query button the hourglass briefly
displays on the screen, but nothing returns.
I must be missing something to run the query, but don't know what it is.
Any help would be greatly appreciated!

*****************************
Private Sub cmdRunQuery_Click()
On Error GoTo Err_cmdRunQuery_Click

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSQL As String
Dim StrTemp As String
Dim qryDefname As String
Dim SQLWHERE As String
Dim SQLWHERECounty As String
Dim SQLWHEREState As String
Dim SQLWHEREStoreStatus As String
Dim SQLWHERECLAUSE As String
Dim varItem As Variant

SQLWHERE = ""
SQLWHEREState = ""
SQLWHEREStoreStatus = ""
SQLWHERECounty = ""
SQLWHERECLAUSE = ""

Set db = CurrentDb

On Error Resume Next
'remove existing querydef if exists
db.QueryDefs.Delete "qryActiveStores"


strSQL = "SELECT tblStoreInfo.Store, tblStoreInfo.Area,
tblStoreInfo.Region,
tblStoreInfo.District, tblStoreInfo.[24 Hour Flag],"
strSQL = strSQL & "tblStoreInfo.DC, tblStoreInfo.Address,
tblStoreInfo.City,
tblStoreInfo.State, tblStoreInfo.Zip,"
strSQL = strSQL & "tblStoreInfo.County, tblStoreInfo.[Area Mgr Name],
tblStoreInfo.[Region Mgr Name], tblStoreInfo.[District Mgr Name], "
strSQL = strSQL & "tblStoreInfo.[Regional Healthcare Mgr Name_1],
tblStoreInfo.[Regional Healthcare Mgr Name_2],"
strSQL = strSQL & "tblStoreInfo.[Store Status], tblStoreInfo.FID,
tblStoreInfo.Open, tblStoreInfo.Closed,"
strSQL = strSQL & "FROM tblStoreInfo "


If State.ItemsSelected.Count > 0 And State.ItemsSelected.Count <
State.
ListCount Then
For Each varItem In State.ItemsSelected
If Len(SQLWHEREState) > 0 Then
SQLWHEREState = SQLWHEREState & " OR tblStoreInfo.State = '"
&
CStr(State.ItemData(varItem)) & "'"
Else
SQLWHEREState = " ( tblStoreInfo.State = " & "'" &
CStr(State.
ItemData(varItem)) & "'"
End If
Next varItem
SQLWHEREState = SQLWHEREState & " )"
End If


If County.ItemsSelected.Count > 0 And County.ItemsSelected.Count <
County.
ListCount Then
For Each varItem In County.ItemsSelected
If Len(SQLWHERECounty) > 0 Then
SQLWHERECounty = SQLWHERECounty & " OR tblStoreInfo.County =
'"
& CStr(County.ItemData(varItem)) & "'"
Else
SQLWHERECounty = " ( tblStoreInfo.County = " & "'" &
CStr(County.
ItemData(varItem)) & "'"
End If
Next varItem
SQLWHERECounty = SQLWHERECounty & " )"
End If


If Len(SQLWHEREState) = 0 Then
Else
SQLWHERECLAUSE = SQLWHERECLAUSE & SQLWHEREState
End If


If Len(SQLWHERECounty) = 0 Then
Else
If Len(SQLWHERECLAUSE) > 0 Then
SQLWHERECLAUSE = SQLWHERECLAUSE & " AND " & SQLWHERECounty
Else
SQLWHERECLAUSE = SQLWHERECLAUSE & SQLWHERECounty
End If
End If


'SQLWHERECLAUSE = SQLWHERECLAUSE & SQLWHEREStoreStatus
If Len(SQLWHERECLAUSE) > 0 Then
strSQL = strSQL & "WHERE tblStoreInfo.[Store Status] = 'open' AND " &
SQLWHERECLAUSE
Else
strSQL = strSQL & "WHERE tblStoreInfo.[Store Status] = 'open'"
End If


'Build query def name
qryDefname = "qryActiveStores"

Set qd = db.CreateQueryDef(qryDefname, strSQL)
DoCmd.OpenQuery qryDefname

Exit_cmdRunQuery_Click:
Exit Sub

Err_cmdRunQuery_Click:
MsgBox Err.Description
Resume Exit_cmdRunQuery_Click

End Sub
**********************************

 

[Marie via AccessMonster.com]

Duane,

Thanks for your quick response.
I borrowed this code from another database with a similar setup. The code
works fine in the other database as posted. But in the new database, the
query will not display.
I am relatively new to writing code - the code in the other database was
written by someone else.

The "qryActiveStores" does not exist in my database. It is built when I run
the query and then deleted.
I am not sure what you mean by updating the SQL property.
The result is the same regardless if the user does not select an entry or
only selects one state, etc.

I don't understand why it is working in one database and not another.
If you need any other info, let me know.
Appreciate your help!

You don't need to delete the querydef. Just update the SQL property of the
existing query.
What have you done to trouble-shoot? Have you tried to open
"qryActiveStores" in design view to see if there is an issue?
Does the behavior depend on the user selections in the list boxes?

I have a form with multiple list boxes that the user can use to select
multiple entries, e.g States, Counties, etc.

[quoted text clipped - 120 lines]

End Sub
**********************************

 

[Duane Hookom]

I asked several questions which you didn't answer:
-What have you done to trouble-shoot?
-Have you tried to open 'qryActiveStores' in
design view to see if there is an issue?
-Does the behavior depend on the user
selections in the list boxes?

If the query doesn't exist then why delete it? If you can be sure it exists,
it might be simpler to use code like:
db.QueryDefs("qryActiveStore").SQL = strSQL


--
Duane Hookom
MS Access MVP

Duane,

Thanks for your quick response.
I borrowed this code from another database with a similar setup. The code
works fine in the other database as posted. But in the new database, the
query will not display.
I am relatively new to writing code - the code in the other database was
written by someone else.

The "qryActiveStores" does not exist in my database. It is built when I
run
the query and then deleted.
I am not sure what you mean by updating the SQL property.
The result is the same regardless if the user does not select an entry or
only selects one state, etc.

I don't understand why it is working in one database and not another.
If you need any other info, let me know.
Appreciate your help!

You don't need to delete the querydef. Just update the SQL property of the
existing query.
What have you done to trouble-shoot? Have you tried to open
"qryActiveStores" in design view to see if there is an issue?
Does the behavior depend on the user selections in the list boxes?

I have a form with multiple list boxes that the user can use to select
multiple entries, e.g States, Counties, etc.

[quoted text clipped - 120 lines]

End Sub
**********************************

 

[Marie via AccessMonster.com]

Duane,
I tried to answer your questions - see below

I asked several questions which you didn't answer:
-What have you done to trouble-shoot?
-Have you tried to open 'qryActiveStores' in
design view to see if there is an issue?

No, because, the way the code was originally designed for the form, the query
would be created after the criteria were selected from the list boxes and the
Run Query button was pressed.
Then I deleted the query, so the next user would start with a query without
any criteria.

-Does the behavior depend on the user
selections in the list boxes?

As I mentioned, the query will not run whether the user selects an entry in
the list box or just clicks the Run Query button. The original form runs the
query regardless if there is a selection(s) in the list boxes.

If the query doesn't exist then why delete it? If you can be sure it exists,
it might be simpler to use code like:
db.QueryDefs("qryActiveStore").SQL = strSQL

I tried creating a query and saving it as "qryActiveStores" and using the
above line of code instead of the following lines of code, but it does not
take the WHERECLAUSE that is built into account.

'Build query def name
qryDefname = "qryActiveStores"

Set qd = db.CreateQueryDef(qryDefname, strSQL)
DoCmd.OpenQuery qryDefname
I cannot understand why this code works on a similar form in a different
database, but not here.
Any other ideas would be greatly appreciated.
Marie

Duane,

[quoted text clipped - 27 lines]

 

[Duane Hookom]

Your code create a query in your mdb named "qryActiveStores". As I asked
twice, please find this query in the database window and attempt to open it
in design view. What happens when you try to view its datasheet? What is its
SQL view?

Have you attempted to compile your code?

--
Duane Hookom
MS Access MVP

Duane,
I tried to answer your questions - see below

I asked several questions which you didn't answer:
-What have you done to trouble-shoot?
-Have you tried to open 'qryActiveStores' in
design view to see if there is an issue?

No, because, the way the code was originally designed for the form, the
query
would be created after the criteria were selected from the list boxes and
the
Run Query button was pressed.
Then I deleted the query, so the next user would start with a query
without
any criteria.

-Does the behavior depend on the user
selections in the list boxes?

As I mentioned, the query will not run whether the user selects an entry
in
the list box or just clicks the Run Query button. The original form runs
the
query regardless if there is a selection(s) in the list boxes.

If the query doesn't exist then why delete it? If you can be sure it
exists,
it might be simpler to use code like:
db.QueryDefs("qryActiveStore").SQL = strSQL

I tried creating a query and saving it as "qryActiveStores" and using the
above line of code instead of the following lines of code, but it does not
take the WHERECLAUSE that is built into account.

'Build query def name
qryDefname = "qryActiveStores"

Set qd = db.CreateQueryDef(qryDefname, strSQL)
DoCmd.OpenQuery qryDefname
I cannot understand why this code works on a similar form in a different
database, but not here.
Any other ideas would be greatly appreciated.
Marie

Duane,

[quoted text clipped - 27 lines]

End Sub
**********************************