Multiple Listboxes on form with subform

**cbrsix** · 07-26-2013, 03:11 PM

I have a form that has 5 listboxes on it. These list boxes are set to allow multiple selections (extended not simple) which the results show in the subform. My problem is that only one of my listboxes is working properly. The others keep saying that there is data mismatch. This because it is comparing a text to number field and I can't figure out how to get it to look at number to number. Here is my code.

Code:

Private Function BuildFilter() As Variant
Dim varWhere As Variant
BuildFilter = varWhere
Dim db As Dao.Database
   Dim qdf As Dao.QueryDef
   Dim varItem As Variant
   Dim strCriteria As String
   Dim strCriteria1 As String
   Dim strCriteria2 As String
   Dim strCriteria3 As String
   Dim strCriteria4 As String
   Dim strSQL As String
   Set db = CurrentDb()
   Set qdf = db.QueryDefs("SearchEmail")
   If Me!BusinessTypeList.ItemsSelected.Count > 0 Then
      For Each varItem In Me!BusinessTypeList.ItemsSelected
         strCriteria = strCriteria & "[Account List].BusinessType = " & Chr(34) & Me!BusinessTypeList.ItemData(varItem) & Chr(34) & "OR "
      Next varItem
      strCriteria = Left(strCriteria, Len(strCriteria) - 3)
    Else
      strCriteria = "[Account List].BusinessType Like '*'"
   End If
   If Me!AccountTypeList.ItemsSelected.Count > 0 Then
      For Each varItem In Me!AccountTypeList.ItemsSelected
         strCriteria1 = strCriteria1 & "[Account List].AccountType = " & Chr(34) & Me!AccountTypeList.ItemData(varItem) & Chr(34) & "Or "
      Next varItem
      strCriteria1 = Left(strCriteria1, Len(strCriteria1) - 3)
    Else
      strCriteria1 = "[Account List].AccountType Like '*'"
   End If
   If Me!ProductTypeList.ItemsSelected.Count > 0 Then
      For Each varItem In Me!ProductTypeList.ItemsSelected
         strCriteria2 = strCriteria2 & "[Account List].[Product Type] = " & Chr(34) & Me!ProductTypeList.ItemData(varItem) & Chr(34) & "Or "
      Next varItem
      strCriteria2 = Left(strCriteria2, Len(strCriteria2) - 3)
    Else
      strCriteria2 = "[Account List].[Product Type] Like '*'"
   End If
   If Me!RepList.ItemsSelected.Count > 0 Then
      For Each varItem In Me!RepList.ItemsSelected
         strCriteria3 = strCriteria3 & "[Account List].Rep = " & Chr(34) & Me!RepList.ItemData(varItem) & Chr(34) & "Or "
      Next varItem
      strCriteria3 = Left(strCriteria3, Len(strCriteria3) - 3)
    Else
      strCriteria3 = "[Account List].Rep Like '*'"
   End If
   If Me!CompanyNameList.ItemsSelected.Count > 0 Then
      For Each varItem In Me!CompanyNameList.ItemsSelected
         strCriteria4 = strCriteria4 & "[Account List].[Company Name] = " & Chr(34) & Me!CompanyNameList.ItemData(varItem) & Chr(34) & "Or "
      Next varItem
      strCriteria4 = Left(strCriteria4, Len(strCriteria4) - 3)
    Else
      strCriteria4 = "[Account List].[Company Name] Like '*'"
   End If
   strSQL = "SELECT * FROM [Account List] " & "WHERE " & "(" & strCriteria & ") AND (" & strCriteria1 & ") AND (" & strCriteria2 & ") AND (" & strCriteria3 & ") AND (" & strCriteria4 & ")" & "; "
   Debug.Print strSQL
   qdf.SQL = strSQL
   Set db = Nothing
   Set qdf = Nothing
End Function

Code:

Private Sub FilterSelections_Click()
'Update the record source
Me.SearchEmailSubform.Form.RecordSource = "Select * From SearchEmail " & BuildFilter
'Requery the subform
Me.Form!SearchEmailSubform.Form.Requery
End Sub

This is where I believe my problem is coming from, as the other listboxes refer to different tables.

Code:

strSQL = "SELECT * FROM [Account List] " & "WHERE " & "(" & strCriteria & ") AND (" & strCriteria1 & ") AND (" & strCriteria2 & ") AND (" & strCriteria3 & ") AND (" & strCriteria4 & ")" & "; "

I can't figure out the best way or any way to have it select from different tables.

Any suggestions would be helpful along with any improvements.

Thanks.

**ssanfu** · 07-26-2013, 03:44 PM

It is a little confusing trying to follow the code, but this is what I see:

Private Sub FilterSelections_Click()
'Update the record source
Me.SearchEmailSubform.Form.RecordSource = "Select * From SearchEmail " & BuildFilter
'Requery the subform
Me.Form!SearchEmailSubform.Form.Requery
End Sub

"BuildFilter" (above in blue) is a call to the function BuildFilter(). In the 3rd line of the function, you have

Code:

BuildFilter = varWhere

That is the only place where you set "BuildFilter" to something. So this line

Code:

Me.SearchEmailSubform.Form.RecordSource = "Select * From SearchEmail " & BuildFilter

is really

Code:

Me.SearchEmailSubform.Form.RecordSource = "Select * From SearchEmail"

What are the field types for the fields:
[Account List].BusinessType
[Account List].AccountType
[Account List].[Product Type]
[Account List].Rep
[Account List].[Company Name]

Number types do not require delimiters. They would not need the double quotes ( Chr(34) ).

What is the result of the debug statement?

**pbaldy** · 07-26-2013, 03:44 PM

For a numeric data type, drop the Chr(34) from around the value.

**cbrsix** · 07-29-2013, 12:30 PM

Thanks guys. The chr(34) was the problem.

**pbaldy** · 07-29-2013, 01:02 PM

Happy to help.

**cbrsix** · 07-31-2013, 12:10 PM

Sorry, to reopen this thread, but I ran into another problem with these listboxes. I had recently switched something in my account table and how the Rep is show. Now that listbox is not working.

Code:

 If Me!RepList.ItemsSelected.Count > 0 Then
      For Each varItem In Me!RepList.ItemsSelected
         strCriteria3 = strCriteria3 & "[Account List].Rep = " & Chr(34) & Me!RepList.ItemData(varItem) & Chr(34) & "Or "
      Next varItem
      strCriteria3 = Left(strCriteria3, Len(strCriteria3) - 3)
    Else
      strCriteria3 = "[Account List].Rep Like '*'"
   End If

This is what makes up [Account List].Rep

Click image for larger version.

Name: rep.JPG
Views: 21
Size: 36.0 KB
ID: 13279

The [Rep Number] is a text field because it is made up of 1 letter and 5 numbers (ex. S09073). Any ideas why this would not be working?

**ssanfu** · 07-31-2013, 08:02 PM

Now that listbox is not working.

What does this mean? What is not working?

What is the result of

Code:

Debug.Print strSQL

Also, I would change "OR " to " OR " and change 3 to 4. (everywhere there is " OR ")

Code:

 If Me!RepList.ItemsSelected.Count > 0 Then
      For Each varItem In Me!RepList.ItemsSelected
         strCriteria3 = strCriteria3 & "[Account List].Rep = " & Chr(34) & Me!RepList.ItemData(varItem) & Chr(34) & " Or "
      Next varItem
      strCriteria3 = Left(strCriteria3, Len(strCriteria3) - 4)
    Else
      strCriteria3 = "[Account List].Rep Like '*'"
 End If

**cbrsix** · 08-01-2013, 06:53 AM

What I mean by that is that when I would select a Rep, the query would just show nothing even though I know there are results. It would not come back with any errors or anything, just show no results.

here is the result of the debug

Code:

SELECT * FROM [Account List] WHERE ([Account List].BusinessType Like '*') AND ([Account List].AccountType Like '*') AND ([Account List].ProductType Like '*') AND ([Account List.Rep] = "S09073") AND ([Account List].CompanyName Like '*') AND ([Account List].Status = 'Active');

**ssanfu** · 08-01-2013, 12:21 PM

There is an error in the result of the debug.
When I broke down the SQL string, it looks like this:

Code:

SELECT * 
FROM [Account List] 
WHERE 
([Account List].BusinessType Like '*') AND 
([Account List].AccountType Like '*') AND 
([Account List].ProductType Like '*') AND 
([Account List.Rep] = "S09073") AND  <<<<<-----
([Account List].CompanyName Like '*') AND 
([Account List].Status = 'Active');

Notice the Rep line. It has a closing bracket in the wrong place.
It should be :

Code:

([Account List].Rep = "S09073") AND

The code you posted in Post #6 looks correct. Is the actual code you use different??? Time to check your code.....

Single step...

**pbaldy** · 08-01-2013, 12:46 PM

Good eye Steve. I looked earlier and didn't spot that. It was before breakfast, so I'm blaming it on hunger.

**cbrsix** · 08-01-2013, 01:10 PM

here is the debug after I fixed that ]

It is still doing the same thing. My code now looks like it does it #6

Code:

SELECT * FROM [Account List] WHERE ([Account List].BusinessType Like '*') AND ([Account List].AccountType Like '*') AND ([Account List].ProductType Like '*') AND ([Account List].Rep = "S09073") AND ([Account List].CompanyName Like '*') AND ([Account List].Status = 'Active');

**ssanfu** · 08-01-2013, 01:33 PM

If you create a new query, switch to SQL view and paste in this line:

Code:

SELECT * FROM [Account List] WHERE ([Account List].Rep = "S09073") AND ([Account List].Status = 'Active');

Do you get the expected results??

**cbrsix** · 08-01-2013, 03:35 PM

Thanks for the help again guys. I checked my results in a query and it wasn't returning the appropriate date (so it's not the code). For some reason, it was looking at the persons name ( john doe instead of the Rep Number). The only thing that worries me is that I don't know how it changed because it was working when I first added it. But oh well, it is working perfectly now. Thanks again.

Multiple Listboxes on form with subform

Thread Tools

Multiple Listboxes on form with subform

Similar Threads

Multiple Listboxes in Search Form

Is the size of all listboxes are tied together on a subform? Can I fix this?

Use 2 listboxes to select field from main form and subform to open form in edit mode

Multiple Mutliselect Listboxes on one form- Access 2003

Multiple Listboxes

Posting Permissions