Loop through selected records on a form (1 Viewer)

raystownlaura

Registered User.
Local time
Yesterday, 20:42
Joined
Apr 16, 2010
Messages
22
I swear I just posted this but it is not showing up.

I'm hoping someone can refresh my memory on how to extract data from records selected on a form. For example, if a user selects 4 records on a form, what is the vba to loop through those records and store data in specific fields in variables?

Any help appreciated!!!
 

Steve R.

Retired
Local time
Yesterday, 20:42
Joined
Jul 5, 2006
Messages
4,705
Depending on how your are "set-up" and what you want to do, there are two options. One is to create a DAO record set. Examples of DAO and ADO. This can give you automated looping.

An other way is to create a filter which will restrict your form to displaying the selected records, which would be functionally equivalent to looping. Of course moving to each record would require a manual mouse "click".
 

raystownlaura

Registered User.
Local time
Yesterday, 20:42
Joined
Apr 16, 2010
Messages
22
Thanks... I'm familiar with looping through dao recordsets. I think where I'm stumped is much more simple. How do I identify just the records that the user has selected and create a recordset out of those records? Is there some property or method that identifies selected records on a form?
 

LPurvis

AWF VIP
Local time
Today, 01:42
Joined
Jun 16, 2008
Messages
1,269
I'm sure you'll be able to twist this into your own needs.

Code:
Function fLoadRstWithSelected(pForm As Access.Form, strPKName As String) As DAO.Recordset
 
    Dim intBottom As Integer
    Dim intTop As Integer
    Dim intI As Integer
    Dim strFilter As String
    Dim rst As DAO.Recordset
 
    With pForm
        intTop = .SelTop
        intBottom = .SelHeight + .SelTop - 1
    End With
 
    Set rst = pForm.RecordsetClone
    With rst
        If intTop > intBottom Then
            strFilter = "1=0"
        Else
            For intI = intTop To intBottom
                .AbsolutePosition = intI - 1
                strFilter = strFilter & "," & .Fields(strPKName).Value
            Next
            strFilter = strPKName & " In (" & Mid(strFilter, 2) & ")"
        End If
        .Filter = strFilter
        Set fLoadRstWithSelected = .OpenRecordset
    End With
    Set rst = Nothing
 
End Function

Assuming that you're talking about selection as in a continuous form or datasheet using the record selectors over contiguous rows.

Cheers.
 

Jiri

New member
Local time
Today, 02:42
Joined
Jul 26, 2015
Messages
1
Hi,

Your code is working if neither filter nor sorting is apply to the grid. In this case you must apply other code.

Regs

Jiri
 

Users who are viewing this thread

Top Bottom