Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jul 2010
    Location
    London
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi All

    I have a form with a field (called Print) that is a tick box. When the user ticks the box, that record will then be included in the form to print out - it is actually a mailing list and I want the users to choose which people to include in printout.

    The form also has a combo box which filters various contact types. I have a select all button which I want to use to set all the visible records Print value to TRUE so that they all print. I do not want this to affect the Print values of any other records that can't be seen.

    I was running through the recordset and doing an update query but that was too slow.

    I then tried to update all the records with one update query using their primary key, but when there are too many records to update at once I get an error message.

    My code is
    Code:
    Private Sub cmdSelectAll_Click()
        Dim strSQL As String
        Dim strSQLID As String
        Dim rst As Recordset
        
        
        DoCmd.SetWarnings False
        
        Set rst = Me.Recordset
        If rst.RecordCount <> 0 Then
            strSQL = "UPDATE Journos SET Journos.Print = True "
            strSQL = strSQL & "WHERE (Journos.JournoID)= "
            rst.MoveFirst
            Do While Not rst.EOF
                If Len(strSQLID) > 0 Then strSQLID = strSQLID & " OR "
                strSQLID = strSQLID & rst.Fields("journoid")
                rst.MoveNext
            Loop
        End If
        strSQLID = strSQLID & ";"
        strSQL = strSQL & strSQLID
        DoCmd.RunSQL strSQL
        Set rst = Nothing
        
        DoCmd.SetWarnings True
        Me.Refresh
    End Sub
    If any one has any suggestions it would be great to hear them.

    many thanks in advance


    Amanda

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    First of all, what is the error message that you get? (I expect it is a timeout error, but it could be another error.) How many records are you trying to update when you get the error, and are these records in an Access database, a SQL Server database, or some other type of database? My immediate suggestion, since you have the recordset open to get the primary key, would be to set the print flag with a statement that changes the record, but using DAO with many records can be slower than running a stored query. The other option you might want to explore is creating a saved query which references the combo box for it's criteria - but if that's creates some sort of complex criteria though multiple selections built in code, then you might what to set the SQL properties of a saved query using the QueryDef property. Let us know how you fare.
    Wendell

  3. #3
    New Lounger
    Join Date
    Jul 2010
    Location
    London
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Wendell

    Thanks so much for your reply.

    The message is "Run time error 3360. The query is too complex."

    It is running intermittently but it is updating all the records in the underlying table and not just the visible ones on the form taking into account the filter.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    The "IN" syntax might be simpler.

    Where JournoID in (2,4,7)

    rather than

    Where (Journoid=2) or (Journoid=4) or (Journoid=7)
    Regards
    John



  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Quote Originally Posted by Amanda Glover View Post
    ...
    The message is "Run time error 3360. The query is too complex."
    I suspected that might be the issue if it wasn't a timeout - the number of records makes the SQL string size exceed the maximum allowed size. Thus you want to use a stored query, or do the process in VBA.

    It is running intermittently but it is updating all the records in the underlying table and not just the visible ones on the form taking into account the filter.
    That indicates that the recordset for the form is all of the records, which would be the case if you are applying a filter. You need to add the filter parameters that are being used to limit the display to either the recordset if you do it in VBA, or to the query if you do it with a saved query.
    Wendell

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    What about this?

    Code:
    Private Sub cmdSelectAll_Click()
    Dim strFilter as string
     Dim strSQL As String
     
    strFilter = me.Filter
    
     strSQL = "UPDATE Journos SET Journos.Print = True "
    if len(strfilter)>0 then
     strSQL = strSQL & " WHERE " & strFilter
    end if
    Debug.print strSQL
    currentDB.Execute strSQL
     
     Me.Refresh
    End Sub
    Regards
    John



  7. #7
    New Lounger
    Join Date
    Jul 2010
    Location
    London
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi John

    Many thanks for your reply.

    I tried the code which gave me the following sql string

    UPDATE Journos SET Journos.Print = True WHERE [leadtime]=[Forms]![frmJournoSel]![cboLead]

    However the error message was Run-time error 3601 - too few parameters. Expected 1

    Don't suppose you know what is missing?

    Many thanks

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    What type of data is cboLead? is it text or a number, or something else?

    if it were text you need

    WHERE [leadtime]=" & chr(34) & [Forms]![frmJournoSel]![cboLead] & chr(34)

    ch(34) is code for double quotes.

    But I am puzzled. One of the reasons for just using me.filter is to avoid these issues. If something works as a filter, exactly the same code should work in SQL.
    Regards
    John



Posting Permissions

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