IN THE SPOTLIGHT: MDE to MDB Conversion Service
(also supports: ACCDE to ACCDB, ADE to ADP, etc)
IN THE SPOTLIGHT: vbWatchdog
VBA error handling just got easier...
" vbWatchdog is off the chart. It solves a long standing problem of how to consolidate error handling into one global location and avoid repetitious code within applications. "
- Joe Anderson,
Microsoft Access MVP
Meet Shady, the vbWatchdog mascot watching over your VBA code →
(courtesy of Crystal Long, Microsoft Access MVP)
IN THE SPOTLIGHT: vbMAPI
An Outlook / MAPI code library for VBA, .NET and C# projects
Get emails out to your customers reliably, and without hassle, every single time.
Use vbMAPI alongside Microsoft Outlook to add professional emailing capabilities to your projects.
Provided by Allen Browne, March 2008. Updated April 2010. (Replaces this older version of the article, which works in Access 95 or 97.)
Combo boxes give quick and accurate data entry:
But how do you manage the items in the list? Access gives several options.
When you enter something that is not in the combo's list, its Not In List event fires. Use this event to add the new item to the RowSource table.
This solution is best for simple lists where there is only one field, such as choosing a category. You must set the combo's Limit To List property to Yes, or the Limit To List event won't fire.
In the Northwind sample database, the Products form has a CategoryID combo. This example shows how to add a new category by typing one that does not already exist in the combo:
Private Sub CategoryID_NotInList(NewData As String, Response As Integer) Dim strTmp As String 'Get confirmation that this is not just a spelling error. strTmp = "Add '" & NewData & "' as a new product category?" If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then 'Append the NewData as a record in the Categories table. strTmp = "INSERT INTO Categories ( CategoryName ) " & _ "SELECT """ & NewData & """ AS CategoryName;" DBEngine(0)(0).Execute strTmp, dbFailOnError 'Notify Access about the new record, so it requeries the combo. Response = acDataErrAdded End If End Sub
Not In List has some limitations:
If the combo's source table has several fields, you need a form. Access 2007 gave combos a new property to make this very easy.
Just set this property to the name of the form that should be used to manage the items in the combo's list. This approach is very simple, and requires no code.
The example below is for a CustomerID combo on an order form. When filling out an order, you can right-click the combo to add a new customer.
|The combo properties (design view)||The right-click shortcut menu (to add a new customer)|
To avoid these limitations, you could choose another event to pop up the form to edit the list. Perhaps the combo's DblClick event, a custom shortcut menu, or the click of a command button beside the combo. This approach does require some programming. There are several issues to solve here, since the edit form may already be open.
Add code like this to the combo's event:
Private Sub CustomerID_DblClick(Cancel As Integer) Dim rs As DAO.Recordset Dim strWhere As String Const strcTargetForm = "Customers" 'Set up to search for the current customer. If Not IsNull(Me.CustomerID) Then strWhere = "CustomerID = """ & Me.CustomerID & """" End If 'Open the editing form. If Not CurrentProject.AllForms(strcTargetForm).IsLoaded Then DoCmd.OpenForm strcTargetForm End If With Forms(strcTargetForm) 'Save any edits in progress, and make it the active form. If .Dirty Then .Dirty = False .SetFocus If strWhere <> vbNullString Then 'Find the record matching the combo. Set rs = .RecordsetClone rs.FindFirst strWhere If Not rs.NoMatch Then .Bookmark = rs.Bookmark End If Else 'Combo was blank, so go to new record. RunCommand acCmdRecordsGoToNew End If End With Set rs = Nothing End Sub
Then, in the pop up form's module, requery the combo:
Private Sub Form_AfterUpdate() On Error GoTo Err_Handler 'Purpose: Requery the combo that may have called this in its DblClick Dim cbo As ComboBox Dim iErrCount As Integer Const strcCallingForm = "Orders" If CurrentProject.AllForms(strcCallingForm).IsLoaded Then Set cbo = Forms(strcCallingForm)!CustomerID cbo.Requery End If Exit_Handler: Exit Sub Err_Handler: 'Undo the combo if it has a partially entered value. If (Err.Number = 2118) And (iErrCount < 3) And Not (cbo Is Nothing) Then cbo.Undo Resume End If MsgBox "Error " & Err.Number & ": " & Err.Description Resume Exit_Handler End Sub Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer) If Response = acDeleteOK Then Call Form_AfterUpdate End If End Sub
Set the combo's Limit To List property to No, and it lets you enter values values that are not in the list.
This approach is suitable for free-form text fields where the value might be similar to other records, but could also be quite different. For example, a comments field where comments might be similar to another record, but could be completely different.
The auto-expand makes it quick to enter similar comments, but it gives no accuracy. It is unnormalized, and completely unsuitable if you might need to count or group by the lookup category. If the combo's bound column is not the display column, you cannot set Limit To List to No.
To populate the combo's list, include DISTINCT in its Row Source, like this:
SELECT DISTINCT Comments FROM Table1 WHERE (Comments Is Not Null) ORDER BY Comments;
Anything you type in the combo is saved in the Comments field. The list automatically shows the current items next time you open the form.
This approach is useful in only very limited scenarios.
In a word, DON'T! No serious developer should let users add items to value lists, despite Access 2007 introducing a raft of new properties for this purpose.
If you set a combo's Row Source Type to Value List, you can enter the list of items (separated by semicolons) in the Row Source. You might do this for a very limited range of choices that will never change (e.g. "Male"; "Female.") But letting the user add items to the list almost guarantees you will end up with bad data.
Access 2007 introduced the Allow Value List Edits property. If you set this to Yes, you can right-click the combo and choose Edit List Items in the shortcut menu. Access opens a dialog where you can add items, remove items, or edit the items in the list.
Let's ignore the fact that this doesn't work at all if the combo's Column Count is 2 or more. The real problem is that there is no relational integrity:
If that's not bad enough, it gets worse when you close the form. Access asks:
Do you want to save the changes to the design of the form?
Regardless of how you answer that question, things go bad:
There is no safe, reliable way for users to add items to the Value List in the form without messing up the integrity of the data.
What about storing the value list in the table instead of the form? Access 2007 and later can do that, but again it's unusable. Don't do it!
Some developers hate the idea of a combo in a table anyway. Particularly if the Bound Column is not the display value, it confuses people by masking what is really stored there, not to mention the issues with the wizard that creates this. For details, see The Evils of Lookup Fields in Tables. But lets ignore this wisdom, and explore what happens of you store the value list in the table.
Select the field in table design, and in the lower pane (on the Lookup tab), set the properties like this:
|Display Control||Combo Box|
|Row Source Type||Value List|
|Allow Value List Edits||Yes|
|Row Source||"dog"; "cat"; "fish"|
Now create a form using this table, with a combo for this field. Set the combo's Inherit Value List property to Yes. Now Access ignores the Row Source list in the form, and uses the list from the table instead. If you edit the list (adding, deleting, or modifying items), Access stores the changes in the properties of the field in the table.
Does this solve the problems associated with keeping the list in the form? No, it does not.
If the database is split (so the table is attached), the changed Value List is updated in the linked table in the front end only. It is not written to the real table in the back end. Consequently, the changed Value List is not propagated to other users. We still have the same problem where each user is adding their own separate items to the list. And we have the same problem where the user's changes are lost when the front end is updated.
(Just for good measure, the Row Source of the field in the linked table does not display correctly after it has been updated in this way, though the property is set if you examine it programmatically.)
At this point, it seems pointless to continue testing. One can also imagine multi-user issues with people overwriting each others' entries as they edit the data if the database is not split.
There is no safe, reliable way for users to add items to the Value List without messing up the integrity of the data.
Multi-valued fields (MVFs - introduced in Access 2007), suffer from the same issues if you let users edit their value list.
The MVFs have one more property that messes things up even further: Show Only Row Source Values. If you set this property to Yes, and allow users to modify the value list, it suppresses the display of items that are no longer in the list. A user can now remove an item from the list — even though 500 records in your database are using it. You will no longer see the value in any of the records where it is stored. At this point, not only have you messed up the integrity of the data, you have also messed up the display of the data, so no end user has any idea what is really stored in the database. (It can only be determined programmatically.)
Use tables, not value lists, to manage lookup data. Create relationships with Relational Integrity.
Use these lookup tables (or queries based on them), as the RowSource for your combos. Do not use Value Lists for anything more than the simplest of choices that the user never needs to edit.
Use the Not In List event to add data to simple, single-field lookups such as types or categories.
If you only use Access 2007 or later, the List Items Edit Form property is a quick and easy way to nominate the form to use for managing the list items.
To edit the list in any version of Access, or to control how the editing works, use another event such as the combo's DblClick.
|Home||Index of tips||Top|
This is a cached tutorial, reproduced with permission.
Have your say - comment on this article.
What did you think of 'NotInList: Adding values to lookup tables'?
13 July 2010
Thank you, spot on advice
Have your say...