Loop through Logfile for New value and update Old Value

[JustKev]

Im new to loops and hope someone can help with what should be a simple solution.

I have a table with Location information imported from another database, the user can change the locationcode to a new value, this "new "value is stored (as well as the original value) in a log table as logtbl.OldValue, logtbl.Newvalue, logtbl.datestamp.

Users can update the Locationcode many times, periodically a new import occurs of inventory data, however the "new" data references the original locationcode so I need to match the locationcode with the first OldValue from logtbl and loop through the records to find the last entry, and then validate that it exists in the Location table, if that makes sense?

Any help would be appreciated.

Kev

 

[Remou]

You can use a Select statement that limits the records, for example:

'Reference required:
'Microsoft DAO 3.x Object Library
Dim rs As DAO.Recordset
Dim strSQL as String

strSQL="SELECT Old, New FROM tblLog " _
& "WHERE Old=" & NumericOldSeeNote _
& " ORDER BY LogDate"

Set rs=CurrentDB.OpenRecordset(strSQL)

rs.MoveFirst
FirstOlds=rs!Old
rs.MoveLat
LastNew=rs!New

rs.Close
Set rs=Nothing

Note: You need delimiters for text (') and date (#) fields.

 

[JustKev]

Thanks Remou I appreciate your prompt reply,

a little more information is that Im trying to update the Inventory table Inv.Locationcode to the "New" (last)updated value (logtbl.Newvalue) in the log table, so I thought I would have envisaged a nested loop, i.e. go through each record in the inventory table then loop through the logtable to check for locationcode updates and then update the inventory.locationcode accordingly.

Inventory table:
inv.locationcode

Locations table:
loc.locationcode

log table:
logtbl.OldValue
logtbl.Newvalue
logtbl.datestamp

 

[Remou]

It is generally not necessary to loop through a recordset in that suitable SQL can usually be used to update batches, however, without some sample data it is difficult to say what this SQL might be.

If, however, you prefer to loop through the recordset, you can use the example I posted above to build the loop and post back your code showing where you are having problems.

 

[JustKev]

Finally got there (sort of) with this approach:

If rstCascade.EOF = False And rstCascade.BOF = False Then

Do

' Walk down the Cascadelog for the new location
varFind = DLookup(strNew_Loc, strCascadeTableName, _
"[" & strOld_Loc & "]='" & rstCascade.Fields(strEQ_LocCode).Value & _
"'")

Do While Len(varFind & "") > 0
' Update record for the location value

strSQL = "Update [" & strEquipmentTableName & "] " & _
" SET [" & strEQ_LocCode & "] = " & _
" '" & varFind & "' WHERE [" & strEQ_LocCode & "] ='" & rstCascade.Fields(strEQ_LocCode).Value & "';"


dbsDAO.Execute strSQL, dbFailOnError

rstCascade.Requery

varFind = DLookup(strNew_Loc, strCascadeTableName, _
"[" & strOld_Loc & "]='" & rstCascade.Fields(strEQ_LocCode).Value & _
"'")

Loop

rstCascade.MoveNext
Loop While rstCascade.EOF = False

End If