Access: select multiple records in a form, process list in VBA?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to use the record selectors on a form to select multiple records
and then process just those records in VBA. Is it possible in VBA to
identify which group of records in a form have been selected?
Thanks!
 
Hi Paul

The record selectors are very limited in their usefulness. For example, you
cannot select records that are not contiguous.

I prefer to use a checkbox and a collection. Here are some ideas to get you
started:

First, add a checkbox to your form named chkSelected, with the following
properties:
Enabled: No
Locked: Yes
ControlSource: =RecordSelected([name of PK field])

Now add a command button over the top of the checkbox named cmdToggleSelect,
and set its Transparent property to Yes.

Next, add the following code to your form module:

To the declarations section:

Dim colSelected As New Collection

And to the procedures section:

Private Sub cmdToggleSelect_Click()
Dim iIndex As Integer
iIndex = RecordSelected([name of PK field])
If iIndex = 0 Then
colSelected.Add "" & [name of PK field]
Else
colSelected.Remove iIndex
End If
Me.Recalc
End Sub

Private Function RecordSelected(PK) As Integer
Dim i As Integer, Key As String
Key = "" & PK
For i = 1 To colSelected.Count
If colSelected(i) = Key Then
RecordSelected = i
Exit Function
End If
Next
RecordSelected = 0
End Function

Finally, in your "do something with these records" procedure, build a filter
string and use it. For example, to print a report:

Private Sub cmdPrint_Click()
Dim sFilter As String, i As Integer
If colSelected.Count = 0 Then
MsgBox "No records selected"
Exit Sub
End If
For i = 1 To colSelected.Count
sFilter = sFilter & colSelected(i) & ","
' if PK is a string, comment line above
' and uncomment next line
' sFilter = sFilter & "'" & colSelected(i) & "',"
Next
' remove final comma
sFilter = Left(sFilter, Len(sFilter) - 1)
sFilter = "[name of PK field] in (" & sFilter & ")"
DoCmd.OpenReport "report name", WhereCondition:=sFilter
End Sub
 
Hi everyone, I have some problems with the procedure written by Graham Mandeno, I am not able to print the report with the selected records only.
Can anyone help me?

P.S. Sorry for my english, but I'm Italian


Graham Mandeno said:
Hi Paul

The record selectors are very limited in their usefulness. For example, you
cannot select records that are not contiguous.

I prefer to use a checkbox and a collection. Here are some ideas to get you
started:

First, add a checkbox to your form named chkSelected, with the following
properties:
Enabled: No
Locked: Yes
ControlSource: =RecordSelected([name of PK field])

Now add a command button over the top of the checkbox named cmdToggleSelect,
and set its Transparent property to Yes.

Next, add the following code to your form module:

To the declarations section:

Dim colSelected As New Collection

And to the procedures section:

Private Sub cmdToggleSelect_Click()
Dim iIndex As Integer
iIndex = RecordSelected([name of PK field])
If iIndex = 0 Then
colSelected.Add "" & [name of PK field]
Else
colSelected.Remove iIndex
End If
Me.Recalc
End Sub

Private Function RecordSelected(PK) As Integer
Dim i As Integer, Key As String
Key = "" & PK
For i = 1 To colSelected.Count
If colSelected(i) = Key Then
RecordSelected = i
Exit Function
End If
Next
RecordSelected = 0
End Function

Finally, in your "do something with these records" procedure, build a filter
string and use it. For example, to print a report:

Private Sub cmdPrint_Click()
Dim sFilter As String, i As Integer
If colSelected.Count = 0 Then
MsgBox "No records selected"
Exit Sub
End If
For i = 1 To colSelected.Count
sFilter = sFilter & colSelected(i) & ","
' if PK is a string, comment line above
' and uncomment next line
' sFilter = sFilter & "'" & colSelected(i) & "',"
Next
' remove final comma
sFilter = Left(sFilter, Len(sFilter) - 1)
sFilter = "[name of PK field] in (" & sFilter & ")"
DoCmd.OpenReport "report name", WhereCondition:=sFilter
End Sub

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand



"Paul Sator"
Click to expand...
 
Back
Top