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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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
 
JohnLute said:
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!
Click to expand...


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?
 
Douglas J. Steele said:
.Execute _
"INSERT INTO tblLocationsDestinations " & _
"(LocationsDestinations, numLocationAddressID) " & _
"VALUES (" & Me!numLocationAddressID & ", '" & _
Me.cbLocationsDestinations & "')", dbFailOnExecute
Click to expand...


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

Hope all is well.

Dirk Goldgar said:
Who're you calling a turkey? I hope that's not a comment on my code! <g>
Click to expand...

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.
Click to expand...

That's what I though, too but go figure!
So, what are the data types of the fields
LocationsDestinations and numLocationAddressID?
Click to expand...

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.
 
JohnLute said:
They're both numeric (Long Integer). I bet Mr. Steele has hit on the
answer...?
Click to expand...

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
 
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
Click to expand...

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!
 
JohnLute said:
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:
Click to expand...

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.
 
JohnLute said:
Thanks, Dirk!
Click to expand...

You're welcome.
Hope your holiday was tasty.
Click to expand...

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.
Click to expand...

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.
 
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...?
 
JohnLute said:
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...?
Click to expand...

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.
 
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!
 
JohnLute said:
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.
Click to expand...


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?
 
Dirk Goldgar said:
What sort of problems did you clear up by removing the DAO reference, if I
may ask?
Click to expand...

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!
 
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.
 
JohnLute said:
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.
Click to expand...


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 said:
I'm going to have to review this when I have a bit more time. Sorry, John,
but I'll get back to you.
Click to expand...

Thanks!!! I just aged 10 more years today.
 
mstrOldDestinations = ("'" +
Me.cbLocationsDestinations.OldValue + "'")
Click to expand...

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.
 
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!
 
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!
Click to expand...

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?
 
Back
Top