Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Mar 2003
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL too complex (Access 2K)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL too complex (Access 2K)

    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

  3. #3
    Lounger
    Join Date
    Mar 2003
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL too complex (Access 2K)

    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.

  4. #4
    Lounger
    Join Date
    Mar 2003
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL too complex (Access 2K)

    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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL too complex (Access 2K)

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

  6. #6
    Lounger
    Join Date
    Mar 2003
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL too complex (Access 2K)

    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!

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL too complex (Access 2K)

    If you wish, you can post a stripped down copy of your database. See <post#=401925>post 401925</post#> for instructions. I'm sure someone will take a look at it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •