Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Combo-Box Column Values

Introduction

The Text Boxes and Command Buttons are the most commonly used controls on a Form. We have seen them in action in different ways and with different designs. For those who have not come across those Articles before then links to them are given below; you may take a look at them.

  1. Command Button Animation
  2. Command Button Animation-2
  3. Double-Action Command Button
  4. Colorful Command Buttons
  5. Transparent Command Button

Next in line is the most preferred and familiar control on Forms; the Combo-Box Control. This can be created not only on Forms but also on Tables as well.

If you would like to see a few examples for the usage of Combo-Boxes in Tables then you have them on your PC itself. Open the Northwind.mdb, sample database from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb (MS-Office2003 pathname) and open Employees Table or Orders Table or Order Details Table in Design View and look at the Fields and Properties given under each Table listed below:

  1. Employees Table
    • Field: TitleofCourtesy

    Note: Select the Lookup Tab from the Field Properties below. Inspect the Row Source Type and Row Source Property Values. Check the other related Property settings as well.

    The Row Source Property Values are keyed-in as Constants separated by semi-colons after setting the Row Source Type value as Value List. This is a single-column list.

    To enter values in a two-column list the Column Count Property value must be 2 and based on that the values entered into the Row Source property will be read in pairs to display.

    While using the constant values (Dr.; Mr.; Miss; Mrs.; Ms.) as Source items for the Combo-Box it is assumed that values other than these are not likely to enter into this field, but if necessary you may enter them manually into the target field. The Limit To List Property Value setting of No suggests this.

    • Field: ReportsTo

    Note: The Row Source Property value EmployeelD is taken in the ReportsTo Field from the same Employees Table.

    The Row Source Type Value is Table/Query and the Row Source Value is an SQL statement, which pulls data from selected fields from the Employees table itself.

    Here, the Limit To List property value is set to Yes, indicating that values other than what is appearing in the Combo-Box (or in the EmployeelD field) will not be accepted in this field. In other words, you cannot enter a value manually into this field, other than what is appearing in the Combo-Box.

    With this setting, an automatic validation check is performed by the Combo-Box to prevent invalid values from creeping into the target data field. If the new value is required, then that must be entered into the source Table/Field of the Combo-box first before that can be inserted into the target field.

    The Combo-Boxes created in the Table Structure have more control over the data going into the target field than an external Combo-Box created on a Form. A Form or Query designed with this Table Column will have the Combo-Box attached to it when the Field is dragged from the Field-List to the Form or Query, or when you create a Form or Report with the built-in Wizards.

  2. Orders Table
    • CustornerlD
    • EmployeelD
    • Ship via

    Open the Orders Qry in normal view and click on one of the rows of Customer Column to see the Combo-Box in action on Query.

  3. Order Details Table
    • ProductID

The Bound Column Property

Normally, Combo-Box will have one or more columns of information like EmployeelD and Employee Name. When clicked the first column value is inserted (this depends on the Bound Column Property Value setting) into the target field and the description appearing in the second Column is shown for information purposes only.

The Column Width Property must be set with Values like 0.5"; 1.5" for each column and the List Width Property Value is equal to the value of all Column Widths added together.

In our above example, the EmployeelD is the value inserted into the ReportsTo field when clicked. The EmployeelD is a number, but the descriptive name is important to the User because it is more meaningful and easy to remember. So in the Combo-Box, the EmployeelD number is kept hidden by setting the first Column Width Value as 0", thereby showing only the name of the Employee in the Combo-Box.

We will go back to the first sentence of the above Paragraph and proceed further on that point. There are times that we need information from other Columns also to insert into other target fields with one click.

Assume that our Employee Combo-Box has one more column for Designation (besides EmployeelD and Name) and when clicked this information also must be inserted into another control on the Form. You can do this with a one-line VBA Code in the On Click Event Procedure in the Form Module like:

Private Sub EmployeeCombo_Click()
     Me![Designation] = Me!EmployeeCombo.Column(0,2)
 End Sub

The Row/column index numbers of Combo-Boxes are 0 based and the third column has an index value of 2.

The value 0 points to the First row in the Combo Box Value List and the value 2 picks the third column value.

Earlier Post Link References:

Share:

6 comments:

  1. [...] a look at the Combo-box usage and how to extract column value other than the bound column: LEARN MS-ACCESS TIPS AND TRICKS Blog Archive Combo-Box Column Values __________________ http://www.msaccesstips.com (Learn MS-Access Tips & [...]

    ReplyDelete
  2. [...] respective fields on the Form. Take a look at the following link for an example with VBA Code: LEARN MS-ACCESS TIPS AND TRICKS Blog Archive Combo-Box Column Values __________________ http://www.msaccesstips.com (Learn MS-Access Tips & [...]

    ReplyDelete
  3. [...] LEARN MS-ACCESS TIPS AND TRICKS Blog Archive Combo-Box Column Values Answer:Set up your combobox using the wizard and include the fields you need, from Left-to-Right. [...]

    ReplyDelete
  4. [...] LEARN MS-ACCESS TIPS AND TRICKS Blog Archive Combo-Box Column Values Answer: Quote: [...]

    ReplyDelete
  5. Muhammad Suleman TariqOctober 2, 2011 at 4:53 PM

    I have developed database in Ms access, facing a bit problem, hope you will guide me...

    1- in posting data into forms, field name is GL_code
    2- when operator enter the expense name i.e Salaries exp - manufacturing dept.
    3- Ms access should filter the name salaries, there may be 5 or 6 Ledger-names salaries, it should show all at one time.

    plz solve my problem

    ReplyDelete
  6. I think what you need is a Main-Form, Sub-Form design.

    1. Create a Main-Form with a combobox with Source Data from Expense Name field on the Header Section of the form.
    2. Change the Name Property Value of the combobox to cboExp.
    3. Design a Sub-Form with the Salaries detail data as Record Source. You need to place the Expense Name field also in the Sub-form from the salaries table.
    4. Insert the Sub-Form into the Detail Section of the Main form.
    5. Display the Property Sheet of the Sub-Form.
    6. Change the Link Master Field Property value to cboExp.
    7. Change the Link Child Field Property value to Expense Name field name.
    8. Save the Form.
    9. Open the Main-Form with the Sub-form in it in normal View.
    10. Select an Expense Name in the Combobox.

    If your design and settings of the Forms are correct you should see the corresponding Salaries Records on the subform.

    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