Microsoft Access - Creating sub form records on main form load/update

Question

I'm relatively new to Microsoft Access but have picked it up pretty quickly. I am trying to figure out what sort of VBA or SQL code I could use to force new record creation in subforms once the main table/form is loaded. Let me explain my current form situation:

Main form name: frmUpload, primary key "ID"
Child subform 1: frmIdentifiers, FK "ApplicantID"
Child subform 2: frmFIdentifiers, FK "ApplicantID"
Child subform 3: frmOther, FK "ApplicantID"

These three subforms exist on the main form and record data to tblIdentifiers, tblFIdentifiers and tblOther, respectively. The three subforms have checkboxes only. In most use cases, the user will fill out applicable information on frmUpload and will fill out information in frmIdentifiers, frmFIdentifiers OR frmOther. It is very unlikely that the checkboxes in multiple subforms will be used.

The form links are established properly. If I enter information on the main form and use a checkbox in one of the subforms, the primary/foreign keys populate automatically. That's not a problem.

My question is this: is there any way to force records to write into the other unused tables? For example, if a user only records data to frmUpload/tblUpload and frmOther/tblOther, what could I do to make frmIdentifiers/tblIdentifiers and frmFIdentifiers/tblFIdentifiers both populate "Applicant ID" equal to the primary key of the main form?

Thank you.

Existing workarounds I've tried for this:

Checkbox with default value to "Yes" that user disables if one of the subforms is not in use. In this way, this forces subform entry and created a new record with proper parent/child links. More inconvenient and less ideal for user interactivity.
VBA code. Created 3 boolean variables set to False by default, If statements for each child subform. On form entry, the variable is set to true for that subform and if the other subforms have their boolean set to false, a SQL statement ran in VBA would attempt to execute and add the records to the subforms' respective tables. The SQL statement did not work properly, no records were inserted to appropriate tables.

Here's the code:

Dim fCheck As Boolean Dim iCheck As Boolean Dim oCheck As Boolean Private Sub frmIdentifiers_Enter() If fCheck = True And oCheck = True Then iCheck = True ElseIf oCheck = True Or fCheck = True Then iCheck = True Else DoCmd.GoToControl "frmFIdentifiers" DoCmd.GoToRecord , , acNewRec DoCmd.GoToControl "frmOther" DoCmd.GoToRecord , , acNewRec cpiCheck = True End If End Sub

The same code is ran for the other two forms, with If statements and booleans switched as needed.

Most anything can be done with enough code. Your question is too broad and lacks code to analyze, therefore is not appropriate for SO. — June7
– June7, Commented Oct 18, 2024 at 14:42
I'm really just looking for ideas. I'll append the code I've tried to my post, but just at a brick wall. I thought I was pretty specific, hopefully the code will clarify what I've tried. — Alex
– Alex, Commented Oct 18, 2024 at 15:00
Lot of unknowns about your situation. Have you set relationships and enforced relational integrity? If so, a new parent record must first be committed to table before related child records can be created. Writing records to table or setting values of controls should be simple. Not seeing any code in your post doing that. You mention an SQL statement yet don't show it. — June7
– June7, Commented Oct 18, 2024 at 15:07
Referential integrity is enforced and relationships are enabled. Parent records do get submitted to table before child record entry, but my concern is adding child records in the subforms that are left blank. I appended the code I'm attempting to use, unsuccessfully. — Alex
– Alex, Commented Oct 18, 2024 at 15:50
Why 3 "identifier" tables? If they have same structure, should be one table with a field for IdentifierType. What is this data? — June7
– June7, Commented Oct 19, 2024 at 14:57

mazoula · Accepted Answer · 2024-10-27 10:05:27Z

In short, add a button to the form and save the form's data to the table.
For instance, Start with a more normalized table structure like the many to many relationship with 1 type table in this example.

After setting up the relationships, please try creating a form for each table in the many to many relationship using the wizards on the ribbon. Play around with each resulting form and pay attention to how the resulting forms built with Access's forms-subforms approach look and work.
I expect you will find that these default forms for complicated relationships add another level of confusion on top of the obtuseness of the tables. Forms should translate from human to database and do as much work as possible. for many to many relationships I like to start with a more user-friendly form on top combined with a query on the bottom that shows the relevant changes in the database.

To be quick I started with a query to get the relevant database changes and then used the wizard to create a tabbed form from the query. Then I added a bunch of unbound controls to the form header for the human friendly top of the form and replaced most text boxes with combo-boxes.

SELECT Applicants.ApplicantID, Applicants.ApplicantName, Identifiers.IdentifierID, Identifiers.IdentifierDescription, IdentifierTypes.IdentifierTypeID, IdentifierTypes.Description, ApplicantsIdentifiers.isChecked FROM IdentifierTypes INNER JOIN (Identifiers INNER JOIN (Applicants INNER JOIN ApplicantsIdentifiers ON Applicants.ApplicantID = ApplicantsIdentifiers.ApplicantID) ON Identifiers.IdentifierID = ApplicantsIdentifiers.IdentifierID) ON IdentifierTypes.IdentifierTypeID = Identifiers.IdentifierTypeID;

'if you still wanted to select multiple identifiers at once 'only chkA shown unselects all as well Private Sub chkA_Click() 'Me.chkB = Me.chkA 'Me.chkC = Me.chkA End Sub Private Sub cmdAddRecord___Click() Dim db As Database Dim rs As Recordset Set db = CurrentDb Set rs = db.OpenRecordset("ApplicantsIdentifiers") 'obviously better to use a multi-select listbox and cycle through the selected items If Me.chkA = True Then rs.AddNew rs!ApplicantID = Me.cmbApplicantUnbound rs!IdentifierID = 1 rs!isChecked = True rs.Update End If If Me.chkB = True Then rs.AddNew rs!ApplicantID = Me.cmbApplicantUnbound rs!IdentifierID = 2 rs!isChecked = True rs.Update End If If Me.chkC = True Then rs.AddNew rs!ApplicantID = Me.cmbApplicantUnbound rs!IdentifierID = 3 rs!isChecked = True rs.Update End If 'clean up rs.Close Set rs = Nothing Set db = Nothing 'reset checkboxes Me.chkA = False Me.chkB = False Me.chkC = False Me.Requery 'show changes to data End Sub

---------------------------------------------------------------------------------- | IdentifierID | IdentifierDescription | IdentifierTypeID | ---------------------------------------------------------------------------------- | 1 | A | 1 | ---------------------------------------------------------------------------------- | 2 | B | 2 | ---------------------------------------------------------------------------------- | 3 | C | 3 | ---------------------------------------------------------------------------------- ------------------------------------------- | IdentifierTypeID | Description | ------------------------------------------- | 1 | Identifier | ------------------------------------------- | 2 | Fidentifier | ------------------------------------------- | 3 | Oidentifier | -------------------------------------------

Collectives™ on Stack Overflow

Microsoft Access - Creating sub form records on main form load/update

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related