Do While/Until loop on Continuous Form

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • McCaff80
    New Member
    • Oct 2017
    • 3

    #1

    Do While/Until loop on Continuous Form

    I have a Deadlines table, which includes a description, date due and frequency. I also have a Frequency table (Freq_ID, Freq_Descr, Interval, Number :the data needed to use a DataAdd method for that frequency such as weekly, daily, annually, so on).

    I built a continuous form on this Deadlines table.

    I need a way for the user to indicate when a record has been completed, and the next due date to be determined and updated in the source.

    I do have a 'yes/no' field on the underlying table. So, the form shows this checkbox. I thought, the user could mark each deadline that is complete and then click a single command button 'Update', and it would loop through the records and unmark the box, increment the date, and then save.

    Problems:
    1. If all records are marked, it doesn't edit every record (I tried a loop that only unmarked the box, and it stops on the last record every time).
    2. When I added the DataAdd method to the events, mark only a single record to update, it will remove the checkmark from the correct record, will retrieve the DataAdd information from the correct record, but then increments the date for the First record present. ?!?!?!

    I have included my current VBA below:
    Code:
    Dim rst As DAO.Recordset
    Set rst = Me.RecordsetClone
        
        rst.MoveFirst
        Do Until rst.EOF
            rst.Bookmark = Me.Bookmark
            If [Forms]![frm_Deadlines2]![Task_Done] = True Then
                [Forms]![frm_Deadlines2]![Task_Done] = False
                Dim strInterval As String
                Dim strNum As Double
                Dim strDate As Date
                Dim strNew As Date
                strInterval = Me.txt_interval.Value
                strNum = Me.txt_number.Value
                strDate = Me.Task_Due.Value
                strNew = DateAdd(strInterval, strNum, strDate)
                rst.Edit
                rst.Fields("Task_Due").Value = strNew
                rst.Update
            End If
            Me.Bookmark = rst.Bookmark
            rst.MoveNext
        Loop
    rst.Close
    Can anyone tell me what I am doing wrong?
  • jimatqsi
    Moderator Top Contributor
    • Oct 2006
    • 1293

    #2
    Try changing line 21 to
    Code:
    Me.Bookmark=rst.LastModified
    Why are line 4 and line 6 both there? It's not clear what you want to do. If you want to start at the beginning of the recordset you need only line 4. If you want to start at the current row of the form then you need only line 6. I suspect you want to get rid of line 6.

    Jim

    Comment

    • McCaff80
      New Member
      • Oct 2017
      • 3

      #3
      Jim,
      I believe I mirrored code from another posting where someone was attempting to do what I am, as far as removing check marks in a loop.

      I have removed line 6, and modified line 21 to your recommendation, but still a no go.

      I have the same issues. Visually, my last record still has a marked checkbox. And the dates still do not change according to the information in the same record.

      Here is how my 4 records look to begin with
      Desc Date_Due Freq_Desc Freq_interval Freq_numb Done
      Item1 1/1/17 Daily w 1 x
      Item2 1/1/17 Weekly ww 1 x
      Item3 1/1/17 Bi-weekly ww 2 x
      Item4 1/1/17 Monthly m 1 x

      After running, I get this
      Desc Date_Due Freq_Desc Freq_interval Freq_numb Done
      Item1 1/1/17 Daily w 1
      Item2 2/1/17 Weekly ww 1
      Item3 2/8/17 Bi-weekly ww 2
      Item4 2/22/17 Monthly m 1 x

      Do we have another idea?

      Comment

      • McCaff80
        New Member
        • Oct 2017
        • 3

        #4
        Update:
        I managed to find a solution that worked as intended. It could possibly be cleaned up a little, as I have used the bookmark method so that I could monitor the movement of the loop as I debugged and decided to leave it to help identify which particular record is the culprit for an error in the future. Here's the final code:

        Code:
        If Me.Dirty Then
                Me.Dirty = False
            End If
            
            Dim rst As DAO.Recordset
            Set rst = Me.RecordsetClone
         
            rst.MoveFirst
            Me.Bookmark = rst.Bookmark
            
            Do Until rst.EOF
                Me.Bookmark = rst.Bookmark
                
                If Me.Task_Done = True Then
                    
                    Dim strInterval As String
                    Dim strNum As Double
                    Dim strDate As Date
                    Dim strNew As Date
                    strInterval = Me.Freq_Interval.Value
                    strNum = Me.Freq_Number.Value
                    strDate = Me.Task_Due.Value
                    strNew = DateAdd(strInterval, strNum, strDate)
                    rst.Edit
                    rst.Fields("Task_Done") = False
                    rst.Fields("Task_Due").Value = strNew
                    rst.Update
                    
                End If
                rst.MoveNext
            Loop
            rst.Close

        Comment

        Working...