SetFocus to a control in a subform datasheet view where checkbox = True

Question

I form that contains a subform that lists multiple records as a datasheet. In the subform is a checkbox type column called "Select". (control name is chk_select).

This column is used to select which records they would like to open in a separate form. After checking which records they would like to open by checking the checkboxes, a button then opens a separate detail form to the records where the "Select" column = True.

My issues is that unless the focus is on a line in the datasheet where the checkbox is selected, Access can't open the detail form.

For example, I would check the box to select the record I wanted to open, then click on a different line where the checkbox is not selected, and try to open the detail form but Access doesn't seem to see that there are records selected. But if I move the cursor to any line where the checkbox is checked, then the detail form opens just fine for all the records where the checkbox is selected.

My current workaround is to display a message box to try and get the user to move the cursor to a line where the checkbox is selected. But I'd like to have this done automatically before opening the other form to avoid any confusion for the user.

So I need a way to use SetFocus to a subform control where the checkbox = True before opening the detail form.

Is there any way to do this or a workaround?

Here is what I have so far.

Thank you.

Private Sub btn_open_estimate2_Click()
If Forms![View Estimates]![TblEstimates subform]![chk_select] = True Then
    'Forms![View Estimates]![TblEstimates subform]![chk_select].SetFocus  ***Need something similar to Where [chk_select]=True
    DoCmd.OpenForm "Edit Estimate", , , "TblEstimates.select = True"
    DoCmd.Close acForm, "View Estimates", acSaveYes
Else
    MsgBox "Please select an Estimate to open, or " & _
    "move the cursor to a selected Estimate to be able to open it", VbMsgBoxStyle.vbExclamation, "No Estimate Selected"
End If
End Sub

This seems like a dangerous game to me. You're triggering opening a specific row of data based on a check box in a table (if I am getting this correctly). As such, you would theoretically need something to uncheck the box as well. Or the box would just remain checked, and then next time you did it it would still pull up the first row that meets that requirement. That coupled with the fact that it is all on a sub-form makes all of this extremely difficult to control. I'll admit, none of this helps all that much, but I personally think the entire process would benefit from being re-thought. — Jiggles32
It seems to be working pretty well so far. I've got a button that will uncheck all the checkboxes for the user. The checkbox idea seemed to make the most sense to me as there wasn't really any other way to be able to select specific records. It just seems to be an issue with Access in not being able to see that there are items that are checked unless the focus is on a line where the checkbox is checked. — skyliner33v

Gustav Gustav · Accepted Answer · 2018-05-18T06:39:06

Use the RecordsetClone:

Private Sub btn_open_estimate2_Click()

    Dim rs As DAO.Recordset

    Set rs = Me![TblEstimates subform].Form.RecordsetClone

    rs. FindFirst "[select] = True"
    If rs.NoMatch Then
        MsgBox "Please select an Estimate to open.", VbMsgBoxStyle.vbExclamation, "No Estimate Selected"
    Else
        DoCmd.OpenForm "Edit Estimate", , , "TblEstimates.select = True"
        DoCmd.Close acForm, Me.Name, acSaveYes
    End If
    rs.Close

End Sub

SetFocus to a control in a subform datasheet view where checkbox = True

2 Answers