Others

How to Create a Database Form for Searching and Editing Records

Nimra Malik Oct 20, 2026 868 views

Question details

The user needs to create a database interface that allows searching for customer records using multiple optional criteria (such as phone number or name), displaying the matches, and enabling direct edits to the selected data.

How to Create a Database Form for Searching and Editing Records

Product: Microsoft Access
Device & OS: not provided
Scenario: Building a functional customer management system where users can filter a large dataset using partial or full information across different fields, without breaking the query when some search boxes are left blank.
Observed behavior: The intended goal is to successfully filter a database based on populated search fields, ignore empty fields using wildcard syntax or conditional queries, and dynamically update a continuous form so records can be selected and modified.

Before you start

Ensure your customer data table is properly structured with a designated primary key (like CustomerID) and that you have basic familiarity with query design and form properties in your database software.

Solution 1Recommended

Build a Search Form using Conditional Queries and Wildcards

Create a dynamic form that filters records based on user input using SQL LIKE operators, allowing partial text searches and ignoring empty search fields.

To achieve a flexible search interface, you need to combine an unbound input form with a results form. By pointing the results form's Record Source to a custom query, you can dynamically apply filtering criteria.

Using wildcards ensures that if a user leaves a specific field blank, the database simply returns all records for that column rather than returning zero results.

Create the Search Interface

Create an unbound form (e.g., 'frmCustomers') and add text boxes for your search criteria, such as 'txtLastName', 'txtFirstName', and 'txtPhone'. Add a Command Button labeled 'Search'.

Design the Filter Query

Create a new query based on your customer table. This query will act as the Record Source for your results form.

Apply Text Field Criteria

In the Query Design view, navigate to the Criteria row for your text fields. Use the LIKE operator with wildcards to allow partial matches. For example, under the LastName field, enter: LIKE "*" & Forms!frmCustomers!txtLastName & "*"

Apply Numeric and Date Criteria

For fields involving numbers, currency, or dates, do not use wildcards. Instead, use exact equality (= Forms!frmCustomers!txtNumber) or range criteria (Between [Start] And [End]).

Add the Requery Action

Select your 'Search' button, open the Property Sheet, and go to the 'On Click' event. Add a macro or VBA code to Requery the results form, ensuring the list updates when criteria are submitted.

Set Up the Editing Form

Configure your results form as a Continuous Form or List Box. Attach a macro to the 'On Dbl Click' event of the record rows to open a separate, bound Customer Form filtered to the selected record's ID for editing.

Build a Search Form using Conditional Queries and Wildcards

Triggering Updates Automatically: Instead of relying solely on the 'Search' button, you can also add the Requery command to the 'After Update' event of each individual search text box to filter results immediately as the user types.

Manage Records Easily

Use Data Forms in WPS Spreadsheets to Search and Edit Records

You don't need a complex database to create a search and edit interface. WPS Spreadsheets includes a built-in 'Form' tool that automatically generates a user-friendly dialog box for any dataset, allowing you to quickly search, update, add, or delete customer records without writing any queries or code.

1. Open Your Data in WPS: Launch WPS Spreadsheets and open the file containing your customer data. Ensure your table has clear column headers (e.g., First Name, Last Name, Phone Number).
2. Select Your Table: Click on any active cell within your data table so the spreadsheet recognizes the dataset range.
3. Open the Data Form: Navigate to the 'Data' tab on the top ribbon menu and click the 'Form' button. A dialog box will appear displaying your first record.
4. Search for Records: Click the 'Criteria' button in the Form dialog. Enter your search terms (such as a specific name or number) into the corresponding blank fields, then press Enter to filter.
5. Edit the Information: Use the 'Find Prev' or 'Find Next' buttons to navigate through matching records. Click directly into the text boxes to edit the customer information, and your spreadsheet will automatically update.

Instantly generate an editing form without SQL or VBA programming.Built-in 'Criteria' search to easily find exact or partial match records.Fully compatible with Microsoft Excel (.xlsx) and CSV database files.Free, lightweight, and perfect for managing small to medium customer datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use wildcards to search numeric or date fields in my database form?

No, wildcards like the asterisk (*) are strictly designed for text fields. For numeric, currency, or date fields, you should use exact-match criteria or range criteria (such as Between [StartDate] And [EndDate]).

Why is my database search query returning zero results when I leave a text field blank?

If you do not handle empty inputs, the database will search for records that are literally blank. To fix this, use wildcard combinations (e.g., LIKE "*" & [SearchControl] & "*") or add conditional logic in your query design to ignore the control if it evaluates to Null.

What is the best format to display the search results before editing?

The most common and user-friendly approach is to use a Continuous Form, a Datasheet view, or a List Box. These options allow users to view multiple matching records at a glance and select the correct one before opening the detailed editing view.

How do I link my search results to an editable customer form?

You can add a command button next to each record or use the 'On Dbl Click' event on a record field. Configure this event to open the detailed editing form, using the 'Where Condition' to match the currently selected CustomerID.

Office Quick Solutions