422,764 Members | 1,223 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,764 IT Pros & Developers. It's quick & easy.

Do While/Until loop on Continuous Form

P: 3
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:
Expand|Select|Wrap|Line Numbers
  1. Dim rst As DAO.Recordset
  2. Set rst = Me.RecordsetClone
  3.  
  4.     rst.MoveFirst
  5.     Do Until rst.EOF
  6.         rst.Bookmark = Me.Bookmark
  7.         If [Forms]![frm_Deadlines2]![Task_Done] = True Then
  8.             [Forms]![frm_Deadlines2]![Task_Done] = False
  9.             Dim strInterval As String
  10.             Dim strNum As Double
  11.             Dim strDate As Date
  12.             Dim strNew As Date
  13.             strInterval = Me.txt_interval.Value
  14.             strNum = Me.txt_number.Value
  15.             strDate = Me.Task_Due.Value
  16.             strNew = DateAdd(strInterval, strNum, strDate)
  17.             rst.Edit
  18.             rst.Fields("Task_Due").Value = strNew
  19.             rst.Update
  20.         End If
  21.         Me.Bookmark = rst.Bookmark
  22.         rst.MoveNext
  23.     Loop
  24. rst.Close
  25.  
Can anyone tell me what I am doing wrong?
Oct 5 '17 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 1,219
Try changing line 21 to
Expand|Select|Wrap|Line Numbers
  1. 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
Oct 8 '17 #2

P: 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?
Oct 8 '17 #3

P: 3
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:

Expand|Select|Wrap|Line Numbers
  1. If Me.Dirty Then
  2.         Me.Dirty = False
  3.     End If
  4.  
  5.     Dim rst As DAO.Recordset
  6.     Set rst = Me.RecordsetClone
  7.  
  8.     rst.MoveFirst
  9.     Me.Bookmark = rst.Bookmark
  10.  
  11.     Do Until rst.EOF
  12.         Me.Bookmark = rst.Bookmark
  13.  
  14.         If Me.Task_Done = True Then
  15.  
  16.             Dim strInterval As String
  17.             Dim strNum As Double
  18.             Dim strDate As Date
  19.             Dim strNew As Date
  20.             strInterval = Me.Freq_Interval.Value
  21.             strNum = Me.Freq_Number.Value
  22.             strDate = Me.Task_Due.Value
  23.             strNew = DateAdd(strInterval, strNum, strDate)
  24.             rst.Edit
  25.             rst.Fields("Task_Done") = False
  26.             rst.Fields("Task_Due").Value = strNew
  27.             rst.Update
  28.  
  29.         End If
  30.         rst.MoveNext
  31.     Loop
  32.     rst.Close
  33.  
Oct 14 '17 #4

Post your reply

Sign in to post your reply or Sign up for a free account.