Submit data from multiple subforms at once in MS Access 2010

Question

Hello Guys,

I'm facing challenges with submitting the data entered in multiple subforms on a main form in Access 2010 (subforms designed using Tab Control function in Access). My requirement is to submit all the entered information within main form  and various subforms (as appearing in the picture) at once with the help of Update button in their associated tables and also, reset all the fields (main and subforms) using Reset button (I could reset the fields on the main form). Please guide me through the VBA code for this. Thank you.

Answer 1

Firstly, I assume that you don't have a Families table, so rather than repeating my code to determine whether the relevant already row exists in the table, you should be determining whether the relevant row exists in the relevant table in your database.  To judge by your first INSERT INTO statement this appears table to be the MonthlyAudit_Master table, which I would expect to be the referenced table in a set of one-to-many relationship types with each of the tables to which each of your subforms relate.  What you need to do here is call the DLookup function to return the primary key of that table in a row which is distinctly identified by one or more values from controls in the parent form.

If the DLookup function returns a Null, then the row does not yet exist, so the INSERT INTO statement is built and executed.  The list of values to be inserted will be obtained from controls in the parent form.  I see that you are referencing objects whose names begin with the tag frm, which suggests to me that these are form names rather than the names of controls.  Also, when calling the AuditTransaction function, you are passing values into the function as literal strings, not as references to controls as I'd expect.

Having inserted a row into the referenced table if necessary, you then need to execute a set of further INSERT INTO statements, each inserting values into one of the referencing tables in the one-to-many relationship type.  From your screen-shot you appear to have five subforms, so I assume there are five referencing tables , and consequently you'll need to build and execution five INSERT INTO statements.  For this you need to insert as a foreign key the value of the primary key column in the row in the referenced table identified by a value or values in controls in the parent form, along with the values from the controls in the relevant subform.  For this the syntax to reference a control in a subform from within a parent form's module is:

    Me.[NameOfSubformControl].Form.[NameOf ControlInSubform]

where NameOfSubformControl is the name of the control in the parent form's Controls collection which houses the subform.  This might or might not be the same name as its source form object.

Note that when building the INSERT INTO statements the values need to be wrapped in quotes characters if the column in question is of text data type, with # characters if the column is of date/time data type, and with no delimiting characters if the column is a number data type.  Note also that a date value needs to be formatted in US short date format of mm/dd/yyyy, or an otherwise internationally unambiguous format such as the ISO standard for date notation of yyyy-mm-dd.  This is done by calling the Format function when building the string expression.

I'm curious why you see the need to insert rows into all tables simultaneously as a single transaction?  A conventional interface of a bound parent form and bound subforms, each linked to the parent form on the keys, would avoid the need for any code.

Answer 2

Ok Ken, so I modified the code and try to run it... Here is the modified code. I'm running into Type Mismatch error on Button Click code at the bottom.

Public Function AuditTransaction(frmMasterDate As Date, frmMasterAuditor As String, frmMasterArea As String, frmMasterMgr As String, SrtPtQ1 As String, SrtCmtQ1 As String, SrtWhoQ1 As String, SrtWhenQ1 As Date, SrtComQ1 As String)

On Error GoTo Err_Handler

    Dim strSQL As String

    Dim strCriteria As String

    DBEngine.BeginTrans

    ' insert row into Families table if doesn't exist

    'If IsNull(DLookup("FamilyName", "Families", "FamilyName = """ & strFamilyName & """")) Then

        strSQL = "INSERT INTO MonthlyAudit_Master ([Master_Date], [Master_Auditor], [Master_Area], [Master_Manager]) Values ('" & frmMasterDate & "','" & frmMasterAuditor & "','" & frmMasterArea & "', '" & frmMasterMgr & "')"

        CurrentDb.Execute strSQL, dbFailOnError

    'End If

   ' insert matching row into FamilyMembers table

    strSQL = "INSERT INTO SORT([Date_Sort], [Area_Sort], [Auditor_Sort], [Supervisor_Sort], [SortQ1Point], [SortQ1Comment], [SortQ1Who], [SortQ1When],[SortQ1Complete]) Values ('" & frmMasterDate & "','" & frmMasterArea & "','" & frmMasterAuditor & "', '" & frmMasterMgr & "', '" & SrtPtQ1 & "', '" & SrtCmtQ1 & "', '" & SrtWhoQ1 & "', '" & SrtWhenQ1 & "', '" & SrtComQ1 & "')"

    CurrentDb.Execute strSQL, dbFailOnError

    DBEngine.CommitTrans

Exit_Here:

    Exit Function

Err_Handler:

    MsgBox Err.Description

    DBEngine.Rollback

    Resume Exit_Here

End Function

---

Private Sub FrmMasterUpdate_Click()

AuditTransaction "frmMasterDate", "frmMasterAuditor", "frmMasterArea", "frmMasterMgr", "SrtPtQ1", "SrtCmtQ1", "SrtWhoQ1", "SrtWhenQ1", "SrtComQ1"

End Sub

Answer 3

Ken, Thank you so much for sparing time and helping me here. I'll take this sample and will modify and run as per my requirement and will let you know if this is working. Thank you once again.

Answer 4

As you are using unbound subforms you will need to execute a series of INSERT INTO statements in code, one for the parent form if necessary, and one for each subform.  The statements should be executed within a Transaction.  The following is a function which does this in a much simpler context of a Families table and FamilyMembers referencing table:

Public Function FamilyTransaction(strFamilyName As String, strFirstname As String)

    On Error GoTo Err_Handler

    Dim strSQL As String

    Dim strCriteria As String

    DBEngine.BeginTrans

    ' insert row into Families table if doesn't exist

    If IsNull(DLookup("FamilyName", "Families", "FamilyName = """ & strFamilyName & """")) Then

        strSQL = "INSERT INTO Families(FamilyName) " & _

             "VALUES(""" & strFamilyName & """)"

        CurrentDb.Execute strSQL, dbFailOnError

    End If

   ' insert matching row into FamilyMembers table

    strSQL = "INSERT INTO FamilyMembers(FamilyName, Firstname)  " & _

        "VALUES(""" & strFamilyName & """,""" & strFirstname & """)"

    CurrentDb.Execute strSQL, dbFailOnError

    DBEngine.CommitTrans

Exit_Here:

    Exit Function

Err_Handler:

    MsgBox Err.Description

    DBEngine.Rollback

    Resume Exit_Here

End Function

This example is taken from a simple demo file, and makes the assumption that family names are distinct and can be used as a key.  In real life this would not be the case of course, and a FamilyID surrogate numeric key would be used.

To reset the form/subforms, each relevant control will need to be set to Null, which for the parent form can be done with:

    Dim ctrl As Control

    On Error Resume Next

    For Each ctrl in Me.Controls

        ctrl.Value = Null

    Next ctrl

The same would be done for each subform, referencing the subform with the following syntax:

    Me.NameOfSubformControl.Form

where NameOfSubformControl is the name of the control in the parent form's Controls collection.  This might or might not be the same as the name of the source form object.