Access form showing event information in the parent, and related equipment in the subform
Experienced Access users know that creating a form that uses multiple tables as the record source can lead you to trouble. Access can find it difficult to know how to insert new data into multiple tables unless you explicitly define the joins between the tables. The easier way is to create a subform that is linked to the "parent" form such that when you navigate from one record to another in the parent form, the subform will change to show only related records.
Collect information from other sources
Why force users to navigate away from their current form to look something up when you can present it in their primary workspace? If you can anticipate what your users might be interested to know about a certain record, Access provides a simple set of tools to fetch data from other tables or queries and show it directly on your form. Unfortunately, the name isn't necessarily an obvious Google search: Domain Aggregate Functions. Essentially, a fancy way to say, "let me collect information from another table/query and summarize it." The most commonly used are:
DSum() - Sum values from a table/query based on some criteriaThere are more functions to get the maximum, minimum, standard deviation, first, or last values. The important thing to know is that you can add one of these to your form without much trouble. Add a textbox to your form, and in the toolbar/ribbon select the "Expression Builder" option (a little magic wand icon). From here, you can add the function you want to use.
DCount() - Count values from a table/query based on some criteria
DAvg() - Average values from a table/query based on some criteria
DLookup() - Get a value from a table/query based on some criteria
Don't be frightened by the cryptic terminology. Replace <<expr>> with the field name you want to sum/count/average/etc. Replace <<domain>> with the table or query name, and finally <<criteria>> with any filter criteria you need to apply. Say you want to sum the revenue from a customer. Your text box would look like this:
=DSum("Revenue","tblOrders","[fk_CustomerID] = " & [CustomerID])
In the above example, we're summing the Revenue column in the table "tblOrders," but only if the customer ID indicator in tblOrders matches the customer ID of the current record on this form. If you see "#Error" you'll know something is wrong. Usually that the field name you specified doesn't exist, or the table/query name is slightly off.
Use the Access tab control to group information
Tab controls can be a bit tricky to learn. You'll have to think of it as a container of sorts, where it can only shrink to be as small as the largest thing inside. So if you find your tab control can't shrink in size, check the tabs to see if there is something "preventing" it from shrinking.
you need to specify the field names on your form
will support any standard mathematic operation you want to apply.
The textbox will not be editable, of course. And as you enter
data into your fields, the text will refresh to show the results
of the calculation for that record in the same manner that Excel
does when you update cells.
Design your forms knowing the key task the user will be carrying out. When needed, you may need to create multiple versions/views of the same information to fit your user's task. For example, a summary form would be ideal to browse a list of products, whereas a detail form might be appropriate for filling out a new order.
In the example above, we're using Customer-Name. That will work fine as long as Customer-Name is always unique. Otherwise, we recommend using the numeric ID field. Also note that you can make your detail form a popup form so that it will display over the summary form. See tip #8 for more on how to set the popup property.