Help for my listbox

[nilses]

Hello ,

I have a little problem with my form. I have two listbox, one for customer and one for engine. When my listbox customer is not in mode multiselect and my listbox is in multiselect mode, it's ok but when i want multiselect for custommer too, it doesn't run.

I select my custommer and i display in my listbox the engine for the customer.

In my listbox, i have this for table/query in contain:

SELECT TypeMachineParClient.Customer, TypeMachineParClient.Engine
FROM TypeMachineParClient
WHERE (((TypeMachineParClient.Customer)=[Formulaires]![MainSwitchBoard]![Customer]))
ORDER BY TypeMachineParClient.Customer;

and this for update my listbox engine when i select an other customer

Private Sub Client_AfterUpdate()
Me.typemachine.Requery
End Sub

I would like when i select for example two customers in my listbox, i would like display all the engine for the two customers.

In " WHERE (((TypeMachineParClient.Customer)=[Formulaires]![MainSwitchBoard]![Customer])) " is for one customer but how can i do if i want two, three Customers

Thanks

Nilses

 

[Jon K]

To base the query criteria on a multi-select list box, you will need VBA code.


Assuming the two multi-select list boxes are named lstCustomer and lstEngine, and lstEngine has a Column Count of 2, the following code in the On click event of lstCustomer will populate lstEngine with the selected customers and their corresponding engines:-

--------------------------------
Private Sub lstCustomer_Click()

Dim varItem As Variant
Dim sCriteria As String
Dim SQL As String

For Each varItem In Me.lstCustomer.ItemsSelected
If Len(Me.lstCustomer.ItemData(varItem)) > 0 Then
sCriteria = sCriteria & ",""" & Me.lstCustomer.ItemData(varItem) & """"
End If
Next varItem

SQL = "SELECT Customer, Engine " & _
" FROM TypeMachineParClient " & _
" WHERE Customer IN (" & Mid(sCriteria, 2) & ")" & _
" ORDER BY Customer; "

Me.lstEngine.RowSource = SQL
Me.lstEngine.Requery

End Sub
--------------------------------

Hope the code helps.

 

[nilses]

Thanks Jon K for your help, ma query run properly but in my listbox engine i have double. I have tryed DISTINCT and DISTINCTROW but it doesn't run. How can i do?.

Thanks

Nilses

This my code now


Private Sub client_Click()
Dim varItem As Variant
Dim sCriteria As String
Dim SQL As String

'Dim Msg
'Dim Response

For Each varItem In Me.client.ItemsSelected
If Len(Me.client.ItemData(varItem)) > 0 Then
sCriteria = sCriteria & "," & Chr(34) & Me.client.ItemData(varItem) & Chr(34)
End If
Next varItem

SQL = "SELECT TypeMachineParClient.NomClient,TypeMachineParClient.Type FROM TypeMachineParClient " & _
" WHERE TypeMachineParClient.NomClient IN (" & Mid(sCriteria, 2) & ")" & _
" ORDER BY TypeMachineParClient.Type;"

'Msg = SQL
'Response = MsgBox(Msg)

Me.typemachine.RowSource = SQL

End Sub

 

[Jon K]

If NomClient + Type are not unique in the table, duplicate rows will be returned. You can add the key word DISTINCT in the SQL statement to make them unique.


To illustrate this, I have attached a sample DB. I have also added a third list box called MachineType_1 in the sample. It is populated only with Type. The code used is:-
-------------------------------------
Private Sub client_Click()
Dim varItem As Variant
Dim sCriteria As String
Dim SQL As String

'Dim Msg
'Dim Response

For Each varItem In Me.client.ItemsSelected
If Len(Me.client.ItemData(varItem)) > 0 Then
sCriteria = sCriteria & "," & Chr(34) & Me.client.ItemData(varItem) & Chr(34)
End If
Next varItem

SQL = "SELECT DISTINCT TypeMachineParClient.NomClient,TypeMachineParClient.Type FROM TypeMachineParClient " & _
" WHERE TypeMachineParClient.NomClient IN (" & Mid(sCriteria, 2) & ")" & _
" ORDER BY TypeMachineParClient.Type;"

'Msg = SQL
'Response = MsgBox(Msg)

Me.typemachine.RowSource = SQL


' Populate list box TypeMachine_1 with Type only.
Dim SQL_1 As String

SQL_1 = "SELECT DISTINCT TypeMachineParClient.Type FROM TypeMachineParClient " & _
" WHERE TypeMachineParClient.NomClient IN (" & Mid(sCriteria, 2) & ")" & _
" ORDER BY TypeMachineParClient.Type;"

Me.TypeMachine_1.RowSource = SQL_1

End Sub
-------------------------------------

The sample DB is in Access 97 format. If you use Access 2000 or 2002, select Convert and save as a new name when the DB is opened for the first time.

Hope it helps.