Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Combo-Box Column Values

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

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

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads

blog.feedspot.com Microsoft Access Blogs

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Accesstips Menus and Toolbars MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery Array List Boxes Command Buttons Emails and Alerts Property Query Combo Boxes Custom Wizards DOS Commands Data Objects VBA ms-access functions msaccess functions msaccess graphs msaccess reporttricks Calculation Class Module Data Type msaccessprocess security advanced Access Security Custom Functions Macros Menus Object Reference Report Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Expression Field Type Fields Form Form Instances Join Methods Microsoft Numbering System Records Security Split SubForm Table Utility Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Controls Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export 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 Recordset Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

Base Class and Derived Object Variants

Last week we have tried an example as how to pass a Base Class Object, through the Set Property Procedure,  to become part of the Object in...

Labels

Blog Archive

Recent Posts