INSERT Statement: Limit on how many rows you can attempt

[chillay]

Hello

I have this code in one of my form. When I try to run it I get an error message about "SQL expression is too complex" whenever I have more than 25 rows in my GroupAttendance listbox. The code below works fine when I have less than 25 items.

Is there a limit on how many rows you can attempt to INSERT in SQL?

Private Sub Command103_Click()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL1 As String, strSQL2 As String, strSQL3 As String
Dim ndx As Integer
Dim rowcount As Integer

'Store all clientid in groupattendance in a temporary table
strSQL1 = "INSERT INTO tblTemp ( clientid ) "
strSQL1 = strSQL1 & "SELECT dsdtcmas.clientid "
strSQL1 = strSQL1 & "FROM dsdtcmas "
strSQL1 = strSQL1 & "WHERE dsdtcmas.clientid In ("

'build the sql string by adding one clientid after another as it iterates down the listbox
For ndx = 0 To Me!GroupAttendance2.ListCount - 1
strSQL1 = strSQL1 & Chr(34) & Me!GroupAttendance2.ItemData(ndx) & Chr(34) & ", "
Next ndx

'remove last ", " and add closing parenthesis
strSQL1 = Left$(strSQL1, Len(strSQL1) - 2) & ")"

MsgBox strSQL1
DoCmd.RunSQL strSQL1

end sub

Thank you.

Chillay

 

[PHV]

You may try something like this:
'Store all selected clientid in groupattendance in a temporary table
DoCmd.SetWarnings False
For ndx = 0 To Me!GroupAttendance2.ListCount - 1
If Me!GroupAttendance2.Selected(ndx) Then
strSQL1 = "INSERT INTO tblTemp (clientid) " _
& "SELECT clientid FROM dsdtcmas " _
& "WHERE clientid='" & Me!GroupAttendance2.ItemData(ndx) & "'"
DoCmd.RunSQL strSQL1
End If
Next ndx
DoCmd.SetWarnings True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Golom]

PHV's suggestion should work (i.e. take them one at a time and run an INSERT for each.) You might also think about loading your listbox entries into a TempClientID temporary table and then doing something like

INSERT INTO tblTemp (clientid)
Select A.ClientID 
FROM dsdtcmas A INNER JOIN TempClientID T 
     ON A.ClientID = T.ClientID