Add a record to a related table via code

[Guest]

Good day;

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!

 

[Pavel Romashkin]

Modifying an existing DB is not a very pleasant task. The design you
have in fact combines a data table (Locations) with a junction table
(LocationID, ProspSubscrID). This limits the possibilities for the DB
use, as you found out the hard way.
I think you can make it fairly transparent though if you add a junction
table that has basically 2 fields - LocationID, ProspSubscrID and its PK
is the combination of the two. Populate the junction table using all the
LocationID, ProspSubscrID data from the Locations table.
How would you design the user interface is another business, but
providing a record source for it that is very similar to the existing
one is easy - just base it on a query that links the Subscriber table to
Locations table via the junction table.
Basically, this will translate your One-to-many relationship to a
Many-to-many without breaking normalization rules (or, in a human
language, without producing redundant records).
Pavel