Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Limit to List Combo Box

Introduction.

Combo Boxes on Tables/Forms are for inserting frequently used common values quickly and easily into Data Fields. The Source Data Values of the Combo Box come from Table, Query, or from Value List. The User is expected to click on the Drop Down control of the Combo Box to display the items and select the required one to insert into the data field. The User can type Values directly into the Source Control of the Combo Box too.

But, the first Property setting out of the following two Property settings of Combo Box allows the user to select items from the existing list only and prevents from typing invalid values directly into the Target Field:

  • Limit to List = Yes
  • On Not in List = [Event Procedure]

When the Limit to List Property Value is set to Yes, you must select/type values available in the Combo Box list only and other values keyed in manually are not accepted in the Target Field. You must add new items in the Source Table of the Combo Box Control first before they can be used for the Combo Box.

For example, assume that you have a Table with a list of Fruits (only two items on the list now: Apple and Cherry) and you are using this list in a Combo Box on the Sales Form. When the Limit to List Property Value is set to Yes; you will not be allowed to enter the value Orange into the Target Field of the Combo Box.

The On-Not-in-List Event.

When the On Not in List Property is set to an Event Procedure; it is executed when the user enters a new value (Orange) manually into the Control-Source Field of the Combo Box. We can write code in the Event Procedure to add the entered new value into the Combo Box Source Table directly (after taking confirmation from the User) and update the Combo Box on the Form.

This method can save time otherwise needed for opening and adding new items in the Combo Box source Table manually. Besides that adding new values manually in the Source Table will not automatically refresh the Combo Box contents.

Let us try this out using the above example items as Source Data.

Combo Box Row Source Table.

  1. Create a new Table with a single Field Name: Fruit and select the Data Type Text.

  2. Save the Table Structure and name it Fruitlist.

  3. Open the Table in Datasheet View and key in Apple and Cherry as two records.

  4. Close and Save the Table with the records.

  5. Create another table with the following Structure:

    Table Structure
    Field Name Data Type Size
    ID AutoNumber
    Description Text 50
    Quantity Numeric Long Integer
    UnitPrice Numeric Double
  6. Before saving the Structure click on the second Field Data Type (Text) Column to select it.

  7. Click on the Lookup Tab on the Property Sheet below.


    Combo Box Property Settings.

  8. Click on the Display Control Property and select Combo Box from the drop-down control.

  9. The Row Source Type Property Value will be Table/Query, if it is not, then select it from the drop-down control.

  10. Click on the drop-down control of the Row Source Property and select the Table Fruit list from the displayed list of Tables.

  11. Change Column Width Property and List Width Property Values to 1".

  12. Change the Limit to List Property Value to Yes.

  13. Save the Table Structure with the name Sales.

  14. Open the Table in Datasheet View and add a new record with Apple, 100, and 1.5 in Description, Quantity, and UnitPrice Fields respectively.

  15. Close and save the Table with the record.

  16. Click on the Sales Table to select it and select Form from Insert Menu.

  17. Create a Form using the Form Wizard in Column Format and save the Form with the name Sales.

    Testing Settings.

  18. Open the Sales Form in the normal view.

    Since we have added the Combo Box on the Table Structure it already appears on the form.

  19. Press Ctrl++ (or click on the New Record control on the Record Navigation control) to add a new blank record on the Form.

  20. Click on the drop-down control of the Combo Box and you will find the list of fruits: Apple and Cherry in it.

  21. But, you Key-in Orange into the Description field and press Enter Key.

    You will be greeted with the following error message:

    If you want to enter the value Orange on the Form, first you must add that item to the Fruit list Table.

  22. Open the Fruit list Table, and add Orange as a new record and close the Table.

But, this action will not refresh the Combo Box contents automatically to add Orange to the list. You have to close the Sales form and open it again before you are able to select Orange from the list. Or you must add a Command Button on the Form and write Code for requery the Combo Box contents.

What we did manually in response to the above error message can be automated by writing a VBA Routine that can be run through the On Not in List Event Procedure. You don't need to close and open the Form to refresh the Combo Box contents either.

Add New Item through VBA

  1. Open the Sales Form in Design View.

  2. Click on the Description Field to select the Combo Box control.

  3. Display the Property Sheet (View - -> Properties).

  4. Find and click on the On Not in List Property.

  5. Select Event Procedure from the drop-down list.

  6. Click on the build button (. . .) To open the VBA Module.

  7. Copy and paste the following Code into the Module overwriting the top and bottom Procedure lines already appearing in the Module:

    Private Sub Description_NotInList(NewData As String, Response As Integer)
    Dim strmsg As String, rst As Recordset, db As Database
    
    If Response Then
        strmsg = "Entered Item not in List!" & vbCr & vbCr & "Add to List...?"
          If MsgBox(strmsg, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in List") = vbYes Then
           Set db = CurrentDb
           Set rst = db.OpenRecordset("FruitList", dbOpenDynaset)
           rst.AddNew
           rst![Fruit] = NewData
           rst.Update
           rst.Close
           Me.Description.Undo
           Me.Description.Requery
           Me![Description] = NewData
           Me.Refresh
        End If
        Response = 0
    End If
    End Sub
  8. Save and Close the Sales Form.

    Trial Run Program.

  9. Open it in a normal view.

  10. Now, type the name of any fruit that is not in the Combo Box list (say Peach) in the Description field.

    You will be greeted with the following Message Box:

  11. Click the Command Button with the LabelYes to add the new item keyed in the Description Field into the Fruit List Table and refresh the Combo Box List automatically.

  12. Now, click on the drop-down control of the Combo Box and you can see that the new item is added to the list and accepted in the Description Field as well.

Share:

5 comments:

  1. Hello,
    I saw your post. I want to share some words. I have been using MS access to develop application for long time. The combo box must have its Row Source set to either a table or a query. The combo box must have the Row Source Type set to Table/Query.
    Thanks

    ReplyDelete
  2. Quite a few of the details associated with this blog post are great however had me wondering, did they seriously indicate that? One thing I have to point out is certainly your writing expertise are very good and I will be returning back again for any fresh post you come up with, you could possibly have a brand-new fan. I bookmarked your main web page for personal reference.

    ReplyDelete
  3. Immigration Lawyers...

    [...]the time to read or visit the content or sites we have linked to below the[...]...

    ReplyDelete
  4. Immigration Solicitors...

    [...]while the sites we link to below are completely unrelated to ours, we think they are worth a read, so have a look[...]...

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code