Re: Create Reciprocal Record via After Update - for Dirk Goldgar o

"JohnLute" <JohnLute@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:8B3004C0-C691-4B4B-A25E-3EF61F8924E9@xxxxxxxxxxxxxxxx
Good grief. This is turning into a nightmare. I thought it was working fine -
NOT!

I'm able to create and delete reciprocal records BUT when I go to update an
existing record by editing/adding a mileage value the dreadful Run-time error
'13' Type Mismatch points to:

Private Sub Form_BeforeUpdate(Cancel As Integer)

' Pick up previous destination ID, if any.
' If the old value is Null, the following assignment will result in
' a zero-length string.
mstrOldDestinations = ("'" + Me.cbLocationsDestinations.OldValue + "'")
& vbNullString

End Sub

This makes sense as ("'" + Me.cbLocationsDestinations.OldValue + "'") should
properly be (" + Me.cbLocationsDestinations.OldValue + "). The problem is
that when I change it to the proper it triggers my friend
Run-time error '3075':
Syntax error (missing operator) in query expression
'LocationsDestinations=140 AND
numLocationAddressID=+Me.cbLocationsDestinations.OldValue+.'

The debugger points to the AfterUpdate event and line:
.Execute _
"DELETE * FROM tblLocationsDestinations " & _
"WHERE LocationsDestinations=" & Me!numLocationAddressID & _
" AND numLocationAddressID=" & mstrOldDestinations, _
dbFailOnError

Douglas explains that this implies that somehow mstrOldDestinations contains
the literal string "+Me.cbLocationsDestinations.OldValue+" rather than the
single numeric value it's supposed to. This escapes me. Furthermore, I can't
understand why this code isn't working considering it works for my other
application - the only difference being is between the number and text field
types.

Not sure if this helps but 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;

Do you have any ideas? I REALLY appreciate all your time.


I'm going to have to review this when I have a bit more time. Sorry, John, but I'll get back to you.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

.