Microsoft Access Tips & Tricks: Pre-Fill Forms Based On Previous Entries

One way to ease the pains of data entry in forms is to have the form already filled out with values based on some knowledge of what will need to be entered so that the user can tab over the filled values, changing only the ones that need to be changed and leaving the rest unchanged. This will not only help reduce the amount of data entry needed, it will also make the data entry much faster and less error-prone.

If you are interested, you can find my earlier posts on finding the median, the mode, the geometric and harmonic means, ranking every row in a query, selecting random rows out of a table, calculating running sums and averages, calculating running differences, creating histograms, calculating probability masses out of given data, calculating cumulative distributions out of given data, finding percentile scores, percentile values, calculating distinct counts, full outer joins, parameter queries, crosstab queries, working with system objects, listing table fields, finding unmatched rows, calculating statistics with grouping, job-candidate matching, job-candidate matching with skill levels, great circle distances, great circle initial headings, using Excel functions in Access, using the windows file-picker, using the Access date-picker, and setting tab indexes quickly and correctly.

There are two common ways of accomplishing this. The first method is simpler, and I will go over that in the next couple of paragraphs. In this first method, we simply use the DefaultValue property of a control to set the values that show up in the form automatically when the user moves to a new entry on the form.

We can either set the default values during the design of the form or we can set then using code. Setting default values during form design is quick and easy, but does not give the user any control over those values. As time goes on, and the data entered changes, the user might find the originally encoded default values unsuitable. This will then result in the user having to type in all the values, overwriting the default values. This defeats the purpose of having default values in the first place.

Setting default values using code is much more flexible. Here again, there are a couple of different choices. The first choice is to automate the process of setting default values. As soon as the user finishes filling out the form with information, we can run code to calculate the default values to be used when a new data entry is required on that form. The way to do this is by using the AfterUpdate() event of the form. We can use code like the following to put default values in the controls based on the values contained in the controls right now.

Private Sub Form_AfterUpdate()
 Me.Control1.DefaultValue = Chr(34) & Me.Control1 & Chr(34)
 Me.Control2.DefaultValue = Chr(34) & Me.Control2 & Chr(34)
'And so on . . .
End Sub

Let us note a few things about the code above: First of all the DefaultValue property of a control has to be a string. So, we take the current value stored in the control, and enclose it in quotes (I prefer to use chr(34) to denote quotes since it makes it easier to see the character I am using rather than putting in quotes next to each other and trying to guess later on how many single and double quotes are actually there!). Secondly, we have set Control1's default value based on the value currently in it, and Control2's default value based on the value currently in it. But this is just for illustration. You can use any logic you want to derive the default values. It can involve calculations, the values of other controls on the form, and so on. You want the default value to reflect what the user might want to enter on the next record, so use your knowledge of the data entry problem to derive the correct default values to use.

The other choice for setting default values is to give the user control over setting the default values rather than automating it based on the AfterUpdate() event of the form. For instance, the few entries the user is making right now in the form might be atypical, and the user may actually not want these values set as defaults for subsequent entries. To give the user control over default values, you can use a command button on the form that explicitly sets the default values when the button is clicked. If the user does not click the button, the default values are not set. The user can navigate among the records already in the table to find a record that he considers most typical and then set the default based on that entry by pressing the command button while on that record.

The code behind the command button's OnClick() event would be identical to the code we have seen for the AfterUpdate() event of the form. Once again, you have to be cognizant of the fact that the DefaultValue property is a string, so take care to enclose the values in quotes. The actual setting of the default values, as we saw previously, can also use any logic you deem necessary to derive the optimal default values for the form.

If default values are not coded into the form at design time, once the form is closed, the default values set by code go away until they are set again. To prevent this from happening, you can code in default values in the form's design, and then use code like the above to modify the default values as entries are made. When the form is closed and reopened, the values coded into the form during design will be retained, but values based on user entries will replace them as and when the code behind the form is executed (either automatically or when the user chooses appropriate default values and clicks the command button executing the code behind it to set the defaults to the selected values).

Once the form's controls have default values, the controls will appear filled in to the user when the form is on a new record. But unless the user changes a control in some way (or saves the record using the Save Record item on the Records menu (the shortcut for this is Shift + Enter)), these values will not be entered into the underlying table. Access is clever enough to realize when no actual data entry has taken place on a form, and will not save such a record. To avoid confusion, it may be best to leave some controls on a form without default values so that the user is forced to enter something before tabbing over to the next record. This will force Access to save that record (with the default values for the remaining fields) because actual user data entry has taken place on that record.

The second method is not to use default values for the controls on the form. In this method, you actually fill out the form with values derived from either the records already entered or based on any calculations you perform in code. But the values are just values that are filled into the form's controls. They are not default values for these controls. I find it easiest to enter the code for accomplishing this in the form's BeforeInsert() event.

The BeforeInsert() event on a form is triggered as soon as the user types the first character in a new record. Because the event is triggered only after the user types in the first character in a new record, the form appears blank to the user until he starts the data entry. As soon as the user starts data entry, the form is filled with the values you want (calculated in code), and the user can tab over the data that he wants to retain without modifications. Because the user has made some data entry (the form is filled only when the user makes some data entry), the form knows it has been updated and the record will be saved. This clears some of the confusion surrounding the setting of default values in which the user might see a fully filled-out form, but no saves take place because the user did not actually change any of the default values.

The question with this method though, is how to calculate the values to be filled in, since the values were not saved when entered? To do this, I use a clone of the form's recordset (the data on which the form is based) so that I can access the data that was previously entered into the underlying table. Thus, this method has the advantage that the values that are filled out in the form are calculated in code, but they don't require the user to either move to an appropriate record and click on a command button, or complete at least one full data entry without the aid of defaults for the defaults to kick in. As soon as the user enters the form, they can go to a new record and as soon as they type in a single character into any control on the form, the form can be filled with calculated values that can ease data entry on the rest of the form significantly. The code I use is as below:

Private Sub Form_BeforeInsert(Cancel As Integer)
With Me.RecordsetClone
   If .RecordCount > 0 Then
       .MoveLast
       me.Control1 = !Field1
       me.Control2 = !Field2
'And so on . . .
   Else
       me.Control1 = Value1
       me.Control1 = Value2
'And so on . . .
   End if
End With
End Sub

Notice that setting control values based on data already in the table will work only when the underlying table has at least one record from which to calculate the values. Hence the check on .RecordCount. If the calculations can be based on data that is not in the table, you can use the Else part of that If statement to perform such calculations to set the control values (as shown above). We then move to the last entry in the table by using .MoveLast on the recordset. We then set values in the new record based on values in that last record. As always, remember that you can use any calculation scheme to come up with the values, you don't have to just take the values in the last record and put them in the new record.

In fact, since you have access to the entire recordset, you can perform quite sophisticated calculations to come up with the new values for this record. You can for instance, use time-series and other prediction methodologies based on all the data in the table to come up with the values in the new record rather than going with just the last value. In that case, you can move around the recordset using MoveFirst, MoveNext, MovePrevious, etc., rather than going straight to the last record using MoveLast, before you perform your calculations.

Notice that we don't have to enclose the values in quotes to make them strings. These are actual values that are filled out in the controls, and are not default values (which are restricted to be strings). So, you can perform your calculations the way you would do normally in VBA. But notice that the right-hand sides of the assignments use field values rather than control values. You are assigning values to controls from the values of fields in a table. Make sure you understand this distinction before you use this method. If you use control names rather than field names on the right-hand sides of the assignments in the above code, you will get errors because the recordset has no knowledge of the control names. The recordset only knows about field names!

Hope this post has been helpful in solving any problems you might have had with pre-filling forms based on previous entries in Access. The VBA code in this post has been tested in Access 2003 and should work without any problems in all versions of Access from Access 97 on up. If you have any problems or concerns, please feel free to let me know by posting a comment. If you have other questions on Access that you would like me to address in future posts, please feel free to let me know through your comments too. Good luck!