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

[Guest]

A couple years ago Dirk helped me with the challenge of creating reciprocal
records in a table. The following is based off his AfterUpdate code which has
always worked. I've modified it slightly for use in another function which
also requires reciprocal records. Except for the field name and table name
changes it's EXACTLY per Dirk's code:

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
End If

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


End With

mstrOldDestinations = vbNullString

End Sub

The problem is that I can create a record but the reciprocal record isn't
reciprocating! I get no error messages - it just doesn't create the
reciprocal record. Is it because in this new function the primary key field
is a number data type? In my other function the primary key field is a text
data type - that's the only difference I see.

If that's the case do I need to change my closing quotation marks?

Thanks a bunch - gobble, gobble!

 

[Douglas J. Steele]

Are you saying that LocationsDestinations is numeric?

Remove the single quotes:

.Execute _
"INSERT INTO tblLocationsDestinations " & _
"(LocationsDestinations, numLocationAddressID) " & _
"VALUES (" & Me!numLocationAddressID & ", '" & _
Me.cbLocationsDestinations & "')"

or, better yet,

.Execute _
"INSERT INTO tblLocationsDestinations " & _
"(LocationsDestinations, numLocationAddressID) " & _
"VALUES (" & Me!numLocationAddressID & ", '" & _
Me.cbLocationsDestinations & "')", dbFailOnExecute

 

[Dirk Goldgar]

A couple years ago Dirk helped me with the challenge of creating reciprocal
records in a table. The following is based off his AfterUpdate code which
has
always worked. I've modified it slightly for use in another function which
also requires reciprocal records. Except for the field name and table name
changes it's EXACTLY per Dirk's code:

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
End If

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


End With

mstrOldDestinations = vbNullString

End Sub

The problem is that I can create a record but the reciprocal record isn't
reciprocating! I get no error messages - it just doesn't create the
reciprocal record. Is it because in this new function the primary key
field
is a number data type? In my other function the primary key field is a
text
data type - that's the only difference I see.

If that's the case do I need to change my closing quotation marks?

Thanks a bunch - gobble, gobble!

Who're you calling a turkey? I hope that's not a comment on my code! <g>

Hi, John.

If you changed a key field from text to numeric, it's certainly going to
make a difference, though I would have thought a data type mismatch error
would be raised. So, what are the data types of the fields
LocationsDestinations and numLocationAddressID?

 

[Dirk Goldgar]

.Execute _
"INSERT INTO tblLocationsDestinations " & _
"(LocationsDestinations, numLocationAddressID) " & _
"VALUES (" & Me!numLocationAddressID & ", '" & _
Me.cbLocationsDestinations & "')", dbFailOnExecute

FailOnExecute? Yep, a lot of my code does that. <g>

 

[Guest]

Hi, Dirk!

Hope all is well.

Who're you calling a turkey? I hope that's not a comment on my code! <g>

Takes one to know one!!!

If you changed a key field from text to numeric, it's certainly going to
make a difference, though I would have thought a data type mismatch error
would be raised.

That's what I though, too but go figure!

So, what are the data types of the fields
LocationsDestinations and numLocationAddressID?

They're both numeric (Long Integer). I bet Mr. Steele has hit on the
answer...?

BTW I haven't changed anything about the functions and tables you helped
with - I'm just using your code to do something similar in a new table.

 

[Dirk Goldgar]

They're both numeric (Long Integer). I bet Mr. Steele has hit on the
answer...?

Close, but I think he assumed only one of them was numeric. Try this:

If Len(mstrOldDestinations) > 0 Then
.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

 

[JohnLute]

Thanks, Dirk!

Hope your holiday was tasty.

Close, but I think he assumed only one of them was numeric. Try this:

If Len(mstrOldDestinations) > 0 Then
.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

This returns a compile error: "Variable not defined" and the debugger points
to where I've placed <>. I can't see what variable isn't defined. I have this
in declarations:
Option Compare Database
Option Explicit

Dim mstrOldDestinations As String

Any ideas?

Thanks!

 

[Douglas J. Steele]

This returns a compile error: "Variable not defined" and the debugger
points
to where I've placed <>. I can't see what variable isn't defined. I have
this
in declarations:

dbFailOnError is defined in the Microsoft DAO 3.6 Object Library. Sounds as
though you don't actually have a reference set to DAO. Either set the
reference, or include

Const dbFailOnError As Long = 128

in your code.

 

[Dirk Goldgar]

Thanks, Dirk!

You're welcome.

Hope your holiday was tasty.

Very nice, thank you. Yours, too, I hope.

This returns a compile error: "Variable not defined" and the debugger
points
to where I've placed <>. I can't see what variable isn't defined.

As Doug said, that constant is defined in the DAO object library. Add that
reference or define it yourself as a constant with the value 128.

 

[JohnLute]

Thanks, Douglas!

I first tried adding the code:
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, _
dbFailOnError
Const dbFailOnError As Long = 128
End If

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

End With

mstrOldDestinations = vbNullString

End Sub

This returned a compile error on the line "Const dbFailOnError As Long =
128". I figured I added it in the wrong place so I took it out and set the
Microsoft DAO 3.6 Object Library. I ran compact/repair and closed/opened the
database. I ran the subform again and this returned:

Run-time error '3075':
Syntax error (missing operator) in query expression
'LocationsDestinations=140 AND
numLocationAddressID=+Me.cbLocationsDestinations.OldValue+.'

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

ARRRGH! What gives? I'm guessing there's a clash with this syntax and the
Microsoft DAO 3.6 Object Library...?

 

[Douglas J. Steele]

Thanks, Douglas!

I first tried adding the code:
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, _
dbFailOnError
Const dbFailOnError As Long = 128
End If

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

End With

mstrOldDestinations = vbNullString

End Sub

This returned a compile error on the line "Const dbFailOnError As Long =
128". I figured I added it in the wrong place so I took it out and set the
Microsoft DAO 3.6 Object Library. I ran compact/repair and closed/opened
the
database. I ran the subform again and this returned:

Run-time error '3075':
Syntax error (missing operator) in query expression
'LocationsDestinations=140 AND
numLocationAddressID=+Me.cbLocationsDestinations.OldValue+.'

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

ARRRGH! What gives? I'm guessing there's a clash with this syntax and the
Microsoft DAO 3.6 Object Library...?

There's something wrong with how you're assigning the values to
mstrOldDestinations. The error message implies that somehow
mstrOldDestinations contains the literal string
"+Me.cbLocationsDestinations.OldValue+" rather than the single numeric value
it's supposed to.

As to the other error, you can only define a constant once. Const
dbFailOnError As Long = 128 should have gone at the top of the module.

 

[JohnLute]

Thanks, Douglas.

I tinkered with this again and found some things in my other procedures.
Everything appears to be working now. Here's what I've got:

Option Compare Database
Option Explicit
Const dbFailOnError As Long = 128

Dim mstrOldDestinations As String

Private Sub Form_AfterDelConfirm(Status As Integer)

' Don't do anything unless the delete was successful.
If Status = acDeleteOK Then

' We will have created a comma-separated list of the IDs to be
deleted.

CurrentDb.Execute _
"DELETE * FROM tblLocationsDestinations " & _
"WHERE LocationsDestinations=" &
Me.Parent!numLocationAddressID & _
"AND numLocationAddressID IN (" &
Mid$(mstrOldDestinations, 2) & ")"

End If

mstrOldDestinations = vbNullString

End Sub

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, _
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

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)

If MsgBox("Would you like to delete this record?", _
vbQuestion + vbYesNoCancel + vbDefaultButton2) = vbYes Then

Cancel = False
Response = acDataErrContinue
Else
Cancel = True
End If

End Sub

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

Private Sub Form_Delete(Cancel As Integer)

' Capture the old destination from each deleted record.
mstrOldDestinations = mstrOldDestinations & "," &
Me.cbLocationsDestinations & ""

End Sub

I explained the reason for this design in a response but then lost it as the
system booted me off. I can't explain it all again!

Anyway, I appreciate all your help (and of course, MR. Goldgar's). BTW I
noticed some other problems with a couple un-related reports and so I removed
the reference to Microsoft DAO 3.6 Object Library and things have cleared up.

Thanks, again!

 

[Dirk Goldgar]

BTW I
noticed some other problems with a couple un-related reports and so I
removed
the reference to Microsoft DAO 3.6 Object Library and things have cleared
up.

John, that doesn't necessarily sound like a good idea to me. Although you
can certainly work without it, I would leave the DAO reference in any
application where I was going to work with DAO objects. It is true that you
can have conflicts with the ADO (ActiveX Data Objects) library, but there
are well-established ways around those.

What sort of problems did you clear up by removing the DAO reference, if I
may ask?

 

[JohnLute]

What sort of problems did you clear up by removing the DAO reference, if I
may ask?

I wish I could tell you! To answer your question I had to set the reference
again. I ran the report that generated the error the first time and it ran
fine. !?

Whatever. I'm having a strange day. I'll keep the reference and let you know
if errors return. I won't know how to resolve them if they do!

 

[JohnLute]

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.

 

[Dirk Goldgar]

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.

 

[JohnLute]

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

Thanks!!! I just aged 10 more years today.

 

[David W. Fenton]

mstrOldDestinations = ("'" +
Me.cbLocationsDestinations.OldValue + "'")

Try this:

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

That will get you double quotes. It also removes the problem that
would happen when the OldValue is Null -- using the + concatenation
operator will propagate the Null, whereas the & concatenation
operator ignores it. With + you'd get Null as the result, where as
with &, you'd get "". That might not work, either, but it won't
trigger an error.

 

[JohnLute]

Thanks, David.

That's an interesting approach. I gave it a try but it returned:
Compile error: expected procedure, not variable.

The debugger pointed to:
dbFailOnError

Not sure how to address this now!

 

[David W. Fenton]

That's an interesting approach. I gave it a try but it returned:
Compile error: expected procedure, not variable.

The debugger pointed to:
dbFailOnError

Not sure how to address this now!

Perhaps you didn't copy it right. Here's a shorter version that
won't need the line wrap character:

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

The constant dbFailOnError should be after the SQL string in your
..Execute, not in the string assignment I gave you.

Perhaps you removed the DAO reference again?