How do I access the selected rows in Access?

by

Here is the code to do it, but there is a catch. 

Private Sub Command1_Click()
     Dim i As Long
     Dim RS As Recordset
     Dim F As Form

     Set F = Me.sf.Form
     Set RS = F.RecordsetClone

     If F.SelHeight = 0 Then Exit Sub

     ' Move to the first selected record.
     RS.Move F.SelTop - 1

     For i = 1 To F.SelHeight
       MsgBox RS![myfield]
       RS.MoveNext
     Next i

End Sub

Here’s the catch:
If the code is added to a button, as soon as the user clicks that button, the selection is lost in the grid (selheight will be zero). So you need to capture that info and save it to a module level variable either with a timer or other events on the form.

Here is an article describing how to work around the catch in some detail.
http://www.mvps.org/access/forms/frm0033.htm

Catch 2: This only works with contiguous selections. They can’t select mutliple non-sequential rows in the grid.

Update:
There might be a better event to trap this, but here is a working implementation using the form.timerinterval property that i have tested (at least in Access 2k3, but 2k7 should work just fine)

This code goes in the SUBFORM, use the property to get the selheight value in the master form.

Public m_save_selheight As Integer

Public Property Get save_selheight() As Integer
    save_selheight = m_save_selheight
End Property

Private Sub Form_Open(Cancel As Integer)
    Me.TimerInterval = 500
End Sub

Private Sub Form_Timer()
    m_save_selheight = Me.selheight
End Sub

More Related Contents:

  1. How does one decompile and recompile a database application?
  2. What are the rules governing usage of parenthesis in VBA function calls?
  3. Autonumber value of last inserted row – MS Access / VBA
  4. Code to loop through all records in MS Access
  5. Bang Notation and Dot Notation in VBA and MS-Access
  6. How do I force VBA/Access to require variables to be defined?
  7. VBA: Difference in two ways of declaring a new object? (Trying to understand why my solution works)
  8. How to diagnose MS access crashes
  9. Equivalent cURL in VBA?
  10. Run-time error ‘3061’. Too few parameters. Expected 1. (Access 2007)
  11. Prevent enabling of shift bypass – Access 2010
  12. VBA procedure to import csv file into access
  13. Transform multiple rows into columns with unique key
  14. how to show query results in a datasheet in the same form?
  15. Access VBA: Find max number in column and add 1
  16. MS Access prepared statements
  17. Create sequential ID value based on the year that a record is added
  18. UrlDownloadToFile in Access 2010 – Sub or Function not Defined
  19. Null values for variables in VBA
  20. Using a centralized VBA module in multiple Access databases
  21. How to execute a query in ms-access in VBA code?
  22. How to add a menu item to the default right click context menu
  23. VBA – Run Time Error 3271 using DAO object
  24. Trouble with InputBoxes
  25. Get Name of Current VBA Function
  26. Show “Open File” Dialog
  27. Access query producing results like ROW_NUMBER() in T-SQL
  28. Calling stored procedure using VBA
  29. Copy a selected range to another worksheet
  30. ODBC Call Failed with stored procedure – Pass through query

Leave a Comment