Search The Web

Today's Headlines

Monday, June 21, 2010

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!

26 comments:

EB said...

I used this in my main form and it worked great. Thanks. However, I am right now trying to modified this to work within a sub form and it is not working. I will have to reference the tables linked to the subform and I am not sure how to go about doing so. Hopefully you still check here as it has been a few years.

Blogannath said...

If you pre-fill the main form, and the subform is linked correctly to the main form, I would think the main form would get pre-filled appropriately also. But, I am not sure since I have never tried this on subforms.

In any case, there is no reference to any table in any of my code for pre-filling forms, only a reference to the form's own recordset. And subforms do have recordsets, so if you just use the construct I have used in the post, it should work. From the form, you might have to code it up as me.child.recordsetclone, or something similar.

If none of that works, post what you have attempted so far, and I will see if I can move it forward.

jsb16 said...

I'm using user-entered criteria to generate a query that my form is based on. Is there any way to use the user's input to pre-fill a control when there are no records in the underlying table?

Blogannath said...

The user's input is contained in controls on the input form. All you have to do is set the output form's controls by referring to the input form's controls. Have you tried something like:

me.control1 = myInputForm.control1

and so on?

jsb16 said...

I'm using [Which course?] as one of the criteria for the query that runs when the form is opened, so the user enters the answer in a pop-up box, not in the form itself. I can't figure out which variable refers to that input.

Blogannath said...

I wouldn't use parameter queries if you are using a form for the rest of the user inputs. The popups from parameter queries look unsophisticated and you cannot control what the user enters in them (no data validation, etc.). Better to use a form to collect that input and create a dynamic query using the inputs in the form.

jsb16 said...

I confess that I'm pretty much a newbie, and the documentation on dynamic queries that I've found all refers to Access 2007 or earlier. Could you point me to a good reference?

Blogannath said...

Whatever works on Access 2007 will work in later versions of Access also. VBA has not changed much since Access 2003 or even Access 97. Try out whatever your references mention, and if you run into problems, I will try to help out. Good luck!

jsb16 said...

I tried the process described here: http://support.microsoft.com/kb/304428, but it gave me parameter popup boxes instead of using the values I typed in the form.

Blogannath said...

The only reason a query would pop up a parameter prompt is if the query cannot figure out what you are trying to refer to. Are the forms and control names spelled correctly? Is the form open when the query is executed?

Anonymous said...

Hi, thanks for this function, and for your blog in general - it is a GREAT source of info for a beginner like me! I am having some trouble with it however, and was hoping you could help me. I want to pre fill a data entry form form based on the last record entered in the table but on a click button rather than using the before insert function. It appears to not find any selected records in the recordset clone from which it can pul lthe info. I have tried .movelast .movefirst and .moveprevious - I am pretty new to access so apologies, and thanks in advance for your help. I can post a dropbox link to the file if you would like to have a look?

Alex

Blogannath said...

Is the form based on a table? A form's recordset exists only if it is based on a table, and that table has records in it. Otherwise, the recordset will be empty. You should run your code in the debugger, and make sure that the recordset is not empty. In my code I verify that the recordset is not empty by using its recordcount property. If it is empty, I fill the form with default values rather than values from the last record.

Anonymous said...

I had pointed to the recordset clone incorrectly so the move last found nothing. I fixed it, and now it works great. thanks!

_jason.mohr said...

I am trying to set 4 default values for a certain subform.
For example, I'd like to input my order of clothes. I usually have an order for 4 sizes: S, M, L, XL. For some clothes, I may wish to add an XS or 2XL size.
Can any Access functionality do this?

Blogannath said...

I am not clear as to what you want to do. Are you talking about a dropdown with 4 values from which you choose one? And for some items of clothing you want to add a couple of more entries to the dropdown? That has nothing to do with prefilling the form with data.

If the dropdown is based on a table, this is easy to do. If the dropdown is hardcoded with just the 4 values, it is slightly harder, but not impossible. I would be happy to make suggestions based on what you have currently.

_jason.mohr said...

I have an order form. It has order details and a recurring data of order size and order quantity. Due to normalization, I split the order form into 2 tables. The actual order form table, and the order details table. Order details table includes the following: order detail # (primary), order# (which is linked to order table), order size, order quantity.
In the order form, I connected the order details table via subform. Normally, one would enter each order detail. But I'd like each new order to have 4 new records immediately, with order size S/M/L/XL.

Blogannath said...

Is the subform a continuous form? Filling stuff in programmatically on a continuous form is tricky. But try the below and see if it works:

Private Sub Form_AfterInsert()


Me.subformcontrol.SetFocus 'sets the focus to subform
Me.subformcontrol![Size].SetFocus 'sets the focus to Size field in subform
Me.subformcontrol![Size] = "S" 'sets the first record's Size field as S

Me.subformcontrol.SetFocus 'sets the focus to subform
Me.subformcontrol![Size].SetFocus 'set the focus to Size field in subform
DoCmd.GoToRecord , , acNewRec 'creates new record in subform
Me.subformcontrol![Size] = "M" 'sets the second record's Size field as M

'Add more lines like above to add subform records for other sizes

End Sub

_jason.mohr said...

It worked!
Thanks!
Will it be fine if I ask for help for other MS Access problems more frequent?

Blogannath said...

Excellent! I am glad it worked. Please feel free to read my other Access articles, and if you have questions that are not addressed in them, please post in the comments. I do monitor the comments and will certainly try to help out.

_jason.mohr said...

I have a problem with inputting deliveries. The case is I have a 2-item inventory. Continuing my example, one clothing set consists of a white shirt and a blue shirt.
I need to track inventory. Sometimes I only deliver 5 sets (blue and white shirt), sometimes only 5 blue shirts, sometimes only 7 white shirts. How do I come up with total out of 10 sets and 2 white shirts?

Blogannath said...

I don't think this has anything to do with the topics in Access I have covered in my blog. It sounds more like a numerical computation question. All you have to do is take elements of the input form and do math on them and use the results to populate other elements of the form. General VBA questions like that are best answered in a general Access forum such as www.utteraccess.com or stackoverflow.com.

Evan said...

Here's a long shot. I've been slowly learning access over the past few months as I built from scratch a fairly simple database. Loved this blog!

I have 2 tables:
tblLeadSheets - Houses financial data, appointment times, customer info.

tblEmployeeList - Houses employee info like name, email, phone #, manager, etc.

I also have 2 forms:

frmLeadSheets - Data entry & search functionality.
frmEmployeeList - data entry and search functionality. Also has a subform in it that pulls in all appointments for the selected employee.

If I am in the leadsheet form and I search for a customer .I see the employee assigned to it and want to see what else they are assigned to so I open the employee List form (There is a cmd button to open it) to see what appointments this particular employee has had recently and has upcoming.

How can I set it to default to the employee record that the lead sheet form had? I can only get it to go to a new record (which is fine for normal operation). I appreciate any help you can provide!

Blogannath said...

What is the command you use to open the employee form? There must be an option to open it up to a specific employee record rather than just a new employee record.

ali suleiman said...

Please help to make order as supermarket order

ali suleiman said...

Please help i have table of items and its prices
So i want to make ordering form so that when enter id of item its price and name appear so i enter the number only
Supppse the custumer have 5 item in his vouter
Please help

May Bo said...

THANK YOU THANK YOU THANK YOU THANK YOU x 100000000..... I searched forever on how to do this but didn't understand how until I read your blog. THANK YOU!!

Visitors Country Map

Free counters!

Content From TheFreeDictionary.com

In the News

Article of the Day

This Day in History

Today's Birthday

Quote of the Day

Word of the Day

Match Up
Match each word in the left column with its synonym on the right. When finished, click Answer to see the results. Good luck!

 

Hangman

Spelling Bee
difficulty level:
score: -
please wait...
 
spell the word:

Search The Web