Insert record and set a position

[peace77]

The Data set I am working on has only one field ("All Data"). I want to insert a new record that reads "Balance" just above an existing record that reads "Opening Balance"


I have the code for adding the record but when i run that it adds the record randomly rather than the the position that I want to set it up (ie above existing record that reads "Opening Balance").

This is the code that I have written but am unable to correctly set the position for the new recordset

Dim RS2 As DAO.Recordset
Dim thisonedb As Database

Set thisonedb = CurrentDb
Set RS2 = thisonedb.OpenRecordset("Select * from Data")
If RS2.RecordCount > 0 Then

RS2.MoveFirst

    Do Until RS2.EOF = True
    
    If InStr(1, RS2("AllData"), "Opening Balance") Then

        RS2.AddNew
        RS2("AllData").value = "Balance"
        RS2.Update
      End If
      
     RS2.MoveNext
    
    Loop
    
End If

I am totally new with vba and am unable to find out if it is possible to set the position at all.

Your help is appreciated.

Thanks

 

[Remou]

You may wish to read:

http://www.r937.com/relational.html

 

[peace77]

Thanks Remou!

My database is not relational (just working on one table) and I just want to temporarily insert rows to run some logic..once that logic is run then i got to delete those rows...so i am guessing it wont add to anomalies.

 

[JoeAtWork]

Records are not inserted in any particular position, that's how databases work. If you want some sort of order, you need a field that you can use an ORDER BY clause on. An autonumber field might suit in your case.