microsoft access - list box to build criteria

[salemnj1]

I am using a form in Microsoft Access that I'm trying to use to create a criteria statement for a query. In the list box, I have the following code entered as After Update:

Private Sub market_nm_AfterUpdate()
Dim strFilter As String
Dim varItem

strFilter = "In("
For Each varItem In market_nm.ItemsSelected
strFilter = strFilter & """ & varItem.Value & "","
Next varItem
strFilter = Left(strFilter, Len(strFilter) - 1)
strFilter = strFilter & ")"

txtFilter = strFilter


The idea is that it will create the following statement:

In("list_item1","list_item2","etc.")

But when I try to use that as my query filter (by setting the criteria line in the related query field to [forms]![form1]![market_nm],I get no results even though I know that the values in the list box are definitely in the file.

Do you know what is going wrong wtih that VBA code to prevent it from correctly forming the criteria "in" list?

Thanks,
Steven

[Hack]

Welcome to the forums.

Oddly enough, I just did this today. Although the code is in VB6, it shouldn't be that difficult to translate to VBA. With this, I'm building an IN clause

For i = 0 To lstProviders.ListCount - 1
    If lstProviders.Selected(i) = True Then
       strINClause = strINClause & "'" & lstProviders.List(i) & "'" & ", "
    End If
Next
 
'remove the last comma from the IN clause
strINClause = Left(strINClause, (Len(strINClause) - 2))

[salemnj1]

do i need to substitute something for

lstProviders.ListCount

?

[Hack]

do i need to substitute something for

lstProviders.ListCount

?

What is the VBA equivalent that will tell you how many entries there are in the ListBox? You need to loop through the entire thing, and gather all that have been selected, so you will need to know how many entries there are.

[salemnj1]

the truth is i don't really know... i'm not a real VB programmer. i'm using microsoft access and just trying to get the thing to work... so i find it all a little bit confusing.

[Hack]

I'm not a VBA programmer so I think the best way I can help you at this point is to move your question into the forum section where all the VBA people hang out. I'm certain that this can be fairly easily resolved by one of them.

Access VBA question moved to Office Development

[salemnj1]

thanks.. i hope so... i've been getting nowhere on my own...

[salemnj1]

no ideas out there?

[littlepd]

Try this:

Private Sub market_nm_AfterUpdate()
 
Dim strFilter As String
Dim i As Integer
 
strFilter = "In ("
For i = 0 To market_nm.ListCount - 1
    If market_nm.Selected(i) = True Then
        strFilter = strFilter & """ & market_nm.list(i) & "","
    End If
Next i
strFilter = strFilter & ")"
 
End Sub

[Ecniv]

strFilter = strFilter  & iif(i>0,",","") & "'" & market_nm.list(i) & "'"

Should get rid of the trailing comma...

Debug.print your sql statement to check if you need the above

[salemnj1]

My VBA Code is working swell, and it produces the exact criteria I need it to... but sadly, when I set the criteria to look at the VBA result, it doesn't work!

This is my VBA:

Private Sub market_nm_AfterUpdate()
Dim i As Integer
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim TxtFilter

For i = 0 To market_nm.ListCount - 1
If market_nm.Selected(i) Then
If market_nm.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & market_nm.Column(0, i) & "',"
End If
Next i
strWhere = "in (" & Left(strIN, Len(strIN) - 1) & ")"

TxtFilter = strWhere
End Sub

Then in my query, I set the criteria line equal to:
[forms]![test1]![txtFilter]

When I run, nothing comes back. But if I go into the VBA debugger and copy and paste the value that is being produced by the VBA code into the criteria line, the query returns a result.

Does anyone have any idea what is going on??

[kfinpgh]

I had this same problem. Here is my solution:

Make a new table: tbl selected items with 1 field: selected item. Make it text and 255 bytes.

Set up a query to collect the data wanted (names, ZIP codes, whatever)

on form load event:
Write code to open the query.
Use the query results to populate the list1 box. Microsoft's KB has a nice code sample for that.
This is what I used:
Private Sub Form_Load()
Call clearListBox
Me!mySelections.Value = Null

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim StrSQL As String, strItem As String
Dim datItem As Date
Call clearListBox
Me!mySelections.Value = Null

StrSQL = "SELECT DISTINCT [tbl month list].[set date] FROM [tbl month list];"
Set db = CurrentDb
Set rs = db.OpenRecordset(StrSQL)
Do Until rs.EOF
datItem = rs.Fields("set date").Value
strItem = Format(datItem, "mmm yy")
Me.List1.AddItem strItem ' Row Source Type must be Value List
rs.MoveNext

Loop

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub


Allow Extended selection from list1 to list2. That allows multiple items to be picked from list1 and copied to list2. To save wait time, dont remove items from list1 when they are in list2.
When user clicks the OK button,
1) delete records from tbl selected items (a generic table with 1 field - selected item)
2) loop through list2 and append a new record to tbl selected items.
see code below:
Dim oItem As Variant
Dim sTemp As String
Dim iCount As Integer
iCount = 0

If Me!List2.ListCount <> 0 Then
' set all items in list2 to selected
For iCount = 0 To Me!List2.ListCount
Me!List2.Selected(iCount) = True
Next iCount
Else
MsgBox "Nothing was selected from the list", vbInformation
Exit Sub 'Nothing was selected
End If

iCount = 0
'clean out selections list table
DoCmd.SetWarnings False
DoCmd.OpenQuery "qry delete tbl selected items contents"
DoCmd.Close acQuery, "qry delete tbl selected items contents"
' tbl selected items is generic and allows anything to be added.
Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("tbl selected items", dbOpenDynaset)
'loop through list2 and add items to the now empty table.
'this process varies with each different use. table has long text fields
'should set up contents of table to match other lookups.
For Each oItem In Me!List2.ItemsSelected
'load list2 data to string variable
sTemp = Me!List2.ItemData(oItem)
MyRS.AddNew
MyRS![selected item] = sTemp
MyRS.Update
Next oItem
MyRS.Close

Use the tbl selected items in a direct relationship to the master table. Access will find only matching records.
You need to do a separate form for each field type (ZIP, State, City, name).

[kfinpgh]

go to www.mvps.org/access and search for findandreplace(). this function allows you to replace any character or substring in a string with anything else. I used it to replace the comma with " and", then reversed the process after the user clicked OK on the form.