Manipulating an independent check box field in a form (1 Viewer)

[pacctono]

Hello!

I have a form that its details is fill with fields from a query. I would like to have an independent check box field for each detail that I would like to change. I create the field in the form but when I change it on one record it chages all the records. I am thinkig to create a temporal table and aggregate a boolean field to use as the checkbox field. But I would like to use a more direct method. Is it possible to isolate each independent filed and associate it with a record?

Thanks for your help

 

[theDBguy]

It's possible in a continuous form using some code. I am not sure if it's possible if you're using datasheet view. I know it's not possible in a query grid.

 

[Galaxiom]

Create a table with one Boolean field and one record.
Make a query with a Cartesian Join between your data table and that table.

Create an ADODB recordset based on that query with the following properties..
CursorType = adOpenKeyset
CursorLocation = adUseClient
LockType = adLockBatchOptimistic

Disconnect the recordset by making its ActiveConnection Property equal to Nothing.
Set the Recordset Property of the form to the disconnected recordset.

Bind the form's checkbox control to the Boolean field
The checkboxes on each record will now behave independently with the information stored in the Boolean field in the recordset.

To get data on the form back to a table, loop through a Clone of its Recordset and pass the values to the parameters of an Update or Insert query. Apply a Filter to the recordset clone if you just want to process records that have the checkbox ticked.

 

[MajP]

There are several methods
1. Add the boolean field to the table itself
2. Create a temp table
3. Make a disconnected recordset
4. Somewhere store the selections. This can be a table, string, collection, etc. Then use a function to set the value of the checkbox based on if the value is stored

I created a checklist class so I can create anywhere a checklist like this with a couple lines of code.
0. Add the class to your project
1. Need to add an unbound checkbox to the form
2. Set the checkbox's control source to the function
=IsSelected([PrimaryKeyFieldName])
where PrimaryKeyFieldName is the actual name of the PK field
3. In code add the following

Dim PersonnelSelector As New Record_Selector

Private Sub Form_Load()
  Me.ID.SetFocus
  PersonnelSelector.Initialize Me.chkSelected, "ID", "ID"
End Sub

Public Function IsSelected(ID As Long) As Boolean
  IsSelected = PersonnelSelector.InSelection(ID)
End Function

Where in this case ID is the primary key field and also a field where I can set the focus. (I have to move focus after clicking a checkbox)

 

[Galaxiom]

I created a checklist class so I can create anywhere a checklist like this with a couple lines of code.

That is a brilliant, sophisticated piece of work but I hope you don't mind me making a couple of suggestions for enhancements.

On my system all the checkboxes go though briefly showing a dot as they update.
To avoid this glitch I would suggest disabling Application.Echo at the beginning of m_Checkboc_GotFocus and reenabling it at the end (structuring it to reenable in case of an error of course.)

I can see the IDs can be exported as a semicolon delimited string using the ToString Method.

A nice touch would be to specify the separator via an optional parameter. I would default it to a comma. Exported as a comma separated string it could be used directly with IN() when building a dynamic query to write data to a table. Alternatively it could be used as a Parameter Array.

Another idea would be to determine the datatype of the associated ID and have the option to quote the IDs if they were strings.

 

[CJ_London]

there is also the method I demonstrate here

A multi select form using unbound check boxes

Thought I would put this up since it is a fairly common topic and I have put it up elsewhere - selecting records from a list for onward processing without the need for a table based tickbox. Similar in concept to the conditional formatting version I provided here...

www.access-programmers.co.uk

 

[arnelgp]

I am thinkig to create a temporal table and aggregate a boolean

you can have a Temp table (zTmp) with just 2 fields (the Account No (the first column on your image)and Yes/No field).
create a query that will join YourTable with Temp table (see Query1).
create a Datasheet form from the Query (datasheetForm).
on the Open event of the form, erase all records from Temp table.
then insert the Account No into the Temp table (see Open event of the form).
and on the Load event, requery your form (see Load event).

 

[KitaYama]

There are several methods
1. Add the boolean field to the table itself
2. Create a temp table
3. Make a disconnected recordset
4. Somewhere store the selections. This can be a table, string, collection, etc. Then use a function to set the value of the checkbox based on if the value is stored

I created a checklist class so I can create anywhere a checklist like this with a couple lines of code.
0. Add the class to your project
1. Need to add an unbound checkbox to the form
2. Set the checkbox's control source to the function
=IsSelected([PrimaryKeyFieldName])
where PrimaryKeyFieldName is the actual name of the PK field
3. In code add the following

Dim PersonnelSelector As New Record_Selector

Private Sub Form_Load()
  Me.ID.SetFocus
  PersonnelSelector.Initialize Me.chkSelected, "ID", "ID"
End Sub

Public Function IsSelected(ID As Long) As Boolean
  IsSelected = PersonnelSelector.InSelection(ID)
End Function

Where in this case ID is the primary key field and also a field where I can set the focus. (I have to move focus after clicking a checkbox)

@MajP I saw something like (not exactly the same) as your version several years ago. But I couldn't use it. Your version has the same effect.
Copy and paste the data in your table to be more than 200 records.
Open the form and scroll with mouse middle button.

Then I found @CJ_London 's method by accident . It's very stable and doesn't cause screen flickering even when the form shows a large set of data.

PS: I just noticed something else in MajP's version. If I click the scroll bar and don't release the mouse button, all checkboxes are ticked until I release the button.

 

[pacctono]

Wow! That is a lot of suggestions. Thanks you boys.
I am going to try some. But I did not say the resordsource of the for is not a table is a aggregate query:

SELECT n.CONCEPTO, co.tx_descripcion, Sum(n.VALOR_FIJO) AS monFijo, Sum(n.VALOR_VARIABLE) AS monVariable, Sum(n.VALOR_FIJO+n.VALOR_VARIABLE) AS monTotal
FROM nomina AS n LEFT JOIN ap_conceptos AS co ON n.CONCEPTO = co.tx_clave
GROUP BY n.CONCEPTO, co.tx_descripcion
HAVING Sum(n.VALOR_FIJO+n.VALOR_VARIABLE > 0;

 

[arnelgp]

same thing you can create a Temp table (zTmp)
and use zTmp in your form (dsForm, datasheet).
see the Open and Load event of the form.

 

[MajP]

@Galaxiom
You may find this version better and more interesting. I added your suggestions to the original class, but could not get rid of the flicker. This is strange because it is really the same technique as @CJ_London but implemented differently. So I wanted to see if I could write generic code to take a recordsource and build an ADO in memory recordset. What you suggested is non-trivial for most Access users. Doing this was easier than I thought. So the main part of the code is appending the fields needed to include the checkbox field. Basically get the dao field type and use a generic ADO field type.

Public Sub CreateInMemoryRecordset(Domain As String)
  Dim rsFill As DAO.Recordset
  Dim strSql As String
  Dim fld As DAO.Field

With InMemoryRS
   .CursorType = adOpenKeyset
   .CursorLocation = adUseClient
   .LockType = adLockPessimistic
    '.Open
End With
Set rsFill = CurrentDb.OpenRecordset(Domain)
For Each fld In rsFill.Fields
   Append_ADO_Field fld
   'Debug.Print "append " & fld.Name
Next fld

InMemoryRS.Open
Set rsFill = CurrentDb.OpenRecordset(Domain)
Do While Not rsFill.EOF
   InMemoryRS.AddNew
     For Each fld In rsFill.Fields
       InMemoryRS.Fields(fld.Name) = rsFill.Fields(fld.Name)
       Next fld
   InMemoryRS.Update
   rsFill.MoveNext
Loop
InMemoryRS.MoveFirst

End Sub

Public Sub Append_ADO_Field(DAO_FLD As DAO.Field)
  Dim Is_PK As Boolean
  Dim is_FK As Boolean
  Dim FieldType As Long
  Dim tdf As TableDef
  Dim sourcetable As String
  FieldType = DAO_FLD.Type
  sourcetable = DAO_FLD.sourcetable
   
  Select Case FieldType
      Case dbBoolean
      Me.InMemoryRS.Fields.Append DAO_FLD.Name, adBoolean
    Case 16, 9, 2, 102, 103, 104, 15, 3, 4, 17 'Store all as a long
       Me.InMemoryRS.Fields.Append DAO_FLD.Name, adBigInt, , adFldMayBeNull
    Case 105, 106, 108, 5, 20, 7, 21, 19, 6 ' store as double
       Me.InMemoryRS.Fields.Append DAO_FLD.Name, adDouble
    Case 18, 109, 10 ' store as text
      Me.InMemoryRS.Fields.Append DAO_FLD.Name, adVarChar, 255, adFldMayBeNull
    Case dbMemo
         Me.InMemoryRS.Fields.Append DAO_FLD.Name, adLongVarWChar
    Case 8, 22, 23 'Store as datetime
         Me.InMemoryRS.Fields.Append DAO_FLD.Name, adDate, , adFldMayBeNull
    Case Else
  End Select
End Sub

Again the value of this approach it requires the user to do very little. They do not have to add controls to forms, add tables, or functions. The entire code to use is.

Dim ADOC As New ADO_Checklist
Private Sub Form_Load()
  ADOC.Initialize Me, "dtaNames", Me.chkSelected.Name
End Sub

This also opens up other possibilities for editing "non-editable" recordset besides having a checkbox.

 

[CJ_London]

This also opens up other possibilities for editing "non-editable" recordset besides having a checkbox

Certainly does - I use disconnected recordsets all the time. Also useful for things like an alternative to a multi value combo or listbox, displaying the equivalent of a value list etc

 

[MajP]

After playing with this some more, I am convinced that if you do not want to mess with your data table then the best approach is what @arnelgp describes in thread 7. This is easy to implement, persists the selections, easy to reuse the selections in other queries/reports/forms, fast. No functions, controls, have to be designed.

I added a class to make doing this a little easier.
1. Build the temp table with the two fields. If using my class the two fields are called
PrimaryKey 'either Numeric or text depending on what type of keys you have in the data table. This stores your keys from the data table.
IsSelected 'boolean
2. Left join your data to this temp table by the primary key to the field called "primaryKey"

If you do that then you can use the class to handle the updates to the temp table, (loading and unloading the keys)

To use your form

Private RecSecTT As New Record_Selector_TempTable
Private Sub Form_Load()
  RecSecTT.Initialize Me.RecordSource, "ID"
End Sub

You pass in the recordsource for your form, and the name of the Primary Key you store in the selection table.

This demo has the four main techniques I can think of

1. ADODB in memory recordset
Pro: No temp table
Con: Requires reference to ADODB, Slower, Somewhat complicated so may not handle all cases, Results not persisted easily

2. Saving the selections in a control, table, or other structure and setting the checkbox to a function that searches to see if the value is saved
Pro: No temp table
Con: Somewhat complicated may not handle all cases, Results not persisted easily. Can be slow

3. Using a temp table
Pro: Very easy to program should handle all cases, fast, easy to persist selections
Con: Requires temp table but flexible when you cannot modify data table

4. Adding the selection field directly in the table
Pro: Easiest to program should handle all cases, fast, easy to persist selections
Con: Requires a field in data table which you may not be able to add

The easiest and fastest solution is to simply add the selection field to the table if you can. The overhead is negligible and has all the benefits. Each has benefits and drawbacks. The temp table is similar but more flexible.