Is there any way to select all or some row of records and then hit a command button that will effect the row of records the same way?

Question

I have a continuous form that contains a varying number of records. Typically it doesn't exceed 100 but it can. Below is the form showing a few row of records. I was wondering if its possible to select a few or all of the records and after selecting them, I click a command button that will run its code accordingly with each record selected.

]1

Answer 1

Ok, I'll take a stab at this!

First suggestion: Add a column to the table - but we scratch that, since you can't, and that's OFTEN the case.

2nd suggestion. Use a temp table - this can work, but then again, not too fond of this one, since then you need a temp table!
(but, not the end of the world - it can be done).

3rd suggestion. Add a check box, and use a custom function to DRIVE the data source of the check box.

We unfortantly can't use a un-bound check box (since then it would apply to all rows).

but , what we can do is create a custom function that "returns" if the row is selected or not.

We also have to code out keeping track of the selected ones. Now, this is NOT too hard.

so, here is how it works:

Your code is thus this (form level collection to keep track of checked boxes

this:

Option Compare Database  
Option Explicit  
  
Public CheckItems As New Collection  
  
Public Function MySel(vID As Variant) As Boolean  
  
   If IsNull(vID) Then  
      MySel = False  
      Exit Function  
   End If  
   ' check if id exists in colleciton.  
   On Error Resume Next  
   Dim mydummy As Long  
   mydummy = CheckItems(CStr(Me!ID))  
   If Err.Number = 0 Then  
      ' found it, return true!!  
      MySel = True  
      Exit Function  
   End If  
     
   MySel = False  
  
End Function  
  
  
Private Sub cmdCheck_Click()  
  
   ' check in our list  
   If MySel(Me!ID) Then  
      ' already in list - remove  
      CheckItems.Remove CStr(Me!ID)  
   Else  
      ' not in the list - add it  
      CheckItems.Add Me!ID.Value, CStr(Me!ID)  
   End If  
     
   Me.ckSel.Requery  
  
End Sub  
  

so, we have a collection, a function (mysel), and a button click event.

So we now drop BOTH a check box onto the grid and ALSO a transparent button on top of the check box!

It looks like this:

Make sure after you put that button on top, you set it to transparent, and also do this:

This just makes sure the transparent button is on top - so we can click on it.

Now the check box? We bind to our function above called MySel like this:

Also - do this BEFORE you drop the transparent button on top of the check box - else you have trouble getting to the property sheet for the check box.

And now the last part, the code/loop to get the list

It looks like this:

   Dim strWhere      As String  
   Dim v             As Variant  
     
   For Each v In CheckItems  
      If strWhere <> "" Then strWhere = strWhere & ","  
      strWhere = strWhere & v  
   Next  
     
   DoCmd.OpenReport "rptHotels", acViewPreview, , "ID IN (" & strWhere & ")"  
     
   ' or  
     
   Dim strSQL As String  
     
   strSQL = "SELECT * from tblHotels WHERE ID IN (" & strWhere & ")"  
     
   Dim rst     As DAO.Recordset  
   Set rst = CurrentDb.OpenRecordset(strSQL)  
     
   Do While rst.EOF = False  
     
       ' process reocrds  
         
       rst.MoveNext  
   Loop  
   rst.Close  
     

The great part? Works with any grid/display you have - SharePoint, SqlServer, linked tables, even to a linked Excel sheet!!!
(it just don't matter, since this is code driven - not by the data table).

The bad part? It does cause a "bit" of a flicker - but it not all that bad, the end result looks like this:

So it a wee bit of code, but not a lot. This is quite much the only way i know of doing this un-bound, but I have done and used this trick for years and years .

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Answer 2

Hi. Will you need to select non-contiguous rows of records?

Answer 3

Okay, as far as I know, Access continuous forms do not allow for non-contiguous multiple records selection. Therefore, my recommendation to you is to use a bound checkbox to offer the user a record selection utility.

Hope that helps...

Answer 4

Oh, I see this is crossposted here.

aea6ba8d-9d94-4d44-a08a-a923ebfd180f

In that case, my suggestion would be to use a temp table to allow for multiple records selection. Other than that, perhaps you can use an external collection object to keep track of the user selections.

Answer 5

If you select multiple records next to each other, you could use the SelTop and SelHeight properties of the Form to determine which records were selected.