Need help retrieving in clause from text box

[klg]

Hi,

I'm trying to use a multi list box to capture the constraints for an in clause. I format the selections of the multi-select list box into a text box and am trying to point my query constraint to that text box. Below is my VBA Code:
-----------
Private Sub Command3_Click()
On Error GoTo Err_Command3_Click

Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

If Me.List0.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 criteria"
Exit Sub
End If



Set ctl = Me.List0
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & "'" & "," & "'"
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 3)
'open the report, restricted to the selected items
Me.Text9 = "'" & strWhere & "'"
Me.Form6.Requery


Exit_Command3_Click:
Exit Sub

Err_Command3_Click:
MsgBox Err.Description
Resume Exit_Command3_Click
End Sub
-------------
As for the constraint in my query, it's fairly simple
In ([Forms]![Form5]![Text9])

What am I doing wrong? If I manually input a single value, the query works fine, but multiple values it returns nothing. It's driving me crazy!!! Please HELP!!!

Thanks,
ED

[boblarson]

A few things - It isn't going to work like that. You will need to set the Where by using a querydef object. You can replace the whole SQL statement but you can also take this free code from Access MVP Armen Stein
http://www.jstreettech.com/files/basJStreetSQLTools.zip
and put it into a standard module and name the module basJStreetSQLTools and then you can just replace the where clause using the ReplaceWhereClause function in his code. I use it here where I work and it is great.

So, if you have that code in your database you would do this:

Private Sub Command3_Click()
On Error GoTo Err_Command3_Click
 
Dim strWhere As String
Dim qdf As DAO.QueryDef
Dim varItem As Variant
 
If Me.List0.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 criteria"
Exit_Command3_Click
End If
 
 
For Each varItem In Me.List0.ItemsSelected
strWhere = strWhere & Chr(34) & Me.List0.ItemData(varItem) & Chr(34) & "," 
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)
 
strWhere = "Where FIELDNAMEHERE In(" & strWhere & ")"Set qdf = CurrentDatabase.QueryDefs("QueryNameHere")
qdf.SQL = ReplaceWhereClause(qdf.SQL, strWhere)
qdf.Close
 
'open the report, restricted to the selected items
Me.Form6.Requery
 
 
Exit_Command3_Click:
Exit Sub
 
Err_Command3_Click:
MsgBox Err.Description
Resume Exit_Command3_Click
End Sub

Also, what is ME.FORM6.REQUERY? Is Form6 a subform on the current form? Me refers to the current form, so if you are requerying the current form then it would just be ME.REQUERY.

Also, you forgot in your other code to have the IN( ) part in there. But it still won't work that way. Also, you had your quotes in the wrong place. You had it after the comma which isn't right and so the very first one would not have a quote which would make it fail anyway.

[klg]

Thanks for the help. Just to give more background. I have a main form where the controls are. Also in this main form is a subform that display a Pivot Chart based upon a query (hence the me.form6.requery). Unfortunately I need to stay away from opening new forms or pop-ups, hence I need to requery or refresh the subform pivot chart.

I would love to try out the method you mentioned. Do you happen to have the sample code from Access MVP Armen Stein? I assume the new object will replace the where and from there I can just requery my subform right?

Thanks,
Ed

[boblarson]

I would love to try out the method you mentioned. Do you happen to have the sample code from Access MVP Armen Stein?

I posted the link to the zip file for the code. But here it is again:
http://www.jstreettech.com/files/basJStreetSQLTools.zip

Just unzip it and paste the contents of the text file into a brand new standard module.

I assume the new object will replace the where and from there I can just requery my subform right?

That is correct.

[klg]

Sorry, missed the link. Will give it a shot.

Thanks,
Ed

[klg]

hi boblarson, i did as specified (saved the module and modified the code). however i'm getting a 'Object required' error. am i missing something?

thanks,
ed

[boblarson]

1. Do you have a DAO reference set? You should have one based on the file type (mdb or accdb) you are using.

ACCDB would require - Microsoft Office Access 14.0 Database Engine Object Library

MDB would require - Microsoft DAO 3.x (where .x could be either 51 or 6 depending on what is available. Use .6 if available).

And if that doesn't help. Find out which line the code is erroring on and post that.

[klg]

yes, i do have the DAO reference set up. I can even see the QueryDef object in the object browser (am using ACCDB). how do I find out which line of code is the error on (sorry, I'm fairly new at this)?

[boblarson]

yes, i do have the DAO reference set up. I can even see the QueryDef object in the object browser (am using ACCDB). how do I find out which line of code is the error on (sorry, I'm fairly new at this)?

Make sure any error handling is commented out (just the ON ERROR GOTO bits, you don't have to comment out the actual error handlers) and then when the error comes up, click the DEBUG button and it will take you to the offending line of code. Post that so we can see what it is.

[klg]

Thanks for the direction. Below is the line of code that caused the error

Set qdf = CurrentDatabase.QueryDefs("Query1")

[boblarson]

So you made sure to

1. include the
Dim qdf As DAO.QueryDef
code in the procedure? Also, make sure it is QueryDef and not QueryDefs

2. Query1 exists and is saved?

[klg]

Removed the s and still having the same error. Query1 is saved. Below is the code I have:

Private Sub Command3_Click()
'On Error GoTo Err_Command3_Click

Dim strWHERE As String
Dim qdf As DAO.QueryDef
Dim ctl As Control
Dim varItem As Variant

'make sure a selection has been made
If Me.List0.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 criteria"
Exit Sub
End If

'add selected values to string
Set ctl = Me.List0
For Each varItem In ctl.ItemsSelected
strWHERE = strWHERE & Chr(34) & Me.List0.ItemData(varItem) & Chr(34) & ","
Next varItem
'trim trailing comma
strWHERE = Left(strWHERE, Len(strWHERE) - 1)

strWHERE = "Where BU_NAME In(" & strWHERE & ")"
Set qdf = CurrentDatabase.QueryDef("Query1")
qdf.SQL = ReplaceWhereClause(qdf.SQL, strWHERE)
qdf.Close

'Requery subForm
Me.Form6.Requery

Exit_Command3_Click:
Exit Sub

Err_Command3_Click:
MsgBox Err.Description
Resume Exit_Command3_Click
End Sub

[boblarson]

No, you don't remove the S from this line:
Set qdf = CurrentDatabase.QueryDef("Query1")

it should be

Set qdf = CurrentDatabase.QueryDefs("Query1")

I meant the Dim qdf As DAO.QueryDef line. But are you absolutely sure you have the right DAO Reference checked? If your file is an ACCDB file you need to NOT use DAO 3.x but instead Microsoft Office 14.0 Access Database Engine Object Library. And that is in the VBA Window under TOOLS > REFERENCES.

[klg]

Yes. I am certain I have the Engine Object Library. I was able to the QueryDef and QueryDefs object within my Object Browser.

[klg]

I was able to resolve the error by using CurrentDb instead of CurrentDatabase. However I can't seem to get my subform to refresh using the edited query with the new Where clause.