Unbound check box on continuous subform for selecting records

[SurreyNick]

This is related to my thread "School database: How to use controls and a macro to simplify student exam registrations"

I need a method of selecting some records in a continuous subform that I want to do something with – in this instance copy those selected records to another table along with user input data from a couple of unbound controls.

The ideal solution would be a checkbox (used to denote selection) but I want it to be a temporary local user input choice and not a permanent data choice i.e. I really don’t want to create a yes/no field in a table. The trouble is when I add an unbound checkbox to my subform and then check it every record in the subform gets checked i.e. it’s an “all or nothing” option and that’s no good.

I have scoured the internet and on one of the forums there was a discussion dating back to 2008 about using a function to maintain either a collection or array of selected elements PK's (whatever that means) and use that to bind to a checkbox.

Apparently a guy called Albert Kallal came up with a solution, described as:

“Multi Select Example.”
This little example shows you how to select multiple items from a form, and then send it to report. The nice feature of this example is that it does not use a actual field in the database to accomplish this.
I have not yet written this one up...but the code involved is VERY simple.
Access2000: click here for the code

I have attached an image of his form and copied his code below.

What I’d really like to know is can this be adapted to my purpose? I’m using Access 2010.

I will want to copy the selected field records [Students]![StudentID] from the subform to my Tests table and append to each of those records the user input data from the two unbound controls on the main form called [TestDate] and [ExamPaperID].

Multi Select Example Form

Multi Select Example Code

Option Compare Database
Option Explicit

Dim colCheckBox As New Collection
----------------------------------------------------------------------

Public Function IsChecked(vID As Variant) As Boolean

Dim lngID As Long

IsChecked = False

On Error GoTo exit1

lngID = colCheckBox(CStr(vID))
If lngID <> 0 Then
IsChecked = True
End If

exit1:

End Function
----------------------------------------------------------------------

Private Sub Command13_Click()
Debug.Print "contact = " & Me.ContactID

If IsChecked(Me.ContactID) = False Then
colCheckBox.Add CLng(Me.ContactID), CStr(Me.ContactID)
Else
colCheckBox.Remove (CStr(Me.ContactID))
End If
Me.Check11.Requery

End Sub
----------------------------------------------------------------------

Private Sub Command14_Click()

MsgBox "records selected = " & MySelected, vbInformation, "Multi Select example"

End Sub
----------------------------------------------------------------------

Private Function MySelected() As String

Dim i As Integer

For i = 1 To colCheckBox.Count
If MySelected <> "" Then
MySelected = MySelected & ","
End If
MySelected = MySelected & colCheckBox(i)

Next i

End Function
----------------------------------------------------------------------

Private Sub Command16_Click()

Dim strWhere As String

strWhere = MySelected

If strWhere <> "" Then

strWhere = "contactID in (" & strWhere & ")"

End If

DoCmd.OpenReport "Contacts1", acViewPreview, , strWhere
DoCmd.RunCommand acCmdZoom100

End Sub
----------------------------------------------------------------------

Private Sub Command17_Click()

Set colCheckBox = Nothing
Me.Check11.Requery

End Sub

 

[theDBguy]

Hi. Using my phone right now, but I don't see why not. You probably just have to use the MySelected() function to identify your selected records for your code.
Sent from phone...

 

[Ranman256]

you cant use unbound check box on continuous form to select records, ALL checks will be the same.
youd have to use a bound checkbox (field: Marked), then you can select individuals.

 

[MajP]

You can but that is a half solution if you persist the selections. You need to save to the table. If you ever come back you need to populate the selections.
Here are some examples, but this is the same as a multiselect listbox.
1)On form current Read selections from table where data is stored
2) update the checks
3) On close of form or after update write and delete from table.

Couple of ideas

Return field name into array where field is true

I'm not the one who said the design was poor. Yes you did. I skimmed over that thread and this one without absorbing it all. Too much at this point. However, this stuck out The users don't have to select yes or no for everything, they just work through the list and tick the problems they note...

www.access-programmers.co.uk

Faking a Multi Value Field

I have seen a few posts lately on people wanting to use multi valued fields. Most Access developers steer away from them and build their own related tables. Although they are properly normalized and work well, they can get very confusing even if you know the ins and outs. I wanted to see if I...

www.access-programmers.co.uk

all techniques are basically the same for doing this. Truthfully, the easiest is really to add the "selected" boolean field to the table. You still have to do the same techniques though.

To be clear that technique shown is fine, but it is likely just incomplete if you need to show previously selected and then write/delete from a table.

 

[SurreyNick]

Thanks. I'll give both solutions a go.

 

[arnelgp]

the sample db does the same functionality only it highlight (in yellow) the selected records.
you can Select/De-Select record by clicking on the Record Selected (the Box to the left of "ID" column).

 

[Galaxiom]

Hard to be sure because I don't know your ultimate goal but I suspect you have gone down an unnecessarily complex path in pursuit of your implied goal.

Have a look at the sample database in post three of this thread to see it it or something like it will solve your real needs without any of the complexity that many developers assume is required. Many have failed to understand its elegant simplicity because it uses subforms in a different way to the usual. It employs the strengths of the engine and contains hardly any code. Don't give up too easily.

 

[CJ_London]

there is also this example

multi select form

Responding to a thread where I suggest using a multiselect listbox, I thought it would be useful to upload this simple example where you can turn a form into a multi select form. It only does a 'simple' version but with more work could probably also be extended to mimic the extended form. The...

www.access-programmers.co.uk

probably similar to ArnelGP's solution

 

[SurreyNick]

Thank you all for your help. Within this thread there is more than one suitable solution to my needs and all I need to do now is decide which one to adopt ! I am really pleased I joined this forum.

 

[GregFox]

the sample db does the same functionality only it highlight (in yellow) the selected records.
you can Select/De-Select record by clicking on the Record Selected (the Box to the left of "ID" column).

Arnelgp,

I have reviewed your sample database and like your solution but I cannot figure out how the highlighting is triggered. Please explain.

Thanks,
Greg

 

[theDBguy]

Arnelgp,

I have reviewed your sample database and like your solution but I cannot figure out how the highlighting is triggered. Please explain.

Thanks,
Greg

Hi Greg. This being your first post, welcome to AWF!

Since this is an old thread, I've tagged @arnelgp to make sure he sees your question.

 

[arnelgp]

I have reviewed your sample database and like your solution but I cannot figure out how the highlighting is triggered. Please explain.

view the code on the Click event of the form.
in design view of the form, you will noticed a "hidden" textbox (txtSelected).
whenever you Click on the Record Selector, the ID of that record is Added/Removed to txtSelected.
There is a Conditional Format that does the Highlighting of all textbox on the form.

 

[GregFox]

I copied the hidden textbox, buttons, and code into my form. The filter works but the conditional formatting does not highlight the record. How do i set the conditional formatting?

Thanks,
Greg

 

[Gasman]

https://www.youtube.com/results?search_query=conditional+formatting+access

 

[arnelgp]

to set the conditional format:
1. bring your form in design view.
2. select all your textbox/comboboxes.
3. on ribbon->Format->Conditional Format
4. Click "New Rule"
5. On, Format only cells the: , select Expression Is
6. input this expression on the Opposite textbox:
7. InStr(1,[txtSelected] & "",[ID] & ",")>0 And Not IsNull([ID])
(note on 7, that the hidden textbox here is txtSelected and the Primary Key is ID).

 

[June7]

A yes/no field in table to select records will cause conflict if db has multiple simultaneous users.

This is an old and common topic. Review https://stackoverflow.com/questions...bound-checkboxes-on-an-access-continuous-form

 

[GregFox]

Thanks to everyone for your responses. I'll let you know if I have any other questions.

Greg