little help with writing values for code comparing current and previous values

[joe789]

Hi Folks,

I could use a little help with writing values for code comparing current and previous values ...

The code is below:

Option Compare Database
Function runcompare()

Dim rst As Recordset
Dim dbs As Database
Dim querystr As String
Dim qryprev As String
Dim qrycurrent As String
Dim rstcnt As Integer
Set dbs = CurrentDb

querystr = "SELECT subno, c_proc from BaseNormalized;"

Set rst = dbs.OpenRecordset(querystr)
rst.MoveLast
rst.MoveFirst

For rstcnt = 0 To rst.RecordCount - 1
qryprev = rst.Fields("subno").Value
qryprev = qryprev & rst.Fields("c_proc").Value
If rstcnt <> rst.RecordCount - 1 Then
rst.MoveNext
qrycurrent = rst.Fields("subno").Value
qrycurrent = qryprev & rst.Fields("c_proc").Value
'If qryprev <> qrycurrent Then WRITE QRYPREV TO TABLE [DIFFERENCE]
End If
End If
Next rstcnt
rst.Close
dbs.Close

End Function

What I am attempting to do is compare current values in terms of the field subno and the values c_proc to the previous values; and if different, to write that previous value (both subno and c_proc) into a table. I am not quite sure if the above logic is correct, I think it is? But what I know I need help with would be writing the elements to a table ... using the docmd.gotorecord I don't see how I would use that command to actually insert the values of qryprev into a table if qryprev <> qrycurrent. Any help would be greatly appreciated.

Thank you,

Joe

 

[Access_guy49]

Firstly, the variables that hold the 2 values... if it were me, I would make them arrays.
qrycurrent(2)
That way you have a "location" in the array for each value, make seperating the values easier in my opinion.

Also, let me pose a question. are there only ever 2 records in this table that you query?
A Guru might correct me on this, but from what I understood, there isn't a garantee that code like this would result in sequential records, starting with the most recent.
In order to ensure that you would need to search for the records based on a unique ID or date.. or something that can ensure you pull the 2 most recent records.. (Again, this is only if you have more than 2 records in the table)

 

[Galaxiom]

Firstly you must order the query by some field. Tables have no intrinsic order to the records.

It is easier use a different sysntax:
recordsetname!fieldname rather than recordsetname.Fields("fieldname")

Open a recordset against the destination table and write to that.

With rsOutput
   .AddNew
   !fieldname= whatever
   .Update
End With

 

[joe789]

Thanks for the help! The current code is below. I went ahead and added a sort to the query to ensure the previous and current values that get compared have been sorted properly first and foremost. I also created a new RecordSet to accept values and write those to a table where a difference between current record and previous record exist; however, when I run the code it runs without error but just dumps all records into the 'differences' table ... I am not sure what I am doing wrong as in the program it is only suppose to write when difference between current and previous.

Function runcompare()

Dim rst As Recordset
Dim rst2 As Recordset
Dim dbs As Database
Dim querystr As String
Dim qryprev As String
Dim qrycurrent As String
Dim rstcnt As Integer
Set dbs = CurrentDb

querystr = "SELECT subno, servdate, c_proc from BaseNormalized order by subno, servdate, c_proc;"


Set rst = dbs.OpenRecordset(querystr)
Set rst2 = dbs.OpenRecordset("results", dbOpenDynaset)


rst.MoveLast
rst.MoveFirst


For rstcnt = 0 To rst.RecordCount - 1
qryprev = rst.Fields("subno").Value
qryprev = qryprev & rst.Fields("c_proc").Value
If rstcnt <> rst.RecordCount - 1 Then
rst.MoveNext
qrycurrent = rst.Fields("subno").Value
qrycurrent = qryprev & rst.Fields("c_proc").Value
If qrycurrent <> qryprev Then
With rst2
.AddNew
!subno = rst.Fields("subno").Value
!c_proc = rst.Fields("c_proc").Value
.Update
End With
Else
End If
End If
Next rstcnt
rst.Close
dbs.Close

End Function


Thanks for any help,

Joe

 

[Galaxiom]

qrycurrent = qryprev & rst.Fields("c_proc").Value

That line would be wrong.

Also note that instead of the For Loop with a counter you can use

While Not rst.EOF
{do stuff}
rst.MoveNext
Wend

 

[jdraw]

I responded to your similar post. see
http://www.access-programmers.co.uk/forums/showthread.php?t=209938