Solved Form DAO.Recordset Looses Position On Requery

[dalski]

My form is based on a DAO.Recordset, a dynaset based on a table & view on SQL Server with the recordset.Restartable = True. The join is done in the queryDef of the recordset of the form where the view is a calculation.
Two important controls determine the outcome which sets the opposing ctrl null on entering data:

Private Sub txtCtrl1_AfterUpdate()
  If Not txtCtrl1 Then
    Me.txtCtrl2 = Null
    Me.Recordset.Requery
  End If
  Private Sub txtCtrl2_AfterUpdate()
  If Not txtResEquivalentTo Then
    Me.txtCtrl1 = Null
    Me.Recordset.Requery
  End If

Which then fires the beforeUpdate event of the form which does data-validation on numerical limits... If data-validation is successful the BeforeUpdate event I don't cancel the BeforeUpdate event. BUT sometimes the current record is lost in the BeforeUpdate event. Presumably with the bookmark being lost. At this stage the recordset contains the correct Me.Recordset.Recordcount but it is set to Me.Recordset.BOF & Me.Recordset.EOF causing 'No current record' error. If I try to comabit this within the BeforeUpdate event:

  If Me.Recordset.RecordCount > 1 And Me.Recordset.BOF Or Me.Recordset.EOF Then
    Me.Recordset.FindFirst "FieldName = " & Me.txtRecordID
  End If

I get the 'runtime error 3426 The action was cancelled by an associated object'. What is a sound coding pattern to avoid this?

EDIT - It's all avoidable if I don't requery the form but I want the user to be able to see the changes in real-time, whilst staying on the current record.

 

[dalski]

Sorry guys, after struggling several hours & shortly after posting I think I've found a solution. In the AfterUpdate of the ctrl's call a function to do data-validation. If data-validation is satisfied then Refresh from the ctrl's AfterUpdate events to reflect changes, else if data-validation is not satisfied then Me.Undo.

I haven't fully tested yet but looks promising. Don't want to waste peoples time if possible. Very embarrassing, apologies.

 

[The_Doc_Man]

Doing ANYTHING to modify a record in the .AfterUpdate event code (and Me.Undo counts as modifying a record) is ALWAYS questionable because at that point, the update has already occurred and the record might not be where you thought it was. I.e. it might be too late to silently modify anything. Modifying some other record (like, perhaps, making a log-table entry) is possible - but all sorts of record-use conflicts tend to pop up if you are changing the record for which an .AfterUpdate event just fired.

 

[AHeyne]

  If Me.Recordset.RecordCount > 1 And Me.Recordset.BOF Or Me.Recordset.EOF Then

Regardless of the actual topic, I expect that the logic used here does not quite match your requirements, as "And" ranks higher than "Or" in the operator hierarchy.

You can use parentheses to specify this as desired.

Your current code is essentially equivalent to this code:

If (Me.Recordset.RecordCount > 1 And Me.Recordset.BOF) Or Me.Recordset.EOF Then

However, I suspect that this is what you want:

If Me.Recordset.RecordCount > 1 And (Me.Recordset.BOF Or Me.Recordset.EOF) Then

See the documentation: https://learn.microsoft.com/en-us/o...rence/user-interface-help/operator-precedence

 

[dalski]

Thanks guys, Doc I changed the ctrl's to BeforeUpdate to call the data-validation fn & Me.Undo occurs there now avoiding a write if data-validation is not satisfied.

AHeyne that's helpful thanks. I've managed to make that block redundant now & avoid loosing the current record by only refreshing; opposed to requerying.

Understandably Access prevents .refresh used within the ctrl's BeforeUpdate event. So in each ctrl which causes a data-validation update I need to call the refresh method in the AfterUpdate of each individual ctrl; annoying I can't find a more efficient pattern.

EDIT - I imagine it's possible to wrap each ctrl in a class & handle the refresh there, but I'd probably need to write a class for each type of ctrl maybe.