473,321 Members | 1,748 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

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:
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
3 4590
jimatqsi
1,271 Expert 1GB
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
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
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

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

Similar topics

3
by: Damian | last post by:
Hi. Is there a way to programatically populate a continuous form? I have an array of descriptions that I want to display in the continuous form and I have a textbox on the form called...
1
by: tmuhammad | last post by:
Hi there, I am trying to populate a continuous form using ADO, following is the function which needs fixing, can someone kindly help me with this. Thanks much Private Sub Form_Load() On Error...
10
by: DFS | last post by:
I'm sure this has been discussed before, but I'm too tired to hunt for it. I have a survey table containing questions with different answer scales. Some are 1 to 5, others are A,B,C, etc. I show...
20
by: Robert | last post by:
Need some help to stop me going around in circles on this one.... Have a nested subform (subform2) which simulates a continuous form for the record on the parent subform. Subform2 has rows of...
2
by: bobh | last post by:
Hi All, In Access97 I have a form setup as a continuous form several of the bound controls have calculations that are done in the 'after update' event via VBA code and all works fine. My...
3
by: ApexData | last post by:
I am using a continuous form for display purposes. Above this form, a single record is displayed so that when the user presses my NewButton they can enter a NewRecord which gets added to the...
3
by: boliches | last post by:
I am trying to get a continuous form to highlight lines individually (ideally on hover). I have used the following code as recommended by a previous entry on this website. My problems begin in that...
4
by: Michiel Rapati-Kekkonen | last post by:
Is there maybe a trick so that when I make a continuous form, the records fit exactly? Now, without such trick, often the last visible record shows only half and I have to stretch the form...
3
by: Jollywg | last post by:
I have a continuous form with 5 columns (Qty, PartNum, Desc, ItemCost, TotalCos). At the bottom of the form i have a field labeled Invoice Total. Right now the Invoice Total is totaling everything...
2
by: ChipR | last post by:
I'm suddenly having a major problem trying to switch between rows in a continuous form in Access 2007. The form represents a timesheet and has a row for each activity that an employee may work on. ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.