Query that generates a new number everytime it updates

[snypa]

Hi guys

I am trying to get my append query to write one record at a time into one table from a join of two other tables. I need to do this because I need to increment a value from the what was inserted in the previous record.

I cannot use an autogenerate number becuase we are not allowed to modify the structure of thr recipient table.

Is there something in access that is similar to the cursor in SQL?

 

[Dennisk]

probally best to use either DAO or ADO. This will allow you to use recordsets with a variety of different cursors.

 

[snypa]

can I use conditions and use the following logic

1. initiate the variable = x
DO
2. check if record2 field = record1 field
3. if true variable = x+1
Loop

 

[Dennisk]

if you open two recordsets you can compare the values in the various columns.

So when you open the recordset, and the cursor will be positioned at the first record. then you can step through one record at a time or find a unique records or froups of records. Search help for recordsets, ADO and DAO

 

[snypa]

Do I need any modules for this to work coz I've been looking at it for a while and I want to put the code under a button. Do you have an example I can look at

 

[Dennisk]

Here is an example of reading a table row by row

Sub Test()
Dim db As DAO.Database
    Dim rst As DAO.Recordset
    
On Error GoTo Err_Handler
    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT StaffID FROM tblStaffBooked WHERE [DiaryID] = " & DiaryID & "AND [StartSlot] = " & StartSlot & " AND tblStaffBooked.Deleted = False")
    
    Do While Not rst.EOF

        rst.MoveNext
    Loop
    
ERR_Exit:
    db.Close
    Set rst = Nothing
    Exit Function
    
Err_Handler:
    MsgBox Err.Description
    Resume ERR_Exit

End Sub