Multi Select on Continuous Form - How to iterate through selected rows

[joel009]

I have a form where the data is showing in the detail section and I am able to select mutiple rows. Is there a way to iterate through the selected items? I've been looking at the properties and nothing seems to work. Probably simple but it's been a long day

Joel

 

[MajP]

Looks like I need to write a FAQ. See thread702-1668957

 

[joel009]

MajP - Are you saying there is no way to iterate through the mutiple selections on a continuous form unless you have another field to reference like the check box?
Seems strange that MS and Bill Gates would allow you to multiselect on a continuous form and then not allow you to identify or reference those that are selected but then again...

Joel

 

[MajP]

Are you saying there is no way to iterate through the mutiple selections on a continuous form unless you have another field to reference like the check box?

NO. I do not think I said anything like that. I provided example code to show you how to iterate a selection. Inside the iteration I did something. Do you not see the iteration?

if you pass a form object into this code as I showed, it iterates the selection. Read it again.

Public Sub SelectAll(frm as Access.form)
  Dim rs As DAO.Recordset
  Dim frm As Access.Form
  Dim recordStart As Integer
  Dim recordEnd As Integer
  Dim I As Integer
  
  Set frm = Forms(FormName)
  Set rs = frm.RecordsetClone
  recordStart = frm.SelTop - 1
  recordEnd = recordStart + frm.SelHeight - 1
  rs.MoveFirst
  For I = recordStart To recordEnd
    rs.AbsolutePosition = I
      ' Your code here
    rs.Edit
    rs.Fields(SelectField) = True
    rs.Update
  Next I
  frm.Requery
  frm.Recordset.AbsolutePosition = recordStart
End Sub

The big trick I showed was a way to be able to trigger a procedure without losing the selection. The only way I thought to do that was a shortcut menu because it I have a command button or other form event I lose the selection

 

[MajP]

BTW not only does Bill allow it, he provides a link

http://support.microsoft.com/kb/208502

Basically the same approach.

 

[joel009]

Thanks for the help.

"The only way I thought to do that was a shortcut menu because it I have a command button or other form event I lose the selection "

That is my problem also. What I am trying to do is delete the selected records and as soon as I click on another control (cmdDelete) I lose the record selections except for the first record.

You may have pointed me in the right direction though. Still playing with it.

Joel

 

[MajP]

Try this demo

Sub CreateSimpleShortcutMenu()
    Dim cmbShortcutMenu As Office.CommandBar
    'need reference Microsoft Office 1X.0 Object Library
    ' Create a shortcut menu named "SimpleShortcutMenu".
    Dim cmdBar As CommandBar
    Dim newcontrol As CommandBarControl
    For Each cmdBar In Application.CommandBars
      If cmdBar.Name = "SimpleShortcutMenu" Then
        Application.CommandBars(cmdBar.Name).Delete
        Exit For
      End If
    Next cmdBar
    
    Set cmbShortcutMenu = CommandBars.Add("SimpleShortcutMenu", _
                        msoBarPopup, False, False)
                        
    ' Add your custom selection
    cmbShortcutMenu.Controls.Add Type:=msoControlButton, Id:=605
    Set newcontrol = cmbShortcutMenu.Controls.Add(Type:=msoControlButton)
    newcontrol.Caption = "Iterate Selected"
    newcontrol.OnAction = "IterateSelected"
    Set cmbShortcutMenu = Nothing
End Sub

Public Sub IterateSelected()
  Dim rs As DAO.Recordset
  Dim frm As Access.Form
  Dim recordStart As Integer
  Dim recordEnd As Integer
  Dim I As Integer
  Set frm = Screen.ActiveForm
  Set rs = frm.RecordsetClone
  recordStart = frm.SelTop - 1
  recordEnd = recordStart + frm.SelHeight - 1
  rs.MoveFirst
  For I = recordStart To recordEnd
    rs.AbsolutePosition = I
    'put code here
      MsgBox rs.Fields(0).Value
  Next I
  frm.Requery
  frm.Recordset.AbsolutePosition = recordStart
End Sub

 

[joel009]

Yes, I found that link. I am having trouble with the mouse events.
I am using Access 2010 in 2007 mode, window xp SP2. The form name is frmPartUsages. The error I am getting is "Microsoft Access Cannot find the object 'SelRecord([Forms]![frmPartUsages],"Move")'. I also get an error using 'SelRecord([frmPartUsages],"Move")'.
The error occurs when I move my mouse over the button with the mouse event populated like in the refered MS link.


Any ideas?

Joel

 

[MajP]

It should simply be [Form] if it is on the main form
if it is the subform [subform control name].[form]

 

[joel009]

MajP - I am unable to get the Demo to work. I placed the "Sub CreateSimpleShortcutMenu()" in the form for testing and called it from the form load event and it does not look like anything happened. Perhaps I don't know where to look for the newly created button? I put "Public Sub IterateSelected()" in a module.

Meanwhile, my deadline is aproaching so they will just have to accept single row delete ability.

I would like to get this to work but do not have the time now but I will work on it this weekend. Thanks for the pointers.

Joel

 

[MajP]

It is a shortcut menu. Right click after selecting.

 

[MajP]

'Class Name: MultiselectIterator
'Author: MajP
'Purpose: Turns any form with a command button into a multiSelect Iterator
'
'Use: Paste this code in a class module called MultiSelectIterator

Option Compare Database
Option Explicit

Private mMouseDown As Boolean
Private mFirstRecord As Long
Private mLastRecord As Long
Private WithEvents mFrm As Access.Form
Private WithEvents mCtrl As Access.CommandButton
Private mRS As DAO.Recordset
Public Event Click()

Public Sub initialize(TheForm As Access.Form, TheClickControl As Access.CommandButton)
  Set mFrm = TheForm
  Set mRS = mFrm.RecordsetClone
  Set mCtrl = TheClickControl
  mCtrl.OnClick = "[Event Procedure]"
  mFrm.OnMouseDown = "[Event Procedure]"
  mFrm.OnMouseUp = "[Event Procedure]"
  mFrm.OnMouseMove = "[Event Procedure]"
End Sub
Public Property Get FirstRecord() As Long
  FirstRecord = mFirstRecord
End Property
Public Property Let FirstRecord(ByVal Value As Long)
  mFirstRecord = Value
End Property
Public Property Get LastRecord() As Long
  LastRecord = mLastRecord
End Property
Public Property Let LastRecord(ByVal Value As Long)
  mLastRecord = Value
End Property
Public Property Get Recordset() As DAO.Recordset
  Set Recordset = mRS
End Property
Private Property Set FormRecordset(ByVal Value As Recordset)
  mRS = Value
End Property
Private Sub mCtrl_Click()
  mFrm.SelTop = Me.FirstRecord + 1
  mFrm.SelHeight = Me.LastRecord + 1
  RaiseEvent Click
End Sub
Private Sub mFrm_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
  mMouseDown = True
End Sub
Private Sub mFrm_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
  If Not mMouseDown Then
    FirstRecord = mFrm.SelTop - 1
    LastRecord = mFrm.SelHeight - 1
  End If
End Sub
Private Sub mFrm_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
  mMouseDown = False
End Sub

To turn any form into a Multiselect Iterator

'Declare a variable as a MultiSelectIterator
Public WithEvents MSI As MultiSelectIterator

Private Sub Form_Load()
  'Initialize in the form event and pass the form and the command button
  Set MSI = New MultiSelectIterator
  MSI.initialize Me, Me.cmdClick
End Sub

Private Sub MSI_Click()
  Dim i As Integer
  For i = MSI.FirstRecord To MSI.LastRecord
    MSI.Recordset.AbsolutePosition = i
    'Place code here 
    'MsgBox MSI.Recordset.Fields(0) & " " & i
  Next i
End Sub

 

[MajP]

Oops.
I noticed a problem in the math. Replace these methods in the class module.

Private Sub mCtrl_Click()
  mFrm.SelTop = Me.FirstRecord + 1
  mFrm.SelHeight = (Me.LastRecord - Me.FirstRecord) + 1
  RaiseEvent Click
End Sub

Private Sub mFrm_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
  If Not mMouseDown Then
    FirstRecord = mFrm.SelTop - 1
    LastRecord = FirstRecord + mFrm.SelHeight - 1
  End If
End Sub