Results 1 to 9 of 9
  1. #1
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    116

    amended vba sql update says "about to update 0 rows" message

    Hi, I am struggling with amending a vba update query to including a few more fields in the update ,sample1 works but sample2 fails
    sample1 with error saying "about to update 0 rows"
    Code:
        'strSql = "UPDATE Attend SET Attend.AttType = " & TypeAttend _
        & " WHERE (((Attend.AttStudent)=" & Me![scrStudent] & ") AND" _
        & "((Attend.AttDate)=#" & Format(TDate, "mm/dd/yy") & "#));"
    :

    output of debug from example1

    UPDATE Attend SET Attend.AttType = 0 WHERE (((Attend.AttStudent)=219477) AND((Attend.AttDate)=#02/02/16#));

    sample2
    Code:
     
        strSql = "UPDATE Attend SET Attend.AttType = " & TypeAttend _
        & " WHERE (((Attend.AttStudent)=" & Me![scrStudent] & ") AND" _
        & "((Attend.AttDate)=#" & Format(TDate, "mm/dd/yy") & "#) AND" _
        & "((Attend.LogOnUser)='" & Me![txtUserId] & "')AND" _
        & "((Attend.LastAmendedDate)=#" & Format(Now, "mm/dd/yy") & "# ));"
    output from debug for example2 sql


    UPDATE Attend SET Attend.AttType = 2 WHERE (((Attend.AttStudent)=219477) AND((Attend.AttDate)=#02/01/16#) AND((Attend.LogOnUser)='gerrynew1')AND((Attend.Las tAmendedDate)=#02/10/16# ));
    Last edited by gint32; 02-09-2016 at 06:28 PM. Reason: typo

  2. #2
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    have you tried converting this to a select query to see what records are returned - error implies no record meets your criteria

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In addition to what Ajax said, I have two comments:

    1) I would use 4 digit years, not two: (Format(Now, "mm/dd/yyyy")

    2) Watch your spaces (or lack of spaces)
    Code:
        strSql = "UPDATE Attend SET Attend.AttType = " & TypeAttend _
        & " WHERE (((Attend.AttStudent)=" & Me![scrStudent] & ") AND" _
        & "((Attend.AttDate)=#" & Format(TDate, "mm/dd/yy") & "#) AND" _
        & "((Attend.LogOnUser)='" & Me![txtUserId] & "')AND" _
        & "((Attend.LastAmendedDate)=#" & Format(Now, "mm/dd/yy") & "# ));"

  4. #4
    kagoodwin13 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Feb 2012
    Posts
    181
    What is the data type for the field Me![txtUserId] - is it a number, or alphanumeric?

    If it is a number, your command should be: " & Me![txtUserId] & " (remove single quotes)

  5. #5
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    116
    Hi,
    And thanks for replying,

    Code:
    Me![txtUserId] = GetCurrentUserName()
    that particular field Me![txtUserId] is actually text (alphanumeric) as it captures who ever is logged on at that time of the update, (I know its a bad naming convention), but I did not create the tables.:0



    Oddly, though my insert Strsql works just fine:

    Code:
           strSql = "INSERT INTO Attend ( AttStudent, AttDate, AttType, LogOnUser, LastAmendedDate ) VALUES (" & _
       Me![scrStudent] & ", #" & Format(TDate, "mm/dd/yy") & "#, " & TypeAttend & _
       ", '" & Me![txtUserId] & "',#" & Format(Now, "mm/dd/yy h:m AM/PM") & "# );"
    So I thought the update should work similarly..Oddly, though my insert Strsql works just fine:

    Code:
           strSql = "INSERT INTO Attend ( AttStudent, AttDate, AttType, LogOnUser, LastAmendedDate ) VALUES (" & _
       Me![scrStudent] & ", #" & Format(TDate, "mm/dd/yy") & "#, " & TypeAttend & _
       ", '" & Me![txtUserId] & "',#" & Format(Now, "mm/dd/yy h:m AM/PM") & "# );"
    So I thought the update should work similarly..



    Oddly, though my insert Strsql works just fine:

    Code:
           strSql = "INSERT INTO Attend ( AttStudent, AttDate, AttType, LogOnUser, LastAmendedDate ) VALUES (" & _
       Me![scrStudent] & ", #" & Format(TDate, "mm/dd/yy") & "#, " & TypeAttend & _
       ", '" & Me![txtUserId] & "',#" & Format(Now, "mm/dd/yy h:m AM/PM") & "# );"
    So I thought the update should work similarly..

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Using the format function converts the date to a string, so I would try the following:
    Code:
           strSql = "INSERT INTO Attend ( AttStudent, AttDate, AttType, LogOnUser, LastAmendedDate ) VALUES (" & _
       Me![scrStudent] & ", #" & TDate & "#, " & TypeAttend & _
       ", '" & Me![txtUserId] & "',#" & Now() & "# );"
    It really doesn't matter what format the date is (Now() vs Format(Now, "mm/dd/yy h:m AM/PM") ) because the date is actually stored as a double (IIRC). Access displays it using the default regional properties. In forms you can modify how it is displayed.

  7. #7
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    116
    Hi,
    Thanks for your suggestion, but I still am getting an error, but at least this time it says its about to append 1 row(s), instead of 0 row(s), but it then goes on to fail with another message saying: MS can't append due to (1) key violations, do want to run anyway yes/no

    the output now of debug : INSERT INTO Attend ( AttStudent, AttDate, AttType, LogOnUser, LastAmendedDate ) VALUES (219477, #2/2/2016#, 0, 'gerrynew1',#2/11/2016 6:52:33 AM# );

    So I had a play around with a new update query and added and renamed a few fields, but finally got the following update query to work via query, trouble is this will not solve my vba as I can't past this from sql view directly into my StrSql= bla bla as I already tried this and et lots of errors and mostly the code stayed in red.

    Code:
    UPDATE Attend SET Attend.AttStudent = 219477, Attend.AttDate = #2/3/2016#, Attend.AttType = 3, Attend.LoggedOnUser = "gerrynew1", Attend.LastAmendedDate = Now()
    WHERE (((Attend.AttStudent)=219477) AND ((Attend.AttDate)=#2/3/2016#) AND ((Attend.AttType)=2));
    Thing is I don't know how to write this into my strsql = "UPDATE Attend SET Attend.AttStudent = 219477, Attend.AttDate = #2/3/2016#, Attend.AttType = 3, Attend.LoggedOnUser = "gerrynew1", Attend.LastAmendedDate = Now()
    WHERE (((Attend.AttStudent)=219477) AND ((Attend.AttDate)=#2/3/2016#) AND ((Attend.AttType)=2));"

    Finally My best attempt to convert the sql to strSQL which does fails is:
    Code:
           strSql = "UPDATE Attend SET Attend.AttType = " & TypeAttend _
        & "((Attend.LoggedOnUser)= & '" & Me![txtUserId] & "',(Attend.LastAmendedDate)=#" & _
        Format(Now(), "mm/dd/yy") & "#)WHERE (Attend.AttStudent)=" & Me![scrStudent] & " AND" _
        & "(Attend.AttDate)=#" & Format(TDate, "mm/dd/yy") & "#;"
    and the debug output from this is :
    Code:
    UPDATE Attend SET Attend.AttType = 3((Attend.LoggedOnUser)= & 'gerrynew1',(Attend.LastAmendedDate)=#02/11/16#)WHERE (Attend.AttStudent)=219477 AND(Attend.AttDate)=#02/03/16#;
    Last edited by gint32; 02-10-2016 at 09:05 PM. Reason: adding text

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    How about this:
    Code:
    strSql = "UPDATE Attend SET"
    strSql = strSql & " Attend.AttType = " & TypeAttend & ","
    strSql = strSql & " Attend.LoggedOnUser = '" & Me![txtUserId] & "',"
    strSql = strSql & " Attend.LastAmendedDate = #" & Now() & "#"
    strSql = strSql & " WHERE Attend.AttStudent = " & Me![scrStudent] & " AND"
    strSql = strSql & " Attend.AttDate = #" & TDate & "#;"

  9. #9
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    116
    Yep, thats done it works a treat for me, and thanks this coding layout wise, is far easier to follow and amend, as I was getting really confused as to where one line of code finished and the next piece started ...Your a champ

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-18-2015, 07:59 PM
  2. Replies: 3
    Last Post: 06-20-2014, 06:09 PM
  3. Suppress "Error" message following "Cancel = True"
    By GraeagleBill in forum Programming
    Replies: 7
    Last Post: 03-23-2014, 05:40 PM
  4. Replies: 5
    Last Post: 06-26-2013, 02:29 PM
  5. Replies: 3
    Last Post: 03-07-2011, 08:37 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums