Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Combo-Box Column Values

Introduction

Command Buttons are the most commonly used control on a Form. We have seen them in action in different ways and with different designs. 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 few examples for the usage of Combo-Boxes in Tables then you have them on your PC itself. Open 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 for 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 to No suggests this.

    • Field: ReportsTo

    Note: The Row Source Property value EmployeelD is taken for 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 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 creeping into the target data field. If 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
    • Shipvia

    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 purpose 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 have 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(2)
 End Sub

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

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.

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 Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object Graph Charts Query VBA msaccessQuery Calculation Event List Boxes Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference TreeView Control ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button ImageList Control 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 ListView Control Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference 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 Import Labels List Logo Macro Mail Merge Main Form Memo 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