Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

List Box and Date Part Two



We have seen that when working with date values in List Boxes we have to convert the selected value into one form or the other before they can be used for data processing tasks. We will try two more examples here with different date expressions. In all these cases the output is same but data selection parameters are used differently.

We can Filter Data using Data Range parameters i.e. by entering Start-Date in a Text Box and End-Date in another, on a Report Parameter Screen, or by entering these values directly into Parameter Queries to filter data from the Source Table or Other Queries. But here we are trying to do it differently and get some familiarity in working with Date related expressions.

Modifying the Form

  1. Open the Form LISTBOXDATE that we have created in the earlier example in Design View.
  2. Make a copy of the List Box and Paste it in the same area of the Form. Drag and place it to the right side of the Combo Box. See the Sample image given below:
  3. Place the child Label on the top and display its Property Sheet (View - -> Properties). Change its Caption Property to List (Type-2).
  4. Click on the List Box and display the Property Sheet (if you have closed it) and change the following Properties:
    • Name = List2
    • Row Source Type = Value List
    • Row Source = 01;"Jan";02;"Feb";03;"Mar";04;"Apr";05;"May";06;"Jun";07;"Jul";08;"Aug";09;"Sep";10;"Oct";11;"Nov";12;"Dec"
    • Column Count = 2
    • Column Heads = No
    • Column Width = 0";1.5"
    • Bound Column = 1
    • Default Value = 1
    • Multi Select = None

    At this stage your attention is drawn on few property settings on this List Box. Check the Row Source Property setting. Here the List Box item values are inserted in pairs like 01;"Jan" for January and others. The Column Count property says there are 2 columns in this List Box. When the contents of the List Box is displayed these values should appear in two columns, value 01 in first column and Jan in second. But here the value Jan from 2nd column only shows up.

    The first column value is kept hidden with the Column Width Property settings 0";1.5". The first column width 0" prevents the value from showing up in the List Box. The Bound Column = 1 property setting takes the selected value from the first column, even if it is kept hidden. The Default Value = 1 says take Jan as default value, if nothing is selected by the User.

  5. Create two Text Boxes to the left of the List Box and below the other Text Boxes. Change the Caption of the child Labels to Method-2 and Method-3.
  6. Change the Property Values of the first Text Box that you have drawn now to the following Values:
    • Name = Method2
    • Control Source = =Format(DateSerial([cboyear],[List2],1),"mmm-yyyy")
  7. Change the Property Values of the second Text Box to the following Values:
    • Name = Method3
    • Control Source = =[cboYear]*100+[List2]
  8. Create a Command Button to the right of the existing two buttons and change the Property Values as shown below:
    • Name = cmdDisplay2
    • Caption = Display-2
  9. Create another Command Button and place it to the right and change the Property Values as given below:
    • Name = cmdDisplay3
    • Caption = Display-3
  10. Display the VBA Code Module of the Form (View - - > Code), add the following VBA Code into the Module by copying and pasting it below the existing Code:
    Private Sub cmdDisplay2_Click()
    Me.RefreshDoCmd.OpenQuery "Display2_listbox", acViewNormal
    End Sub
    Private Sub cmdDispaly3_Click()
    Me.RefreshDoCmd.OpenQuery "Display3_listbox", acViewNormal
    End Sub
  11. Open a New Query in Design View without selecting any file from the displayed list. Open the SQL editing Window (View - -> SQL View), copy and paste the following SQL String and save the Query with the name DISPLAY2_LISTBOX:
    SELECT Orders.*
    FROM Orders
    WHERE (((Format([orderdate],"mmm-yyyy"))=[Forms]![LISTBOXDATE]![Method2]));
  12. Open another New Query in Design View, Copy and Paste the following SQL String into the SQL editing Window and save it with the name DISPLAY3_LISTBOX:
    SELECT Orders.*
    FROM Orders
    WHERE (((Format([orderdate],"yyyymm"))=[Forms]![LISTBOXDATE]![Method3]));

    Test Runs

  13. Open the LISTBOXDATE Form in normal view and click on the Command Button Display-2. The Query DISPLAY2_LISTBOX will open up with filtered output data using the current value in the Text Box with the name Method2. Select different Values in the Year Combo Box and the new List Box and try it again. Check the accuracy of the data filtered.

    NB: If the Query displays some error then try to link the essential Library Files to your Project. Visit the Page Command Button Animation for details of Library Files and follow the procedures explained there. The Orders Table doesn't have all twelve months data except for the Year 1997. Check for the Range of months available in 1996 and 1998 year's data in this Table and select a month for available data for testing.

    Every time you select different Values in the List Box and the Combo Box, the value in the Text Box with the name Method2 and Method3 also changes. Close the Datasheet View of the Query before clicking the Command Button with a different selection of values.

  14. Click on the Command Button Display-3 to open the Query DISPLAY3_LISTBOX with the filtered output using the Text Box named Method3 Value.

NB: You must change the Visible Property of these Text Boxes to No to keep them hidden from your Application Users. Study the expressions written in the Text Boxes and their corresponding Formula written in the Query Column to compare both values.

The List Box Settings

We have used the Multi Select Property of the List Boxes in the first two Articles Selected List Box Items and Dynamic Query and Create List from Another List Box With Simple and Extended value settings respectively but here we have turned it off.

When you open the Form with the List Boxes with these settings (Extended or Simple) for the first time, the Text Boxes with the expressions using values from the List Boxes will be loaded with the values calculated from the default values, if Default Value Property is set with some value. But when you click on an item in the List Box the Text Boxes will show either #Error or will be Empty.

When the Multi Select Property is set with the Simple Value you can click on List items one by one to select one or more items or to deselect them, when clicked on it again. When the Extended value is set you can select a series of items, adjacent to each other, by clicking the first and last item by holding the SHIFT Key. Or click and drag the Mouse over the list items without the use of SHIFT Key. You must hold CTRL Key and click on items one by one for random selections as we did with Simple value setting.

We have used the Multi Select Property value equal to None (default setting) because our examples presented here works on a single item from the List Box.


No comments:

Post a Comment

Comments are subject to moderation before publishing.


Subscribe in a reader
Your email address:

Delivered by FeedBurner

Blog Directory Visit blogadda.com to discover Indian blogs


Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email


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

Featured Post

ROUNDDOWN Function of Excel

We have introduced ROUNDUP() Function of Excel in Microsoft Access earlier.  It works like ROUND() Function except that the number is alway...


Blog Archive

Recent Posts