RS: Find a change – record in table

Topic

New Reply

Hello!

My new assignment is to step trough the recordset and every time scale is changing – write into table as

ID_____Date_____OldScale_____Date______NewScale

ID______Date________Scale
11____1/1/2002_______112
11____1/1/2003_______112
11____1/1/2006_______111

So the output in this case will have to be:

ID_____Date_____OldScale_____Date______NewScale
11____1/1/2003___112_______1/1/2006______111

Please, help me with code if you can, thanks so much.

Reply | Quote

Replies

Here is some sample code. It requires a reference to the Microsoft DAO 3.6 Object Library, and of course you need to substitute the correct names.

Code:

Sub ListScaleChanges()
  Dim dbs As DAO.Database
  Dim rstIn As DAO.Recordset
  Dim rstOut As DAO.Recordset
  Dim strSQL As String
  Dim lngPrevID As Long
  Dim dtmPrevDate As Date
  Dim lngPrevScale As Long
  Set dbs = CurrentDb
  strSQL = "SELECT * FROM tblData ORDER BY ID, TheDate, Scale"
  Set rstIn = dbs.OpenRecordset(strSQL, dbOpenForwardOnly)
  Set rstOut = dbs.OpenRecordset("tblScaleChanges", dbOpenDynaset)
  Do While Not rstIn.EOF
	If rstIn!ID = lngPrevID And Not rstIn!Scale = lngPrevScale Then
	  rstOut.AddNew
	  rstOut!ID = lngPrevID
	  rstOut!oldDate = dtmPrevDate
	  rstOut!OldScale = lngPrevScale
	  rstOut!NewDate = rstIn!TheDate
	  rstOut!NewScale = rstIn!Scale
	  rstOut.Update
	End If
	lngPrevID = rstIn!ID
	dtmPrevDate = rstIn!TheDate
	lngPrevScale = rstIn!Scale
	rstIn.MoveNext
  Loop
  rstOut.Close
  Set rstOut = Nothing
  rstIn.Close
  Set rstIn = Nothing
  Set dbs = Nothing
End Sub

See the attached sample database. The code is in the module basCode.

Reply | Quote

Thank you SO MUCH, Hans!
It works in your .mdb – I will apply changes and it’ll be awesome!
Thanks!!!

Reply | Quote