Data Has Been Changed / SQL Trigger Problem

[bdtmike]

Access XP
I have a form that is based on linked tables to a SQL server back end.
The table in SQL has a trigger that fires whenever an update is made
that recalculates some values of a couple of fields. When a user is
editing the form and saves the record, access sends the update to SQL.
This part is normal. However, the trigger in SQL fires and changes the
backend data. When the user edits another field, Access detects that
the data has been changed and puts up the dreaded "Data Has Been
Changed" message.

The only way I can see around this is to force a requery in the form's
AfterUpdate event. This has the unintended sideeffect of changine the
active control. I can save the most recent active control and set the
focus afterwards.

However, there are certain fields where I force the record to be saved
using docmd.runcmd accmdSaveRecord in the afterupdate event for that
control. In this situation, if a user changes a field and
tabs/shift-tabs to another field, the focus remains on that field. is
there any way of determining the "NextControl" where the focus "should"
be going to?

 

[Dirk Goldgar]

Access XP
I have a form that is based on linked tables to a SQL server back end.
The table in SQL has a trigger that fires whenever an update is made
that recalculates some values of a couple of fields. When a user is
editing the form and saves the record, access sends the update to SQL.
This part is normal. However, the trigger in SQL fires and changes
the backend data. When the user edits another field, Access detects
that the data has been changed and puts up the dreaded "Data Has Been
Changed" message.

The only way I can see around this is to force a requery in the form's
AfterUpdate event. This has the unintended sideeffect of changine the
active control. I can save the most recent active control and set the
focus afterwards.

However, there are certain fields where I force the record to be saved
using docmd.runcmd accmdSaveRecord in the afterupdate event for that
control. In this situation, if a user changes a field and
tabs/shift-tabs to another field, the focus remains on that field. is
there any way of determining the "NextControl" where the focus
"should" be going to?

Does it help if you refresh the form (Me.Refresh), rather than
requerying it? That should not move the current record the way Requery
does.

If you really need to know which control is next in the tab order, you
can try using this function:

'----- start of code -----
Function NextInTabOrder(StartCtl As Access.Control) As String

' Returns the name of the next active control in the tab order,
' if possible. Returns a null string if there isn't one.
'
' Written by Dirk Goldgar
' You may use this code, but you can't sell it except as part
' of an application, or claim it's your own work.

Dim astrControlNames() As String
Dim obj As Object
Dim ctl As Access.Control
Dim intI As Integer

On Error Resume Next
'to ignore errors we expect to raise

Set obj = StartCtl.Parent

ReDim astrControlNames(obj.Controls.Count)

' Build a list of available controls indexed by TabIndex.
' We aren't interested in controls that are invisible,
' disabled, not a tab stop, or not in the same section
' as StartCtl.
For Each ctl In obj.Controls
With ctl
If .Section = StartCtl.Section Then
If .Visible = True _
And .Enabled = True _
And .TabStop = True Then
astrControlNames(.TabIndex) = .Name
End If
End If
End With
Next ctl

Set obj = Nothing

' Loop forward through the list
For intI = StartCtl.TabIndex + 1 To UBound(astrControlNames)
If Len(astrControlNames(intI)) > 0 Then
NextInTabOrder = astrControlNames(intI)
Exit Function
End If
Next intI

' If we didn't find one, start at the beginning of the list.
For intI = 0 To StartCtl.TabIndex - 1
If Len(astrControlNames(intI)) > 0 Then
NextInTabOrder = astrControlNames(intI)
Exit Function
End If
Next intI

' If we get here, there's no available control.
Exit Function

End Function
'----- end of code -----

You pass the function a reference to the control you're currently at,
and it returns (hopefully) the name of the next control in the tab
order.

 

[bdtmike]

Nope, refresh doesn't cut it. And the problem with the tab situation
is that a user may no neccessarily be going to the next control in the
tab order. The user could have used shift-tab to go backwards, or
clicked on another control altogether.