Fix code to duplicate a related record (except PK)

[Guest]

Good day

My database deals with subscribers to a service. The database, which is in operation, cannot have its table/relationship structure modified, and has a one-to-many relationship between subscribers and locations. Each subscriber may have many locations, a location belongs to a single subscriber. This is implemented with tblSubscriber (PrimaryK-ProspSubscrID) related to tlbLocation (PrimaryK-LocationID, ForeignK-ProspSubscrID)

I'd like to occasionaly create a duplicate record (all but the autonumber primary key) in tblLocation for a co-subscriber. Currently, the end user first enters subscriber 1 data (in the main form) and then enters a location manually, related to subscriber 1 (in the subform). After saving subscriber 1 data, the end user enters subscriber 2 data (in the main form) and I would like my application to TRIGGER THE SAME LOCATION data to be populated automatically for subscriber 2 (which would show automatically in the subform). As I mentioned above, it is too late to modify the table/relationship structure - so that is why I would like to duplicate a record occasionally

One of the fields in tblSubscriber is CoSubscriberID which has a functioning look-up relationship with an alias of tbllSubscriber. CoSubscriberID is shown on the main form as a combo with ProspSubcrID bound in the embedded SQL statement. I'm thinking of having that selected value (ProspSubscrID) trigger an update query programmatically. After searching previous newsgroup messages I found similar issues and came up with the following code to fit my situation. It does NOT cause an error, but also does not duplicate the record in the location table (the desired effect). Can you shed some light on this perhaps

Private Sub CoSubscriberID_AfterUpdate(

Dim sSQL As Strin

'Duplicate the related record
sSQL = "INSERT INTO tblLocation (Street1, SatelliteID, LocnTypeID, ProspSubscrID)" &
" " & "SELECT tblLocation.Street1, tblLocation.SatelliteID, tblLocation.LocnTypeID, tblLocation.ProspSubscrID" &
" " & "FROM tblLocation" & " " &
"WHERE (tblLocation.ProspSubscrID = " & Me.ProspSubscrID & ");
DBEngine(0)(0).Execute sSQ

End Su

Thanks very much for your help.

 

[Michel Walsh]

Hi,


You miss the optional argument dbFailOnError ( in the database.Execute
method).

If the record SEEMS to be not added, it may be because your code adds
it, and your code is another user, and we do not see records added by other
users unless we requery.

If the record IS not added, even if you requery or re-open the
form/subform, it may be because a field requiring a value (not allowing
null) and not having a default value is not specified a value, OR that a
referential data integrity rule would be violated, OR any kind of such
legitimate error. Can you, MANUALLY, add a new record, in the table,
directly, just specifying the only four fields you actually specify... with
the actual values you try to insert?



Vanderghast, Access MVP

Good day;

My database deals with subscribers to a service. The database, which is

in operation, cannot have its table/relationship structure modified, and has
a one-to-many relationship between subscribers and locations. Each
subscriber may have many locations, a location belongs to a single
subscriber. This is implemented with tblSubscriber (PrimaryK-ProspSubscrID)
related to tlbLocation (PrimaryK-LocationID, ForeignK-ProspSubscrID).

I'd like to occasionaly create a duplicate record (all but the autonumber

primary key) in tblLocation for a co-subscriber. Currently, the end user
first enters subscriber 1 data (in the main form) and then enters a location
manually, related to subscriber 1 (in the subform). After saving subscriber
1 data, the end user enters subscriber 2 data (in the main form) and I would
like my application to TRIGGER THE SAME LOCATION data to be populated
automatically for subscriber 2 (which would show automatically in the
subform). As I mentioned above, it is too late to modify the
table/relationship structure - so that is why I would like to duplicate a
record occasionally.

One of the fields in tblSubscriber is CoSubscriberID which has a

functioning look-up relationship with an alias of tbllSubscriber.
CoSubscriberID is shown on the main form as a combo with ProspSubcrID bound
in the embedded SQL statement. I'm thinking of having that selected value
(ProspSubscrID) trigger an update query programmatically. After searching
previous newsgroup messages I found similar issues and came up with the
following code to fit my situation. It does NOT cause an error, but also
does not duplicate the record in the location table (the desired effect).
Can you shed some light on this perhaps:

Private Sub CoSubscriberID_AfterUpdate()

Dim sSQL As String

'Duplicate the related record.
sSQL = "INSERT INTO tblLocation (Street1, SatelliteID, LocnTypeID, ProspSubscrID)" & _
" " & "SELECT tblLocation.Street1, tblLocation.SatelliteID,

tblLocation.LocnTypeID, tblLocation.ProspSubscrID" & _