Home>

In Access, I am creating a form to modify the database record as shown in the image below.

【Thing I want to do】
1. 1.
Enter keywords on the upper left side of the form
⇒ Press the search button
⇒ In the data sheet at the bottom of the form, make sure that only records containing keywords are displayed in the specified field.

I wrote the search button code as below.
Result is,
In the data sheet, the value of the search text box is registered in the label name field of the search text box.
After that, a window for entering parameters will appear.
How would you modify the code to do what you really wanted to do?

Code

Private Sub btn_fix_2_Click ()
    Me.Filter = "Use_Place like'*"&Use_Place_key&"*'"
    Me.FilterOn = True
    Me.Filter = "Class_1 like'*"&Class_1_key&"*'"
    Me.FilterOn = True
    Me.Filter = "Class_2 like'*"&Class_2_key&"*'"
    Me.FilterOn = True
'' ~ Omitted ~
End Sub

2.
After adding a text box for searching, in the datasheet at the bottom of the form,
A field for the label name in the search text box was added.
Don't add,
Or, if you can search by the above method
Is there a way to make the leftmost field on the sheet always visible (selected)?

I understand ExcelVBA to some extent, but ACCESS is a beginner.
I may not know the basics, but I would like to ask your professor.

  • Answer # 1

    Thank you for fixing the question.
    There are still some unclear points, but I got an overview.

    First of all, for the requirements like this one, I think the split form is difficult to use.

    I think it is more flexible to embed the datasheet view as a subform on the main form.
    For example, in the case of split form, if you place an unbound text box (for search) in the single form, it will also be displayed, but in the main/sub form format, that is not the case.

    If you place the search text box in the form header, it will not be displayed on the data sheet side even in the split form. However,
    Since the layout of the search text box and the correction text box are up and down, if you are concerned about the layout presented in the question, the split form is impossible.

    First decide whether to go in the main/subform format or to place the search textbox in the split form in the form header. Once that is decided, I would like to answer accordingly.


    There was a comment that I would like to go in the main/subform format, so I will answer with that.

    First, create a cut form with the table as the record source. In the details section, place a search text box (unbound) and a text box bound to the field.
    Alternatively, you can change the "default view" of the current split form to "cut form". If you don't change the design, that is faster.

    Create a datasheet-style form that uses the same table as the record source. (Easy to create with the Form Wizard.) Save and close.

    Open the above cut form in Design view and drop the above datasheet format form into the details section. It will now be embedded as a subform.
    The "link parent field" and "link child field" of the subform control may be set automatically, so delete them if they are set.
    Widen the width of the subform control to the full width of the main form. Also, reduce the height of the detail section so that there are no gaps under the subform control.
    Set both "Horizontal Anchor" and "Vertical Anchor" of the subform control to "Both".
    This will look almost the same as a split form.

    After that, place a command button for searching. Name it btn_fix_2.
    (I think the title more accurately represents the process you want to do with "extract" than with "search".)

    After that, place a command button for canceling extraction. Name it cmd_Filter_Clear.

    Write the code of the form module as follows.

    Option Compare Database
    Option Explicit
    Private Sub Form_Open (Cancel As Integer)
        Set Me.SF1.Form.Recordset = Me.Recordset
    End Sub
    Private Sub btn_fix_2_Click ()
        Dim strFilter As String
        If Me.txtF1<>"" Then
            strFilter = strFilter&"And F1 Like'*"&Me.txtF1&"*'"
        End If
        If Me.txtF2<>"" Then
            strFilter = strFilter&"And F2 Like'*"&Me.txtF2&"*'"
        End If
        If Me.txtF3<>"" Then
            strFilter = strFilter&"And F3 Like'*"&Me.txtF3&"*'"
        End If
        Me.Filter = Mid (strFilter, 5)
        Me.FilterOn = strFilter<>""
        Set Me.SF1.Form.Recordset = Me.Recordset
    End SubPrivate Sub cmd_Filter_Clear_Click ()
        Me.txtF1 = ""
        Me.txtF2 = ""
        Me.txtF3 = ""
        Me.Filter = ""
        Me.FilterOn = False
        Set Me.SF1.Form.Recordset = Me.Recordset
    End Sub

    Subform control name SF1
    Table field names F1, F2, F3
    Search text box name txtF1, txtF2, txtF3
    The code is assumed to be, so change it according to the actual name.

  • Answer # 2

    Thank you for fixing the question.
    There are still some unclear points, but I got an overview.

    First of all, for the requirements like this one, I think the split form is difficult to use.

    I think it is more flexible to embed the datasheet view as a subform on the main form.
    For example, in the case of split form, if you place an unbound text box (for search) in the single form, it will also be displayed, but in the main/sub form format, that is not the case.

    If you place the search text box in the form header, it will not be displayed on the data sheet side even in the split form. However,
    Since the layout of the search text box and the correction text box are up and down, if you are concerned about the layout presented in the question, the split form is impossible.

    First decide whether to go in the main/subform format or to place the search textbox in the split form in the form header. Once that is decided, I would like to answer accordingly.