Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

List Box and Date Part Two

Continued from LIST BOX AND DATE PART ONE

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.

  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]));
    
  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.

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.

Share:

List Box and Date Part One

One of our regular readers from South Africa, Mr. Nick Els, has made a suggestion to bring out an Article on usages of List Boxes with Date related settings. This is the First Part of a two part Series on this particular Topic. You may go through the earlier Posts: Selected List Box Items and Dynamic Query and Create List from Another List Box.

Descriptive names of Months or its Numeral form combined with Year Values from Combo Boxes or List Boxes can be used in a variety of ways for comparing with Date Field values in Queries for filtering data. I mentioned variety because all methods require creating expressions at Query level or in Text Boxes on Forms to reformat the values into a compatible form before they are being compared. We will split this Article into two parts instead of overcrowding with all of them here.

List Boxes can be created on Data Entry Forms, Main Switchboards (Control Screen) for opening Forms or Reports or on Report Parameter Forms for using in Queries for Data Processing tasks and so on. One or more Values from List Boxes can be selected and used directly in queries or VBA Routines to filter data from underlying tables, as we did in the earlier examples with List Boxes.

A reference to the selected List Box item can be set directly in a Query Criteria Row or extract the selected value into a Text box with the help of a formula (like =[List1] in the Control Source property) and reference it in Query to Filter Values from the underlying Table.

There are few things that we should know about the Multi Select Property settings of List Boxes, advantages or disadvantages and how they influence the expressions, which we build using the selected item in Text Boxes or in referencing directly on Query Criteria. These can be looked into in the second part of this Article instead of getting too deep into them at this stage and get confused.

  1. Download the following four Tables from the Northwind.mdb sample database. We will be using only the Orders table now. There are references to the other Tables from Orders table and this may cause errors while opening the Query created on Orders Table without the other tables in your Project. If you are not sure about the Location of the NorthWind.mdb sample Database, visit the Page Saving Data on Forms not in Table for its location references.

    If you prefer to use a Table from your own Project you may do so, but you have to edit the expressions to change the Table Name and Field Names presented here before they are used with your Table.

    • Orders
    • Customers
    • Employees
    • Shippers
  2. Copy and paste the following SQL string into the SQL Editing window of a new Query and save the Query with the name OrderYearQ.
    SELECT Year([OrderDate]) AS OrderYr
    FROM Orders
    GROUP BY Year([OrderDate]);
    
  3. Open a New Form in Design View. If the Toolbox is not visible then select Toolbox from View Menu.
  4. De-select the Control Wizard (top right-side control on the Toolbox) if it is in selected state. Select the List Box Tool and draw a List Box as shown on the design below.
  5. Click on the Child Label attached to the List Box and display the Property Sheet (View - - > Properties) change the Caption Property to List1 (Type-1) and position the Label above the List Box.
  6. Select the List Box, display the Property Sheet (if you have already closed it) and change the following property values as indicated against each one:
    • Name = List1
    • Row Source Type = Value List
    • Row Source = "January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"
    • Column Count = 1
    • Column Widths = 1.5"
    • Bound Column = 1
    • Default Value = "January"
    • Multi Select = None
  7. Turn on the Control Wizard we have disabled in Step 5. Select the Combo box Tool and draw a Combo Box at the top and to the right of the List Box. Select the OrderYearQ Query, that we have created in Step-1, from the Queries List.
  8. Change the following Property Values of the Child Label and the Combo Box:
    • Child Label:Caption = Year
    • Combo Box: Name = cboYear
    • Column Count = 1
    • Column Heads = No
    • Column Widths = 0.5"
    • Bound Column = 1
    • List widths = 0.5"
    • Default Value = =DMin("orderyr","orderyearQ")+1
  9. Create a Text Box below the Combo Box and change its Child Label Caption to Method-1. Select the Text Box and change the following Properties:
    • Name = Method1
    • Control Source = =Format(DateValue("01" & "-" & [List1] & "-" & [cboyear]),"yyyymm")

    The Visible Property of this Control can be set to No to hide it from Users, if needed.

  10. Create two Command Buttons below the List Box.
  11. Change the first Command Button's Name Property to cmdDisplay0 and change the Caption Property to Display-0.
  12. Create a second Command Button to the right of the earlier one and change the Name Property to cmdDisplay1 and the Caption Property to Display-1.
  13. Display the VBA Module of the Form (View - - > Code), copy and paste the following VBA Code into the Module and save the Form with the name LISTBOXDATE.
    Private Sub cmdDisplay0_Click()
    Me.Refresh
    DoCmd.OpenQuery "Display0_listbox", acViewNormal
    End Sub
    
    Private Sub cmdDisplay1_Click()
    Me.Refresh
    DoCmd.OpenQuery "Display1_listbox", acViewNormal
    End Sub
    

    Note: You must save the Form with the above name for our examples. We will be setting references to the List Box, Combo Box and Text Box on this Form to use their current values in Query Criteria Rows.

  14. Open a new Query in Design View without selecting any of the Files displayed. Display the SQL Window (View - -> SQL View), copy and paste the SQL string given below and save it with the name DISPLAY0_LISTBOX.
    SELECT Orders.*,
     Format([orderdate],"mmmm") AS MTH,
     Year([ORDERDATE]) AS XYEAR
    FROM Orders
    WHERE (((Format([orderdate],"mmmm"))=[Forms]![LISTBOXDATE]![List1]) AND ((Year([ORDERDATE]))=[Forms]![LISTBOXDATE]![cboYear]));
    
  15. Create another Query with the following SQL string and save it with the name DISPLAY1_LISTBOX.
    SELECT Orders.*
    FROM Orders
    WHERE (((Format([OrderDate],"yyyymm"))=[Forms]![LISTBOXDATE]![Method1]));
    
  16. Open the Form LISTBOXDATE in normal View.
  17. By default January month is selected in the List Box and Year 1997 is set in the Year Combo Box as default value.

    The Text Box below the Combo Box displays the result of the formula that we have written using the List Box's current selection of month and the Combo box value combined.

    The two Queries, which we have created, uses different methods to reference the contents of the List Box and the Combo Box.

  18. Select a month from the List Box. Select a different Year in the Combo Box, if needed (but be careful with the month selection because all twelve months data are not available except for the year 1997 in Orders Table).
  19. Click on the Command Button with the Caption Display-0. The DISPLAY0_LISTBOX Query will open in Datasheet view with the data corresponding to the Month and Year settings in the List box and the Combo box respectively. Close the Datasheet View of the Query before trying it out with the different settings.

    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 procedures explained there.

  20. Open the first Query DISPLAY0_LISTBOX in design view and check the criteria settings that we have created to compare the Order Date with the settings on the Form.

    We have created two columns with expressions for converting the Order Date Value into the descriptive name of the Month in the first column and to extract the Year Value in the second Column. On the criteria row we have set a direct reference to the selected month in the List Box and the second column criteria is set with a reference to the current Value of the Combo Box.

  21. Click the Command Button with the Caption Display-1.

It will open the second Query DISPLAY1_LISTBOX with the same result. But this query has only one column with an expression to compare the value with the Text Box contents on the Form. The Text box has the formula =Format(DateValue("01" & "-" & [List1] & "-" & [cboyear]),"yyyymm") to combine both Month and Year values, from List Box and Combo box respectively, together and referenced from the Query criteria row.

Study the expressions written on the Queries and on the Form controls and try to understand how they work. You may create Text Boxes and Queries of your own using the same List Box and Combo Box values and try them out differently, which will give you more insight into these methods.

Share:

Create List from another Listbox.

If you have not tried the earlier example Selected List Box Items and Dynamic Query please visit that page and try it out before continuing with this. The Table and Queries created for that example are used here also.

I will give you briefly what you should do before continuing. Import the Orders Table from the Northwind.mdb sample database. Create two Select Queries by copying and pasting the following SQL String into the SQL editing window and save the Queries with the names as suggested below.

  1. Query : OrdersinQ
    SELECT Orders.*
    FROM Orders
    WHERE ((([Orders].[OrderDate]) Between #5/1/1998# And #5/31/1998#));
    
  2. Query : OrdersOUTQ
    SELECT OrdersINQ.*
    FROM OrdersINQ
    WHERE (((OrdersINQ.OrderID) In (11067)));
    

In the earlier example we have used a method by highlighting items and using them as criterion for the output Query. Here, we are using different methods for the same result in a variety of ways.

When you create a Form with Microsoft Access Form Wizard it will give you a list of Fields from the Source Table or Query in one control and will ask you to select the required fields for the Form. You are allowed to select the fields and put them into another List Box for creating the Form with the selected list. You are allowed to Add or Remove Fields from the List Boxes before moving into the Next step. We are going to create something similar to that.

To try this method we need two List Boxes. When the Form is open the first List Box will be populated with Order Numbers and Customer Codes from the Source Query OrdersinQ, that we have used in the earlier example Selected List Box Items and Dynamic Query. The User will select one or more items from the first List Box and click a Command Button with the right arrow > indicator to move the selected items to the right side List Box and remove them from the first one. A finished sample image in running view is given below.

If the user changes her mind and wants to remove one or more items from the Second List she can do so by highlighting them and clicking the second Command Button (with the left arrow < indicator). The selected items will be moved back into the original list and removes them from the second List Box. The user can repeat this selection process any number of times till she is satisfied with the final list and ready to click the Preview Orders Button to open either the Output Query OrdersOutQ itself or a Report or Form designed using it.

The selected items' Order Numbers will be extracted from the list and used for redefining the Select Query OrdersoutQ, as we did in the previous example.

Since, the method is different from what we have tried earlier, it takes one more list box and 3 more Event Procedures to implement it.

We use the Form_Load() event procedure to populate the first list box items rather than setting the Row Source property with the Columns of OrdersinQ Query manually. The user is given the freedom to move the items from one list to the other at will and the moved items are removed from the source list. To do this kind of tricks we cannot directly use the OrdersinQ Query as Row Source for the List Box, as we did in the earlier example.

We can easily implement another trick with only one line change in each of the Click Event Procedures of the Command Buttons and can remove both the Command Buttons altogether. Change the Header line Private Sub cmdin_Click() to Private Sub List1_Click() of the first command button. Similarly change Private Sub cmdout_Click() to Private Sub List2_Click() of the second Command Button and you can eliminate both the Command Buttons.

With this method multiple item selection is not possible because when you click on an item it will move out into the other list immediately.

You have now three different methods for List Boxes that you can use suitably in your Projects as the situation demands and show off your skill in variety of methods.

  1. Open a New Form in Design View.
  2. Disable the Control Wizard on the Toolbox and create a List Box on the Form. Change the Label Caption to Orders.
  3. Click on the List Box and display the Property Sheet (View - - > Properties) and change the following Properties:
    • Name = List1
    • Row Source Type = Value List
    • Column Count = 2
    • Bound Column = 1
    • Column Width = .5";1"
    • Multi Select = Simple
  4. Select the List Box, copy and paste it to the right side, giving enough space between them to create Command Buttons (as shown in the design above) and change the Name Property of the new List Box to List2. Check and confirm that the other properties are matching with the above values. Change the child Label's Caption as Selected Orders.
  5. Create two small Command Buttons in between the List Boxes. Change the Name Property of the top one to cmdin and put a greater than symbol (>) in the Caption Property.
  6. Change the Name Property of the second Command Button to cmdout and insert a less than symbol (<) in the Caption property.
  7. Create another Command Button below both List Boxes. Change the Name Property to cmdPreview and change the Caption Property to Preview Orders.
  8. Select File - -> Save and save the Form with the name ORDERLIST.
  9. Display the VBA Code Module of the Form (View - -> Code). Copy and paste the following Codes into the VBA Module of the Form.
    Private Sub Form_Load()
    '------------------------------------------------------
    'Author: a.p.r. pillai
    'Date  : 10/05/2008
    'URL   : http://www.msaccesstips.com
    'All Rights Reserved by msaccesstips.com
    '------------------------------------------------------
    Dim db As Database, rst As Recordset
    Dim xinlist As ListBox, strlist As String
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset("OrdersinQ", dbOpenDynaset)
    Set xinlist = Me.List1
    strlist = ""
    
    Do While Not rst.EOF
        If Len(strlist) = 0 Then
            strlist = rst![OrderID]
            strlist = strlist & ";" & Chr$(34) & rst![CustomerID] & Chr$(34)
        Else
            strlist = strlist & ";" & rst![OrderID]
            strlist = strlist & ";" & Chr$(34) & rst![CustomerID] & Chr$(34)
        End If
    rst.MoveNext
    Loop
    rst.Close
    
    xinlist.RowSource = strlist
    xinlist.Requery
    End Sub
    

     

    Private Sub cmdin_Click()
    '------------------------------------------------------
    'Author: a.p.r. pillai
    'Date  : 10/05/2008
    'URL   : http://www.msaccesstips.com
    'All Rights Reserved by msaccesstips.com
    '------------------------------------------------------
    Dim xinlist As ListBox, xoutlist As ListBox, strRSource As String
    Dim listcount As Long, j As Long, strRS2 As String
    
    Set xinlist = Me.List1
    Set xoutlist = Me.List2
    
    listcount = xinlist.listcount - 1
    strRSource = xoutlist.RowSource
    strRS2 = ""
    
    For j = 0 To listcount
        If xinlist.Selected(j) = True Then
            If Len(strRSource) = 0 Then
                strRSource = xinlist.Column(0, j)
                strRSource = strRSource & "; " & Chr$(34) & xinlist.Column(1, j) & Chr$(34)
            Else
                strRSource = strRSource & ";" & xinlist.Column(0, j)
                strRSource = strRSource & ";" & Chr$(34) & xinlist.Column(1, j) & Chr$(34)
            End If
        Else
            If Len(strRS2) = 0 Then
                strRS2 = xinlist.Column(0, j)
                strRS2 = strRS2 & ";" & Chr$(34) & xinlist.Column(1, j) & Chr$(34)
            Else
                strRS2 = strRS2 & ";" & xinlist.Column(0, j)
                strRS2 = strRS2 & "; " & Chr$(34) & xinlist.Column(1, j) & Chr$(34)
            End If
        End If
    Next
    xoutlist.RowSource = strRSource
    xinlist.RowSource = strRS2
    xoutlist.Requery
    xinlist.Requery
    End Sub
    

     

    Private Sub cmdout_Click()
    '------------------------------------------------------
    'Author: a.p.r. pillai
    'Date  : 10/05/2008
    'URL   : http://www.msaccesstips.com
    'All Rights Reserved by msaccesstips.com
    '------------------------------------------------------
    Dim xinlist As ListBox, xoutlist As ListBox, strRSource As String
    Dim listcount As Long, j As Long, strRS2 As String
    
    Set xinlist = Me.List1
    Set xoutlist = Me.List2
    
    listcount = xoutlist.listcount - 1
    
    strRSource = xinlist.RowSource: strRS2 = ""
    For j = 0 To listcount
        If xoutlist.Selected(j) = True Then
            If Len(strRSource) = 0 Then
               strRSource = xoutlist.Column(0, j)
                strRSource = strRSource & ";" & Chr$(34) & xoutlist.Column(1, j) & Chr$(34)
            Else
                strRSource = strRSource & "; " & xoutlist.Column(0, j)
                strRSource = strRSource & ";" & Chr$(34) & xoutlist.Column(1, j) & Chr$(34)
            End If
        Else
            If Len(strRS2) = 0 Then
                strRS2 = xoutlist.Column(0, j)
                strRS2 = strRS2 & ";" & Chr$(34) & xoutlist.Column(1, j) & Chr$(34)
            Else
                strRS2 = strRS2 & ";" & xoutlist.Column(0, j)
                strRS2 = strRS2 & ";" & Chr$(34) & xoutlist.Column(1, j) & Chr$(34)
            End If
        End If
    Next
    xinlist.RowSource = strRSource
    xoutlist.RowSource = strRS2
    xoutlist.Requery
    xinlist.Requery
    End Sub
    

     

    Private Sub cmdPreview_Click()
    '------------------------------------------------------
    'Author: a.p.r. pillai
    'Date  : 10/05/2008
    'URL   : http://www.msaccesstips.com
    'All Rights Reserved by msaccesstips.com
    '------------------------------------------------------
    Dim strsql0 As String, crit As String, strsql As String
    Dim db As Database, qryDef As QueryDef, crit0 As String
    Dim strOrders As String, xoutlist As ListBox, listcount As Integer
    Dim j As Integer
    
    strsql0 = "SELECT Orders.* FROM Orders "crit0 = "WHERE (((Orders.OrderID) In ("
    
    Set xoutlist = Me.List2
    listcount = xoutlist.listcount - 1
    If listcount < 0 Then
        strsql = strsql0 & ";"
        GoTo nextstep
    End If
    
    For j = 0 To listcount
        If Len(strOrders) = 0 Then
            strOrders = xoutlist.Column(0, j)
        Else
           strOrders = strOrders & "," & xoutlist.Column(0, j)
        End If
    Next
    strsql = strsql0 & crit0 & strOrders & "))); "
    nextstep:
    Set db = CurrentDb
    Set qryDef = db.QueryDefs("OrdersoutQ")
    qryDef.Sql = strsql
    db.QueryDefs.Refresh
    DoCmd.OpenQuery "OrdersOutQ", acViewNormal
    End Sub
    
  10. Display the Property Sheet of the Command Buttons and check whether the setting [Event Procedure] is appearing in the On Click property. If not then select [Event Procedure] from the drop down list at the right side and save the Form.
  11. Open the Form in normal view and click on any number of items one by one in the first List Box and Click the Command Button with the > indicator. The selected items will move from the first list into the second one.
  12. Select one or two items from the right side List Box and click the Command Button with the < indicator. The selected items will move back into the end of the first list. You may try the selection method this way few more times.
  13. When you have completed the selection process click the Preview Orders Command Button.

The OrderOUTQ Query will open in Datasheet View with the filtered data with the selected Order Numbers. You can use this Query for preparing reports or design Form to display the contents or use it as source for other processing steps.

If the right side List Box is empty when you click the Preview Orders command button the OrderOUTQ Query will pick all the Items from the OrdersINQ for Output.

Share:

Selected List Box Items and Dynamic Query

Billing Department of Northwind Traders processes their Orders selectively for shipment of materials. An Order Selection Screen has been provided with a List Box on which they can highlight the Order Numbers, Filter the selected Orders from the main file and prepare Customer Invoices and Shipping Documents. They must be able to process all the Orders appearing in the list as well.

The items appearing in the List Box themselves are records selected from within a Date-Range from the Main Order File. By creating two text controls for start-date and end-date values we can control the selection of records for the list box, if needed. But for now, we will work with items selected from the List Box, for simplicity.

We need the Orders Table from the Northwind.mdb sample Database for our example.

  1. Import the Orders Table from the sample Database NorthWind.mdb, if it is not already done in our earlier examples. If you are not sure about the exact location of this Database on your PC, visit the page: Saving Data on Form not in Table for its location references.
  2. Copy and paste the following SQL String into a new Query's SQL editing window and save the Query with the Name OrdersINQ
    SELECT Orders.* 
    FROM Orders
    WHERE (((Orders.OrderDate) Between #5/1/1998# And #5/31/1998#));

    With the above Query we are selecting all the Orders for the Month of May 1998 for the List Box items. The criterion is set as a constant in the Query.

  3. Copy and paste the following SQL string into another Query's SQL editing window and save it with the Name OrdersOUTQ
    SELECT Orders.*
    FROM Orders
    WHERE (((Orders.OrderID) In (11071)));

    The second Query definition will be changed (the criteria part) dynamically based on the selection of items from the List Box.

  4. Open a new Form in Design View.
  5. If the Toolbox is not visible click the Toolbox button on the Toolbar above or select Toolbox from View Menu.
  6. Ensure that the Control Wizard button (top right button on the Toolbox) is selected. Select the List Box Control on the Toolbox and draw a List Box on the Form as shown in the design image below.
  7. On the List Box Wizard ensure that the "I want the List box to look up the values in a Table or Query" is selected and Click Next.
  8. Select Queries Option in the next view to display the Queries List. Scroll down the list and find the Query with the name OrdersINQ, select it and then click Next.
  9. Select the Fields OrderID and CustomerID from the Available Fields list and move them to the Selected List window, click Next and then click Finish.
  10. Align the List Box and its child Label as shown in the sample design above.
  11. Click on the List box and display the Property Sheet (View - -> Properties) and change the following property Values as given below:
    • Name = List1
    • Multi Select = Simple
  12. De-select the Control Wizard Button on the Toolbox and select the Command Button Tool and draw a Command Button underneath the List Box.
  13. Select the Command Button, display the Property Sheet, if it is not visible, and change the following property Values:
    • Name = cmdView
    • Caption = View Orders
  14. Create another Command Button to the right of earlier one. Display the Property Sheet and change the following Property Values:
    • Name = cmdReset
    • Caption = Reset
  15. Display the Code Module of the Form (View - - > Code). Copy and paste the following VBA Code into the Form Module and save the Form.
    Private Sub cmdview_Click()
    '-----------------------------------------------------------------------
    'Author : a.p.r. pillai
    'Date   :  01/05/2008
    'URL    :  www.msaccesstips.com
    'All Rights Reserved by msaccesstips.com
    '-----------------------------------------------------------------------
    Dim strsqlO As String, crit As String, strsql As String
    Dim db As Database, qryDef As QueryDef
    Dim strOrders As String, xoutlist As ListBox, listcount As Integer
    Dim j As Integer, selectcount As Integer
    
    strsql0 = "SELECT OrdersINQ.* FROM OrdersINQ "
    crit = "WHERE (((OrdersINQ.OrderID) In ("
    
    Set xoutlist = Me.List1
    listcount = xoutlist.listcount - 1
    
    strOrders = "": selectcount = 0
    For j = 0 To listcount
      If xoutlist.Selected(j) = True Then
        selectcount = selectcount + 1
        If Len(strOrders) = 0 Then
           strOrders = xoutlist.Column(0, j)
        Else
           strOrders = strOrders & ", " & xoutlist.Column(0, j)
        End If
      End If
    Next
    
    If selectcount = 0 Then
       strsql = Trim(strsql0) & ";"
    Else
       strsql = strsql0 & crit & strOrders & "))); "
    End If
    
       Set db = CurrentDb
       Set qryDef = db.QueryDefs("OrdersOUTQ")
       qryDef.Sql = strsql
       db.QueryDefs.Refresh
       DoCmd.OpenQuery "OrdersOUTQ", acViewNormal
    
       Set db = Nothing
       Set qryDef = Nothing
    End Sub

     

    Private Sub cmdReset_Click()
    '-----------------------------------------------------------------------
    'Author : a.p.r. pillai
    'Date   :  01/05/2008
    'URL    :  www.msaccesstips.com
    'All Rights Reserved by msaccesstips.com
    '-----------------------------------------------------------------------
    Dim xoutlist As ListBox, j As Integer
    Dim listcount As Integer
    
    Set xoutlist = Me.List1
    listcount = xoutlist.listcount - 1
    For j = 0 To listcount
      If xoutlist.Selected(j) = True Then
         xoutlist.Selected(j) = False
      End If
    Next
    End Sub
  16. Open the Form in Normal View. Click on few Orders one by one in the List Box to select them. You can click on the selected item again to de-select it or click on the Reset Command Button to de-select all.
  17. Click on the View Orders Command Button to redefine the second Query OrdersOUTQ and open it to show the selected Orders in Datasheet View.

If you need all items in the List for output then Click Orders View Command Button without making any selection or after clicking Reset Command Button.

You can use the OrdersOutQ Query with selected items as Source to link with other Queries or related Tables and design Reports to print Invoices or Design Screen to display selected Order Details.

Share:

Database Daily Backup

If your Database is installed on a Local Area Network (LAN) location then a regular server backup is done on a Daily/Weekly/Monthly/Quarterly basis by the Network Team and kept them in Fire-proof Cabinets away from the Computer Center. If something happens to your database, like database corruption or deleted by mistake etc., you can always send a request to the Computer Department giving details of location, database name and safe backup date from which you would like to restore from. This may take few hours to few days to get it done, because the backup tapes or other mediums must be transported back from its storage location before they are able to complete your request.

If your Project have its own backup procedure and does it on regular basis then you don't have to go after Network backups, causing delays in restoring the Database. This will also ensure that your Application's down time is very minimal.

You can secure the Database Objects (Forms, Queries, Tables, Reports etc.) from within the Database but when it comes to the safety of the Database File itself this will not work. When the database is on a Network location several Users have access rights to that folder, besides your Application Users, and the database is not safe there.

You cannot make a Database Read-Only under Network Security to protect it from inadvertent loss. If you do that then you cannot work with the Database.

As a precautionary measure we can take a quick Daily Backup of the Database File, from Server to Local Disk or vice versa with a DOS Batch File. The Backup should run immediately on opening the Database for the first time of the day, by any one of the users.

We can do this with a simple VBA Routine to create a DOS Batch File in the database folder and run it from there to make a copy to local drive. We need a small table with a single record to keep track of the Backup event. The backup program should run only once a day, when the database is open for the first time of the day, by any one of the users and should prevent the program from running on subsequent shut-downs and re-opening events.

  1. Create a Table with the following structure and save it with the name Bkup_Ctrl and add a single record with a date earlier than today in the bkupdate Field. Leave the other field blank.
    Table : Bkup_Ctrl Structure
    Srl. Field Name Type Size
    1. bkupdate Date/Time  
    2. workstation Text 20
    Table : Bkup_Ctrl
    bkupdate workstation
    01/05/2008 PC1-1234
  2. Copy and Paste the following Code into a Global Module of your Project and save the Module.
    Public Function SysBackup()
    '------------------------------------------------------'
    'Author: a.p.r. pillai
    'Date  : 01-Apr-2008
    'URL   : http://www.msaccesstips.com
    'All Rights Resersed by msaccesstips.com
    '------------------------------------------------------
    Dim dbPathName, j As Long, t As Date
    Dim bkupdate, strBatchFlle As String, qot As String
    
    On Error GoTo sysBackup_Err
    
    qot = Chr$(34)
    bkupdate = Nz(DLookup("bkupdate", "Bkup_ctrl"), 0)
    ' bkupdate+7 > date() for weekly backup
    If bkupdate = Date Or bkupdate = 0 Then
        Exit Function
    End If
    
    dbPathName = CurrentDb.Name
    j = InStrRev(dbPathName, "\ ")
    
    If j > 0 Then
        strBatchFlle = Left(dbPathName, j)
        strBatchFile = strBatchFlle & "bakup.bat"
        Open strBatchFile For Output As #1
            Print #1, "@Echo off"
            Print #1, "Echo :------------------------- "
            Print #1, "Echo : " & dbPathName
            Print #1, "Echo Daily Backup to C:\ "
            Print #1, "Echo :------------------------- "
            Print #1, "Echo : "
            Print #1, "Echo :Please wait... "
            Print #1, "Echo : "
            Print #1, "Copy " & qot & dbPathName & qot & " " & qot & "C:\ " & qot
    'add lines here for Back-end database or for other Files
        Close #1
    
    'Copy file
        Call Shell(strBatchFile, vbNormalFocus)
        t = Timer
        Do While Timer <= t + 10
           DoEvents 'wait for 10 seconds
        Loop
    
        DoCmd.SetWarnings False
        DoCmd.RunSQL "UPDATE Bkup_Ctrl SET Bkup_Ctrl.bkupdate = Date(), Bkup_Ctrl.workstation = Environ('COMPUTERNAME');"        DoCmd.SetWarnings True
    
        'Kill strBatchFileEnd If
    
    sysBackup_Exit:
    Exit Function
    
    sysBackup_Err:
    MsgBox Err.Description, , "sysBackup()"
    Resume sysBackup_Exit
    End Function
    
  3. Add the following line of Code in the On_Load() Event Procedure of the Startup Screen or Main Switchboard or any other Form that opens immediately after loading the Database.

SysBackup

At the beginning of the SysBackup() routine, the Program reads the last backup date from the Bkup_Ctrl Table and checks whether it matches today's date, if it does then stops the program from proceeding further. By replacing the expression bkupdate = date() with the expression bkupdate+7 > date() you can schedule the Backup to run on weekly intervals on a particular Day of the Week.

The VBA Routine creates a DOS Batch File in the same folder of your Database and Runs it. The DOS Copy command is used for copying the Database File to the User's local drive. Even though a VBA FileCopy() Function is available, this will not work from within to make a copy of the same Database.

You may modify the line to change the Target Location C:\ to a different one, if needed.

A delay loop is built into the routine to slow down the program for about 10 seconds, to give enough time for the DOS Command to complete copying the Database. Normally, the VBA Code execution will not wait for the DOS Command to complete before executing the next statement. This will also prevent the User from start working with the Database before the copy operation is complete. You may increase or decrease this value depending on the size of the Database, or after trial runs of the procedure to determine approximate time it takes to copy.

The control table's bkupdate Field is updated with current date immediately after completion of the Copy operation and this will prevent further running of this procedure in subsequent Sessions on the same day. If your Application has a Back-End Database then install this table in there and link it to the Front-End. If your Application is on a Network and shared by several Users then by referring the workstation field you can easily find out which machine have the latest Backup Copy.

The Kill strBatchFile statement (if enabled) will delete the DOS Batch File after the backup operation. The delay loop protects the DOS Batch File from this statement for about 10 seconds. Enable this line if you don't want the batch file to remain in the database folder.

You can create a DOS Batch file manually, with a Text Editor like Windows Notepad, install it in the Database Folder and run it from Code or Macro. You may define the Source and Target Locations manually for the Copy command.

The advantage of the above Code is portability and convenience. You can copy the Code and the bkup_ctrl Table into your other Projects and run it without much change or worrying about the Source or Target Location addresses of the Database.




Download Demo Database



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