Form event procedure not moving to last record-CodePudding

I am experiencing some odd behavior from a form I am creating in an access database. I have a form called frmSeedling with a subform called frmSeedling detail. frmSeedling gets launched by an event procedure from a command button on a separate form called frmTransect. The OpenArgs passes the primary key of frmTransect called Transect_OID to frmSeedling. Transect_OID is the link field between frmSeedling and frmSeedlingDetail. I have a Form_Current event procedure in frmSeedling to count the number of unique entries on frmSeedling, and create a custom unique id called Seedling_OID. The tables that are the record source for these forms are linked ODBC tables. Below is the code:

Private Sub Form_Current()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
If Not (rs.EOF) Then
rs.MoveLast
End If
Set rs = Nothing
If IsNull(Seedling_OID) Then
Entry_No = Nz(DCount("Seedling_OID", "rd_Seedling", "Transect_OID = '" & Me.Transect_OID & "'"), 0)   1
Seedling_OID = Transect_OID & "SD" & Entry_No
End If
End Sub

However, when I launch frmSeedling from frmTransect, I get an error saying I am trying to overwrite the primary key. When I look at the form, this is happening because somehow Access is try to create a new record at the beginning of the form instead of the end of the form, thus thinking that the unique Id I have: created has already been used. Here is a screenshot to show what I mean:

What is curious is that with a separate form called frmDWD with subform frmDWDdetail, I have used this exact setup and it worked fine. Here is the code from frmDWDdetail:

Private Sub Form_Current()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
If Not (rs.EOF) Then
rs.MoveLast
End If
Set rs = Nothing
If IsNull(DWD_OID) Then
DWD_Piece = Nz(DCount("DWD_OID", "rd_DWD", "Transect_OID = '" & Me.Transect_OID & "'"), 0)   1
DWD_OID = Transect_OID & "W" & DWD_Piece
End If
End Sub

And here is what that looks like:

Notice how in frmDWD, the record being edited is the last record in the form, while in frmSeedling it is trying to edit the first record in the form. I have set all of the data properties exactly the same in both form and subform, and at least to my eye the code looks identical. Any SQL reasons why I am getting this behavior? Any ideas for fixes? Thanks!!

CodePudding user response：

After hours of playing "Which-of-these-things-is-not-like-the-other" I discovered that the issue was in the property sheet of the subform. I switched "Filter on Empty Master" from "Yes" to "No" and that solved my issue.

CodePudding user response：

You are not using the recordset for anything, so try this reduced code:

Private Sub Form_Current()

    Dim Entry_No As Long

    If IsNull(Me!Seedling_OID.Value) Then
        Entry_No = Nz(DCount("*", "rd_Seedling", "Transect_OID = '" & Me!Transect_OID.Value & "'"), 0)   1
        Me!Seedling_OID.Value = Me!Transect_OID.Value & "SD" & Entry_No
    End If

End Sub