database application development - forms
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.
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.