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?
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.
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.
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
Code:
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)
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.
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...
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).
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
Code:
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.
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:
Code:
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;
@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.
Code:
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.
Code:
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.
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
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
Code:
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.
