Limit to List Combo Box

Introduction.

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 come from Table, Query, or from Value List. The User is expected to click on the Drop Down control of the Combo Box to display the items and select the required one to insert into the data field. The User can type Values directly into the Source Control of the Combo Box too.

But, the first Property setting out of the following two Property settings of Combo Box allows the user to select items from the existing list only and prevents from typing invalid values directly into the Target Field:

• Limit to List = Yes
• On Not in List = [Event Procedure]

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

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

The On-Not-in-List Event.

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

Combo Box Row Source Table.

1. Create a new Table with a single Field Name: Fruit and select the Data Type Text.

2. Save the Table Structure and name it 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.

   
   
   

   Combo Box Property Settings.

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 Fruit list 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 new 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.

    Testing Settings.

18. Open the Sales Form in the normal view.

    Since we have added the Combo Box on the Table Structure it already appears on the form.

19. Press Ctrl++ (or click on the 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 to the Fruit list Table.

22. Open the Fruit list 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 to 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 for requery 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.

Add New Item through VBA

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 Event Procedure 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.

   Trial Run Program.

9. Open it in a 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 Fruit List 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 to the list and accepted in the Description Field as well.

    

• Form and Report Open Arguments
• Indexing and sorting with VBA
• Data Upload Controls
• Auto Numbering in Query Column
• Web Browsing within Access Form

Input Masks and Data Entry

Introduction

Input masks are a special group of characters that can be used in the input mask 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 in upper case automatically or inserts slash (/) characters or other valid characters in Date Format separating Day, Month, and Year segments, or insert hyphen (-) characters in Telephone Numbers separating Country Code, Area Code, and Telephone Number and so on.

When a large volume of information is fed into the System manually this kind of support in the data entry process goes a long way in making the work easier for 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:

Input Mask of Telephone Number

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

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

(___) ___-____

The keystrokes that you will make to key in the telephone number are +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 Input Mask Property Value is expressed in three segments separated by a 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 separator characters (brackets, space, and hyphen) are stored with the data in the field, like (+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 in 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 in the data field. Input mask character 0 allows us to enter the digits 0 to 9 only and cannot enter Plus or Minus symbols.

Input Mask Date Field.

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 in Text Field.

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 in Upper Case and the rest of the Text into small letters and accepts up to 15 characters or less in the field.

Technorati Tags: microsoft

Earlier Post Link References:

• Indexing and sorting with VBA
• Data Upload Controls
• Auto Numbering in Query Column
• Web Browsing within Access Form
• Text Search Filter Web Style

Menus with Option Group Control

Introduction

We can create cascading Menus with Tab Control and Options 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 the Main Menu with three Options, each representing a different category, and a Group Sub-Menu Options.

When the 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 on 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 in the same place with a magical touch and can be Programmed with VBA or Macros to run the detail options.

Simple Interface Design and Code

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 the Design view.

2. Select the Option Group Control from the Toolbox and draw it near 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 Options Group control as shown on the design above.

6. Click on the outer frame of the Options 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 Options Group Control (check the design image above).

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

9. While the Tab Control is still in the selected state (if it is not, then click on 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 the 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.

    Data Tables Menu.

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 Options 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 Options 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 a different set of Options.

    The Reports Menu.

20. Follow Step-13 to 19 to create Option Group Control on the 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.

    Data View Menu.

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 a 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 Page Captions show 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 an 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 the program, depending on the selection made on the Main Menu by the User.

    Code for Main Menu.

    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 the 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

    Trial Run of Menu.

24. Save and close the Form with the name Main Switchboard.

25. Open the Main Switchboard in a 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.

Forms Menu.

Now, we will write VBA Code like the 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.
   
   

   Macros for Report Menu.

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

5. Select the Macro tab in the Database window and select New to open a new Macro in the 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.

   Attach Macro to Report Options.

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 Options 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 it 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 on 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 Menu.

• Data Upload Controls
• Auto Numbering in Query Column
• Web Browsing within Access Form
• Text Search Filter Web Style
• Filter with BuildCriteria Function

Digital Clock on Main Switchboard

Introduction

You may find several pieces of gadgets like Clocks, Wrist Watches, and so on around you to check the Date and Time.  How about inserting a Digital Clock into your Project's Main Switchboard Form so that your User can check the Date and Time with a casual glance in the middle of their normal work without interrupting what they are doing?

Besides all that it is a matter of style to put an animated Digital Clock on the Main Switchboard.  It takes only a few lines of VBA Code and a Label control on the Form.

We can stop the Clock when you open other Forms over the Main Switchboard and re-start the Clock with the current time when the Main Switchboard becomes active again.

If you have not started using VBA in your Databases and don't know where to begin then this is the time to learn something interesting and simple.

Simple Clock Design.

Let us do it together.

1. Open one of your existing Databases.

2. If you have a Control Screen (Main Switchboard) in your database, then open it in Design View. You can open any Form in Design View to try this out.

3. Display the Toolbox (View - ->Toolbox) if it is not visible.

4. Click on the Label Tool (button face with the letter Aa on it).

5. Draw a Label where you would like the Digital Clock to appear on the Form.

   I have used a copy of the Main-Switchboard Form from Microsoft Access Sample Database - Northwind. An image of the Form in Design View, with a Label Control inserted, is given below:

   

6. Type at least one character (any character) in the Label control otherwise the label control will get deleted when you click somewhere else.

7. While the Label Control is still in the selected state display its Property Sheet (View - ->Properties).

8. Change the following Property Values as given below:

   • Name    =   lblClock
   • Width   =  1.5938"
   • Height   =  0.3125"
   • Border Style = Transparent
   • Font Size = 8
   • Font Weight = Bold
   • Text Align   =  Center

   Now, we need two lines of VBA Code to start running our Digital Clock. One line of code to start running the IntervalTimer of the Form through Form_Load() Event Procedure, immediately after the Switchboard Form is open.

9. Click on the top left corner of the Form, where the Horizontal and Vertical Rulers meet, to select the Form. Now you will see the Form-level Property Values in the Property Sheet you displayed earlier. If you have closed the Property Sheet follow Step-7 above to display the Property Sheet of the Form.

10. Find the On Load Property and click on it to select it.

11. Select [EventProcedure] from the drop-down list box.

12. Click on the build (. . .) button at the right edge of the Property Sheet to open up the VBA Module with an empty skeleton of the VBA Sub-Routine as given below:

    The Form_Load() Event and Code.

    Private Sub Form_Load()
    
    End Sub

13. Write (or copy) the following line of VBA Code in the middle of the above lines of Code:

    Me.TimerInterval = 1000 

    This line of code says that the program control should be passed to the Form's Timer Sub-Routine (we will write the code for that next) at every one-second interval. So whatever program we will write in the Timer Sub-Routine will be executed sixty times per minute or once every second. We will write a one-line Code in the Timer Sub-Routine to take the System Date and Time and update the Caption of the Label we have created at the beginning. So, we will be seeing a continuous-time change every second.

14. Select Timer from the drop-down control at the top of the VBA Module Window.

    The opening and closing lines of the Timer Sub-Routine will be inserted into the VBA Module.  You must write the line given in the middle by giving spaces and other punctuations correctly between double-quotes (date/time format string).

    Private Sub Form_Timer()
        Me.lblClock.Caption = Format(Now(), "dddd dd, mmm-yyyy hh:nn:ss")
    End Sub

    Alternatively, you may copy and paste all three lines of the VBA Code anywhere within the Form Module.

15. Close and Save the Form.

16. Open the Form in Normal View.

    

Your digital clock will show the Current Date and Time and the change of time is updated every second.

When you open other Forms or run different programs or macros the Main Switchboard may become inactive and we can turn off the clock temporarily till the Main Switchboard become active again. This will help other programs to run faster without interruption from the digital clock taking time to update the label on the Main Switchboard.

We will write two more lines of code for the On Deactivate() and On Activate() Event Procedures to turn Off (when the Main Switchboard is inactive) and to turn On (when the Main Switchboard is active again) respectively.

1. Open the Form in Design View.

2. Display the VBA Module of the Form (View - ->Code).

3. Copy and paste the following VBA Code into an empty area of the Module.

   Private Sub Form_Activate()
       Me.TimerInterval = 1000
   End Sub
   
   Private Sub Form_Deactivate()
       Me.TimerInterval = 0
   End Sub

   Trial Run of Form Activity.

4. Save the Form and Close it.

5. Open it in the normal view.

6. Open some other Forms over the Main Switchboard from your database.

7. Click on the Title Area of the second Form and drag away from the Main Switchboard Form so that you can see the Digital Clock on it.

   You can see that the clock is not getting updated.

8. Close the second Form.

Now the Main Switchboard Form becomes active and the Clock will start updating the Date/Time again.

Technorati Tags: Microsoft, animation

• Filter with Buildcriteria Function
• Save User-Specific Parameter Values
• Creating Using Form Custom Property
• MS-Access and Data Processing-2
• MS-Access and Data Processing