none
Update Command not Updating but not giving any error message RRS feed

  • Question

  • Hi, I'm running the following code and not getting any error messages, but neither is it actually updating my field.  Can anyone give me any pointers?  Thanks!

    All my debug.print commands print what I expect them do, it's just that it somehow is not updating the actual table.

    Sub AssignLessonDates()

    Dim LLrs As dao.Recordset
    Dim Calrs As dao.Recordset
    Dim LLrsSQL As String
    Dim CalrsSQL As String
    Dim AssignDate As Date
    Dim SQL As String

    LLrsSQL = "SELECT StudentName, [Class Id], Lesson, DateDue FROM DailyAssignments where completed = false"
    Set LLrs = CurrentDb.OpenRecordset(LLrsSQL)
    LLrs.MoveFirst
    AssignDate = Date - 1
    Debug.Print AssignDate & " Assign Date"
    Do While (Not LLrs.EOF)
      
      CalrsSQL = "SELECT min(SchoolDate) as SchoolDate1 FROM Calendar WHERE SchoolDate > DateValue(#" & AssignDate & "#) and SchoolDay = 'S'"
      'Debug.Print CalrsSQL
      Set Calrs = CurrentDb.OpenRecordset(CalrsSQL)
        
      If Calrs.RecordCount > 0 Then
        If IsNull(Calrs!SchoolDate1) Then
          Debug.Print LLrs!Lesson & " Couldn't Assign Date: End of Calendar File"
        Else
          Calrs.MoveFirst

          AssignDate = CDate(Calrs!SchoolDate1)
         
          Calrs.Close
          Debug.Print LLrs!StudentName & " " & LLrs![Class ID] & " " & LLrs!Lesson & " " & AssignDate
          LLrs.Edit
          LLrs!DateDue = AssignDate
          LLrs.Update
        End If
      Else
        Debug.Print "No School Dates Left to Assign: " & LLrs!Lesson
        Calrs.Close
      End If
      
      LLrs.MoveNext
    Loop
    LLrs.Close
    End Sub

    Thursday, April 7, 2016 9:10 PM

Answers

  • I just assumed it would sort the table by its key.


    Tables are sets, and, by definition, a set has no intrinsic order, so there is no beginning, middle or end.  If you want to see rows in the order of their insertion into the table, the simplest reliable way is to datetimestamp each row and order a query on the datetimestamp column.

    One amendment I'd suggest to your code is:

    DateValue(#" & Format(AssignDate, "yyyy-mm-dd hh:nn:ss") & "#)

    By formatting the date literal in the ISO standard for date/time notation international unambiguity is ensured.  As it stands you code would fail where the system's regional short date setting is not in US short date format or an otherwise internationally unambiguous format.  On my system, for instance, 4th July would be interpreted as 7th April and vice versa.


    Ken Sheridan, Stafford, England

    Saturday, April 9, 2016 4:38 PM

All replies

  • Hi R,

    Based on your code, I failed to find the root cause. It would be helpful if you could share us your simple project, and then we could test at our side.

    For a general suggestion, I suggest you check DateDue and AssignDate before and after you run “LLrs.Update”.

    Also, I suggest you check whether you have disabled error message.

    DoCmd.SetWarnings false
    DoCmd.SetWarnings true
    
    Application.DisplayAlerts = false
    Application.DisplayAlerts = true
    

    In addition, I suggest you check whether you could manually modify DateDue.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, April 8, 2016 5:40 AM
  • I feel like a dunce, but for some reason it really WAS updating (the part of the records where it could assign a date, just like I wanted it to), but because I hadn't specified a sort, it was updating in the middle of the table, and so I didn't see it when I opened the table.   I just assumed it would sort the table by its key.  I'm living and learning.  Thank you for your help.   It gave me the confidence to think outside of the code.


    Saturday, April 9, 2016 12:51 AM
  • I just assumed it would sort the table by its key.


    Tables are sets, and, by definition, a set has no intrinsic order, so there is no beginning, middle or end.  If you want to see rows in the order of their insertion into the table, the simplest reliable way is to datetimestamp each row and order a query on the datetimestamp column.

    One amendment I'd suggest to your code is:

    DateValue(#" & Format(AssignDate, "yyyy-mm-dd hh:nn:ss") & "#)

    By formatting the date literal in the ISO standard for date/time notation international unambiguity is ensured.  As it stands you code would fail where the system's regional short date setting is not in US short date format or an otherwise internationally unambiguous format.  On my system, for instance, 4th July would be interpreted as 7th April and vice versa.


    Ken Sheridan, Stafford, England

    Saturday, April 9, 2016 4:38 PM