Sunday 10 July 2011

Dealing With a Combo Box Entry that is "Not In List"

Following on from my last post on Customizing an Access Combo Box, this tip is about dealing with a Combo Box Entry that is Not In List.  Basically this occurs when instead of selecting an item from the Combo Box list, the user manually types in an entry that is not one of the items from the drop down list. It works by running a small section of VBA code when the Combo Box NOT IN LIST Event fires (more about this later).  The code then creates a new record containing the user's entry in the table upon which the combo box list is based.

Let's use the example of a Combo Box on a Products Form to illustrate this.  The Combo Box is used in this example to enter the Category field of the Product record.  The Record Source of the form is tblProducts, and the Row Source of the Combo Box is tblCategory.  If you want to run a copy of this example, you can download the Not In List Example Database by clicking the link (you will need to Enable the Content if you save it a location that is not trusted).

Figure 1: The Products form with a Combo Box on the Category field.

As you can see from Figure 1 above, the Category field uses a Combo Box with a list of potential categories.  These are stored in a separate table called tblCategory, which is the value of the Combo Box ROW SOURCE property.  The screen shot shows that there are three categories - Office Equipment, Office Furniture, and Stationery.  So what happens if the user wants to create a new product record, for say, a software package? There is no existing category for Software, so once the user enters the product name - lets say it is MS Office Access 2010 - he or she is unable to select a suitable category from the drop down list.

Now might be good time to mention the Combo Box LIMIT TO LIST property (located in the DATA TAB of the PROPERTY SHEET).  When this is set to YES (which is the case in our example), Access would normally display an error message saying THE TEXT YOU ENTERED IS NOT AN ITEM IN THE LIST; it then asks the user to select an item which is, or type in text that matches one of the listed items. This message is show in Figure 2 below:

Figure 2: The default message shown when an item is not in list.
However, when LIMIT TO LIST is set to yes, Access first fires the NOT IN LIST event. This enables us to pre-empt the standard message by writing code to display a custom message of our own.  This code also gives the user the opportunity to add the new Category to the table which is the row source for the combo box list.

Let's take a look at the code used in our example database.


Private Sub ctlCategory_NotInList(NewData As String, Response As Integer)
On Error GoTo myError
    
    Dim rst As DAO.Recordset
        
    Set rst = CurrentDb.OpenRecordset("tblCategory", dbOpenDynaset)
    
        If vbYes = MsgBox("This Entry is not in list. Do you wish to add " _
                & NewData & " as a new category?", _
                vbYesNoCancel + vbDefaultButton2, _
                "New Category") Then
                
            rst.AddNew
                rst!categoryName = NewData
            rst.Update
            
            Response = acDataErrAdded
            
        Else
        
            Response = acDataErrContinue
            
        End If
       
leave:

    If Not rst Is Nothing Then
        rst.Close: Set rst = Nothing
    End If
    
    Exit Sub
    
myError:
   
    MsgBox "Error " & Err.Number & ": " & Error$
    Resume leave
    
End Sub

We enter this code into the Visual Basic Editor by clicking the three dot symbol on the far right of  ON NOT IN LIST (located on the EVENTS Tab of the Combo Box's PROPERTY SHEET).  The CODE BUILDER option was then selected from the CHOOSE BUILDER Dialogue Box.

Figure 3: The EVENTS Tab of the PROPERTY SHEET.

So how does the code work?

The first line of code was created automatically by Access when the VBA editor was opened via the ON NOT IN LIST line of the property sheet.  There are two arguments enclosed within the brackets - NewData and Response.  The first of these contains the new category value just entered by the user as a string variable.  The later relates to how access is going to handle the Not In List Event.  It's default value is 0 which represents the standard way of doing so ie displaying the error message and preventing the user from adding the new data.  Needless to say, we are going to alter this value later on in the code!

The third and fourth line of code relates to the object variable rst which represents a DAO recordset based on tblCategory, the Row Source of our Combo Box list.  Object variable rst is first declared in the Dim Statement, and then Set to tblCategory via the openrecordset method of the database object.

The fifth line of code relates to the custom message displayed to the user when he or she enters an item that is not in list.  The msgbox function displays our message as well as determining which buttons are offered, which button is the default, and the msgbox title.  The user response, ie the button the user clicks, is then processed by the If statement - so if the user clicks YES, the code branches to the code below where a new category record will be added.

This new category record is added using the addNew method of the Recordset Object.  The categoryName field is then set to the value of the NewData variable- ie the value of the new category entered by the user which was passed by Access as a parameter to the sub.  The new record is then saved to tblCategory using the Update method of the Recordset Object.

The next line of code changes the value held in the response variable (passed by Access as a parameter in the first line of the sub) via the acDataErrAdded constant.  Doing so tells access that new data can be added to the Combo Box list, and not to display the default Not In List error message.  It also Requeries the Combo Box list so that the new data appears immediately.

However, if the user had clicked the NO or CANCEL Command button mentioned above, the program flow branches to the Else Statement where it goes on to run the line of code changing the value held in the Response variable via the acDataErrContinue constant.  This tells Access not to add the new data to the Combo Box list, but continue without displaying the default Not In List message.

After the End If statement the program flow converges once again.  The last section of code (before the error handling section) deals with closing the rst Recordset Object and re-setting its value to nothing.  The sub's program flow then exits the sub via the Exit Sub statement.

9 comments:

  1. Justin, Great explanation, but shouldn't this say "no" instead of 'yes':
    However, when LIMIT TO LIST is set to yes,

    ReplyDelete
  2. Hi Sandy

    Thanks for your comment. I can understand why you are asking whether LIMIT TO LIST should be set to NO instead of YES.

    However, I can confirm that what I have written is correct. The LIMIT TO LIST property does need to be set to YES so that the NOT IN LIST Event fires (if it is set to NO the event will not fire).

    It is the firing of the NOT IN LIST EVENT which triggers our VBA Code; and it is this customized code that adds our entry to the list.

    Hope this helps,
    Justin

    ReplyDelete
  3. Thanks for this useful tip!

    ReplyDelete
  4. Justin, This is excellent help! Can I use the same code to update multiple fields in tblCategory?

    ReplyDelete
  5. Rajko - thanks for you comment. Glad you found the tip useful.

    JimP - thanks Jim. To answer your question - yes it is possible to update more than one field at a time, although I am not sure whether it would make sense to do so in the context of this particular exercise.

    The section of code which updates the field is here:

    rst.AddNew
    rst!categoryName = NewData
    rst.Update

    So if you have a field called fldThisField, you would add this line somewhere between rst.AddNew and rst.Update:

    rst!fldThisField = MoreNewData

    You can add multiple fields this way.

    Hope this helps,

    Justin

    ReplyDelete
  6. Sir, the code is updating the record immediately if item is not in the list, but i want to update the records in the table from the command button (Save Data) and not from the combo box list how should i do it

    ReplyDelete
    Replies
    1. Hi

      Am I right in understanding that you want to enter a value into an unbound textbox, click a command button to save the value into a table? If so, you might find this blog post of interest:

      The DAO Recordset in Action

      This provides an example of how to do this in relation to a Lending Library Database. The user types in the book barcode clicks a button, and a set of loan details are stored in one of the tables.

      Delete
  7. Thank you! It took a bit of frustrated thinking but I got it to work. I will be bookmarking your webpage for future reference!

    ReplyDelete
  8. Thanks for the Posting Justin. I used it to add entries to a Geographical Location table after customizing it a bit (see the code bellow) but my string comparison (If Not "ProvinceOrTerritory = '" & Me!Combo6 & "'") gives an Error 13: type mismatch.

    I would really appreciate any suggestion you might have.
    Thanks,
    Mihaela

    Private Sub Combo8_AfterUpdate()
    On Error GoTo myError

    'MsgBox "After Update - Not About to Requery Combo Box"
    Me!Combo8.Requery

    leave:

    Exit Sub

    myError:

    MsgBox "Error " & Err.Number & ": " & Error$
    Resume leave

    End Sub

    Private Sub Combo8_BeforeUpdate(Cancel As Integer)

    Dim rst As DAO.Recordset

    If IsNull(Combo8) = True Then Exit Sub

    Set rst = CurrentDb.OpenRecordset("GeographicalLocation", dbOpenDynaset)
    rst.FindFirst "CityOrRegion = '" & Me!Combo8 & "'"

    If rst.NoMatch = True Then
    Response = MsgBox("Do you wish to add " & Me!Combo8 & " as a new location.", vbYesNoCancel, "New Location")
    If Response = vbYes Then
    MsgBox "Adding New Location"
    rst.AddNew
    rst!CityOrRegion = Me!Combo8
    rst!ProvinceOrTerritory = Me!Combo6
    rst.Update
    End If
    Else: If Not "ProvinceOrTerritory = '" & Me!Combo6 & "'" Then rst.AddNew
    rst!CityOrRegion = Me!Combo8
    rst!ProvinceOrTerritory = Me!Combo6
    rst.Update
    End If


    leave:

    If Not rst Is Nothing Then
    rst.Close: Set rst = Nothing
    End If

    Exit Sub

    myError:

    MsgBox "Error " & Err.Number & ": " & Error$
    Resume leave


    End Sub

    Private Sub Combo8_NotInList(NewData As String, Response As Integer)
    On Error GoTo myError

    Dim rst As DAO.Recordset

    Set rst = CurrentDb.OpenRecordset("GeographicalLocation", dbOpenDynaset)

    If vbYes = MsgBox("This Entry is not in list. Do you wish to add " _
    & NewData & " as a new location?", _
    vbYesNoCancel + vbDefaultButton2, _
    "New Location") Then

    rst.AddNew
    rst!CityOrRegion = NewData

    rst!ProvinceOrTerritory = Me!Combo6
    rst.Update

    Response = acDataErrAdded

    Else

    Response = acDataErrContinue

    End If

    leave:

    If Not rst Is Nothing Then
    rst.Close: Set rst = Nothing
    End If

    Exit Sub

    myError:


    MsgBox "Error " & Err.Number & ": " & Error$
    Resume leave


    End Sub

    ReplyDelete