List Box and Date Part Two

Continued from LIST BOX AND DATE PART ONE

When working with date values in ListBoxes, it's necessary to convert the selected values into a compatible format before using them in data processing tasks. In this section, we’ll explore two additional examples that use different date expressions. Although the resulting output remains the same, the methods of specifying the date parameters will vary.

Typically, we can filter data using a date range — by entering a start date and an end date in TextBoxes on a Report parameter Form, or by entering these values directly into parameter queries to extract records from a source table or query. However, in the examples that follow, we’ll approach this task differently to gain some insight into working with date-related expressions in Microsoft Access VBA.

Modifying the Form.

1. Open the Form LISTBOXDATE that was created in the earlier example in Design View.

2. Copy the ListBox and paste it into the same area of the Form. Drag it and place it on the right side of the ComboBox. See the sample image given below:

   

   Right-click to open the Large Image in a New Window.

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 point, let’s focus on a few important property settings of the List Box.
   First, check the Row Source property. In this example, the List Box items are entered as value pairs, such as 01; "Jan" for January, and so on for the other months. The Column Count property is set to 2, meaning the List Box contains two columns — the numeric value (e.g., 01) in the first column and the corresponding month abbreviation (e.g., Jan) in the second.

   However, when the List Box is displayed, only the second column (Jan, Feb, etc.) appears. This is because of the Column Widths property, which is set to 0";1.5". The first column has a width of  0 inches, effectively hiding it from view.

   Even though it’s hidden, the Bound Column property is set to 1, which means that when a user selects a month, the value from the first (hidden) column — such as 01 — is actually the one returned by the List Box.

   Finally, the Default Value property is initialized to 1, ensuring that if the user doesn’t make a selection, “Jan” (the first item in the list) is chosen by default.

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 with filtered output data using the current value in the TextBox named Method2. Select different Values in the Year ComboBox and the new ListBox, and try it again. Check the accuracy of the filtered data.

    

    Right-click to open the Large Image in a New Window.

    NB: If the Query displays an error, then try to link the essential Library Files to your Project. Visit the Page CommandButton Animation for details of Library Files and follow the procedures explained there. The Orders Table doesn't have all twelve months of data except for the Year 1997. Check for the Range of months available in the 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 ListBox and the ComboBox, the value in the TextBoxes Method2 and Method3 also changes. Close the Datasheet View of the Query before clicking the CommandButton with a different selection of values.

14. Click on the CommandButton Display-3 to open the Query DISPLAY3_LISTBOX with the filtered output using the TextBox named Method3 Value.

NB: You must change the Visible Property of these TextBoxes 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 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,  but here we turned it off.

When you open the Form containing the ListBoxes (with their Multi-Select property set to either Simple or Extended) for the first time, the TextBoxes that depend on expressions using values from these ListBoxes will automatically display results based on the Default Value property, if one is defined. However, once you start interacting with the ListBox—by clicking on an item—the TextBoxes may display #Error or become blank.

When the Multi-Select property is set to Simple, you can select or deselect items by selecting one at a time.
When it is set to Extended, you can:

• Select a range of adjacent items by clicking the first and last items while holding down the SHIFT key, or by clicking and dragging the mouse across the list (without using SHIFT).

• Select non-adjacent items by holding the CTRL key and clicking on each desired item individually — just as you would in Simple mode.

Set the Multi-Select Property value to None (default setting) because the examples presented here work on a single item from the ListBox.

• 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

Date Selection ListBox.

One of our dedicated readers, Mr. Nick Els from South Africa, has proposed an insightful idea for an Article focusing on the practical applications of ListBoxes with date-related settings. This Topic is split into a two-part series to explore the topic's different aspects. Please review our previous posts on Selected ListBox  Items and Dynamic Query, and Create a List from Another ListBox for a comprehensive understanding leading up to this series.

Descriptive Month names or their numerical form combined with Year values from ComboBoxes or ListBoxes can be used in various ways to compare with Date Field values in Queries for filtering data. I used the term variety because all methods require creating expressions at the Query level or in a  TextBox on a Form to reformat the values into a compatible type before using them as criteria. We will split this article into two parts instead of overcrowding it 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. We select one or more Values from List Boxes and use them with queries or VBA Routines to filter data from underlying tables, as we did in the earlier examples with ListBoxes.

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

There are a few important aspects to understand about the Multi-Select property settings of List Boxes,  their advantages, limitations, and how they affect expressions that reference selected items in Text Boxes or Query Criteria. These topics will be explored in greater detail in the second part of this article, rather than delving too deeply into them here and risking unnecessary complexity at this stage.

Get Northwind Sample Tables.

1. Download the following four tables from the Northwind.mdb sample database. For now, we will be using only the Orders table. However, since the Orders table contains references to other tables, missing those related tables in your project may cause errors when opening queries based on the Orders table.

   If you’re unsure where the Northwind.mdb sample database is located, refer to the page “Saving Data on Forms Not in a Table” for location details and reference instructions.

   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 the View Menu.
   

   Designing a Form with a List Box.

4. Deselect the Control Wizard (top-right control on the Toolbox), if it is in selected state. Select the ListBox Tool and draw a ListBox as shown in the design below.

   

   Right-click to open Large Image in New Tab/Window.

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 that we have disabled in Step 5. Select the ComboBox Tool and draw a ComboBox at the top and to the right of the ListBox. 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 ComboBox:
   • 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 TextBox below the ComboBox and change its Child Label Caption to Method-1. Select the TextBox and change the following Properties:
   • Name = Method1
   • Control Source = =Format(DateValue("01" & "-" & [List1] & "-" & [cboyear]),"yyyymm")

   The Visible Property of this control is set to No to hide it, if needed.

10. Create two CommandButtons below the List Box.
11. Change the first CommandButton's Name Property to cmdDisplay0 and change the Caption Property to Display-0.
12. Create a second CommandButton 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 set references to the ListBox, ComboBox, and TextBox 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.

    

    Right-click to open the Large Image in a New Window.

17. By default, January is selected in the ListBox, and 1997 is set in the Year ComboBox as the default selection value.

    The TextBox below the ComboBox displays the result of the formula that is written using the ListBox's current month selection and combined with the ComboBox value.

    The two Queries were created to experiment with different methods and to reference the ListBox and ComboBox contents.

18. Select a month from the ListBox. Select a different Year in the ComboBox, if needed (but be careful with the month selection because not all twelve months' data are available except for the year 1997 in the Orders Table).
19. Click on the CommandButton 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 ListBox and the ComboBox, respectively. Close the Datasheet View of the Query before trying it with different settings.

    NB: If the Query displays an 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 Design view and check the criteria settings to compare the Order Date with the settings on the Form.

    Create 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, set a direct reference to the selected month in the ListBox, and the second column criteria are set with a reference to the current value of the ComboBox.

21. Click the CommandButton with the Caption Display-1.

The second Query DISPLAY1_LISTBOX will open 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 the List Box and the Combo box respectively, together and referenced from the Query criteria row.

Study the expressions written in the Queries and form controls, and try to understand how they work. You may create TextBoxes and Queries using the same ListBox and ComboBox values and try them out to gain 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.

Create List From Another ListBox.

If you haven’t tried the earlier example, “Selected List Box Items and Dynamic Query,” please visit that page and complete it before proceeding. The same table and queries created in that example will also be used here.

Here’s a brief overview of what you need to do before continuing:

1. Import the Orders table from the Northwind.mdb sample database.

2. Create two Select Queries by copying and pasting the SQL strings provided below into the SQL editor window.

3. Save the queries using the suggested names.

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 used a method where selected items are used directly as criteria for the output query. In this example, we’ll achieve the same result using a different approach — in several creative ways.

When you create a Form using the Microsoft Access Form Wizard, it displays a list of available fields from the source table or query in one list box and allows you to select which fields to include on the form. You can move the selected fields to another list box and add or remove fields before proceeding to the next step.

We’re going to create something similar to that.

To try this method, we’ll use two list boxes on a form:

• When the form opens, the first list box will be populated with Order Numbers and Customer Codes from the source query OrdersinQ, which we used in the earlier example, Selected List Box Items and Dynamic Query.

• The user can then select one or more items from the first list box and click a command button with a right-arrow (>) indicator to move the selected items to the second list box — while simultaneously removing them from the first.

A sample image showing the form in action is provided below.

The List Boxes Image

Right-click to open Large Image in New Tab/Window.

If the user changes her mind and wants to remove one or more items from the second list, she can do so by highlighting the desired items and clicking the second CommandButton (with the left arrow < as its indicator). The selected items will then be moved back into the original list and removed from the second list box. The user can repeat this process any number of times until satisfied with the final selection, and then click the Preview Orders button to open the output query OrdersOutQ, or a report or form based on it.

The selected order numbers are extracted from the list and used to redefine the SELECT query OrdersOutQ, as we did in the previous example.

Since this is something different from what we did earlier, it requires an additional ListBox and three more event procedures to handle item transfers and interactions.

The Form_Load() event procedure populates the first ListBox, instead of assigning the OrdersInQ query as its row source. This design gives the user full control to move items freely between the two lists while automatically removing moved entries from the source list.

Because of this dynamic behavior, we cannot use the OrdersInQ query as the ListBox row source, as we did in the earlier example. Instead, we must populate it through VBA code to maintain full control over the item transfers.

Preventing Multiple Item Selection

We can easily implement another variation of this technique with just a single line change in each of the click event procedures of the CommandButtons—and even remove both CommandButtons entirely.

Simply change the header line
Private Sub cmdIn_Click() to Private Sub List1_Click()

for the first CommandButton, and likewise change

Private Sub cmdOut_Click() to Private Sub List2_Click()

for the second CommandButton. With these minor edits, both CommandButtons become unnecessary and can be safely deleted from the form.

However, note that multiple-item selection is not possible because each item is transferred immediately when clicked.

You now have three distinct methods for working with ListBoxes—each suited to different scenarios in your projects. Use them as needed, and demonstrate your versatility in handling ListBox interactions with creative approaches.

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 existing List Box, then copy and paste it to the right side of the form, leaving enough space between the two List Boxes to accommodate the Command Buttons (as shown in the design above). Change the Name property of the newly created List Box to List2, and verify that all other property settings match those of the original List Box. Finally, update the Caption of the attached label to “Selected Orders.”

5. Create two small CommandButtons between the ListBoxes. 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 CommandButton 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 as ORDERLIST.

9. Display the VBA Code Module of the Form (View -> Code). Copy and paste the following code 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] appears 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 select any number of items one by one in the first ListBox, and click the Command Button with the > indicator. The selected items will move from the first list into the second ListBox.

12. Select one or two items from the right-side ListBox and click the Command Button with the < indicator. The selected items will move back to the end of the first list. Try out the selection method 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 preparation of a Report or design a Form 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

Dynamic Query.

The Billing Department of Northwind Traders selectively processes customer orders for shipment. The Order Numbers are displayed in a ListBox, allowing users to select specific orders for processing. The selected order records are retrieved from the Orders table, after which the corresponding customer invoices and shipping documents are prepared. The system also provides the flexibility to process all orders listed in the ListBox when required.

The items displayed in the ListBox are records retrieved from the main Orders file based on a specified date range. By adding two text boxes to capture the start date and end date, users can easily control which records appear in the list. However, for simplicity in this example, we will focus on working directly with the items selected from the ListBox.

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 has not already been done in our earlier examples. If you don't know 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 the 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 Orders for 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 is changed (the criteria part), based on the selection of items from the ListBox.

   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 the View Menu.

6. Ensure that the Control Wizard button (top right 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.

   

   Right-click to open the Large Image in a New Window.




7. In the ListBox Wizard, select “I want the ListBox to look up the values in a Table or Query”, and then click Next.

8. In the next view, select the Queries option to display the list of available queries. Scroll through the list, locate and select the OrdersINQ query, 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 the ListBox and display the Property Sheet (View -> Properties), and change the following Property Values as given below:

    • Name = List1

    • Multi Select = Simple

12. Deselect the Control Wizard Button on the Toolbox and select the Command Button tool, and draw a CommandButton underneath the List Box.

13. Select the CommandButton, display the Property Sheet if it is not visible, and change the following property Values:

    • Name = cmdView

    • Caption = View Orders

14. Create another CommandButton 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

    

    Right-click to open the Large Image in a New Window.

    
    

    Test Run of the Program.

16. Open the Form in Normal View. Select a few Orders one by one in the ListBox. You can deselect the desired item again by clicking it, or click the Reset CommandButton to deselect 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 the Orders View Command Button without making any selection, or after clicking the 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 a 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

Database Daily Backup Procedure.

If your database is installed on a Local Area Network (LAN), regular backups are typically performed daily, weekly, monthly, quarterly, and yearly. The Network Administration Team manages these backup media and stores them securely in fireproof cabinets located away from the computer center.

In the event of database corruption or loss, you can request to restore it from backup. Send a request to the Network Administrator providing details such as your database file path and the desired backup date. However, the actual file recovery may take several hours or even a few days, as the backup storage media must be transported from the remote location before restoration.

If your project maintains its own independent backup routine and performs backups regularly, you can avoid relying on the centralized network backup system. This ensures faster database recovery and ensures the Application's downtime remains minimal.

You can secure the Database Objects (Forms, Queries, Tables, Reports, etc.) from within the Database, but the physical Database File's safety is a concern. When the database resides on a network location, multiple users — beyond your designated application users — may have access rights to that shared folder. This can pose a security risk, as the database is not fully protected in such an environment.

You cannot change the access rights of 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 the Server to the Local Disk, or vice versa, with a DOS Batch File. The Backup should run immediately when the Database is opened for the first time of the day.

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 backup 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 when the database is opened the first time that day. It should not run again on subsequent database closing and opening activities on the same day. 

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 fields 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 it.

   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, it stops the program. Replace the expression bkupdate = date() with the expression bkupdate+7 >= date() to schedule the Backup to run at weekly intervals on a particular Day of the week.

The VBA Code creates a DOS Batch File in the same folder as your Database and runs it. The DOS Copy command copies the Database from the LAN Server to the User's local Drive. 

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

A delay loop is built into the routine to pause the program for 10 seconds for the DOS command to complete. 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 work with the Database before the copy operation is complete. You may increase or decrease this value based on the size of your Database, or after trial runs to determine the approximate time it takes.

The control table's bkupdate Field is updated with the current date immediately after the Copy operation. This ensures that the Program will not run in subsequent Database Sessions on the same day. If your Application has a Back-End Database, move this table into the Back-End and link it to the Front-End Database. 

If your application is installed on a Network and shared by multiple users, you can examine which workstation holds the latest backup copy from the Workstation field.

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 a 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 can change the Database 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 backup_ctrl Table into your other Projects and run it.

Download

Download Demo Daily Backup

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

Read More