How do I add a value to a combo box with Not In List?

The combo box control has a nifty event called NotInList.  If the LimitToList property of my combo box is set to Yes, and I type a value that is not in the drop-down list, the NotInList event will fire.  This will allow me to capture this event and add some programming to it. 

Before I go further, I should explain that a combo box can have its drop-down list (the RowSource property) filled by a query, a field list or a value list.  The scenario I'm addressing here is a the row source filled by a query from a separate look-up table.

So suppose I have a form where I want to fill in Employee information including what Training they've had. There's a combo box on the form called cboTraining that is filled from a table called tbxTraining that lists the available training opportunities (Word, Excel, etc.).  Now suppose further that an employee takes a new type of training that is not already in the training table, say Access.  Since I have LimitToList set to Yes, I can't just type "Access" into the training field.  I have to select an item in the list.  But I also don't want my data entry person to have to stop what they're doing, open the tbxTraining table, add the value, then go back to entering data.

This is where the NotInList event comes in.  I can programmatically add the value to my tbxTraining table, refresh the combo box, and the data entry person can just continue on.

To add code to the combo's NotInList event, I open the form in design view and open the the property sheet of the combo box.  In the NotInList event, choose [Event Procedure] in the drop-down list.  The VBA editor will open with the first and last line of the subroutine already created.  Something like this:

Private Sub cboTraining_NotInList(NewData As String, Response As Integer)

End Sub

What I'm going to discuss now is the code that goes between those two lines.

There are a number of different ways to implement adding a value to the drop-down list.  Some are easier but dangerous.  Others are harder to implement, but safer.

The easiest thing for me to do is to just automatically add a new value to my lookup table when a new type of training is added to the transaction table. To do this, I would add something like the following to my NotInList event of my combo box:

Private Sub cboTraining_NotInList(NewData As String, Response As Integer)
Dim strsql As String
MsgBox "Training type does not exist. Creating new Record in Training table."
strsql = "Insert Into [tbxTraining] ([Training]) values ('" & NewData & "')"
CurrentDb.Execute strsql, dbFailOnError
Response = acDataErrAdded
End Sub

You do want to be a little careful about allowing this depending on the data entry people. It's possible to get multiples of training with slight variations. For instance, a data entry person could add "Access" or "Access Database" to the training table, both of which would refer to the same type of training. I'm not saying this shouldn't be implemented, but you do need to be careful how you do it.

A little safer is to ask the user if they want to add it. That requires only a slight variation:

Private Sub cboTraining_NotInList(NewData As String, Response As Integer)
Dim strsql As String, x As Integer
x = MsgBox("Do you want to add this value to the list?", vbYesNo)
If x = vbYes Then
strsql = "Insert Into [tbxTraining] ([Training]) values ('" & NewData & "')"
CurrentDb.Execute strsql, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

This can still give me logical duplicates if my user isn't careful. (Sometimes they'll just say "yes" without ever thinking about it, which is just as bad as the first option).

Another way that is even safer is to programmatically open the maintenance form (say, frmTraining) when the user types a value that isn't in the list, allow them to add the record manually, then save and return to the record in the transaction form.

Private Sub cboTraining_NotInList(NewData As String, Response As Integer)
Dim strsql As String, x As Integer
x = MsgBox("Do you want to add this value to the list?", vbYesNo)
If x = vbYes Then
DoCmd.OpenForm "frmTraining"
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

There are other variations on this theme.  On my website, I have a small sample database called NotInList.mdb which illustrates these and other options.

.

Access 2010: Unrecognized Database Format

I've been seeing more and more people having problems with the Unrecognized Database Format when creating or modifying an Access 2007 (accdb) format database in Access 2010 and then trying to open it again in Access 2007.

The issue appears to be that Access 2010 does not have its own file format.  When you add a feature that is specific to A2010 (like the navigation control, data macro, or calculated column), that file becomes forever unreadable to A2007, even if the new feature(s) are removed.  The solution appears to be:

1. Remove all the new features from the file in A2010
2. Create a new, blank accdb format database in A2010
3. Import all the objects from the old database to the new. 

The file will then be readable by A2007.  The following link explains it more in detail: http://msdn.microsoft.com/en-us/office/cc907897.aspx.

I'm told that it is also possible to make the original file A2007 readable by removing the A2010 features and then making a minor edit to the file in a Hex editor.  But I'd be wary of that approach.  Importing the objects to a new database is safer, more reliable, and frankly simpler.

New Sample: Form_TreeView_BOM

Form_TreeView_BOM

by A.D. Tejpal

This sample db demonstrates extraction of complete hierarchical chain of sub-assemblies and components for the selected item. The user can select the desired item either by selection via combo box or by using the navigation buttons on the master subform at top left, bound to the master table T_PartMaster. Fields PartName and UnitPrice are available for user interaction (i.e. editing or new entry). UnitPrice is to be entered only for base parts (items which are not meant to have any child assemblies or components)


For the selected item, complete hierarchical chain of sub-assemblies and components gets displayed in the lower subform at right. For base parts, total quantity as well as total price are also shown in respective columns. Summary information (like maximum nesting levels, number of distinct assemblies and base parts as well as total cost of base parts needed for the selected item) gets depicted in label caption at top of this subform. For convenient analysis, the user can select any of the following styles of display, via option group below this subform:

(a) All parts (Complete hierarchical chain).

(b) Assemblies only - arranged as per relevant nesting level.

(c) Assemblies only - Straight.

(d) Base parts only - arranged as per relevant nesting level.

(e) Base parts only - Straight.

Simultaneously, the hierarchical chain of sub-assemblies and components gets depicted as tree view. Total cost of base parts (i.e. items which have no child assemblies or components) also gets depicted at bottom of tree view. Contents of tree view remain in step with the latest selection (out of five listed above) in option group.

Also, there is two way synchronization between tree view and the lower subform at right. If the user selects any node in tree view, corresponding record in subform becomes the current record. Conversely, if the user navigates to any record in the subform, corresponding node in tree view assumes selected state. PartID of matching record in the subform gets highlighted in a manner similar to the shade signifying selected node in treeview.

As the user navigates through master subform at top left, the contents of tree view as well as the two subforms at right keep getting updated as per current selection (treating current PartID on master subform as the top item for which hierarchical chain is to be extracted).

You can find the sample here: http://www.rogersaccesslibrary.com/forum/topic545_post557.html#557

.

New Sample: RollingAverages.MDB

RollingAverages.MDB

by Roger Carlson

This sample illustrates two different ways to create a "Rolling Average" query.  It averages the latest 13 months in the sequence.

Sequence	tWeek	tValue	RollingAverage
20	Week 26	10	39.8461538461538
19	Week 25	11	43.8461538461538
18	Week 24	22	48.4615384615385
17	Week 23	44	47.6923076923077
16	Week 22	55	45.4615384615385
15	Week 21	44	46.1538461538462
14	Week 20	22	45.4615384615385
13	Week 19	77	48.5384615384615
12	Week 18	88	46.1666666666667
11	Week 17	11	42.3636363636364
10	Week 16	74	45.5
9	Week 15	35	42.3333333333333
8	Week 14	25	43.25
7	Week 13	62	45.8571428571429
6	Week 12	71	43.1666666666667
5	Week 11	12	37.6
4	Week 10	15	44
3	Week 9	64	53.6666666666667
2	Week 8	35	48.5
1	Week 7	62	62

The first two use DCount and the second two use a Sub Query.  You must identify a unique column in the query to create the sequence on.  I used an autonumber ID field, which while in order, has gaps in it. 

 The DCount method creates an updateable recordset.  The SubQuery method produced non-updateable recordsets.

 You can find the sample here: RollingAverages.mdb.

.

What’s the Difference Between Early Binding and Late Binding?

Some time ago, I ran into this question on the internet:

Question:

This is something I've never really figured out about Office Automation. These all seem to be equivalent. Is there a preferred version?

Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Set objXLApp = New Excel.Application
Set objXLBook = objXLApp.Workbooks.Open("C:\MyFile.xls")

------

Dim objXLApp As Object
Dim objXLBook As Object
Set objXLApp = New Excel.Application
Set objXLBook = objXLApp.Workbooks.Open("C:\MyFile.xls")

------

Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("C:\MyFile.xls")

-----

Dim objXLApp As Object
Dim objXLBook As Object
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("C:\MyFile.xls")

------
The only difference I can find is the last one does not require a Reference to Excel set. Any advantage to or against this?

Answer:

The difference is between what's called Early Binding and Late Binding.

Early binding gives you faster access at runtime to an object's methods and properties and a smaller executable. This is because early binding lets the compiler "hard code" in the links between the app and the object. Early binding also ties you to whatever object is specified at design time because, under the hood, early binding uses the object unique identifier to flag all references. To use early binding you must instantiate the object with the New keyword. At runtime, New finds the object in the Windows registry using a direct access based on the object's unique identifier.

Late binding gives you slower access at runtime and a larger executable because code to search for the object's methods and properties that you ask for must be searched for at runtime. Late binding allows you to load a variety of different objects provided only that the object has the right method names (with the right parameters). To use late binding you must instantiate your object with CreateObject, which takes longer because the code performs a lookup in the registry using the object's ProgId.

To get IntelliSense support at design time you must declare your variable as a specific datatype (i.e. not "Object"). To use IntelliSense you must also add a reference to the object's library which is where the definition of the object's datatype is held. However, you can still use either early or late binding by using either New or CreateObject (respectively) to instantiate the object.

So, the first code sample is an example of Early Binding with IntelliSense because it instantiates the object with the New keyword and declares the variable with its datatype.

The second code sample is an example of Early Binding with without IntelliSense because it instantiates the object with the New keyword, declares the variable as "Object". This is probably the least useful because it requires a reference set but still doesn't give IntelliSense.

The third sample is an example of Late Binding with IntelliSense because it does not use the New keyword to instantiate the object, but it does declare the variable with the datatype.

The last sample shows Late Binding without IntelliSense.

Traditionally, Access has been notoriously bad at resolving references at runtime when a new version of a library was installed on the computer or when the Access application was moved to a different computer.

To avoid this problem, I've made it a practice to use CreateObject to instantiate the object because it allowed me to avoid having to add a reference to my Access project. However, to avoid giving up IntelliSense, I'll add a reference to the object's library at design time and declare their variables with a specific datatype. Then, when it was time to release the application, I'll remove the reference to the library and change the declarations to use "Object" rather than a specific datatype.

In terms of the above samples, I'll design using sample 3 and convert it to sample 4 when I put it into production.

Access 2010 Web Databases: Other Compatibility Errors

This article has moved: Access 2010 Web Databases: Other Compatibility Errors

Access 2010 Web Databases: Viewing Tables In SharePoint

This article has moved: Access 2010 Web Databases: Viewing Tables In SharePoint