Update records in a table

[JohnJoker]

I am using the code below to update the fields in a table. It works fine if I do one record at a time. What I want is to update all records when I click the button, I have tried so many things and none of them worked. What I am doing wrong?


Dim db As Database
Dim rst As Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("Survey")

If IsNull(SpecimenDate) = False Then
Ageyrs = (SpecimenDate - Dob) / 365.5
Agemths = (SpecimenDate - Dob) / 30.46
Agewks = (SpecimenDate - Dob) / 7
Year = DatePart("yyyy", SpecimenDate)
Quarter = DatePart("q", SpecimenDate)
If Agemths < 2 Then
Agegp = "a. <2 months"
Agegp2 = "a. <3 months"
End If

rst.Close

Set rst = Nothing


JohnJoker

 

[Bluejay07]

you need a loop structure of some kind, such as:
for i = 0 to rst.recordcount - 1
' place code here
next i

If at first you don't succeed, then sky diving wasn't meant for you!

 

[JohnJoker]

Thank you it's working but I have to go though each record and click the button. It does not do for all the records in the table. Do you know what I mean?

 

[BobRodes]

Hi John. Can you post your changed code? Sounds like you're not iterating through all the records in the recordset still.

 

[JohnJoker]

There you go

Private Sub Command147_Click()

Dim db As Database
Dim rst As Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("Survey")

For i = 0 To rst.RecordCount - 1
' place code here

If IsNull(SpecimenDate) = False Then
Ageyrs = (SpecimenDate - Dob) / 365.5
Agemths = (SpecimenDate - Dob) / 30.46
Agewks = (SpecimenDate - Dob) / 7
Year = DatePart("yyyy", SpecimenDate)
Quarter = DatePart("q", SpecimenDate)
If Agemths < 2 Then
Agegp = "a. <2 months"
Agegp2 = "a. <3 months"
End If
End If
Next i
rst.Close

Set rst = Nothing


End Sub

 

[TysonLPrice]

I don't see a rst.MoveNext in the loop.

 

[JohnJoker]

I have NEXT i.



Private Sub Command147_Click()

Dim db As Database
Dim rst As Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("Survey")

For i = 0 To rst.RecordCount - 1
' place code here

If IsNull(SpecimenDate) = False Then
Ageyrs = (SpecimenDate - Dob) / 365.5
Agemths = (SpecimenDate - Dob) / 30.46
Agewks = (SpecimenDate - Dob) / 7
Year = DatePart("yyyy", SpecimenDate)
Quarter = DatePart("q", SpecimenDate)
If Agemths < 2 Then
Agegp = "a. <2 months"
Agegp2 = "a. <3 months"
End If
End If
Next i
rst.Close

Set rst = Nothing


End Sub

 

[TysonLPrice]

Are you sure next i gets the next record?

 

[JohnJoker]

Do You think that I also have to use
rst.movenext

 

[TysonLPrice]

Absolutly

I did a quick test even though I'm sure of it. If you do a Debug.Print on one of the values you will see the same value each time. It just loops through however many records there are.

Put this right before the next i

rst.movenext

 

[JohnJoker]

I have done it, tested and is going through all the records but it is not making the fiels = Null.
Thanks,

 

[TysonLPrice]

If the loop is working with the recordset you need something like:

With rst
    For i = 0 To rst.RecordCount - 1
        If IsNull(.Fields("SpecimenDate")) = False Then
            .Fields("Ageyrs") = .Fields("SpecimenDate") - .Fields("Dob") / 365.5
            .Fields("Agemths") = (.Fields("SpecimenDate") - .Fields("Dob")) / 30.46
            .Fields("Agewks") = (.Fields("SpecimenDate") - .Fields("Dob")) / 7
            .Fields("Year") = DatePart("yyyy", .Fields("SpecimenDate"))
            'etc...
        End If
       .movenext
    Next i
    rst.Close
    Set rst = Nothing
End With

Do you have Option Explicit at the very top of your form?

 

[Golom]

Your code does not reference the recordset "rst" other than in the FOR loop definition. That means that it is doing the same calculation on every pass through the loop.

"Next i" just increments to the next FOR loop counter. It does not reposition the recordset. You need rst.MoveNext to do that.

Assuming that "SpecimenDate" and "DOB" are fields in the recordset, you probably need something like

With rst
    Do Until .EOF

        If Not IsNull(![SpecimenDate]) Then
            Ageyrs = (![SpecimenDate] - ![Dob]) / 365.5
            Agemths = (![SpecimenDate] - ![Dob]) / 30.46
            Agewks = (![SpecimenDate] - ![Dob]) / 7
            Year = DatePart("yyyy", ![SpecimenDate])
            Quarter = DatePart("q", ![SpecimenDate])
            If Agemths < 2 Then
                Agegp = "a. <2 months"
                Agegp2 = "a. <3 months"
            End If
        End If
        .MoveNext
    Loop
    .Close
End With

 

[Golom]

Just another comment ...

You are going through a loop on the records in a recordset but you are assigning the computed values to (I assume) single variables (e.g. Ageyrs, Agemnths, etc.) That means that each pass through the loop overwrites the values computed on the previous pass. Is that what you intend?

 

[JohnJoker]

No. What I want is to check if the SpecimenDate is Null. If is not null assign values to
Ageyrs
Agemths
Agewks
Year
Quarter

Thanks

 

[gmmastros]

It appears as though you are using DAO (just a guess). I haven't used DAO in an extremely long time, but....

If you want to update the columns in the table, you need to use code similar to what TysonLPrice posted. But, I think he might have missed an rst.update command. Just setting the values of the recordset object isn't enough to save the data back to the database, you also need to call the update method of the recordset object.

Now, it seems like you have a Survey table with a bunch of columns. It also seems like you recently added a few columns and want to populate the data based on the SpecimenDate and DOB columns. I am more familiar with Microsoft SQL Server. If that is the database you were using, I would have recommended a single query to update your data. It would look something like this.

Update Survey
Set    Ageyrs = (SpecimenDate - DOB) / 365.5,
       Agemths = (SpecimenDate - DOB) / 30.46,
       Agewks = (SpecimanDate - DOB) / 7,
       Year = DatePart(year, SpecimenDate),
       Quarter = DatePart(quarter, SpecimenDate),
       etc...
Where  SpecimenDate Is Not NULL

Now, it's entirely possible that this query will not work with your database (I'm guessing it's Microsoft Access). But, there's a lot of sharp folks that read this forum and may be able to help you convert this query (and execute it with DAO). The query modifications should be minimal, and the VB6 to execute it should be relatively simple.

The advantage of this method is that you will have a lot less VB6 code, and it is very likely to execute MANY times faster. If you only have a couple hundred rows in your Survey table, you may not notice any performance improvements, but any more than that, you will.

Hope this helps.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JohnJoker]

Yes t is Access database, and I am using VB6 because I am testing many conditions.

Thanks

 

[PeterWallace]

open Table
with rst
do while not .eof
check fields
if wrong ... adjust them
.update
end if
.movenext ' get next record
loop

close table

 

[PeterWallace]

After WITH Rst
' ensure at first record
if not .bof them .MoveFirst