Access 97- Trying to update fields using Recordset methods

[mehmet]

I'm using Access 97 and have a table (named "tblCompiledSorted") where records are set up like this:

Record No | Identifier
-------------------------------
1 12
2 14
3 14
4 17
5 19
6 19


I'm trying to write a module to change all my "Record No" change to "010" unless the identifier directly below the current row is identical to the identifier directly above it, in which case I want it named "012". Basically, I want my table to look like this when it's done:


Record No | Identifier
-------------------------------
010 12
012 14
010 14
010 17
012 19
010 19


Here's the code I wrote to try and do this:


Public Function UpdateRecordNo()
Dim db As Database
Dim strsql As String
Dim rstTop As Recordset, rstBottom As Recordset, rstRecNum As Recordset
Dim strTop As String, strBottom As String, strRecNum As String

Set db = CurrentDb

'Establish rstTop
strsql = "SELECT Identifier FROM tblcompiledsorted"
Set rstTop = db.OpenRecordset(strsql)
strTop = rstTop.Fields("Identifier").Value


'Establish restBottom
strsql = "SELECT Identifier FROM tblcompiledsorted"
Set rstBottom = db.OpenRecordset(strsql)
rstBottom.MoveNext
strBottom = rstBottom.Fields("Identifier").Value

'Establish rstRecNum
strsql = "SELECT * FROM tblcompiledsorted"
Set rstRecNum = db.OpenRecordset(strsql)
strRecNum = rstRecNum.Fields("Identifier").Value

'label Record Numbers
Do While rstRecNum.EOF = False
With rstRecNum
If strTop = strBottom Then
.Edit
![Record No] = "012"
.Update
Else
.Edit
![Record No] = "010"
.Update
End If
End With
rstTop.MoveNext
rstBottom.MoveNext
rstRecNum.MoveNext
Loop

rstTop.Close
rstBottom.Close
rstRecNum.Close

End Function


The problem is, when I run this I get a "No current record" message for the rstBottom.MoveNext command that in the looping section. Since it won't move to the next record, it pretty much screw my entire bit of code. I'm assuming it's because of the rstBottom.MoveNext I have in the 'Establish rstBottom section, but I haven't quite figured that out yet.

Does anyone know why this is happening or have any ideas on how I can fix it? I'd like to salvage this bit of code if possible as starting over would be rather vexing. Thanks in advance!

[RobDog888]

This will take care of all your 010 values.
UPDATE tblCompiledSorted
SET [Record No] = '010'
WHERE Count(Identifier) = 1Still working on the 012's

[Ecniv]

Thats cause you've done it wrong ;)

It was a good attempt, but as you have said it doesn't work.

You need three variables to hold very previous, previous and current ID values. A variable for the recordset (only one). Variable for Max records, and one for the current looping record.
Oh and a new column for the OrderID (the one you are changing to 010 or 012)

Set very previous and previous to -1

Loop through all records in the recordset using a for next loop

On pass one
- set the OrderID to 010
- set previous to the value

On pass two
- compare value to previous value
--- set to 012 it matches set to 010 if not
- set very prev to previous
- set previous to current

On the rest of the passes
- compare very prev to prev to current
--- set orderid appropriately



Or something like that.. I've just noticed that the table you resulted in is wrong.. unless you have that

[code]
Record No | Identifier
-------------------------------
010 12
012 14
010 14
010 17
012 19
010 19
[code]

The one in bold or the one above should be the other id value?


Vince

[mehmet]

Originally posted by Ecniv

Or something like that.. I've just noticed that the table you resulted in is wrong.. unless you have that

The one in bold or the one above should be the other id value?


Vince

The one in bold should be 10 because its identifier is 17 and the identifier below it is 19. If the identifier below the 17 was 19, then I'd need the top 17's record number to be 012 and the bottom 17's record number to be 010.

I'm working within a couple of rules I should have specified earlier:

1) There will never be more than 2 identical identifiers.
2) If there is a duplicate identifier, it will be directly below its matching identifier (this if from sorting I've done previously on the table).

I'm still working on this and I appreciate the help. Thanks!