question

LopezAhiezer-8335 avatar image
0 Votes"
LopezAhiezer-8335 asked AlbertKallal-4360 edited

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?

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.

![Continuous Form with Records][1]


office-vba-devoffice-access-dev
image.png (83.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlbertKallal-4360 avatar image
1 Vote"
AlbertKallal-4360 answered AlbertKallal-4360 edited

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:

138817-image.png

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

138805-image.png

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:


138853-image.png

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:

138834-image.png

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




image.png (7.4 KiB)
image.png (22.0 KiB)
image.png (8.6 KiB)
image.png (65.5 KiB)
· 30
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I am getting an error in the first line of the if statement. Also, the check box doesnt have a check when I click on it, where did I go wrong?

138887-image.png


138942-image.png


Command button
Command Button


Command Button properties
Command Button Properties

The checkbox properties.
CheckBox






0 Votes 0 ·
image.png (48.6 KiB)

ID in my case is the row PK id from the database. So, you have to replace ID with the PK row id you using for that database, or at the very least a column name that is unique to the database.

Remember, since the check box is bound to a function, then you can't actually check on that check box. What you clicking on is the button we placed on TOP of the check box - but we set that button to transparent. So you never actually clicking on the check box, you clicking on the button placed on top of the check box. So we using the button click event. To get this working, place that button beside the check box if you really want to see how this works. But, we actually placed a button on top of the checkbox. the click event we are using is thus the button.

Since the check box (named ckSel in this example is bound to that custom function, then you actually can't change its state by clicking on it.

So in my example:
MySel - the custom function we defined.

CheckItems - the custom collection we defined - NOTE VERY careful how this was defined at the start of the form's code module - that var is thus global to the form and each function.

ckSel - the name of the check box we dropped into that form.


cmdCheck - the name of the button we dropped on top of the check box. So the code that drives this is the cmdCheck_Click event - that is the button click event, not the check box event. we NEVER touch or use ANY events of the check box control - we can't even click on it since a button is placed on top.







0 Votes 0 ·

Okay, I think I understand now. Im just a little confused as to how your check boxes are showing a check when you say we never actually hit the check box since the command button is on top of it.

138830-image.png


0 Votes 0 ·
image.png (5.8 KiB)
Show more comments

Hi. It should be the same deal as with my sample earlier where I used ID. You changed it to Project, right? Try that again...

0 Votes 0 ·
thedbguy avatar image
0 Votes"
thedbguy answered LopezAhiezer-8335 commented

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

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Not quite sure what you mean by that.

0 Votes 0 ·

For example, let's say you have the following rows:

1
2
3
4
5

Will you be selecting rows:

1
3
5

Or, will it always be like:

1
2

or

1
2
3

or

2
3
4

Hope that makes sense...

0 Votes 0 ·

Okay, understood. I will be selecting rows like in 1, 3, 5. I want to the user to have the ability to choose whichever row and however many rows. Hope that answers your question.

0 Votes 0 ·
thedbguy avatar image
0 Votes"
thedbguy answered thedbguy commented

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...

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Do you mean unbound checkbox?

0 Votes 0 ·

No, I said "bound" checkbox.

0 Votes 0 ·

Assuming I understand what you mean. That would involve adding a Yes/No field to the table and then to a query. Unfortunately I cant do that because I have a second query involved that is a Totals query.

0 Votes 0 ·

Say I do contiguous multiple record selections, how would I got about that?

0 Votes 0 ·

See my reply at the bottom regarding SelTop and SelHeight.

0 Votes 0 ·
thedbguy avatar image
0 Votes"
thedbguy answered thedbguy commented

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.

· 20
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The external collection object sounds interesting. Could you explain what this is?

0 Votes 0 ·

The simplest experiment is this. Add an unbound Textbox on the Header section of your Form and keep it visible. Then, add a small button to the left of the Detail section, so that the user can click on this button for each record they want to select.

In the Click event of the button, you could try something like:

Me.UnboundTextboxName = Me.UnboundTextboxName & "," & Me.ID

Try it out by clicking several records and watch the Textbox.

0 Votes 0 ·

Okay, what is Me.ID though?

0 Votes 0 ·

I tried your small experiment. Since the button is unbound it is selecting all of them for each row. Instead of Me.ID I put the project number.


![138616-image.png


138665-image.png


0 Votes 0 ·
image.png (97.9 KiB)
image.png (11.8 KiB)
Show more comments
thedbguy avatar image
0 Votes"
thedbguy answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.