Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.
    Attached Files Attached Files

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you SO MUCH, Hans!
    It works in your .mdb - I will apply changes and it'll be awesome!
    Thanks!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •