Access VBA how to select last added record?

1
votes

I have Access form with a subform. The subform has a datasheet. The main form has drop down box and button which adds records to the subform. This all works fine (VBA code). Now I want to highlight the most recently inserted record as if I clicked on the row header.

How should I go about doing this? The main form's combobox should still be set to the proper record at tpoint, so how do I highlight the subform record?

1
ms-accessvba

1 Answers

2
votes

Make your target record the subform's current record. Then use RunCommand to "select" it ... which will also highlight the record.

DoCmd.RunCommand acCmdSelectRecord

Seems like your question is morphing into "how do I make the last added record the current record".

If the last added record is still the current record, then you're there already, so no problem.

If the user navigated to a different record after adding the last one, there are at least 2 ways to get back to it.

  1. DoCmd.GoToRecord
  2. Use a find method on the recordset clone.

Choose the approach which best fits your situation. If the bound value of your combo box is the numeric primary key for the row last added, you can try that second suggestion like this by replacing pkey_field, YourComboNameHere, and SubformControl with the actual names you're using.

Private Sub FindLastRecordAdded()
    Dim rst As DAO.Recordset
    Dim strCriteria As String

    strCriteria = "[pkey_field] =" & Me.YourComboNameHere

    Set rst = Me.SubformControl.Form.RecordsetClone
    rst.FindFirst strCriteria
    If rst.NoMatch Then
        MsgBox "Oops. This shouldn't happen.", vbInformation
    Else
        Me.SubformControl.Form.Bookmark = rst.Bookmark
    End If

    Set rst = Nothing
End Sub