List Box and Date Part Two

Continued from LIST BOX AND DATE PART ONE.

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 the same, but data selection parameters are used differently.

We can Filter Data using Data Range parameters, i.e., by entering Start-Date in a TextBox 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 into the same area of the Form. Drag and place it on 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 to a 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 are displayed, these values should appear in two columns, value 01 in the first column and Jan in the second. But here the value Jan from the 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 of 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 to take Jan as the default value if nothing is selected by the User.

5. Create two TextBoxes to the left of the List Box and below the other TextBoxes. 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), and add the following VBA Code into the Module by copying and pasting it below the existing Code:

    Private Sub cmdDisplay2_Click()
    Me.Refresh
    DoCmd.OpenQuery "Display2_listbox", acViewNormal
    End Sub
    
    Private Sub cmdDispaly3_Click()
    Me.Refresh
    DoCmd.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 years' 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 TextBoxes set to No to keep them hidden from your Application Users. Study the expressions written in the TextBoxes 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 TextBoxes with the expressions using values from the List Boxes will be loaded with the values calculated from the default values, if the Default Value Property is set to some value. But when you click on an item in the List Box the TextBoxes will show either #Error or will be Empty.

When the Multi-Select Property is set to 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, then 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 of items without the use of the SHIFT Key. You must hold CTRL Key and click on items one by one for random selections as we did with the Simple value setting.

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

• Double Action Command Button
• Sending Alerts to Workstations
• Refresh Dependent Combo Box Contents
• Filtering Data for Different Users
• Crosstab Union Queries for Charts

Read More

List Box and Date Part One

Introduction.

One of our dedicated readers, Mr. Nick Els of South Africa, has proposed an insightful idea for an article focusing on the practical applications of List Boxes with Date-related settings. This marks the beginning of a two-part series dedicated to exploring this specific topic. We encourage you to review our previous posts on Selected List Box Items and Dynamic Query and Create List from Another List Box for a comprehensive understanding leading up to this series.

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

List Boxes can be created in Data Entry Forms, Main Switchboards (Control Screen) for opening Forms or Reports, or on Report Parameter Forms for use in Queries for Data Processing tasks, and so on. One or more Values from List Boxes can be selected and used directly with 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 a 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 needing clarification.

Get Northwind Sample Tables.

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 the Orders table and this may cause errors while opening the Query created on the 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.
   

   Designing a Form with List Box.

4. De-select the Control Wizard (top right-side control on the Toolbox) if it is in the selected state. Select the List Box Tool and draw a List Box as shown in 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.
    
    

    The Form Class Module Code

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.

    Sample Queries.

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

    Test Runs.

16. Open the Form LISTBOXDATE in Normal View.

    

17. By default, January month is selected in the List Box and the Year 1997 is set in the Year Combo Box as the 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 the month and the Combo Box value combined.

    The two Queries, that we have created, use 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 the 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 Years 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 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.

20. Open the first Query DISPLAY0_LISTBOX in the 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 extracting 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 are 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 TextBoxes 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.

• Sending Alerts to Workstations
• Refresh Dependent Combo Box Contents
• Filtering Data for Different Users
• Crosstab Union Queries for Charts
• Union Query

Read More

Create List from another Listbox.

Introduction.

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 a 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 in 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, which 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 the running view is given below.

The List Boxes Image

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 < as an indicator). The selected items will be moved back into the original list and removed 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 trick we cannot directly use the OrdersinQ Query as Row Source for the List Box, as we did in the earlier example.

Preventing multiple Item Selection

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 a 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 matched 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.

   Form Class Module Code.

   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 on 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 to the end of the first list. You may try the selection method this way a few more times.

13. When you have completed the selection process, click the Preview Orders Command Button.

Using the Output Query.

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 Forms to display the contents or use it as the source for other processing steps.

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

• Refresh Dependent Combo Box Contents
• Filtering Data for Different Users
• Crosstab Union Queries for Charts
• Union Query
• Percentage on Total Query

Read More

Selected List Box Items and Dynamic Query

Introduction.

The Billing Department of Northwind Traders selectively processes their Orders 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.

Get Orders Table from Northwind.mdb

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.
   
   

   The SQL for sample Query.

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.

   Design a Sample Form.

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 the 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 the earlier one. Display the Property Sheet and change the following Property Values:

    • Name = cmdReset

    • Caption = Reset

    
    

    The Form's Class Module Code.

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

    
    
    

    Test Run of the Program

16. Open the Form in Normal View. Click on a few Orders one by one in the List Box to select them. You can click on the desired 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 a Source to link with other Queries or related Tables and design Reports to print Invoices or Design Screen to display selected Order Details.

• Filtering Data for Different Users
• Crosstab Union Queries for Charts
• Union Query
• Percentage on Total Query
• Selecting Office Assistant

Read More

Database Daily Backup

Introduction

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 being 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. This may take a few hours to a few days to get done because the backup tapes or other mediums must be transported back from their storage location before they are able to complete your request.

If your project has its own backup procedure and does it on a regular basis, then you don't have to go after centralized Network backups, causing delays in restoring the Database. This will also ensure that your Application's downtime 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 this will not work. When the database is on a Network location several Users can 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.

Automatic Daily Backup

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 the 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 on the day, by any one of the users and should prevent the program from running on subsequent shutdowns and re-opening events.

Preparations

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
   'dbPathName = "\\ServerName\Accounts\MIS\MISDB.Accdb" 'If BE on LAN Server
   
   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 'increase for bigger database 
          DoEvents 'wait for 10 seconds to complete the process
       Loop
   
       DoCmd.SetWarnings False
       DoCmd.RunSQL "UPDATE Bkup_Ctrl SET Bkup_Ctrl.bkupdate = Date(), Bkup_Ctrl.workstation = Environ('COMPUTERNAME');"        DoCmd.SetWarnings True
   
       'Kill strBatchFile
     End 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

How does it Work?

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 at 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 starting to work 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 the approximate time it takes to copy.

The control table's bkupdate Field is updated with the 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 to the workstation field you can easily find out which machine has 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.

Create Batch File Manually.

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 the Code or Macro. You may define the Source, and Target Locations manually for the Copy command.

Portability Considerations

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

Download Demo DailyBakup

• Crosstab Union Queries for Charts
• Union Query
• Percentage on Total Query
• Selecting Office Assistant
• Office Assistant with Check Box Menu

Read More