Make a search form in Microsoft Access 2016

Question

Make a search form in Microsoft Access 2016

Access has never worked before. I was faced with the task of making a search form by last name, first name, patronymic or phone number. The idea is this. Create a ribbon form, drop the input field and the search button on this form. Tell me. How can I do such a search?

0

форма поиск ms-access

Author: Mr.Evgen, 2019-07-29

Source

1 answers

score 0 · Answer 1

There is an option with a nested form. You create a regular unbound form and add 4 text fields on top: last name, first name, patronymic, phone number; + search button. Below, embed a tabular or ribbon form with data from the data source. Next, you hang the handler on the Click button event:

dim flag as Boolean, sqlstr as String
flag = False
If IsNull(Me.Controls("телефон").Value) Then
    sqlstr = "Select * From table Where"
    If not IsNull(Me.Controls("фамилия").Value) Then 
        sqlstr = sqlstr & "[фамилия] = '" & Me.Controls("фамилия").Value & "'"
        flag = True
    End If
    If not IsNull(Me.Controls("имя").Value) Then
        If flag Then sqlstr = sqlstr & " and"
        sqlstr = sqlstr & "[имя] = '" & Me.Controls("имя").Value & "'"
        flag = True
    End If
    If not IsNull(Me.Controls("отчество").Value) Then
        If flag Then sqlstr = sqlstr & " and"
        sqlstr = sqlstr & "[отчество] = '" & Me.Controls("отчество").Value & "'"
        flag = True
    End If
Else
    sqlstr = "Select * From table Where [телефон] = '" & Me.Controls("телефон").Value & "'"
End If
With Me.Controls("форма_с_данными").Form
    .ResordSource = sqlstr
    .Requery
    .Refresh
End With

It is assumed that the form fields have Russian names corresponding to the names of the table fields.

If the search is supposed to be based on the partial text of the fields, then replace the equal sign in the query text with the Like operator. For example:

// Вот этот кусок кода
sqlstr = sqlstr & "[фамилия] = '" & Me.Controls("фамилия").Value & "'"

// будет выглядеть вот так
sqlstr = sqlstr & "[фамилия] Like '" & Me.Controls("фамилия").Value & "'"