Unanswered: Incrementing PK on a main form when subform accessed first
I am using MS Access 2007.
I have a main form with a subform. My main form has a primary key that is autonumbered. All required fields on the main form is filled out when the form loads except for the primary key field. From a user perspective, the first place they start entering data is the subform.
I've noticed that when this happens the primary key on the main form remains at (New). The data in the subform gets created and saved but then becomes orphaned. It has no tie to the main form since the primary key was never assigned the next number.
So my question is...how do I get the primary key to move to the next unique number on a main form when the subform is the first place that data is entered?
One way would be to force the creation of a new record at the main form level (more precisely at the recordset level of the main form or directly in the underlying dataset), then retrieve the primary key of this new record to synchronize the main form and its subform.
There are several possibilities. In this one, the code is in the Current Event of the main form. It tests to see if the main form points to a new record and, if so, it actually creates this record. SysCounter is the primary key of the table thats is the data source of the main form. See the attached database for more details.
Private Sub Form_Current()
Const strsql As String = "INSERT INTO Tbl_Test_1 (SysCounter) VALUES ( <PK> )"
Dim lngNewPK As Long
Dim rst As DAO.Recordset
If IsNull(Me!SysCounter) Then
lngNewPK = DMax("SysCounter", "Tbl_Test_1") + 1
CurrentDb.Execute Replace(strsql, "<PK>", CStr(lngNewPK))
Set rst = Me.RecordsetClone
rst.FindFirst "SysCounter = " & CStr(lngNewPK)
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Very nice. I took a quick glance at the sample. That just might work and to ensure that blank records are not entered everytime the New button is clicked, I can change the current record to dirty and force the user to save or not save.
of course the question itself inherently raises the question as to whether you have the Mainub relationship correctly established in the first place..... if you have data in the Sub for which there needs not be data in the Main...then perhaps their roles should be reversed....