G
Guest
Hi;
My database deals with subscribers to a service. The database, which is in operation and not easily modifiable, 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 (PK-ProspSubscrID) and tlbLocation (PK-LocationID, FK-ProspSubscrID).
I now have to modify the database (in the most practical way possible) to facilitate two subscribers sharing the same location - in other words, co-subscribers. Where modifying the relationship structure is not easily viable and co-subscribers happen in-frequently, I'd like to create a duplicate record (all but the autonumber primary key) in tblLocation for a co-subscriber. Here's the vision: The end user first enters subscriber 1 data and then enters a location manually related to subscriber 1. After saving subscriber 1 data, the end user would then enter subscriber 2 data and trigger the same location data to be populated automatically for subscriber 2.
The main form has subscriber demographic data and the subform has location(s). One of the fields in tblSubscriber is CoSubscriberID which has a look-up relationship with an alias of tblSubscriber. 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 trigger an update query programmatically. The following code 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" & _
" " & "FROM tblLocation" & " " & _
"WHERE (tblLocation.ProspSubscrID = " & Me.ProspSubscrID & ");"
DBEngine(0)(0).Execute sSQL
End Sub
Thanks!
My database deals with subscribers to a service. The database, which is in operation and not easily modifiable, 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 (PK-ProspSubscrID) and tlbLocation (PK-LocationID, FK-ProspSubscrID).
I now have to modify the database (in the most practical way possible) to facilitate two subscribers sharing the same location - in other words, co-subscribers. Where modifying the relationship structure is not easily viable and co-subscribers happen in-frequently, I'd like to create a duplicate record (all but the autonumber primary key) in tblLocation for a co-subscriber. Here's the vision: The end user first enters subscriber 1 data and then enters a location manually related to subscriber 1. After saving subscriber 1 data, the end user would then enter subscriber 2 data and trigger the same location data to be populated automatically for subscriber 2.
The main form has subscriber demographic data and the subform has location(s). One of the fields in tblSubscriber is CoSubscriberID which has a look-up relationship with an alias of tblSubscriber. 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 trigger an update query programmatically. The following code 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" & _
" " & "FROM tblLocation" & " " & _
"WHERE (tblLocation.ProspSubscrID = " & Me.ProspSubscrID & ");"
DBEngine(0)(0).Execute sSQL
End Sub
Thanks!