Update column in subform

[paramesium]

Hi All,

I am working on a database project where I will Select a record on a subform

Please see the picture attached.



I know that using DAO recordset, i can update the checkbox one by one, but it become way too long when I have thousands of record...

May I know if there is a way to Select entire column of the subform?

Please advice, thank you!

[ItsMe]

If you know the criteria of the subform's filtered recordset, you can create an SQL UPDATE query

Does it take a long time to use a DAO recordset clone?

[paramesium]

Thank you for your reply, I actually did this, way too long...

With RstSearchSelected        
.MoveFirst
        Do While Not .EOF
         .Edit
             If !Selected <> False Then
             !Selected = False
             End If
         .Update
         .MoveNext
        Loop
    End With

I have used the following:

Forms!frmFindChecklist!frmFindChecklist_Sub.Form.FilterOn = true

the result in query no longer tally with what displayed in subform , and I am not sure how to use SQL on subform...

[ItsMe]

Can you post the code where you ...

Set RstSearchSelected = ?

[paramesium]

ok, this is the entire sub function

Private Sub cmdSelectAll_Click()


 'Parameters
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim RstSearchSelected As DAO.Recordset
    
   
    Set db = CurrentDb()
    Set RstSearchSelected = frmFindChecklist_Sub.Form.Recordset
    


    With RstSearchSelected


        Do While Not .EOF
         .Edit
             If !Selected <> True Then
             !Selected = True
             End If
         .Update
         .MoveNext
        Loop


    End With
    
End Sub

thank you

[ItsMe]

What is the name of the control that is bound to field "Selected"?

[paramesium]

The Selected field is actually a yes/no format of a table, which means I can update the each record of the "Selected" field directly, there are no other control that bound to it except the "cmdSelectAll" that I wanted code it to update the field for me when it is clicked.

[ItsMe]

Try this. I am not able to test it, but it should do the trick. I assume that the control name is "Selected"

Dim strFilterOld
Dim strFilter As String
If Forms!frmFindChecklist!frmFindChecklist_Sub.Form.FilterOn = True Then
    strFilterOld = Forms!frmFindChecklist!frmFindChecklist_Sub.Form.Filter
    strFilter = (strFilterOld & " AND ") & "[Selected] = -1"
    
Else
    
    strFilterOld = ""
    strFilter = "([Selected] = -1)"
    
End If

Forms!frmFindChecklist!frmFindChecklist_Sub.Form.FilterOn = False
Forms!frmFindChecklist!frmFindChecklist_Sub.Form.Filter = strFilter
Forms!frmFindChecklist!frmFindChecklist_Sub.Form.FilterOn = True

    'Parameters
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim RstSearchSelected As DAO.Recordset
    
   
    Set db = CurrentDb()
'    Set RstSearchSelected = frmFindChecklist_Sub.Form.Recordset
    Set RstSearchSelected = frmFindChecklist_Sub.Form.RecordsetClone
    
        'Allow subform to be edited
       
'    With Me![frmFindChecklist_Sub]
'        .Form.AllowEdits = True
'        .Form.AllowAdditions = True
'        .Form.AllowDeletions = True
'    End With
    
    With RstSearchSelected
        .MoveFirst
        
        !Selected = 0
        
'        Do While Not .EOF
'         .Edit
'             If !Selected <> False Then
'             !Selected = False
'             End If
         .Update
         .MoveNext
        Loop
    End With
    
If strFilterOld = "" Then
Forms!frmFindChecklist!frmFindChecklist_Sub.Form.FilterOn = False
Else
Forms!frmFindChecklist!frmFindChecklist_Sub.Form.FilterOn = False
Forms!frmFindChecklist!frmFindChecklist_Sub.Form.Filter = strFilterOld
Forms!frmFindChecklist!frmFindChecklist_Sub.Form.FilterOn = True

End If

[paramesium]

I have code it on my project, I have add one more .edit line, without that I couldnt compile, but when I added, it only clears the first record...

[ItsMe]

I have code it on my project, I have add one more .edit line, without that I couldnt compile, but when I added, it only clears the first record...

Are you trying to update the field to be True or False? Go ahead and post your revised code here.

[paramesium]

sorry that I didnt make myself clear, I am actually working on a search form,

the search form will activate the filter on subform

I want to make all the checkbox of the filtered data = true/false.

[ItsMe]

Believe it or not, I got the part where you are using a filtered subform. That is why I am trying to see if there is a way to make DAO work.

I understand you want to update a field on a table called "Selected". I assume there is a control on the subform named "Selected".

Do you want to update all of the records in the subform or just certain records within the filtered subform?

What value do you want to assign to the field in your table, the field named "Selected"?

[ItsMe]

This will update all of the records in the subform's filtered recordset. It will update the field "Selected" to True.

    Dim RstSearchSelected As DAO.Recordset
    Set RstSearchSelected = Forms!frmFindChecklist!frmFindChecklist_Sub.Form.RecordsetClone

    With RstSearchSelected
    
        .MoveFirst
        
        While Not .EOF
        .Edit
        !Selected = -1
         .Update
         .MoveNext
        Wend
        
    End With

[paramesium]

strange, it is far more faster now!!

I remember how I see those tick, ticking one by one in my last code ...

do you mind explaining how is it work?

Thank you so much!

[ItsMe]

I don't know. There were several things that did not add up in the code you presented to me. I just tried to understand what you were trying to do and then use the least amount of code to get it done.

One thing was to use the RecordsetClone. I do not remember seeing that in your original code.