Loop not working as expected (1 Viewer)

[John Lee]

Hi I have this loop in the on Load event of my form and its not behaving as I would like, it updates the first record, but doesn't update the remaining records

Start Code:
Option Compare Database

Private Sub Form_Load()

Dim StartDate As Date
Dim RefresherDate As Date

Dim db As DAO.Database
Dim rs As DAO.Recordset

' Open the database and recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM qryHealthSafetyMatrixFireSafetyFireWarden")

' Check if the recordset contains any records
If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst ' Move to the first record

Do Until rs.EOF
StartDate = Me![txtStartDate].Value
RefresherDate = DateAdd("yyyy", 3, StartDate)
Me![txtRefresherDate].Value = RefresherDate
MsgBox "RefresherDate"
' Move to the next record
rs.MoveNext
Loop
Else
MsgBox "No records found."
End If

' Clean up
rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

End Code

I have put in a message box to confirm it is looping through, but it doesn't show the date I'm expecting to show in the message box which would then populate the fields with the required date.

I've attached some screen shots, which might help

Any suggestions on what might be wrong with what I have done would be appreciated.

 

[theDBguy]

Try using:

Me.txtRefresherDate=RefresherDate

 

[Gasman]

You are updating the form control?
NOT the recordset.

 

[John Lee]

Thank you for your response, I just tried that it didn't make any difference the message box still came back as per the images sent.

I cleared down the first record of the date just to be sure and still only the first record is populated.

Kind regards

 

[theDBguy]

I forgot, use this in conjunction with my other suggestion

StartDate=Me.txtStartDate

 

[RonPaii]

Thank you for your response, I just tried that it didn't make any difference the message box still came back as per the images sent.

I cleared down the first record of the date just to be sure and still only the first record is populated.

View attachment 123643


Kind regards

Are you tiring to update qryHealthSafetyMatrixFireSafetyFireWarden? In you loop you are updating a form control from data on the form but never check the current record on the form while looping though the recordset.

 

[John Lee]

Hi, thanks, but still got the same result only the first record is being populated. The loop just doesn't want to play any ideas would be appreciated.

Kind Regards

 

[Gasman]

If you want to add 3 years to the startdate, why not just run a query to do so, before opening form?

 

[theDBguy]

Hi, thanks, but still got the same result only the first record is being populated. The loop just doesn't want to play any ideas would be appreciated.

Kind Regards

Can you explain what you are trying to accomplish?

 

[Gasman]

Hi, thanks, but still got the same result only the first record is being populated. The loop just doesn't want to play any ideas would be appreciated.

Kind Regards

Loop is working fine. Walk it yourself and see.
You are updating the wrong data.

 

[John Lee]

If you want to add 3 years to the startdate, why not just run a query to do so, before opening form?

Hi I could do that, but the problem is that future records will have a different criteria in that some may be 1 year and others may be 5 years, so I wanted to test this out before I went down that road.

 

[DHookom]

John Lee, you haven’t posted any code that reads or writes value from or to your recordset.

 

[plog]

So, the main issue is solved above--reread the posts, the answer is there. But you also made another error in trying to debug the main error:

Me![txtRefresherDate].Value = RefresherDate
MsgBox "RefresherDate"

It should be no surprise that your message box doesn't display a date. You told it to always display the characters "RefreshDate"--no matter what. Look at the line above it, what's the difference between the two?

 

[Gasman]

Hi I could do that, but the problem is that future records will have a different criteria in that some may be 1 year and others may be 5 years, so I wanted to test this out before I went down that road.

Perhaps look at the recordsetclone then, if you want to do it that way.

 

[John Lee]

John Lee, you haven’t posted any code that reads or writes value from or to your recordset.

Hi, thank you for your response, I thought I had by writing this inside the loop:


Start Code:
StartDate = Me.txtStartDate
RefresherDate = DateAdd("yyyy", 3, StartDate)
Me.txtRefresherDate = RefresherDate
End Code

So what am I missing?

any assistance appreciated.

Kind Regards

 

[John Lee]

Perhaps look at the recordsetclone then, if you want to do it that way.

Okay, I'll read up on that then

 

[Gasman]

Hi, thank you for your response, I thought I had by writing this inside the loop:


Start Code:
StartDate = Me.txtStartDate
RefresherDate = DateAdd("yyyy", 3, StartDate)
Me.txtRefresherDate = RefresherDate
End Code

So what am I missing?

any assistance appreciated.

Kind Regards

No, that just writes the same value to a form control, as many times as there are records in the recordset.

 

[John Lee]

Thank you everyone for your help, I'll go away are read up some more on your suggestions.

Kind Regards

 

[Gasman]

Whether you use your recordset or the recordsetclone, you still need to update the recordset field NOT the form control.

 

[RonPaii]

Hi I could do that, but the problem is that future records will have a different criteria in that some may be 1 year and others may be 5 years, so I wanted to test this out before I went down that road.

Easy to change the years to what you want and do it in one line.

CurrentDB.Execute _
"UPDATE qryHealthSafetyMatrixFireSafetyFireWarden " & _
"SET qryHealthSafetyMatrixFireSafetyFireWarden.[RefresherDate] " & _
"""= DateAdd(""YYYY""," & NumberOfYears & "," & [me]![txtStartDate] & ");"

PS: Your original code only references the recordset for loop control, never for data.

PS: Showing your code in <Code> would make it easer to help you