multi select requery not working

[MaryMalone]

I am trying to setup a multi select list box on a form to update a subform on
the same form, but I cant get it to requery. Can you please take a look at
the code I am using and tell me if there is something obviously incorrect? I
took it from someone elses post and then tried to adapt it to my project, but
so far hasnt worked.

Thanks!

Private Sub Command21_Click()
On Error GoTo Err_Command21_Click

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String, strWhere As String
Dim i As Integer

Set db = CurrentDb

'*** create the query based on the information on the form
strSQL = "SELECT ProjTable.* FROM ProjTable "
strWhere = "Where CostCenter IN( "
For i = 0 To lstCostCenter.ListCount - 1
If lstCostCenter.Selected(i) Then
strWhere = strWhere & "'" & lstCostCenter.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ");"
strSQL = strSQL & strWhere
'*** delete the previous query
db.QueryDefs.Delete "qryListBox"
Set qdf = db.CreateQueryDef("qryListBox", strSQL)

DoCmd.Requery "subfrmprojchange"

Exit_Command21_Click:
Exit Sub

Err_Command21_Click:
MsgBox Err.Description
Resume Exit_Command21_Click

End Sub

 

[Tom Wickerath]

Hi Mary,

There's actually no reason to have to write the SQL statement to a saved
query. You should be able to simply assign your new SQL statement as the
recordsource for the subform, which will cause it to requery at the same
time. I have several QBF examples available for download, but the easiest
one, which involves a single list box is shown here:

http://www.accessmvp.com/TWickerath/downloads/elements.zip

Other QBF examples include the following downloads, in case you are
interested:

Easiest (does not involve creating the SQL statement in code)
http://www.accessmvp.com/TWickerath/downloads/customdialogbox.zip

http://www.accessmvp.com/TWickerath/downloads/Chap08QBF.zip

and, finally one more example that is currently shown as the February 12,
2008 download on the Seattle Access User's group:

http://www.seattleaccess.org/downloads.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[MaryMalone]

Tom,

Thanks for replying so quickly.

The problem is that the main form has several combo boxes that also filter
the subform. Now I am trying to add the multi select list box to the same
form. I am not exactly an expert so I am trying to figure out if it is
possible to take the link you sent and modify it to also include other combo
boxes? Is there a way to do that? Thats why I had the code that added a new
query so that I could just link that query to the main query and it would
filter correctly and the other combo boxes still worked. Any thoughts?

Thanks!

 

[Tom Wickerath]

Hi Mary,

Have a look at the last download I suggested, which is on the Seattle Access
web site. This download includes a sample database and a Word document that
should (hopefully) be helpful to you.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________