VBA Move/Update record

[stevenblanc]

Alright folks,

So I haven't spent much time i the past modifying recordsets with vba so I'm off to a slow start. I've been looking around and I'm still having trouble with the navigation.

I have a listbox(lstGuidelines) which contains the records from a record set.

The first column displays the first field which is a manually set identification number which is also the primary key. This identification number is the equivalent of an autonumber, but since I need to manipulate the numbers I can't use autonumbers.

Each record in this recordset will be used to build several controls on another form, and therefore the order in which they appear is important.

I want users to be able to move rearrange the records by moving a record either up or down.

So the user has selects the record to move from the listbox and clicks either move up or down. For down this should increase the primary key by 1 and decrease the primary key of the following record by 1.

I've gotten this far:

Private Sub btnMoveDown_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblGuidelines")
    
    'To ensure actions take place only if records exist, this is an extra precaution. As these actions can only take place if there is at least one item in lstGuidelines
    If rs.RecordCount <> 0 Then
    
        'To ensure that action does not take place if the last record is selected
        If Me.lstGuidelines.ListIndex <> Me.lstGuidelines.ListCount - 1 Then
            
            rs.Edit
            'Increase tblGuidlines.pedGuide by 1 for the selected
            
            
            DoCmd.FindRecord Me.lstGuidelines.Column(0) - 1, , False, , False, , True
            
            rs.Fields("pedGuide") = Me.lstGuidelines.Column(0) + 1
            MsgBox rs.Fields("pedguide") ' 4
            
            DoCmd.GoToRecord , , acNext
            MsgBox rs.Fields("pedguide") '4
            
            rs.Fields("pedGuide") = Me.lstGuidelines.Column(0)
            MsgBox rs.Fields("pedguide") '3
            rs.Update
            
            
                                    'MsgBox Me.lstGuidelines.Column(0) & vbNewLine & Me.lstGuidelines.Column(0) + 1
                
            'rs.FindFirst "pedGuide = " & Me.lstGuidelines.Column(0) <--- ERROR  
            
            'Decrease tblGuidelines.pedGuide by 1 for the record below <-- TODO , should be easy enough once I figure how to do the first part.
            
            'Refresh Me.lstGuidelines to reflect change
            'Call Refresh Function
        
        
        
        End If
        
    End If
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

As you can probably tell, I'm spinning in mud here. I've tried a number of approaches none of which have gotten me anywhere. I'm assuming I'm going about this all wrong. Any help is appreciated.

Steven

 

[boblarson]

The PK should not be changed. There should be a sort order field which would get changed and should have its index property set to Yes(duplicates OK).