Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2009
    Location
    Sacramento, CA
    Posts
    3

    Question 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?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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.
    Have a nice day!

  3. #3
    Join Date
    Dec 2009
    Location
    Sacramento, CA
    Posts
    3
    So how would you force create a new record? Do you have an example or piece of code you can share? Thanks!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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.
    Code:
    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))
            Me.Requery
            Set rst = Me.RecordsetClone
            rst.FindFirst "SysCounter = " & CStr(lngNewPK)
            Me.Bookmark = rst.Bookmark
            rst.Close
            Set rst = Nothing
        End If
            
    End Sub
    Attached Files Attached Files
    Have a nice day!

  5. #5
    Join Date
    Dec 2009
    Location
    Sacramento, CA
    Posts
    3
    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.

    I'll try it out. Thanks Sinndho!

  6. #6
    Join Date
    Oct 2009
    Posts
    340
    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....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •