DataBaseZone.com
Conrad Muller
Seattle, Washington

Email: conrad at
databasezone
dot com

Chapter One: Building the Basic Search Form


©Conrad Muller 2005

Build a working form.

In this chapter you will build the basic form by following these steps:

  1. Make a copy of the database file memberdata.mdb and name it member.mdb.
  2. Create an SQL query in the new database file.
  3. Build and modify a form based on the query.
  4. In the next chapter we will add the search Text Box.

Naming Conventions

A convention is a set of rules that are used voluntarily. You should use these standardized naming conventions for programming in all Versions of Visual Basic. Using standard naming will make your code easier to write, maintain, and debug.

We will start with naming conventions for some database objects:
  1. No spaces in names.
  2. Only letters, numbers, $ and the underscore character _.
  3. Always begin the name with a letter.
  4. Tables, queries, forms, and controls should never have plural names.

Make a copy of an Access database file.

  1. Open the Windows Explorer.
  2. Find the Examples folder on the CDROM that came with this book, and drag the folder to a convenient location on your working drive. C:\My Documents\examples is a possibility.
  3. After dragging the folder to your working drive, all of the files will be Read-only. To remove the Read-only attribute, select the files you wish to change.
  4. Then select Files/Properties and uncheck Read-only in the File Properties dialog box. If you have selected more than one file, you will be changing all that you selected.
  5. In the folder Examples select memberdata.mdb. Hold down the Ctrl key and use the mouse to drag the file to an empty space in the folder's window.
  6. You should now have a new file named Copy of memberdata.mdb. Use a right mouse click context menu to rename the file member.mdb.

Inspect the new Access database file.

  1. Double-click on the filename member.mdb to open it in Microsoft Access.
  2. Look at the Tables, Queries, Forms, Reports, Macros, and Modules. You should find only one table, tbl_member, and no other objects.
  3. Open the table by double-clicking on it. Use the scroll bars to look around. Notice the record navigation buttons in the lower left of the form, and the record count.
  4. Now close the table, but not the Database Window.

Create an SQL query to retrieve the data for our form.

  1. Click on the Queries button of the Database Window.
  2. Double-click the entry Create Query in design view.
  3. Use the Show Table dialog box to add table member to the top pane of the Query Builder, and then close the Show Table dialog box.
  4. Add the columns we will need in our form to the query grid: Member_id, FirstName, LastName, AreaCode, Phone, Email, City, PaidUntil, and Board.

  1. Choose to Sort Ascending on the LastName column, as in column three of the Query Builder picture above.
  2. Save the query as qsel_search.
  3. Use the View button, all the way on the left of the Tool Bar, to see the each of the three views of the query: datasheet view, design view, and SQL view.
  4. The Design View is on the facing page. The Datasheet View and the SQL View are below.
  5. Close the Query when you are sure it works correctly.

This is the Datasheet View, showing the results of running the query.

This is the SQL view, showing the actual code that was written by the Query Builder and sent to the database server for execution.

Create a form based on the query.

  1. Click the Forms button in the database window.
  2. Double-click Create form by using wizard.
  3. Be certain that Query: qsel_search is selected in the first Form Wizard dialog box.
  4. Use the "fast forward" button to move all of the fields to the Selected Fields pane. Click Next.
  5. Select a tabular layout, and then click Next.
  6. Choose the Standard style, and then click Next.
  7. Name the form frm_search, and then click Finish.
  8. The resulting form is truly ugly, but we can fix it up.

Fix up the form generated by the Form Wizard.

  1. Right mouse-click on the blue bar at the top of the form. Choose Form Design from the top of the context menu that appears.
  2. Make the Form Header about ?" high.
  3. Move the labels to the very bottom of the header.
  4. Move the Text Boxes to the very top of the Detail section.
  5. Adjust all of the Text Boxes to have a height of 0.1771" and Scroll Bars = None.

  1. Move the bottom of the Detail Section up, so there is no space beneath the Text Boxes in the Detail Section.
  2. Make all of the Text Box Widths fit the data they have to display. You will have to switch between Design View and Form View as you adjust widths to fit. Do not leave space between Text Boxes. Use the picture below as a guide.
  3. Create a ?" Form Footer, and line up the Labels with the Text Boxes. Save your work, and take a look at your form in Form View.

Name all of the objects on the form.

 

  1. Put the form back into Design View.
  2. Open the Properties list dialog box, and click the Other tab.
  3. Click the member_id Text Box.
  4. Set properties for the member_id textbox control.
  5. Name: txt_member_id
  6. Tab Stop: No.
  7. Rename all of the Text boxes by putting "txt" at the beginning of each name, and set Tab Stop: No.
  8. Name the Board Check Box by adding chk to the beginning of the name, to make it chk_board, and set Tab Stop: No.
  9. Don't miss setting the Name property, and setting Tab Stop to No, on any detail section control.
  10. Now rename the Labels in the Form Header. They will look like member_id_Label. Remove the Label, and add lbl to the beginning of the name. For example lbl_member_id.
Hint: Once the Properties List dialog box is open, click on any form object to display its properties. Just leave the Properties List open while you work on the form.

Lock all of the Text Boxes on the form.

  1. Put the form into Design View again.
  2. Open the Properties list, and click the Data ?tab.
  3. Click the txt_member_id Text Box.
  4. Set Locked: Yes
  5. Lock all of the other Text Boxes and the Check Box.
  6. Save your work. Now, go to Form View and take a look.

What's next?

  1. Notice the Title Bar at the top of the form. It would be nice if the Form Caption said something more interesting than frm_search. It would also be nice to remove the three small buttons on the right end of the blue Title Bar.
  2. We will also remove the navigation buttons and a scroll bar from the bottom of the form. We aren't going to need any of that.
  3. Before I forget, we will need some way to close the form after we remove the buttons on the top right. We'll add a Close button next.
  4. Change the form to Design View. Be sure the Wizard button on the Toolbox is depressed (turned on). The Wizard button icon is a magic wand.

  1. Click on the Command Button button. Draw a button on the far right of the Form Footer. The Command Button Wizard should start.
  2. In the left pane of the dialog box choose Form Operation. In the right pane of the dialog box choose Close Form. Click the Next button.
  3. Choose Text and make the Text simply say Close. Click Next.
  4. Name the control cmd_close and click Finish. Save your work.
  5. Change to Form View, and try your Close button.

Clean up the form by setting Form Properties.

  1. Change back to Design View. Open the Properties List.
  2. If there isn't a black dot in the button on the top left of the form window, click on the button.
  3. This causes the properties of the form object (overall form properties) to be displayed in the Properties List dialog box.

Set the Format properties for frm_search.

Select the Format tab of the properties sheet. Change these properties:

Set the Data properties for frm_search.

Select the Data tab. Change this property: Allow Additions: No

Note: We have changed the Form Border to Thin, and Auto Resize to No so that the form will always appear in the size we have planned.

We have removed all of the default navigation and control buttons, because we are going to provide all of the control the user will need, and it would be distracting to give the user so many choices.

We have disabled editing on this form, because this form is used only for searching, and because some of the fields are going to be hyperlinks, and hyperlinks should not be edited.

This is our form, ready to add the Search Text Box in the next chapter.

Try to make your form look as much like this one as you can. We will be adding a lot of functionality to this form, and it should be as attractive as it is functional.

All of Conrad Muller's work on this site is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 2.5 License.

Home | Resume | Project Portfolio | Writings | Developer Resources | Contact Form