Access运行时错误3159：无效书签及FindFirst性能问题咨询

The core problem here is that bookmarks from a standalone OpenRecordset aren’t compatible with your form’s recordset. Here’s a breakdown of why the two methods behave differently:

• When you used Me.RecordsetClone, you were working with a direct copy of the exact recordset that powers frmPanelUnitEntryHeader. Bookmarks from this clone are interchangeable with the form’s bookmarks because they reference the same underlying recordset structure.
• But when you use CurrentDb.OpenRecordset(strSQL), you’re creating a brand new, independent recordset—even if it pulls from the same table. Access doesn’t recognize bookmarks from this separate recordset as valid for your form’s recordset, hence the runtime error 3159.

Fixing the Slow FindFirst Issue (The Better Solution) #

Your original FindFirst method was slow because there’s almost certainly no index on the JOB_NUMBER and RELEASE_NUMBER fields in your MAIN_SUB table. Without an index, Access has to scan every record in the table to find a match, which is why it takes over a minute.

Here’s how to fix that:

1. Open your MAIN_SUB table in Design View.
2. Create a composite index for the two fields:
   • Click the "Indexes" button (icon looks like a set of keys).
   • In the Indexes window, enter a name for the index (e.g., JobReleaseIndex).
   • In the first row under "Field Name", select JOB_NUMBER.
   • In the next row (same index name), select RELEASE_NUMBER.
   • Save the table.

Once you add this index, your original FindFirst code will run almost instantly—no need to switch to OpenRecordset at all.

Alternative: If You Still Want to Use OpenRecordset #

If you need to use a standalone recordset for some reason, you can’t rely on bookmarks to sync the form. Instead, use a filter to jump to the matching record:

Private Sub txtJOB_NUMBER_GotFocus()
    Dim strFilter As String
    ' Escape single quotes to avoid syntax errors (if your values can contain them)
    Dim safeJobNum As String, safeReleaseNum As String
    safeJobNum = Replace(gtxtCurrentJobNumber, "'", "''")
    safeReleaseNum = Replace(gtxtCurrentJobRelease, "'", "''")
    
    strFilter = "[JOB_NUMBER] = '" & safeJobNum & "' And [RELEASE_NUMBER] = '" & safeReleaseNum & "'"
    
    ' Check if the record exists
    If DCount("*", "MAIN_SUB", strFilter) = 0 Then
        DoCmd.GoToRecord , , acNewRec
    Else
        Me.Filter = strFilter
        Me.FilterOn = True
        ' Ensure we land on the matching record
        DoCmd.GoToRecord , , acFirst
    End If
End Sub

A Note on the GotFocus Event #

Running this logic every time the text box gets focus might lead to unexpected behavior—for example, if the user clicks into the text box to edit it, the code could jump to a different record or create a new one without their intent. Consider moving this logic to:

• A dedicated button click event (e.g., a "Load Job" button)
• The frmPackingSlipHeader’s Current event (so it triggers automatically when the user navigates to a new packing slip)
• A custom subroutine that you call explicitly when needed

Content of the question originates from Stack Exchange, asked by Tim