none
Lists from a form input RRS feed

  • Question

  • I have designed a form and would like to have a field where a I can enter a value (user name) and then have Access search a specified table and list all the rows that have that user_name in them.

    Sunday, September 2, 2018 1:50 PM

All replies

  • Start by creating a form based on the table; it can be a single form or a continuous form.

    Create a text box txtUserName in the form header (or footer if you prefer).

    Create a command button cmdFilter next to the text box.

    With the command button selected, activate the Event tab of the Property Sheet (press F4 if you don't see the Property Sheet).

    Select [Event Procedure] from the dropdown list in the On Click event.

    Click the builder dots (the button with three dots …) to the right of the dropdown arrow in this event.

    This activates the Visual Basic Editor.

    Make the code look like this:

    Private Sub cmdFilter_Click()
        Me.Filter = "[user_name]='" & Me.txtUserName & "'"
        Me.FilterOn = True
    End Sub

    Substitute the actual field name for user_name if necessary.

    Switch back to Access and close / save the form.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, September 2, 2018 2:01 PM
  • You might like to take a look at FindRecord.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the option to 'Open form to filter by a foreign key' illustrates how to filter a form by entering or selecting a contact by name in a combo box.  Note that the column in the ContactProjects table on which the form is filtered is not the contact's name but a distinct ContactID foreign key numeric value referencing the primary key of a Contacts table.  Personal names are unsuitable as keys because they can legitimately be duplicated.  This would not be true of a login UserName on a system of course, but would be true if the name is the personal name of users.  I worked with two Maggie Taylors myself.

    For an example of how to filter a form on a name take a look at the option to 'Open form to filter by a non-key value' in the same file.  This can return multiple contacts with the same last name.

    Ken Sheridan, Stafford, England

    Sunday, September 2, 2018 6:08 PM