Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Limit to List Combo Box

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 comes from Table or Query. The User is expected to click on the Drop Down control of the Combo Box to display the list of items and select the required item to insert into the data field. The User can type Values into the Source Control of the Combo Box also.

The following two Property setting of Combo Box allows the User to select items from the available list only and prevents from entering invalid values directly into the Target Field:

  • Limit to List = Yes
  • On Not in List = [EventProcedure]

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 into the Source Table of the Combo Box Control first before they can be used from the Combo Box.

For example, assume that you have a Table with a list of Fruits (only two items in 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.

When the On Not in List Property is set with an Event Procedure; it is executed when the User enters a new value (Orange) manually into the Target 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 directly. 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.

  1. Create a new Table with a single Field Name: Fruit and select the Data Type Text.
  2. Save the Table Structure with the name: 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.
  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 FruitList 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 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.
  18. Open the Sales Form in normal view.

    Since, we have added the Combo Box on the Table Structure it is already appeared on the form.

  19. Press Ctrl++ (or click on 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 into the FruitList Table.

  22. Open the FruitList 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 into 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 to Re-Query 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.

  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 EventProcedure 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.
  9. Open it in 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 Label Yes to add the new item keyed in the Description Field into the FruitList 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 into the list and accepted in the Description Field as well.
Share:

Input Masks and Data Entry

Input masks are special group of characters that can be used in the InputMask Property of data fields to make data entry work easier in Microsoft Access.  You can use them on Forms too.

Keyed-in Text Value changes into upper case automatically or inserts slash (/) characters or other valid characters in Date Format separating Day, Month and Year segments or inserts hyphen (-) characters in Telephone Numbers separating Country Code, Area Code and Telephone Number and so on.

When large volume of information is fed into the System manually this kind of support in data entry process goes a long way in making the work easier to the User, besides maintaining/displaying the data in a standard format.

Let us look into an example. Assume that we have a Text Field for entering Telephone Numbers and the sample InputMask Property setting is given below:

(###) ###-#######;0;_

When the field is active before entering any values into the field it will look like the display below:

(___) ___-____

The key strokes that you will make to key-in the telephone number is +914792416637 but the value will be automatically positioned in appropriate places guided by the Input mask as (+91) 479-2416637. You don't need to key-in the brackets, space or hyphen in between separating Country Code, Area Code and Telephone Number.

The Inputmask Property Value is expressed in three segments separated by semi-colon.

The first segment value is the Input mask itself: (###) ###-#######.

The second segment value is 0 or 1. If the value is 1 then the separation characters (brackets, space and hyphen) are stored with the data in the field as (+91) 479-2416637 (the field size must be big enough to store all the characters). If the value is 0 then the keyed-in data alone is stored in the field as +914792416637 and the Input Mask is used for displaying the value only.

The third segment value (the underscore character in our above example) is used for filling the empty positions with underscore characters showing the data entry field size.

When the Input mask character is # in all required character positions; you are allowed to enter Digits, Spaces, Plus or Minus symbols only into the field or you may leave the entire field empty.

Input mask character 9 works in a similar way but it will not allow the usage of Plus or Minus symbols into the data field. Input mask character 0 allows to enter the digits 0 to 9 only and cannot enter Plus or Minus symbols.

Input Mask Example2 (Date Field Input Mask): 99/99/0000;0;_

Sample Data Entry: _1/_1/1984 or 01/01/1984

Date value changes into: 01/01/1984

In the Day and Month positions you are allowed to enter a Space but in the Year position all four digits must be entered because the 0 input mask will not allow Spaces and cannot leave that area empty. But, you can leave the Date Field totally empty.

Input Mask Example3 (Text Field): >CCCCCCCCCCCCCCC;0;_

Allowed to enter any Character or Space or you can leave the data field blank. The Text Value entered will be converted into Upper Case and you don't need to bother about the CAPS-LOCK settings.

If you use the word Password as Input Mask Value then whatever data you enter into the field will appear as a series of * characters and the actual value entered is not shown.

The list of Input Mask characters and their usage descriptions are given below.

Character    Description

0            Digit (0 to 9, entry required, plus [+] and minus [-] signs not allowed).

9            Digit or space (entry not required, plus and minus signs not allowed).

#            Digit or space (entry not required; spaces are displayed as blanks while in Edit mode, but blanks are removed when data is saved; plus and minus signs allowed).

L            Letter (A to Z, entry required).

?            Letter (A to Z, entry optional).

A            Letter or digit (entry required).

a            Letter or digit (entry optional).

&            Any character or a space (entry required).

C            Any character or a space (entry optional).

. , : ; - /  Decimal placeholder and thousand, date, and time separators (separator: A character that separates units of text or numbers.). (The actual character used depends on the settings in the Regional Settings Properties dialog box in Windows Control Panel).

<            Causes all characters to be converted to lowercase.

>            Causes all characters to be converted to uppercase.

!            Causes the input mask to display from right to left, rather than from left to right. Characters typed into the mask always fill it from left to right. You can include the exclamation point anywhere in the input mask.

\            Causes the character that follows to be displayed as the literal character (for example, \A is displayed as just A).

Password     Displays * in all keyed character positions.

You can use the above characters in a mixed form to get the output the way you want.

For example, the Input mask >C<CCCCCCCCCCCCCC;0;_ will change the first character into Upper Case and the rest of the Text into small letters and accepts up to 15 characters or less in the field.

Technorati Tags:
Share:

Menus with Option Group Control

We can create cascading Menus with Tab Control and Option Group Controls on Form. Several Menus can be arranged neatly one behind the other and allow the User to make the one he/she would like to see by selecting the Main Menu Option.

For example: the Sample image given below shows a Main Menu with three Options, each representing different category and Group of Sub-Menu Options.

When Data Files Option is selected in the Main Menu, the Sub-Menu at the right displays its corresponding Options. The User can click on any one of the options shown at the right to open and work with that file.

When the User selects the Option Reports in the Main Menu; Report Options will appear in the same place replacing the Data Files Options displayed earlier. The Main Menu Option Views will bring up its Sub-Menu Options replacing the earlier display. This way several Menus can be arranged and displayed at the same place with a magical touch and can be Programmed with VBA or Macros to run the detail options.

You don't need to work with any complicated VBA Programs except a few simple lines of Code and Macros. The design task is very simple and once you know the trick you can implement it anywhere in no time.

The sample Design image of the above Form is given below:

  1. Open a new Form in Design view.
  2. Select the Option Group Control from the Toolbox and draw it near to the left side of the Form in the Detail Section.
  3. Enter the three Options (Data Files, Reports and Views) pressing Tab Key in each step to advance to the next line in the Wizard.
  4. Click Finish to create the Option Group Control with Radio Button Type Controls with the Keyed-in Values as Labels.
  5. Change the Caption Value of the attached Child-Label as Main Menu and position it on top of the Option Group control as shown on the design above.
  6. Click on the outer frame of the Option Group Control to select it and display its Property Sheet (View - -> Properties).
  7. Change the Name Property Value to Frame0 and the Border color Property Value to 0.
  8. Select the Tab Control from the Toolbox and draw a Tab Control to the right of the Option Group Control (check the design image above).

    A Tab Control with two Pages will be created.  We must insert one more Page to the Tab Control.

  9. While the Tab Control is still in selected state (if it is not then click at the right of the Tab Pages) Right-Click on it to display the Shortcut Menu.
  10. Select Insert Page from the Shortcut Menu to add another Page to the Tab Control.
  11. While the Tab Control is still in selected state display its Property Sheet.
  12. Change the Name Property Value to TabCtl9.

    NB: No dot (.) at the end of the name when you change it on the control.

  13. Click on the first Page of the Tab Control to make it current.
  14. Select Option Group Control from the Toolbox and draw it on the first Page of the Tab Control.
  15. Enter the following Options (or Form Names of your own Tables in your Database) by pressing Tab Key after each option on the Wizard:
    • Employees
    • Orders
    • Order Details
    • Customers
    • Products
  16. Click Finish to complete and create an Option Group with Radio Button Style options.
  17. Display the Property Sheet of the Option Group (View - ->Properties).
  18. Change the following Property Values as shown below:
    • Name = Frame1
    • Default Value = 0
    • Border Color = 0
  19. Change the Caption of the Child-Label attached to the Option Group Control to Data Files, make its width as wide as the Option Group Control and position it above, as shown in the design image above.

    We must create two more Option Group Controls on the 2nd and 3rd Pages of the Tab Control with different set of Options.

  20. Follow Step-13 to 19 to create Option Group Control on 2nd Page of the Tab Control with the following Options and name the Option Group Frame as Frame2 and the Child-Label Caption as Report List:
    • Employee Address Book
    • Employee Phone Book
    • Invoice
    • Monthly Report
    • Quarterly Report

    You may create Report Names from your own Database replacing the above List.

  21. Create another Option Group Control on the 3rd Page of the Tab Control with the following Options or create your own Options and name the Option Group as Frame3 and Child-Label Caption as View Options:
    • View Inventory
    • View Orders
    • View Customers
    • View Suppliers

    Now, we have to write few lines of VBA Code for the Main Menu Option Group to select the detailed Options Page of the Tab Control based on the menu selection. Even though the Page Captions shows something like Page10, Page11 and Page12 (this may be different on your design) each Page is indexed as 0, 1 and 2. If you want to select the second Page of the Tab Control to display the Report Options then you must address the Tab Control Page2 in Code as TabCtl9.Pages(1).Setfocus.

    We can select individual Page of the Tab Control by clicking on it too.  But, this manual action will not synchronize with the Main Menu selection. The items on the Option Group Menu also have the index numbers 1 to the number of items on the Menu (Report List options 1 to 5).

    When the User clicks on one of the items on the Option Group Main Menu we can test its index number and make its corresponding detailed menu on the Tab Control Page current.

    In the final refinement of the Menus we will hide the Tab Pages of the Tab Control so that the Sub-Menus on them can be accessed only through program, depending on the selection made on the Main Menu by the User.

    First, let us write a small VBA Routine on the On Click Event Procedure of the Frame0 Option Group Control (Main Menu) to allow the User to select one of the Options on it and display its corresponding detailed Sub-Menu on the Tab Control. By default 1st item (Data Files) on the Main Menu will be in selected state and the Data Files list will be visible on the Sub-Menu.

  22. Display the Code Module of the Form (View - -> Code) or click on the Module Icon on the Toolbar Button.
  23. Copy and paste the following VBA Code into the Module:
    Private Sub Frame0_Click()
    Dim k
    k = Me![Frame0]
    Select Case k
        Case 1
            Me.TabCtl9.Pages(0).SetFocus
        Case 2
            Me.TabCtl9.Pages(1).SetFocus
        Case 3
            Me.TabCtl9.Pages(2).SetFocus
    End Select
    
    End Sub
  24. Save and close the Form with the name Main Switchboard.
  25. Open the Main Switchboard in normal view.
  26. Click on the 2nd Option Reports on the Main Menu to display the Report List on the 2nd Page of the Tab Control.
  27. Try selecting other options on the Main Menu and watch the sub-menu changes on the Tab Control Pages.

Now, we will write VBA Code like above example to open Data File Forms, when the User select Options from the Sub-Menu.

  1. Open the Main Switchboard in Design View.
  2. Display the Code Module of the Form (View - ->Code).
  3. Copy and Paste the following VBA Code into an empty area of the Module:
    Private Sub Frame1_Click()
    Dim f1
    f1 = Me![Frame1]
    Select Case f1
        Case 1
            DoCmd.OpenForm "Employees", acNormal
        Case 2
            DoCmd.OpenForm "Orders", acNormal
        Case 3
            DoCmd.OpenForm "Order Details", acNormal
        Case 4
            DoCmd.OpenForm "Customers", acNormal
        Case 5
            DoCmd.OpenForm "Products", acNormal
    End Sub
  4. Save and Close the Main Switchboard Form.

    For running the Report Options we will create a Macro and attach it to the Option Group Control (with the name Frame2) rather than using VBA routine.

  5. Select the Macro Tab on the Database window and select New to open a new Macro in design view.
  6. You must display the Condition Column of the Macro by selecting the Toolbar Button with the Icon Image (or similar image) given below:
  7. Write the following Macro lines, as shown in the image given below, with the appropriate Parameter Values at the bottom Property Sheet for opening each Report in Print Preview/Print:
  8. Save the Macro with the name RptMac.
  9. Open the Main Switchboard Form.
  10. Click on the 2nd Page of the Tab Control to display the Reports Option Group Menu.
  11. Click on the outer frame of the Option Group Menu to select it.
  12. Display the Property Sheet (View - ->Properties).
  13. Find and Click on the On Click Property to select it.
  14. Click on the drop-down list at the right edge of the Property and select the RptMac name from the list to insert in into the On-Click Event Property.

     

    NB: You may create another Macro/VBA Routine for the third menu and attach it to the Frame3 Option Group Menu, before doing the next step.

    In the next step we are going to remove the Pages of the Tab Control, so that the transition of the Tab Pages through Code gives a magical touch to the Sub-Menu as different Menus will appear in the same place inter-changeably.

    You can further refine the Sub-Menus by changing the dimension and position, by changing the following values same on all the three Sub-Menus on the Tab Control pages.

    • Top
    • Left
    • Width
    • height
  15. Click on the outer edge of the Tab Control (or click at the right side of the third page) to select it.
  16. Display the Property Sheet (View - ->Properties).
  17. Find the Style Property in the Property Sheet and change the value Tabs to None.
  18. Save and close the Main Switchboard Form.
  19. Open the Form in normal view and try out the Menus.
Share:

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads


Blogs Directory

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 Menus and Toolbars Accesstips MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery List Boxes Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards DOS Commands ms-access functions msaccess functions msaccess graphs msaccess reporttricks msaccessprocess security advanced Access Security Array Custom Functions Data Macros Menus Property Report Top Values VBA msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Form Join Microsoft Numbering System Records Security Split SubForm Table Utility Variables Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting Calculation ChDir Color Palette Conditional Formatting Controls Data Filtering Data Type Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Field Type Fields Filter Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Objects 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

Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Va...

Labels

Blog Archive

Recent Posts