Vba – Access VBA how to select last added record

ms-accessvba

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?

Best Answer

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.

DoCmd.GoToRecord
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

Some examples of how to avoid select

Use Dim'd variables

Dim rng as Range

Set the variable to the required range. There are many ways to refer to a single-cell range:

Set rng = Range("A1")
Set rng = Cells(1, 1)
Set rng = Range("NamedRange")

Or a multi-cell range:

Set rng = Range("A1:B10")
Set rng = Range("A1", "B10")
Set rng = Range(Cells(1, 1), Cells(10, 2))
Set rng = Range("AnotherNamedRange")
Set rng = Range("A1").Resize(10, 2)

You can use the shortcut to the Evaluate method, but this is less efficient and should generally be avoided in production code.

Set rng = [A1]
Set rng = [A1:B10]

All the above examples refer to cells on the active sheet. Unless you specifically want to work only with the active sheet, it is better to Dim a Worksheet variable too:

Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Set rng = ws.Cells(1, 1)
With ws
    Set rng = .Range(.Cells(1, 1), .Cells(2, 10))
End With

If you do want to work with the ActiveSheet, for clarity it's best to be explicit. But take care, as some Worksheet methods change the active sheet.

Set rng = ActiveSheet.Range("A1")

Again, this refers to the active workbook. Unless you specifically want to work only with the ActiveWorkbook or ThisWorkbook, it is better to Dim a Workbook variable too.

Dim wb As Workbook
Set wb = Application.Workbooks("Book1")
Set rng = wb.Worksheets("Sheet1").Range("A1")

If you do want to work with the ActiveWorkbook, for clarity it's best to be explicit. But take care, as many WorkBook methods change the active book.

Set rng = ActiveWorkbook.Worksheets("Sheet1").Range("A1")

You can also use the ThisWorkbook object to refer to the book containing the running code.

Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1")

A common (bad) piece of code is to open a book, get some data then close again

This is bad:

Sub foo()
    Dim v as Variant
    Workbooks("Book1.xlsx").Sheets(1).Range("A1").Clear
    Workbooks.Open("C:\Path\To\SomeClosedBook.xlsx")
    v = ActiveWorkbook.Sheets(1).Range("A1").Value
    Workbooks("SomeAlreadyOpenBook.xlsx").Activate
    ActiveWorkbook.Sheets("SomeSheet").Range("A1").Value = v
    Workbooks(2).Activate
    ActiveWorkbook.Close()
End Sub

And it would be better like:

Sub foo()
    Dim v as Variant
    Dim wb1 as Workbook
    Dim  wb2 as Workbook
    Set wb1 = Workbooks("SomeAlreadyOpenBook.xlsx")
    Set wb2 = Workbooks.Open("C:\Path\To\SomeClosedBook.xlsx")
    v = wb2.Sheets("SomeSheet").Range("A1").Value
    wb1.Sheets("SomeOtherSheet").Range("A1").Value = v
    wb2.Close()
End Sub

Pass ranges to your Subs and Functions as Range variables:

Sub ClearRange(r as Range)
    r.ClearContents
    '....
End Sub

Sub MyMacro()
    Dim rng as Range
    Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:B10")
    ClearRange rng
End Sub

You should also apply Methods (such as Find and Copy) to variables:

Dim rng1 As Range
Dim rng2 As Range
Set rng1 = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10")
Set rng2 = ThisWorkbook.Worksheets("SomeSheet").Range("B1:B10")
rng1.Copy rng2

If you are looping over a range of cells it is often better (faster) to copy the range values to a variant array first and loop over that:

Dim dat As Variant
Dim rng As Range
Dim i As Long

Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10000")
dat = rng.Value  ' dat is now array (1 to 10000, 1 to 1)
for i = LBound(dat, 1) to UBound(dat, 1)
    dat(i,1) = dat(i, 1) * 10 ' Or whatever operation you need to perform
next
rng.Value = dat ' put new values back on sheet

This is a small taster for what's possible.

Best Answer

Related Solutions

Vba – How to add new record to subform

Excel – How to avoid using Select in Excel VBA

Some examples of how to avoid select

Related Topic