Create Reciprocal Record via After Update - for Dirk Goldgar or ot

[JohnLute]

Thanks for sticking with me, David. You guys are great (even that Dirk guy
but let's not let his head get too big).

Here's what I've got:
Private Sub Form_AfterUpdate()

With CurrentDb

' Delete the old destination, if it existed.
If Len(mstrOldDestinations) > 0 Then
.Execute _
"DELETE * FROM tblLocationsDestinations " & _
"WHERE LocationsDestinations=" & Me!numLocationAddressID & _
" AND numLocationAddressID=" & mstrOldDestinations = Chr(34)
& Me.cbLocationsDestinations.OldValue & Chr(34)

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

This returns Error 3078 Cannot find the input table or query 'False'. The
help says to check for missing underscores. The debugger points to the line:
mstrOldDestinations = Chr(34) & Me.cbLocationsDestinations.OldValue & Chr(34)

I haven't a clue as to how to remedy that!

The DAO reference is there but it doesn't appear that this error has
anything to do with that...?

 

[Douglas J. Steele]

David was saying to have code

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

somewhere before the statement that's failing. You'd then use

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

 

[JohnLute]

Thanks, Doug.

David was saying to have code

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

somewhere before the statement that's failing. You'd then use

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

I've revised to this:
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=" & mstrOldDestinations,
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

This is now returning Error 3464 Data type mismatch in criteria expression.
Would a Find method remedy this...?

 

[Douglas J. Steele]

Is numLocationAddressID a text field or a numeric one? You're putting quotes
around it: if it's numeric, just use

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

Actually, looking at the names of the fields and the names of the controls,
is that correct? You're using numLocationAddressID from the form against the
LocationsDestinations field in the table, and cbLocationsDestinations from
the form against numLocationAddressID. Have you perhaps reversed it, so that
it should be

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

 

[JohnLute]

Is numLocationAddressID a text field or a numeric one? You're putting quotes
around it: if it's numeric, just use

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

DOUG!!! I was jumping up and down like a giddy school girl because this DID
IT. But then I went from jumping to crying like a big baby again. Everything
is working EXCEPT updating the reciprocal record's TotalMiles field results
in the deletion of the original (parent record's) TotalMiles value!

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

The subform's SQL is:
SELECT tblLocationsDestinations.LocationsDestinations,
([tblLocations].[txtLocationID] & " " & [tblLocations].[Name] & " â— " &
[tblLocationIDsAddresses].[Address] & " â— " &
[tblLocationIDsAddresses].[City] & ", " &
[tblLocationIDsAddresses].[StateOrProvince] & " " &
[tblLocationIDsAddresses].[CountryRegion]) AS FullAddress,
tblLocationsDestinations.numLocationAddressID,
tblLocationsDestinations.TotalMiles, tblLocationsDestinations.Comments
FROM tblLocations INNER JOIN (tblLocationsLocationIDs INNER JOIN
(tblLocationIDsAddresses INNER JOIN tblLocationsDestinations ON
tblLocationIDsAddresses.numLocationAddressID =
tblLocationsDestinations.LocationsDestinations) ON
tblLocationsLocationIDs.numLocID = tblLocationIDsAddresses.numLocID) ON
tblLocations.txtLocationID = tblLocationsLocationIDs.txtLocationID
ORDER BY tblLocationsDestinations.LocationsDestinations;

Why on earth are the TotalMiles records behaving like this? Is it because
the subform is updating everything...?

Thanks for your continued help. I feel like I'm almost there but yet so far
away!

 

[Douglas J. Steele]

I'm sorry, we're too deep in the thread for me to remember what's already
transpired.

If Dirk or David don't chime in, I'd recommend starting a new thread,
outlining what your set up is. By starting a new thread, you might get
someone new to jump in, and perhaps see something that we missed (although
both Dirk and David are pretty thorough)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Is numLocationAddressID a text field or a numeric one? You're putting
quotes
around it: if it's numeric, just use

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

DOUG!!! I was jumping up and down like a giddy school girl because this
DID
IT. But then I went from jumping to crying like a big baby again.
Everything
is working EXCEPT updating the reciprocal record's TotalMiles field
results
in the deletion of the original (parent record's) TotalMiles value!

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

The subform's SQL is:
SELECT tblLocationsDestinations.LocationsDestinations,
([tblLocations].[txtLocationID] & " " & [tblLocations].[Name] & " ? " &
[tblLocationIDsAddresses].[Address] & " ? " &
[tblLocationIDsAddresses].[City] & ", " &
[tblLocationIDsAddresses].[StateOrProvince] & " " &
[tblLocationIDsAddresses].[CountryRegion]) AS FullAddress,
tblLocationsDestinations.numLocationAddressID,
tblLocationsDestinations.TotalMiles, tblLocationsDestinations.Comments
FROM tblLocations INNER JOIN (tblLocationsLocationIDs INNER JOIN
(tblLocationIDsAddresses INNER JOIN tblLocationsDestinations ON
tblLocationIDsAddresses.numLocationAddressID =
tblLocationsDestinations.LocationsDestinations) ON
tblLocationsLocationIDs.numLocID = tblLocationIDsAddresses.numLocID) ON
tblLocations.txtLocationID = tblLocationsLocationIDs.txtLocationID
ORDER BY tblLocationsDestinations.LocationsDestinations;

Why on earth are the TotalMiles records behaving like this? Is it because
the subform is updating everything...?

Thanks for your continued help. I feel like I'm almost there but yet so
far
away!

 

[David W. Fenton]

I'm sorry, we're too deep in the thread for me to remember what's
already transpired.

At this point we're past the logic of constructing and executing the
SQL string to digging into the logic of what the SQL is doing, and
I'd need a lot more information to offer any more suggestions.