Requery and/or Update problem with a form and recordset (1 Viewer)

[mbentley]

I have a form (actually a subform, but that's not relevant), displayed as a continuous form, that the user can use to edit the display order of certain records. The form displays the records and allows the user to move them up or down in the order by clicking an up or down arrow. In the background, the code is opening a recordset to update a sorting variable for the records eac time a button is clicked. Once the sort order is changed, the code requeries the form (Me.Requery). I'm finding two problems:

1) The form intermittently fails to requery, leaving the results displayed in the wrong order. Closing and reopening the form fixes this. Is there some trick to requery?

2) The code intermittently brings up a record locking error and cannot amend the underlying record.

 

[Pat Hartman]

1. There must be some case when the requery code fails to run. Check your code.
2. You are confilicting with yourself. You have an open form where changes might be made and you're running an update query behind the scenes. You can force Access to save the current record if necessary prior to running the update query. That should get rid of the conflict.

If Me.Dirty Then
    DoCmd.RunCommand acCmdSaveRecord
End If
Run the update query

 

[mbentley]

Thanks Pat. I actually do force a save beforehand, so that's not the problem. I'm also not using an update query. I'm using an ADO recordset in case that makes a difference.

 

[mbentley]

Here's my code in case it helps:

Private Sub cmdUp_Click()

On Error GoTo EH

'Renumbers the order for this section and patient,
'moving the active record up one in the sort order.

    Dim cnnLayout As ADODB.Connection, rstLayout As ADODB.Recordset, strSQL As String

'Does nothing if this is already in the first position or is a blank record.

    If Me.txtSectionPosition = 1 Or IsNull(Me.txtSectionPosition) Then Exit Sub

'Saving the record is essential to prevent record-locking problems.

    If Me.Dirty Then DoCmd.RunCommand acCmdSaveRecord
    
'Brings up the necessary recordset and renumbers the
'existing diagnostic that now has the same number as the active record.
'Net result is swapping the two numbers.
'SQL filters for patient, section, and position.
'Should only return a single result.

    strSQL = "SELECT PatientID, DiagnosticType, ReportSection, SectionPosition " & _
    "FROM tbl_Admin_AssessmentReportLayout " & _
    "WHERE PatientID=" & Me.txtPatientID & _
    " AND ReportSection=" & Chr(34) & conSectionName & Chr(34) & _
    " AND SectionPosition=" & Me.txtSectionPosition - 1 & _
    " AND DiagnosticType<>" & Chr(34) & Me.txtDiagnosticType & Chr(34)
    
    Set cnnLayout = New ADODB.Connection
    With cnnLayout
        .Provider = CurrentProject.Connection.Provider
        .ConnectionString = GetBackendPath("tbl_Admin_AssessmentReportLayout")
        .Open
    End With
    
    Set rstLayout = New ADODB.Recordset
    rstLayout.Open strSQL, cnnLayout, adOpenDynamic, adLockOptimistic

'Safety catch.  Exits sub and messages user if no record is found.

    If rstLayout.EOF And rstLayout.BOF Then
        MsgBox "There is an internal error in the database preventing " & _
        "this section from being updated. Close and reopen this form, and " & _
        "try again. If the error persists, contact the database administrator.", _
        vbOKOnly + vbCritical, "Internal Error"
        rstLayout.Close
        cnnLayout.Close
        Exit Sub
    End If

'Updates the approrpiate records.

    rstLayout.Fields("SectionPosition") = rstLayout.Fields("SectionPosition") + 1
    rstLayout.Update
    rstLayout.Close
    cnnLayout.Close
    
    Me.txtSectionPosition = Me.txtSectionPosition - 1
    DoCmd.RunCommand acCmdSaveRecord

    Me.Requery
    
EH_Exit:
    Exit Sub
    
EH:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume EH_Exit
    
End Sub

 

[mbentley]

Solution

For some reason, the form was not re-applying the sort order in the underlying query when re-querying. I coded it to set a form level OrderBy property, which seems to do the trick. The record locking problem also vanished.

    Me.OrderBy = "[SectionPosition]"
    Me.OrderByOn = True
    Me.Requery

 

[Pat Hartman]

I'm using an ADO recordset in case that makes a difference.

- no it doesn't. You have a second save record that is not necessary. The saveRecord command saves the current record and has nothing to do with the update you did via ADO.

 

[mbentley]

Actually in the line before that I update a control on the active form, which is why I force the save.