Using a MultiList Box and a Combo Box in a query

[atrodden]

Hello,

I'm fairly new to access, very new to Forums and even less experienced with VB so any help would be greatly appreciated.

I currently have a database of customer information and form set up with a MultiList box based on the regions those companies are based. You select the region(s) you require from the list box, click a button and it runs a query.

However I now want to be able to filter the results further using a combo box with business sectors in also.

The code used for the multilist box is as follows (I don't totally understand all the code if I'm honest, but it works )

Private Sub Command2_Click()

Const cstrQuery As String = "CAMPQRY"
Dim strNames As String
Dim strSelect As String
Dim StrWhere As String
Dim varItm As Variant

strSelect = "SELECT C.*" & vbCrLf & "FROM tbl_Company AS C"

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

For Each varItm In Me.List0.ItemsSelected
strNames = strNames & ",'" & _
Me.List0.ItemData(varItm) & "'"

Next varItm
If Len(strNames) > 0 Then
strNames = Mid(strNames, 2)
strSelect = strSelect & vbCrLf & _
"WHERE C.sector IN (" & strNames & ")"
End If

Debug.Print strSelect
CurrentDb.QueryDefs(cstrQuery).SQL = strSelect
DoCmd.OpenQuery cstrQuery

End Sub

Thanks in advance

 

[Guus2005]

Your code looks fine.
With the code below every item selected in the listbox is added to strNames. Which looks ok.

For Each varItm In Me.List0.ItemsSelected
    strNames = strNames & ",'" & Me.List0.ItemData(varItm) & "'"
Next varItm

Some explanation:

Private Sub Command2_Click()

    Const cstrQuery As String = "CAMPQRY"
    Dim strNames As String
    Dim strSelect As String
    Dim StrWhere As String
    Dim varItm As Variant

    'Initiate strNames to make sure it is filled with ""
    strNames=""
    strSelect = "SELECT C.* FROM tbl_Company AS C"

    'check to see if a selection was made
    If Me.List0.ItemsSelected.Count = 0 Then
        MsgBox "Must select at least 1 Sector"
        Exit Sub
    End If

    'Runs through all selections to build strNames ",'Selection1', 'Selection2', 'Selection3'"
    For Each varItm In Me.List0.ItemsSelected
         ' Add single quote because a string is suspected.
         strNames = strNames & ",'" & Me.List0.ItemData(varItm) & "'"
    Next varItm

    If Len(strNames) > 0 Then
        'loose the first quote resulting in "'Selection1', 'Selection2', 'Selection3'"
        strNames = Mid(strNames, 2)
        strSelect = strSelect & "WHERE C.sector IN (" & strNames & ")"
    End If

    Debug.Print strSelect
    CurrentDb.QueryDefs(cstrQuery).SQL = strSelect
    DoCmd.OpenQuery cstrQuery

End Sub

What's the problem?

HTH

 

[atrodden]

Thanks for the reply, sorry I wasn't clear first time round.

I have added a combo box to the form and I want that code to find the values in the list box and the combo box.

and as you can see at the minute it is only finding the values in the list box, so i'm guessing I need some sort of where clause to include my combo box..

Hopefully this makes more sense