After 84 fields, my procedure doesn't run

[nilses]

Hello,

I have small a problem on my procedure. I have Two listbox, one for the customer and one for the type of machine. When i select the customer, i display the machine for the custommer and when i select most customer, i display the machine for these customers and i remove the doubled. I use this code for my procedure. It posts me an error on this line "Me.typemachine.RowSource = SQL" in yellow.

It does not agree to take too much line. It takes the values chosen in my listbox and after selected 84 fields it say me this message Error '2176', 'the parameter of this property is too long' . If i understand, my string supports until 84 fields or if i understand 255 caracters. How then I to make to exceed the 84 fields or 255 caracters in my string.


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.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



Your help will be appreciated.

Nilses

 

[Jon K]

Try this.

Create a table "tblType" with a field "Type".

Change the SQL to append the records to this table, and set the RowSource of the TypeMachine list box to this table:-


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

DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * from tblType"
DoCmd.RunSQL SQL
DoCmd.SetWarnings True

Me.typemachine.RowSource = "tblType"