Search code examples
ms-access

Can I use a form in MS Access to lookup records that match specific criteria

I'm working on a new database for managing a locker rental program. I have three tables, one for the lockers, one for rental agreements, and one for renter contact info. I would like to be able to open a form, enter the locker number, and see the rental agreement history for just that locker. Is this possible in Access? Google has been failing me, but maybe I just don't know the right things to search for. I know I can filter a query or report to be able to do this, but I'm trying to make it as simple as possible since less experienced users will end up using this database.

Solution

  • This boils down to using an unbound combobox (in the header usually) to filter a forms records (many examples). so start by setting up your normalized tables and telling Access about them under DatabaseTools-Relationships:

    enter image description here

    Note How Renters and Lockers are related through the 1 to many relationships with RentalAgreements. Reproduce this table and if you don't understand something review Table Normalization. For table normalization you don't have to restrict yourself to Access as it is a topic common to all sql databases.

    Once you have your normalized tables and Access knows about them you can have Access create starter forms for you. From the main Access window select one of your 3 tables and then hit Create-Form on the ribbon. I hit RentalAgreements as I think starting from the many side of the relationship is best to create a form showing a single record at a time.

    enter image description here

    Aside: create a form from each of the 3 tables in the relationship and play around with them. move through records, add and delete data with the form and see how the underlying tables change. Try to understand how the forms work by viewing the properties of the form parts in design mode (f4 for the properties window usually)

    Next, we improve a default form: first we will want to show many records so I switch the form's default view to continuos forms. (property window, select form, then under format choose continuous forms. It can be tricky to select form at first)

    enter image description here

    To finish we don't show some unnecessary fields by deleting those controls. we change LockerID to a combobox(right-click) to display a readable value instead of an id. finally we add an unbound combobox to the header and use it's afterUpdate event to filter the form.

    enter image description here

    'Unbound combo Box
Private Sub cmbLocker_AfterUpdate()
Me.Filter = "LockerID = " & Me.cmbLocker
Me.FilterOn = True
End Sub

    Note: if you have set up your tables like I did with column 1 as the id and column 2 the description you want to show instead of the id then in the combobox properties set rowsource to Lockers, columns to 2 and column widths to 0,1. id column is then hidden. in the case of the bound combobox in the details section bound column is 1 while the unbound cmbLocker in the header section has a blank bound column propery. You can look all this up with filter form unbound combo box.

    Next: you could continue prettifying the form by removing the record selectors and not displaying an empty record, but adding an add record button to the header, etc