SQL too complex (Access 2K)

Topic

New Reply

I have this procedure attached to a button on a form. This button works fine is I have less than 25 people on the GroupAttendance listbox. When I have more than that, I get an error message about the SQL being too complex. How can I change the code below to shortened the sql string. Getting this one to work already took a long time.

Private Sub cmdCopyNote_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

DoCmd.SetWarnings False

Set frm = Forms!frmGroupAttendanceNotes
Set ctl = frm!GroupAttendance2

‘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)= ‘”

Me!GroupAttendance2.Selected(0) = True

‘make sure all clientids are highlighted
For ndx = 0 To Me!GroupAttendance2.ListCount
Me!GroupAttendance2.Selected(ndx) = True
Next ndx

‘build the sql string by adding one clientid after another as it iterates down the listbox
For Each varItem In ctl.ItemsSelected
strSQL1 = strSQL1 & ctl.ItemData(varItem) & “‘ OR (dsdtcmas.clientid) = ‘”
Next varItem

‘final query statement after deleting the (dsdtcmas.clientid) = ‘” at the end of statement – 27 spaces
strSQL1 = Left$(strSQL1, Len(strSQL1) – 27)
‘MsgBox strSQL1
DoCmd.RunSQL strSQL1

strSQL2 = “INSERT INTO tblClientNotes ( act, servdate, Notes, NoteDate, Clinician, SessionStart, SessionEnd, clientid ) ”
strSQL2 = strSQL2 & “SELECT tblGroupNotes.act, tblGroupNotes.servdate, tblGroupNotes.Notes, tblGroupNotes.NoteDate, tblGroupNotes.Clinician, tblGroupNotes.SessionStart, tblGroupNotes.SessionEnd, tblTemp.clientid ”
strSQL2 = strSQL2 & “FROM tblGroupNotes, tblTemp ”
strSQL2 = strSQL2 & “WHERE (tblGroupNotes.NoteID)=[Forms]![frmGroupNotes]![NoteID];”
‘MsgBox (strSQL2)
DoCmd.RunSQL strSQL2

Call LongLoop

strSQL3 = “DELETE tblTemp.clientid FROM tblTemp;”
DoCmd.RunSQL strSQL3

DoCmd.SetWarnings True

For ndx = 0 To Me!GroupAttendance2.ListCount
Me!GroupAttendance.Selected(ndx) = False
Next ndx

End Sub

Reply | Quote

Replies

Why do you select all items in the list box? You can loop through the items without selecting them.
You can use In(…) to simplify the where clause.

‘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

Reply | Quote

Hello hans,
i tried your code. however, when there is more than 25 items in the listbox I still get the SQL too complex error message. is it normal for Access to behave like this?

i forgot to mention that the original table that i am copying the data from (dsdtcmas) is an old foxpro dbf type file, may be this has something to do with it.

Reply | Quote

I changed my code to look like this and this seems to work so far:

Dim varItem As Variant
Dim strSQL1 As String, strSQL2 As String, strSQL3 As String
Dim ndx As Integer

DoCmd.SetWarnings False

For ndx = 0 To Me!GroupAttendance2.ListCount – 1
strSQL1 = “INSERT INTO tblTemp ( clientid ) ”
strSQL1 = strSQL1 & “SELECT dsdtcmas.clientid ”
strSQL1 = strSQL1 & “FROM dsdtcmas ”
strSQL1 = strSQL1 & “WHERE (dsdtcmas.clientid) = ‘” & Me!GroupAttendance2.ItemData(ndx) & “‘”
‘MsgBox (strSQL1)
DoCmd.RunSQL strSQL1
Next ndx

strSQL2 = “INSERT INTO tblClientNotes ( act, servdate, Notes, NoteDate, Clinician, SessionStart, SessionEnd, clientid ) ”
strSQL2 = strSQL2 & “SELECT tblGroupNotes.act, tblGroupNotes.servdate, tblGroupNotes.Notes, tblGroupNotes.NoteDate, tblGroupNotes.Clinician, tblGroupNotes.SessionStart, tblGroupNotes.SessionEnd, tblTemp.clientid ”
strSQL2 = strSQL2 & “FROM tblGroupNotes, tblTemp ”
strSQL2 = strSQL2 & “WHERE (tblGroupNotes.NoteID)=[Forms]![frmGroupNotes]![NoteID];”
‘MsgBox (strSQL2)
DoCmd.RunSQL strSQL2

strSQL3 = “DELETE tblTemp.clientid FROM tblTemp;”
DoCmd.RunSQL strSQL3

DoCmd.SetWarnings True

End Sub

Reply | Quote

That should work, although it’s not very efficient.

Reply | Quote

yep. it is not very efficient and slower too. i wish there is a better way of doing this but can’t seem to find any on the net. even microsoft homepage don’t offer much about a solution to this one. but thanks for your help hans. you don’t know how how time i spent trying to get this to work. i would not know even where to start without your pointer!

Reply | Quote

If you wish, you can post a stripped down copy of your database. See post 401925 for instructions. I’m sure someone will take a look at it.

Reply | Quote