Referencing another field in current record when looping through recordset

[Thumbs]

I have come to my witts end on this one. I'm trying to loop through a recordset, performing an action on each record as I loop through. As part of that action, I want to reference another field on the current record each time it loops.

I have pasted my current code below.

As of right now, it is updating every field in the column with the same value, based on the value of "CincomPartNumber" in the first record. Ultimately, I need to have each record updated independently, as they will have varying years I need to add to the date based on the warranty info in another table (pulled by Dlookup). I suppose the real question I should be asking is how to properly pull the value from another field in the same record as I loop through the recordset?

Also, as part of my testing, I am trying to force the variable value that would normally be passed as an argument to the function. This is just for testing at the moment, though I ultimately intend on running this code on the On Open event. Its not the prettiest at the moment, I just want to get it working.

Thanks in advance for all your help!

Private Function AddWtyDate(passPart As String)

' Declare variables
Dim dbs As Database
Dim rst As Recordset
Dim Counter As Integer
Dim AddYears As String

' create recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT CincomPartNumber, ReceivedDate, WtyExpireDate FROM AssetData WHERE NOT(CincomPartNumber) Is Null", dbOpenDynaset)

' find number of records in recordset
Counter = rst.RecordCount

While Counter > 0

With rst
' Match part number of current record with part number in WarrantyInfo table, return value from table, and strip all but the first character.

passPart = !CincomPartNumber
AddYears = DLookup("[WarrantyType]", "WarrantyInfo", "[ContractorPartNum] = '" & passPart & "' ")
AddYears = Left(AddYears, 1) * 365
    
    'Check if returned character is numeric. If so, run UPDATE.
    If IsNumeric(AddYears) Then
        sSQL = "UPDATE AssetData"
        sSQL = sSQL & " SET AssetData.WtyExpireDate = ([ReceivedDate] + '" & AddYears & "');"
        DoCmd.RunSQL sSQL
    End If
End With

' moves to next record
rst.MoveNext
Counter = Counter - 1

' restart loop
Wend

End Function

[June7]

The UPDATE needs WHERE clause. However, not sure you need the UPDATE at all. Why not just edit the WtyExpireDate field of the current record of the recordset? http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

Not every year has 365 days, some have 366.

Instead of Counter, try:

While Not rst.EOF
...
Wend

[Thumbs]

The UPDATE needs WHERE clause. However, not sure you need the UPDATE at all. Why not just edit the WtyExpireDate field of the current record of the recordset? http://msdn.microsoft.com/en-us/library/bb243797(v=office.12).aspx

Not every year has 365 days, some have 366.

Instead of Counter, try:

While Not rst.EOF
...
Wend

June, thanks for the reply. I had already changed counter to an EOF method after writing this post. Actually, I rewrote the code from scratch to try to get a fresh approach.

I used your suggestion about updating the recordset (which actually runs about 20x faster than the UPDATE query). After a few more hours of beating my head against the wall with things not working, and even trying to revert back to an UPDATE query for the purpose of the WHERE clause (as records were getting updated that I didnt want to update), I was eventually able to get it working with the use of a join in the recordset...blew the doors right off the hinges for me.

As far as the number of days in a year, I don't need to be specific to the day as to when the warranty runs out. An error margin of a couple of days here and there is acceptable for these purposes.

Thanks again for all your help. Here is what actually wound up working for me. I call the sub OnOpen so that every time the form is opened, the data it displays will be accurate.

Private Sub AddWtyDate()

Dim rst As DAO.Recordset
Dim AddYears As String
Dim passPart As String
Dim sSQL As String
Dim AddDays As Integer
Dim ExpDate As String

Set rst = CurrentDb.OpenRecordset("SELECT CincomPartNumber, ReceivedDate, WtyExpireDate FROM AssetData INNER JOIN WarrantyInfo ON WarrantyInfo.ContractorPartNum = AssetData.CincomPartNumber WHERE NOT(CincomPartNumber) Is Null")

'Check to see if the recordset actually contains rows
If Not (rst.EOF And rst.BOF) Then
    rst.MoveFirst
    Do Until rst.EOF = True
      
    ' Match part number of current record with part number in WarrantyInfo table, return value from table, and strip all but the first character.
    passPart = rst!CincomPartNumber
    AddYears = Nz(DLookup("[WarrantyType]", "WarrantyInfo", "[ContractorPartNum] = '" & passPart & "' "), 0)
    AddYears = (Left(AddYears, 1))
    
    'Check if returned character is numeric and if required fields are not null. If so, run UPDATE to populate WtyExpireDate.
    If IsNumeric(AddYears) And Not IsNull([ReceivedDate]) And Not IsNull([CincomPartNumber]) Then
        AddDays = AddYears * 365
        rst.Edit
        rst!WtyExpireDate = rst!ReceivedDate + AddDays
        rst.Update
    End If

        'Move to the next record.
        rst.MoveNext
    Loop
Else
    MsgBox "There are no records in the recordset."
End If

rst.Close
End Sub