AfterUpdate - Re-establishing some logic!

[JohnLute]

Per request I've started another thread regarding this puzzling issue.

I have a subform that creates a reciprocal record. Here's it's SQL (with
non-essential fields removed):

SELECT tblLocationsDestinations.LocationsDestinations,
tblLocationsDestinations.numLocationAddressID,
tblLocationsDestinations.TotalMiles
FROM tblLocationsDestinations;

Here's the subform's AfterUpdate event in question:
Private Sub Form_AfterUpdate()

With CurrentDb

' Delete the old destination, if it existed.
If Len(mstrOldDestinations) > 0 Then

mstrOldDestinations = Chr(34) & Me.cbLocationsDestinations.OldValue
& Chr(34)

.Execute _
"DELETE * FROM tblLocationsDestinations " & _
"WHERE LocationsDestinations=" & Me!numLocationAddressID & _
"AND numLocationAddressID=" &
Me.cbLocationsDestinations.OldValue, _
dbFailOnError

End If

' Create a reciprocal record to match the record being saved.
.Execute _
"INSERT INTO tblLocationsDestinations " & _
"(LocationsDestinations, numLocationAddressID) " & _
"VALUES (" & Me!numLocationAddressID & ", " & _
Me.cbLocationsDestinations & ")", _
dbFailOnError

End With

mstrOldDestinations = vbNullString

End Sub

Everything returns and works as expected EXCEPT when updating a reciprocal
record's TotalMiles field. This results in the deletion of the original
(parent record's) TotalMiles value.

In other words I create a record in tblLocationsDestinations and enter
TotalMiles. I go to the child record and enter its TotalMiles. I go back to
the parent record and its TotalMiles value has been deleted. I can add it
again but then the TotalMiles
for its child record is deleted.

Does anyone see why the TotalMiles records are affected like this? Is it
because
the subform is updating everything...?

I appreciate any help you might have!!!

 

[David W. Fenton]

I appreciate any help you might have!!!

I don't understand what you're doing. What do you mean by
"reciprocal record?" Please explain your data structure and what
you're expecting the code to do.

 

[JohnLute]

Dirk Goldgar helped with what really turned into a bit of a nightmare! He
came up with this treasure that I've been using and it has been behaving as
expected. Thanks to you and everyone who helped me with this - especially
Dirk! Check this out:

'----- start of code -----
Option Compare Database
Option Explicit

Dim mstrOldLocationsDestinations As String
Dim mstrOldLocationAddressID As String

Private Sub Form_AfterUpdate()

On Error GoTo Err_Handler

Const conERR_DUPLICATE_KEY = 3022

Dim rs As DAO.Recordset

If Len(mstrOldLocationsDestinations) = 0 Then
' This is a new record being added.

' Try to bring up the reciprocal record.
Set rs = Application.DBEngine.Workspaces(0)(0).OpenRecordset( _
"SELECT * FROM tblLocationsDestinations " & _
"WHERE LocationsDestinations = " & Me.numLocationAddressID & _
" AND numLocationAddressID = " & Me.LocationsDestinations)

' If a reciprocal record exists, leave it alone; otherwise, add one.
With rs
If .EOF Then
.AddNew
!LocationsDestinations = Me.numLocationAddressID
!numLocationAddressID = Me.LocationsDestinations
!TotalMiles = Me.TotalMiles
.Update
End If
End With

ElseIf _
CLng(mstrOldLocationsDestinations) = Me.LocationsDestinations And _
CLng(mstrOldLocationAddressID) = Me.numLocationAddressID _
Then

' Just updating details (not end-points) of existing record,
' so do nothing.

Else
' Here we're updating one (or both) of the record's original
' endpoints. If a reciprocal record exists for the original
' endpoints, update it accordingly. If not, create one.

Set rs = Application.DBEngine.Workspaces(0)(0).OpenRecordset( _
"SELECT * FROM tblLocationsDestinations " & _
"WHERE LocationsDestinations = " & mstrOldLocationAddressID & _
" AND numLocationAddressID = " & mstrOldLocationsDestinations)

' Did we find a record?
With rs
If .EOF Then
' No matching record was found, so just prepare to
' create a new one.
.AddNew
Else
' A matching record was found, so we'll be updating it.
.Edit
End If
' Either way, set the reciprocal record's new field values ...
!LocationsDestinations = Me.numLocationAddressID
!numLocationAddressID = Me.LocationsDestinations
!TotalMiles = Me.TotalMiles
' ... and save the record.
.Update
End With

End If

Exit_Point:
On Error Resume Next
If Not rs Is Nothing Then rs.Close
Set rs = Nothing
Exit Sub

Err_Handler:
If Err.Number = conERR_DUPLICATE_KEY Then
' If a duplicate-key error is raised, we must have tried to
' save a record that already exists. We'll ignore that error.
Resume Next
Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
End If

End Sub

Private Sub Form_Current()

' Capture the initial end-point key values of this record.
mstrOldLocationsDestinations = Me.LocationsDestinations & vbNullString
mstrOldLocationAddressID = Me.numLocationAddressID & vbNullString

End Sub