Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Updating Combobox when Not in List is Active

Introduction.

This is all about the Limit to List Property setting of the Combo Box. When this property value is set to Yes you cannot type a new value, other than what you already have, into the combo box control. The user is forced to select/type an existing item from the drop-down list of the combo box.

Typing a new value into the combo box control will trigger an error message and will force you to select an item from the existing list.

Names of Months or Weeks etc. in a combo box are of constant nature and never changes or an addition is not required. But, names of new employees or names of Products, clients and so on need to be added to the combo box list, as and when the need arises.

A new item can only be added to the Source Table directly through a separate Form.  The user has to open source table editing form and add a new record.  Even if the user is prepared to do that the new item added on the source table will not appear on the combo box list on the form immediately. The user must close and open the form with the combo box to refresh and make the new item appear in the list. It is not a user-friendly approach and lot of time is wasted.


An Easy Solution.

But, we can make it very easy to the user with a VBA Program. The user can type a new value into the combo box itself and with his/her permission we can add the new item into the source table and refresh the combo box instantly.

When a new value is typed into the combo box the Limit to List property setting will run our program from On Not in List Event Procedure.  After getting confirmation from the user we can add the new item into the combo box source table and refresh the combo box.


The VBA Program.

The following program adds ProductID and Product Name into the Products Table and refreshes the cboProd Combo box to make the new item appear in the list immediately:

Private Sub cboProd_NotInList(NewData As String, Response As Integer)
Dim strProd As String, strName as String
Dim rst As Recordset, db As Database
Dim msg as string

On Error Goto cboProd_NotInList_Err

'continue without displaying error message
Response = acDataErrContinue

strProd = NewData
msg = "Product ID: " & strProd & " Not found in List!" & vbCr & vbCr & "Add it in Source File...?"

If MsgBox(msg, vbDefaultButton2 + vbYesNo + vbQuestion, "cboProd_NotinList()") = vbYes Then
    'Get Product Name from User
    strName=""
    'Repeat the loop if user presses enter key without entering a value
    Do While strName=""
        strName = InputBox("Product Name: ","cboProd_NotinList()","")
    Loop
    'Add ProductID and Name to the source Table
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Products", dbOpenDynaset)
    With rst

      .AddNew
      ![ProductID] = strProd
      ![PName] = strName
      .Update
      .Close
    End With
    
    'make combobox control source empty
    'in preparation to refresh the combobox
    Me!cboProd = Null
    'refresh the combobox
    Me.cboProd.Requery
    'now the combobox list have the new entry
    'place the new code in the combobox control source
    Me![cboProd] = strProd
 
    Set rst = Nothing
    Set db = Nothing
Else
   'if user refuse to add the new entry into source file
   'then display the error message and exit
   Response = acDataErrDisplay
End If

cboProd_NotInList_Exit:
Exit Sub

cboProd_NotInList_Err:
MsgBox Err & " : " & Err.Description,,"cboProd_NotInList()"
Resume cboProd_NotInList_Exit

End Sub

The above code is run from On Not in List Event Procedure of the cboProd Combo box.

Share:

No comments:

Post a Comment

Comments subject to moderation before publishing.

MS-Access Tips on your Finger-Tip

  • Download Android App 'MSA Guru' Version of LEARN MS-ACCESS TIPS AND TRICKS from Google Play Store.

Want to Post Free Ads on the Web


Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

Search

Popular Posts

Blog Archive

Powered by Blogger.

Follow by Email

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 Menus and Toolbars Objects Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Dictionary Object Graph Charts Query VBA msaccessQuery Calculation Combo Boxes Event List Boxes Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Key Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances Item Macros Menus 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 TreeView Control Utility 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 Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers ImageList Control Import Labels List ListView Control Logo Macro Mail Merge Main Form Memo Monitoring Nodes 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