LEARN MS-ACCESS TIPS AND TRICKS

List Box and Date Part Two

a.p.r. pillaiMay 30, 2008List Boxes, msaccess forms No comments

Continued from LIST BOX AND DATE PART ONE

When working with date values in List Boxes, we’ve seen that it’s often 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 text boxes 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 a deeper understanding of how to work with date-related expressions in Microsoft Access VBA.

Modifying the Form.

Open the Form LISTBOXDATE that we have created in the earlier example in Design View.
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:
Right-click to open the Large Image in a New Window.
Place the child Label on the top and display its Property Sheet (View --> Properties). Change its Caption Property to List (Type-2).
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 set to 1, ensuring that if the user doesn’t make a selection, “Jan” (the first item in the list) is chosen automatically.
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.
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")
Change the Property Values of the second Text Box to the following Values:
- Name = Method3
- Control Source = =[cboYear]*100+[List2]
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
Create another Command Button and place it to the right, and change the Property Values as given below:
- Name = cmdDisplay3
- Caption = Display-3

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

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]));
```
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
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 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 Command Button 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 List Box and the Combo Box, the value in the TextBox with the names Method2 and Method3 also changes. Close the Datasheet View of the Query before clicking the Command Button with a different selection of values.
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 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 List Boxes (with their Multi-Select property set to either Simple or Extended) for the first time, the Text Boxes that depend on expressions using values from these List Boxes will automatically display results based on the Default Value property, if one is defined. However, once you start interacting with the List Box—by clicking on an item—the Text Boxes may display #Error or become blank.

When the Multi-Select property is set to Simple, you can select or deselect items by clicking them 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.

We 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.

List Box and Date Part One

a.p.r. pillaiMay 22, 2008List Boxes, msaccess forms 2 comments

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 marks the beginning of a two-part series dedicated to exploring this specific topic. Please review our previous posts on Selected List Box Items and Dynamic Query, and create a List from Another List Box for a comprehensive understanding leading up to this series.

Descriptive names of the Months or their numerical form combined with Year Values from Combo Boxes or List Boxes can be used in various ways to compare 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 type before they are compared. We will split this article into two parts instead of overcrowding it 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 extracted the selected value into a Text box with the help of a formula (like =[List1] in the Control Source property) and referenced 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.

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
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]);
```
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.
De-select the Control Wizard (top-right control on the Toolbox) if it is in the 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.
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.
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
Turn on the Control Wizard that 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.
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
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.
Create two Command Buttons below the List Box.
Change the first Command Button's Name Property to cmdDisplay0 and change the Caption Property to Display-0.
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.
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.

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

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.

Open the Form LISTBOXDATE in Normal View.

Right-click to open the Large Image in a New Window.
By default, January 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.
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 not all twelve months' data are available except for the year 1997 in the Orders Table).
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 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.
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 ListBox, and the second column criteria are set with a reference to the current value of the ComboBox.
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 the List Box and the Combo box respectively, together and referenced from the Query criteria row.

Study the expressions written in the Queries and in the 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, which will give you more insight into these methods.

Create List from another Listbox.

a.p.r. pillaiMay 16, 2008List Boxes, msaccess forms No comments

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:

Import the Orders table from the Northwind.mdb sample database.
Create two Select Queries by copying and pasting the SQL strings provided below into the SQL editor window.
Save the queries using the suggested names.

Query: OrdersinQ

SELECT Orders.*
FROM Orders
WHERE ((([Orders].[OrderDate]) Between #5/1/1998# And #5/31/1998#));

Query: OrdersOUTQ

SELECT OrdersINQ.*
FROM OrdersINQ
WHERE (((OrdersINQ.OrderID) In (11067)));

In the earlier example, we used a method where selected items were highlighted and 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 command button (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 approach is different from what we have implemented earlier, it requires an additional ListBox and three more event procedures to handle the item transfers and interactions.

The Form_Load() event procedure is used to populate the first ListBox dynamically, instead of assigning the OrdersInQ query as its Row Source manually. 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 directly use the OrdersInQ query as the Row Source for the list box, 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 command buttons—and even remove both command buttons entirely.

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

for the first command button, and likewise change

Private Sub cmdOut_Click() to Private Sub List2_Click()

for the second command button. With these minor edits, both command buttons become unnecessary and can be safely deleted from the form.

However, note that multiple item selection will not be possible with this approach, since each item is transferred immediately when clicked.

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

Open a New Form in Design View.
Disable the Control Wizard on the Toolbox and create a List Box on the Form. Change the Label Caption to Orders.
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
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.”
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.
Change the Name Property of the second Command Button to cmdout and insert a less-than symbol (<) in the Caption Property.
Create another Command Button below both List Boxes. Change the Name Property to cmdPreview and change the Caption Property to Preview Orders.
Select File -> Save and save the Form named ORDERLIST.

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

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.
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.
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 the selection method this way a few more times.
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 designing 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.

Selected List Box Items and Dynamic Query

a.p.r. pillaiMay 09, 2008List Boxes, msaccess forms, Queries 3 comments

Dynamic Query.

The Billing Department of Northwind Traders processes customer orders selectively for shipment. An Order Selection Screen is given with a List Box that allows users to highlight specific Order Numbers, filter those selected orders from the main database, and prepare the corresponding Customer Invoices and Shipping Documents. The system also provides the flexibility to process all orders listed in the box when required.

The items displayed in the List Box 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 the sake of simplicity in this example, we will focus on working directly with the items selected from the List Box.

Get Orders Table from Northwind.mdb

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

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.
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.
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.
Open a new Form in Design View.
If the Toolbox is not visible, click the Toolbox button on the Toolbar above or select Toolbox from the View Menu.
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.

On the List Box Wizard, ensure that "I want the List box to look up the values in a Table or Query" is selected and click Next.
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.
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.
Align the List Box and its child Label as shown in the sample design above.
Click the ListBox and display the Property Sheet (View -> Properties) and change the following Property Values as given below:
- Name = List1
- Multi Select = Simple
De-select the Control Wizard Button on the Toolbox and select the Command Button Tool and draw a Command Button underneath the List Box.
Select the Command Button, display the Property Sheet if it is not visible, and change the following property Values:
- Name = cmdView
- Caption = View Orders
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.

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.

Open the Form in Normal View. Select a few Orders one by one in the ListBox. You can click the desired item again to deselect it or click the Reset Command Button to deselect all.
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.

Database Daily Backup

a.p.r. pillaiMay 02, 2008msaccess tips, Utilities 67 comments

Database Daily Backup Procedure.

If your database is installed on a Local Area Network (LAN), regular backups are typically performed on a daily, weekly, monthly, quarterly, and yearly basis. 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 that it be restored from backup. Send a request to the Network Administrator providing details such as your database file path and the desired backup date. However, the recovery process may take several hours or even a few days, as the backup tapes or other storage media must be retrieved from their remote storage location before restoration can begin.

If your project maintains its own independent backup routine and performs backups regularly, you can avoid relying on the centralized network backup system. This not only reduces delays in database recovery but also ensures that your application's downtime remains minimal.

You can secure the Database Objects (Forms, Queries, Tables, Reports, etc.) from within the Database, but when it comes to the physical safety of the Database File, this will not work. 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 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 the Server to the Local Disk, or vice versa, with a DOS Batch File. The Backup should run immediately when the Database is open 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 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 by any user for the first time of the day, and should prevent the program from running on subsequent shutdowns and reopening events.

Preparations

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

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

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

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 it 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 as 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 may not work successfully from within the Database 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 based 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. This will ensure that the Program will not run in subsequent Database 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 installed on a network and shared by multiple users, you can determine which workstation holds the latest backup copy by referencing 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 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 backup_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 Daily Backup

Days in Month Function

a.p.r. pillaiApril 24, 2008Functions, msaccess tips No comments

Function to Calculate Number of Days

The User-Defined Function DaysM() given below can be used in calculations that involve the number of days of a particular month. Copy and paste the following Code into a Global Module and save it in your Project.

Function VBA Code

Public Function DaysM(ByVal varDate) As Integer 
Dim intYear As Integer
Dim intmonth As Integer

On Error GoTo DaysM_Err

If Nz(varDate) = 0 Then
    DaysM = 0    
    Exit Function
End If

intYear = Year(varDate)
intmonth = Month(varDate)

DaysM = Day(DateSerial(intYear, intmonth + 1, 1) - 1)

DaysM_Exit:
Exit Function
DaysM_Err:
MsgBox Err.Description, , "DaysM()"
DaysM = 0
Resume DaysM_Exit
End Function

Syntax: X = DaysM(varDate)

Replace the varDate parameter with a valid Date. The Number of Days for the Month will be returned in Variable X.

The Parameter value can be a valid Date, a Date in Text format like "15-02-2008", or its corresponding numeric value 39493.

If you would like to rewrite the Function differently by adding a few extra lines of code, then you may replace the expression DaysM = Day(DateSerial(intYear, intmonth + 1, 1) - 1) with the following lines of code:

DaysM = Choose(intmonth, 31, 28 + IIf((intYear Mod 4) = 0, 1, 0), 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)

If intmonth=2 then
   Select Case (intYear Mod 400)
       Case 100, 200, 300
            DaysM = DaysM - 1
   End Select
End if

Usage Options

This Function can be used in VBA Routines, Queries, or Text Controls in Forms or Reports where the number of days of a particular month is involved in calculations.

Example: An Employee resumed duty after her vacation on 15-02-2008. To calculate the balance number of days for her salary payment, one of the three Expressions given below can be used.

Dt = #02/15/2008#

BalDays = 1 + DateDiff("d", Dt, DateSerial(Year(Dt), Month(Dt) + 1, 1) - 1) 

or

BalDays =   1 + Day(DateSerial(Year(Dt), Month(Dt) + 1, 1) - 1) - Day(Dt) 

or

BalDays = 1 + DaysM(Dt) - Day(Dt)

The first two calculations are performed using built-in functions. The `DaysM()` function, which uses the second expression for its primary calculation, produces the same result with fewer characters in the final expression.

Determining the number of days in a month is generally straightforward. We commonly use simple rules to remember that April, June, September, and November (months 4, 6, 9, and 11) have 30 days, while February has 28 days. February alone requires additional calculations to determine whether an extra day should be added in a leap year. A commonly used rule is that a year evenly divisible by 4 is treated as a leap year.

However, this rule requires refinement when dealing with century years. For example, February had 29 days in the year 2000 because it was a leap year. In contrast, the years 1700, 1800, and 1900 were not leap years, despite being evenly divisible by 4. Similarly, the years 2100, 2200, and 2300 will also be treated as common years.

The leap year rule is based on the Earth's orbital period around the Sun. A calendar year is commonly approximated as 365.25 days, with every fourth year designated as a leap year containing 366 days by adding one extra day to February.

However, the Earth's actual orbital period is approximately 365 days, 5 hours, 48 minutes, and 45.5 seconds (365.2422 days). Using an average of 365.25 days takes an excess of approximately 0.0078 days each year. Over a period of about 400 years, this accumulates to approximately 3.12 extra days. To compensate for this excess, century years that are not evenly divisible by 400 are treated as common years, even though they are evenly divisible by 4.

The remaining excess of approximately 0.12 days accumulates to about 1.2 days over 4,000 years. Consequently, under this extended rule, the year 4000 is treated as a common year, even though it is evenly divisible by 400.

Who knows, before then, another scientific discovery may require us to revise the entire system once again.

References: Microsoft Encarta Encyclopedia

History of Calendar.

The Roman Calendar

The original Roman calendar, introduced around the 7th century BC, consisted of 10 months and a Total of 304 days, with the year beginning in March. Later in the same century, two additional months—January and February—were added. However, because the months contained only 29 or 30 days, an extra month had to be inserted approximately every second year to keep the calendar aligned with the seasons.

Days within each month were identified using the Roman system of counting backward from three fixed dates: the Calends, the first day of the month; the Nones, the ninth day before the Ides; and the Ides, which fell on the 13th day in some months and the 15th day in others. Over time, the Roman calendar became increasingly disordered because the officials responsible for adding days and months often manipulated the calendar to extend their terms of office or to hasten or delay elections.

In 45 BC, Julius Caesar, advised by the Greek astronomer Sosigenes (flourished in the 1st century BC), introduced a purely solar calendar. This system, known as the Julian calendar, established the common year as 365 days and designated every fourth year as a leap year with 366 days. The term leap year originates from the effect of the extra day in February, which causes dates after February to occur two weekdays later than in the previous year, rather than one weekday later as in a common year. The Julian calendar also established the order of the months and the seven-day week that form the basis of the modern calendar.

In 44 BC, Julius Caesar renamed the month Quintilis to Julius (July) in his own honor. Later, the month Sextilis was renamed Augustus (August) to honor Caesar Augustus, Julius Caesar's successor. Some authorities maintain that Augustus also established the lengths of the months as they are used today.

The Gregorian Calendar.

The Julian year was 11 minutes and 14 seconds longer than the solar year. This discrepancy accumulated until by 1582 the vernal equinox (see Ecliptic) occurred 10 days earlier, and Church holidays did not occur in the appropriate seasons. To make the vernal equinox occur on or about March 21, as it had in AD 325, the year of the First Council of Nicaea, Pope Gregory XIII issued a decree dropping 10 days from the calendar. To prevent further displacement, he instituted a calendar, known as the Gregorian calendar, which provided that century years divisible evenly by 400 should be leap years and that all other century years should be common years. Thus, 1600 was a leap year, but 1700 and 1800 were common years.

The Gregorian calendar, or the New Style calendar, was slowly adopted throughout Europe. It is used today throughout most of the Western world and in parts of Asia. When the Gregorian calendar was adopted in Great Britain in 1752, a correction of 11 days was necessary; the day after September 2, 1752, became September 14. Britain also adopted January 1 as the day when a new year begins. The Soviet Union adopted the Gregorian calendar in 1918, and Greece adopted it in 1923 for civil purposes, but many countries affiliated with the Greek Church retain the Julian, or Old Style, calendar for the celebration of Church feasts.

The Gregorian calendar is also called the Christian calendar because it uses the birth of Jesus Christ as a starting date. Dates of the Christian era (see Chronology) are often designated AD (Latin anno domini, "in the year of our Lord") and BC (before Christ). Although the birth of Christ was originally given as December 25, 1 BC, modern scholars now place it as about 4 BC.

Because the Gregorian calendar still entails months of unequal length, so that the dates and days of the week vary through time, numerous proposals have been made for a more practical, reformed calendar. Such proposals include a fixed calendar of 13 equal months and a universal calendar of four identical quarterly periods.

Source: Microsoft Encarta Encyclopedia

Earlier Post Link References:

Finding Consecutive Workdays with Query

a.p.r. pillaiApril 18, 2008msaccessQuery, Queries No comments

Finding Consecutive Workdays with Query.

How can we determine whether date values within a certain range are consecutive or intermittent?

Let’s use a real-world example:

A company hires temporary workers on a daily wage to complete a project within 15 days. Workers are informed that if they work 8 hours a day for 7 consecutive days (no weekends or breaks), they will receive a special incentive in addition to their daily wages.

In practice, employees joined on different dates, and not all could maintain a full 7-day streak. Attendance is logged in a table by date and employee code.

After the project ends, we are tasked with identifying which employees worked 7 straight days to award the incentives.

This is actually quite simple to solve—just three queries are enough.

A Table with Sample Data.

Step 1 – Table Structure

Create a new table in Design View with the following fields:

Field Name	Data Type	Description
emp	Short Text	Employee code (e.g., E001, E002)
workdate	Date/Time	The date the employee worked

Step 2 – Primary Key

Select both fields (emp and workdate) together.
Right-click → Primary Key.
This ensures no duplicate date entries are accepted for the same employee.

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

Solution

Query1:
Create a Total Query grouped by emp, take the minimum value from workdate to find the work-start date of each employee, and create a new column, adding 6 days to the work-start date to calculate and store the 7th Day-Date of work for each employee. They must work from the work-start date to this date without a break for a special eligibility incentive.

SQL: Query1

SELECT Table1.emp,
 Min(Table1.workdate) AS min_date,
 Min([workdate])+6 AS Day7Date
FROM Table1
GROUP BY Table1.emp;

Query2: Join Table1 with Query1 on the emp field and select records using the Day7Date as a criterion on workdate that falls on or before the Day7Date.
SQL: Query2
```
SELECT Table1.* 
FROM Query1 INNER JOIN Table1 ON Query1.emp = Table1.emp
WHERE (((Table1.workdate)<= [Day7Date]));
```
Query3: Create a Total Query using Query2 as a source. Group on the 'emp' field, take the count of workdate, and filter the output with workdate Count =7.

SQL: Query3

SELECT Query2.emp,
 Count(Query2.workdate) AS [count]
FROM Query2
GROUP BY Query2.emp
HAVING (((Count(Query2.workdate))=7));

The continuity of dates is validated at the employee level, based on each employee’s individual work start date. All employees will work within the same month or start on a specific date.

However, when the data file contains a large number of records spanning different periods and only records from a particular period need to be evaluated using this method, it’s important to first filter the data. To do this, create a query using Table1, applying the desired Start Date and End Date as criteria to extract only records within that range. Then, use this filtered query as the source for Query1 and Query2 described earlier.

As the saying goes, “There’s more than one way to skin a cat,” we can take an alternative approach to solve this problem by slightly differentiating how the earlier queries are defined. The SQL strings for this alternative method are provided below. Copy them and create the queries to try out this method as well.

Query 1_1 SQL

SELECT Table1.emp,
 Min(Table1.workdate) AS min_date,
 [min_date]+0 AS day1,
 [min_date]+1 AS day2,
 [min_date]+2 AS day3,
 [min_date]+3 AS day4,
 [min_date]+4 AS day5,
 [min_date]+5 AS day6,
 [min_date]+6 AS day7
FROM Table1
GROUP BY Table1.emp;

Query 2_1 SQL

SELECT Table1.emp,
 Table1.workdate
FROM Query1_1 INNER JOIN Table1 ON Query1_1.emp = Table1.emp
WHERE (((Table1.workdate) In ([day1],[day2],[day3],[day4],[day5],[day6],[day7])));

Query 3_1 SQL

SELECT Query2_1.emp,
 Count(Query2_1.workdate) AS CountOfworkdate
FROM Query2_1
GROUP BY Query2_1.emp
HAVING (((Count([Query1_2].[workdate]))=7));

I have not tested these methods extensively to eliminate side effects. You may use them at your own risk.

Do you have different ideas? Please share them with me by posting them in the Comments Section.

Transparent Command Button

a.p.r. pillaiApril 11, 2008Command Buttons, msaccess forms 1 comment

Transparent Command Button.

Command buttons on forms can be hidden by setting their Transparent or Visible property value = 'No', and then made visible again when a specific condition is met or when a particular user opens the form.

Example:
In a secured database where a form is routed through a network for data verification and approval by multiple users, you could:

Create separate command buttons for each user role.
Show each button only when the relevant user opens the form.
Base visibility on the verification status set by users of a lower rank.

The most common approach in such scenarios is not to hide the button, but to disable it while keeping it visible, and then enable it when required. Setting an active button’s Enabled or Visible property cannot be set to 'No'. You must first move the focus to another control before making the change in VBA.

The Transparent property does not have this limitation. You can make a command button invisible by setting the Transparent Property value to'Yes'.

However, there is a side effect:
If a user knows the exact location of the invisible button, clicking that spot will still trigger its action. To prevent this, you can “park” the invisible button in a different location at runtime by modifying its Left property.

Before moving the button, save its current Left position to a module-level global variable.
When it’s time to restore the button, use the saved value to reposition it in its original location.

Designing a Table and Form.

Here, we will use the Transparent Command Button for a Main Switchboard Menu.

Design a Table using the Structure shown below and enter a few sample records as given below.
Right-click to open the Large Image in a New Window.
Use the Form Wizard to design a Tabular Form for the above table and save it with the name DataFiles.
Open the Form in Design view. Retain the ID Desc fields, and remove other columns. Keep the Shift Key down and click on both Fields to select them together. Or click outside the Fields and drag the mouse over to select them both.
Display the Property Sheet (View -> Properties) and change the Following Property Values.
- Enabled = No
- Locked = Yes
- Top = 0.0417"
- Height = 0.1771"
- Back Style = Normal
- Back Color = -2147483633
- Special Effect = Raised
- Border Style = Solid
- Border Color = 0
- Border Width = Hairline
- ForeColor = 0
Select View Menu and remove the check mark from the Form Header/Footer Option. Select Yes to the Warning message to delete the Header/Footer Sections from the Form.
Click the top left corner of the Form to deselect all controls and select the Form. Display the Form Property Sheet (View -> Properties) and change the following Form Property Values.
- Form Width = 2.5417"
- Default view = Continuous Forms
- Views Allowed = Form
- Allow Edits = No
- Allow Additions = No
- Allow Deletions = No
- Record Selectors = No
- Navigation Buttons = No
- Dividing Lines = No
- Scrollbars = No
- Border Style = None
- Control Box = No
- Min Max Buttons = None
- Close Button = No
- What this Button=No
- Allow Design Changes = Design View Only
Draw a Command Button about the size of both Field widths put together somewhere below the Fields so that we can modify the properties of the Command Button and place it over both the Fields in a transparent state.
Right-click to open the Large Image in a New Window.
Select the Command Button and display the Property Sheet. Change the following Property Values:
- Name = cmdMenu
- Transparent = Yes
- Height = 0.1771"
Click on the On Click Property and select Event Procedure from the Dropdown List, and click on the Build (...) button to open the Form's VBA Module.
Here we can write code to test the ID Value of the record clicked by the User and open the Form corresponding to that Number.
The VBA Code for a simple method to test and open the Form corresponding to the record ID that received the click is given below.
Copy and paste the following code into the VBA Module of the DataFiles Form and save the Form.
```
Private Sub cmdMenu_Click()
Dim IDNumber As Integer, strForm As String

IDNumber = Me![ID]
strForm = ""
Select Case IDNumber
    Case 1
       strForm = "CRREQ_MASTER"
    Case 2
       strForm = "Dept_Codes"
    Case 3
       strForm = "Branch"
End Select
If Len(strForm) > 0 Then
   DoCmd.OpenForm strForm, acNormal
End If
End Sub
```
Note: A more powerful and reusable method for handling this functionality is explained in my earlier article, Opening Access Forms. I recommend going through that article, as the method described there is very simple to implement in any project.
In this example, the sample table shown earlier has been adapted from that article’s example, with additional fields (Forms, Macro, and Type). If you use the code provided in that article, you can add any number of records to this table without needing to test each ID value individually, as was required in the earlier approach.
This method also allows you to:
- Open a form directly, or
- Run a process via a macro and, at the end of the process, open a form to display results.
You can control this behavior by setting the Type field value to 0 or 1 when inserting a new record.

To implement this with your command button, drag the transparent button (or, for precise positioning, use Ctrl + Arrow Keys in Office 2000 or the Arrow Keys alone in Office 2003 after selecting it) and place it directly over the ID and Desc fields, as shown below.
Right-click to open the Large Image in a New Window.
Click on the Detail Section of the Form and Display the Property Sheet. Change the Details Section height.
- Detail Section Height = .25"
The completed form design will appear as shown in the image below when viewed in Normal View. This form will function as a subform on the Control Screen (Main Switchboard) for our Data Files menu.

If you prefer not to display the serial numbers on the left side, simply set the Visible property of that field to No in the Property Sheet.
Right-click to open the Large Image in a New Window.

Trial Run
Open the Main Switchboard form of your project (or any other form you’d like to test with) and drag the form you created in the previous step onto it.

In my example, I placed the menu form on the sample Switchboard form used in the Colorful Command Buttons article.
Click on the Sub-Form, display the Property Sheet, and change the following Property Values:

Special Effect = Flat
Border Style = Transparent

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

The transparent command button instances now appear above each record in the form and will respond to user clicks. Using VBA, you can detect which record was clicked and open the form associated with that record’s ID.

When new records are added to the menu table, they will automatically appear in the menu—no design changes required.

Test the menu by replacing the sample form names in the code with actual form names from your project.

Continued from LIST BOX AND DATE PART ONE

Modifying the Form.

Test Runs

The List Box Settings

Date Selection ListBox.

Get Northwind Sample Tables.

Designing a Form with a List Box.

The Form Class Module Code.

Sample Queries.

Test Runs.

Create List From Another ListBox.

The List Boxes Image

Preventing Multiple Item Selection

Form Class Module Code.

Using the Output Query.

Dynamic Query.

The SQL for the sample Query.

Design a Sample Form.

The Form's Class Module Code.

Test Run of the Program.

Database Daily Backup Procedure.

Automatic Daily Backup

Preparations

How does it work?

Create a Batch File Manually.

Portability Considerations

Download

Function to Calculate Number of Days

Function VBA Code

Usage Options

History of Calendar.

The Gregorian Calendar.

Earlier Post Link References:

Finding Consecutive Workdays with Query.

A Table with Sample Data.

Step 1 – Table Structure

Step 2 – Primary Key

Solution

Query1:

SQL: Query2

SQL: Query3

Transparent Command Button.

Designing a Table and Form.

Trial Run

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

Post Feed

Get new posts by email:

Search

USEFUL LINKS

Popular Posts

About Me

Blog Archive

Labels

Statcounter