MS Access™ MVP Awardee: 2007-2017
Blog: GPG on Access
Published Books:
Professional Access 2013 Programming with co-authors Teresa Hennig, Ben Clothier and Doug Yudovich. Grover Park George On Access MS Access™ in a SharePoint World with co-authors Tim Runcie and Ben Clothier.
Forms can be "bound" or "unbound". Bound means that the form is directly connected to either a table or a query in your database. In other words, if you have a table called "tblCustomer", you can create a form to display, add, delete or change records from the tblCustomer table. This form is said to be bound to the tblCustomer table. This is one way Access is different from many other development tools. It gives Access an advantage because it speeds up development. In addition, controls (text boxes, list and combo boxes, etc.) on the form can be bound to fields in the table. For example, on the form bound to tblCustomer, you can have a text box called "txtCustomerName", which gets its values from the field called "Customername" in tblCustomer. We say that txtCustomerName is bound to CustomerName. However, controls on forms don't have to be bound to fields in the underlying table, and there are times when you want to use an unbound control on a form. For example, you can use an unbound form to display the results of a calculation using two other controls. Let's say you have a field for "StartTime" and a second field for "EndTime" in your Project Work table. These fields are bound to controls on a form where you can enter the time you begin working on a project and the time you stop working on it on any given day. To display the total time you spent working on that project that day during that work session, you can create a third, unbound, control and use it to display the calculated elapsed time in minutes. Note: We do not, as a general rule, store that calculated value in the table. It's only displayed on forms, and on reports, or used in other calculations. We avoid storing calculations to prevent the problem of having changes to the underlying source fields not reflected in the calculation. That's why this control on the form is NOT bound. The control source for this unbound field would be: = DateDiff("n", txtStartTime, txtEndTime) where DateDiff is a standard Access function which calculates the elapsed time, "n" tells the function to calculate the time in minutes (as opposed to hours "h", days "d", etc.), and txtStartTime and txtEndTime are the two time (or date) values to be used in the function. This unbound control, perhaps called txtElapsedTime, will always recalculate and display the correct elapsed time for the values in the two bound controls if either of them is updated. Some calculated values can be quite complex. For example: Let's say you have a display only form on which you want to show all of the work done on any given day across multiple projects. In the query to which the form is bound, the same DateDiff function is used to calculate a TotalTime for each project. (This could also be done in the control on the form, by the way. It might be slightly more efficient to do it in the query, depending on how complex the calculation is.) Let's also say that all time spent on a project is recorded, but some of that time is billable to the client, while other time is spent on non-billable tasks related to that project, such as training on a pertinent topic which can't be billed to any one client. So, in addition, there is a calculated control on the form which takes this total project work time and calculates billable time, in hours. This calculated field has as its control source: =Iif([Billable] <> 0, [TotalTime],0)/60 This calculation means that, if the value of the Billable flag is not false (i.e. is not 0) then divide the total time in minutes by 60 minutes per hour to get the billable hours. If, however, the Billable flag is false, i.e. it is 0, divide 0 by 60. The results of this division will always be zero. Note also, that by including the Billable flag in the underlying query, we see two lines for each project if there is both billable and non-billable work for it on the same day. On the other hand, of course, if only billable work, or only non-billable work, is completed, then only that one line will appear on the form.