form and subform send data to one table

[JRRyan]

Hi there, I've never asked a question here, but I have in the excel forum. Hopefully you guys and gals can help as much as they do in the excel forum!

I have a DB form with a subform linked to two tables. The mainform is linked to table1, subform table2. Both tables share some of the same information (id number, first and last name, DOB, etc), for the sake of sanity, I left the redundant fields off the subform.

My problem is I need to update table2 with the information from the subform AND the shared info from the mainform, and I have next to no clue how to manage it. I have scoured the web for an answer, but no joy. Any help would be appreciated!

 

[Phildaburn]

I think you'll find most people reluctant to tackle this task. Having identical data in 2 tables goes contrary to accepted relational database design where each table contains data about one thing: contact information, for example. If I may ask, what does your main form contain and what does the sub form contain? Is the relationship between them one to one? one to many? many to many?

Typically in the form design, the data on the "one" side of the relationship appears on the main form while the data on the "many" side of the relationship appears on the subform with the id number that appears in both being used as the link.

By putting shared data in both fields, the potential exists for data in one table or the other to be changed. Tom Smith in one table could be changed to Thomas Smith in the other and then the 2 tables are out of synch. If the name information only appears in one table, this situation is avoided.

Hope this helps...
Phil...

 

[JRRyan]

Thanks for the reply Phildaburn, especially as it would seem other people are reluctant.

I understand the preference, and it would be my preference as well, however the two tables represent two distinct data sets, with some overlapping entries, and as such they must contain the same data at times. There is also a legal need to keep the data, at least at some level, separate and intact.

The form has a one to many relationship with the subform where one named individual may have several entries in the subform's linked table and only one in the main form.

Reducing the chances of misspelled names and other errata is exactly why I want to update the subform's table using the data from both mainform and subform, the data from the mainform is name and id, the data from the subform is mostly check boxes and combo box selects.

 

[Phildaburn]

Have you considered running an update query? Since both tables will always contain the same id number which links them, you could prepare an update query to execute when the form/subform closes, for example.

Phil...

 

[JRRyan]

Hi, thanks again for the help. I followed your suggestion and used an update query where the fields i wanted to update were filtered using the is null property.

Here's the code I used to call it:

Private Sub Command140_Click()
On Error GoTo Err_Command140_Click
    Dim stDocName As String
DoCmd.Hourglass True
        'Turns off the Access warning messages
        DoCmd.SetWarnings False
    Me.Refresh
    stDocName = "SAFE Xref update"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    
    Me.Refresh
    DoCmd.Hourglass False
        'Turns the Access warning messages back on
        DoCmd.SetWarnings True
    
Exit_Command140_Click:
    Exit Sub
Err_Command140_Click:
    MsgBox Err.Description
    Resume Exit_Command140_Click
    
End Sub

All works great, thanks again!