Access 2010 here. Designing a database for parts inspection and quality control.
I have a peculiar issue with checkboxes in subforms. The database structure is like this:
One Main table, that is the record source of the main form and includes general inspection information. Three child tables
that are in a one-to-one primary key relationship with the main table. Each child table is the record source of a unique
subform. Each child table/suform deals with a certain type of inspection.
The user selects an option box (three options for three types of inspections, AQL, REW, and STD) that makes visible one
subform, while hiding the other two. This option box selection also purges possible duplicate entries using "Db.Execute," and
finally, this option box sets the inspection type in the main table.
'Example code for an AQL selection, uneeded record deletion part
If Me.InspectionType = "AQL" Then
CurrentDb.Execute "DELETE * FROM REWDB WHERE REWID = " & Me.MAINID, dbFailOnError
CurrentDb.Execute "DELETE * FROM STDDB WHERE STDID = " & Me.MAINID, dbFailOnError
The idea behind the form layout/control is to be able to actively display and, if needed, modify all the old records
from the main form, so DataEntry is turned off to display the older records, and turned on for entering new records under
Form_Current. Form_Current also controls all the display for the older records to echo what is in the databases and
re-display what the user has previously entered. (The attached image may help decipher the functionality of the database.)
The problem comes if/when the user decides to modify old records, the only thing they cannot modify, without a "3201" error
"You cannot add or change a record because a related record is required in table "MainTblName"," is a checkbox in one of the
subforms that controls a field in that subform's child table through VBA. It's events are quite simple and operate on the
subform's table alone.
'The user has selected the Pass option for the AQL disposition
Private Sub CheckPASS_GotFocus()
Me.AQLDisposition = "PASS"
'The user has selected the Fail option for the AQL disposition
Private Sub CheckFAIL_GotFocus()
Me.AQLDisposition = "FAIL"
I'm not sure if the Option group actively deleting records in the unused subforms is causing issues with record
"alignment" or if the re-display of the check box doesn't "attach" itself back to the subform's database. This is one of the
last hurdles I need to overcome with this database, and it seems like it could be a simple fix If I could pinpoint the
I know this is quite complex and difficult to abstract well, hence why I am here, but are there any ideas or suggestions?