ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

database application development - forms

Updated on May 30, 2014

Forms are objects in relational databases that we old-timers used to call screens. In Microsoft Access, they can be built automatically using the wizards, based on queries or tables. But they can also be created independent of either.

Basic forms are a user-friendly way to enter data into a table. For this purpose, I will sometimes use the form wizard and then modify it. The form wizard builds a linear form (one field under another.) To do this, select the table involved and click the form wizard button on the tool bar. The wizard will set up the fields by what data type is involved. For instance, a yes/no field would show up as a checkbox; a lookup field will show up as a drop-down list box. Check to be sure the field names (the labels on the form) are clear; we often use truncated field names that are a bit cryptic. The wizard doesn’t always account for long field names, so check that as well; you can widen the area of the label.

Forms can be very fancy, including calculated fields (create a text box, and instead of giving it a data source, type in a formula), photo backgrounds, callouts for macros or other forms… it can really knock your socks off. When working with a client who is tied to the trees (accustomed to paper forms) I use a simple display which looks almost exactly like the paper form. If a client has a data base with a huge number of fields, I will often make them a series of forms for related or rarely-entered data, reached either by a button or through a menu. I don’t use the switchboard feature at all; it’s limited, cumbersome, and a bear to edit. Instead, I have an opening screen with my copyright information, and a button which opens a menu form; use buttons to open the needed forms from there.

Most common challenges

Sometimes forms are the best way to create a query, because you are not tied to the simple questions a query can handle. In a query, you can put a question into the criteria, which shows up as a message box when the query is run, asking for data which is then entered into that field. For instance, the message could be “Please enter vendor’s name”; the user types in a name and hits <Enter> and the query is then run with that criterion. The biggest problem is that the user needs to know exactly how the vendor names are typed – and just what they are. Ifyou use this option, give the user advice on how to enter the information (such as “mm/dd/yyyy”). As an alternative, have the user select from a drop-down list. You’ve all used these lists at least once in your use of applications, or filling out information on the Web.

To see the toolbox – click on the button that looks like a hammer and screwdriver when in the design view of a form. Try using all the tools --- you can create labels (plain text), unbound fields, drop-down lists, forms within a form, control buttons – it’s a veritable playpen. And by getting accustomed to the form design, you will find report design much easier.

How do you create a variable? I create a base table I name Parameters. In this table I create any variable fields that would be appropriate. For instance, if the database were a contact management system, the fields might be report title, a numeric field, first and last names, city. It’s a handy table – you can add fields you need later on the fly. Only the first record is used (unless you want multiple reports for a special reason, like a mail merge); this is achieved by simply having the field get populated instead of appending the information – the first record keeps getting overwritten. If you use this method, be sure to delete the information at the end of the process, which can be achieved by a macro. Create a form based on the Parameters table and ask the user to fill in or select the information s/he wants displayed. Then create a query based on the table with the information, and either link the second table to Parameters by a field or user the Parameters field(s) as criteria: =[Parameters].[fieldname] Now add a control button to the form that says something like “execute”. That button can be configured to open the query via the wizard.

The parameters table is one method for saving a variable. I usually start out with StartDate and EndDate fields, for queries which need date brackets or a single date selection. I also like to have a ReportTitle field for regular reports so I can change its title each generation -- as in "BRS demographics, First Quarter 2006". Anytime you want to change a parameter in a query, it is better to let the user select from a list than to force them to memorize the item, or to change the criterion inside a query. Create a form based on the parameters table which requests the user to select the criteria (parameter) from a combo box. This gets stored in the appropriate field in the parameters table. Add a control button which opens up a query which answers your questions, using the parameter table's field as the criteria. Badda bing, badda boom...

This way you can customize a report or display to the needs of the user. This can get very complex – I once had a button that used four MB of VBA code to generate the report!

Last but not least, you can use forms as message boxes or Help screens - simply don't link it to a table or query. A message box can be opened by VBA code. A help screen might be a control button on another form.

Putting forms to use

Some of the forms you have could take forever to go through to find what you want (do I already have this movie? Is that book loaned out? Is this TV already listed?). These are forms which display a soon-to-be-large database table one record at a time.

One method I use to cut the time, especially in my address book and my WWW listings, is a search. Place the cursor in the field you want to search in (say, last name), and then select Find under the Edit menu -- or be lazy like me and click the binoculars on the toolbar. Type in what you want to search for; the field to be searched is already selected; choose either the whole field (default) or any part of the field - my favorite because it saves typing time and I might not be sure of the entire field contents; click Find Next until you find the record you are looking for. If the whole table is searched without finding what you are looking for, a message box will appear telling you so.

Another great feature is a filter. This comes in very handy when you need to check information on a group of things. Let's take my WWW sites database, which lists the sites I'm registered on [ok, I've been surfing for a looooong time], the URL, the type of site, the username, password and e-mail used. I want to find all email addresses which have "aol" in them because I have cancelled my AOL account and all its screen names; I want to contact all these sites and change the email used. So I place the cursor in the email field and search for the first instance which had "aol" in it (searches and filters are not case-sensitive in Access). When I find the first one I highlight only the "aol" part of the email address, then click the filter icon (looks like a funnel on the toolbar). Now you will notice that the number of records has decreased significantly; that's because only those with "aol" in the email address are being displayed, and they may be navigated as you would the full list. When you are done, click the 'pressed' un-filter tool to get the whole listing back.

One warning about filters - if you go to close the form and are asked if you want to save design properties on the table, do not click Yes, or the table will always open up filtered!

I use the A-->Z, Z-->A sorts constantly -- to find out what was done on a certain date, what fields have blank entries (complicated queries can drop records because there is missing information in one of the fields), and a dozen other things. I tend to use the sort in the table directly; and I will hide certain fields in the table so I can scan information from the sort - this is akin to hiding columns in Excel. Again - be careful not to save the design when closing.

More …

When you have a form that has an auto-number field, don't put that field on the form -- it confuses a lot of users as they try over and over to fill that field. Or remove its tab index so that they don't tab into it.

Huh? Tab index????? Every field in a form has a Tab Index (properties\other tab). This tells the order in which the user should tab through the form, and starts with zero. So tab index 0 should be the upper left hand corner, since that's the way we read in this country. I can tell when a new developer didn't use a wizard (or seriously changed the form if he did) because the tab indexes are all messed up. They are determined by the order the fields are placed on the form. To check it, just place the focus on the first field and start tabbing. Lost the thing? It backed up three fields? Gotta fix that. To fix it, open Properties / Other on the first field. The tab index should be zero. Then click on the next field -- the properties will now show for that one - it should be one. Keep clicking through till you find a number in the wrong place, and type over with the correct number. This needs to be done in the desired order because Access adjusts from the change, giving that wrong tab index to another field and readjusting the sequence.

Justification: as in Excel, the fields justify left for text and right for numbers and dates. You can change the justification of any field and any field label just like you would in Excel. Saves searching a text box to find the data!

What if you have a form where you just want to discourage users from changing the data? Make the background of the text box holding the field values the same color as the form background. Top it off by removing the border. That way it appears like a label.

If you have a form for adding a record, you will never stop users from overwriting the first record by accident. One way to handle this is to have a datasheet view, where the user can see the next available record. But most records take up a whole form for fields (unless you make your user scroll right forever). So make the form data-entry-only. To do this, create a regular data entry form. Then go into its properties and go to the Data tab. At the bottom you'll see a property called Data Entry, at the default No. Change it to Yes. Then when the form is opened, the user will only see an open record to fill out. The user will only be able to see those records created during that session of having the form open. Another approach -- you could view all the records, or you could click a control button called "Add New" -- and you are faced with an empty form. As with all design features, there is more than one way to skin the cat.

If you have a text field as a Lookup data type you probably noticed that when you put it into a form, it automatically becomes a combo box. I find this unnerving (OK, I don't trust users a whole lot.) Sometimes these are fields that might change frequently for one record, but that's rare. For this reason I prefer to make a base table with the lookup list in it and refer to that in a combo box tool when I want that ability. This also allows the user to type in her own information if it isn’t available in the list.

You may want to create an unbound field which does a calculation for you. I do this when I record my gas purchases. I enter the odometer reading and the number of gallons. The calculated field then tells me what my miles-per-gallon year to date is. Lets me know when I need to get a tune-up! For clients, this might calculate commissions or tax.


    0 of 8192 characters used
    Post Comment
    • Bonnie-Jean Rohne profile imageAUTHOR

      Bonnie-Jean Rohner 

      6 years ago from Williamson, New York

      Bryan, once the app is developed, you may consider offering it for sale. has a services-offered section where this can be done. I'm considering this, since I've developed a bazillion apps for home and office.

    • Bryan Eaddy profile image

      Bryan Eaddy 

      6 years ago from Michigan

      Hello Bonnie,

      After I've completed the Excel app that I mentioned, my plan is to create a hub around - videos included. Thereafter, I wholeheartedly welcome your feedback. Thanks!

    • Bonnie-Jean Rohne profile imageAUTHOR

      Bonnie-Jean Rohner 

      6 years ago from Williamson, New York

      Thanks for the good words, Bryan. In most companies and home computers you will find MS Office with Excel, whereas Access is usually an expensive addition. For that reason, you are still in a handy area. Either way, the concepts of databases remain the same; it's just a matter of "syntax" as to how you execute the concept.

    • Bryan Eaddy profile image

      Bryan Eaddy 

      6 years ago from Michigan

      Great hub Bonnie! I'm more of an Excel VBA programmer than DBA. Although Excel isn't a true database application, colleagues consistently ask me to create database like apps in Excel. For that reason, I'm always interested in gaining insight from a real DBA. Thanks!


    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at:

    Show Details
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the or domains, for performance and efficiency reasons. (Privacy Policy)
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)
    ClickscoThis is a data management platform studying reader behavior (Privacy Policy)