Microsoft Access VBA Tutorials for self-paced learning, Class Modules, SQL Techniques, and AI Integration Guides.

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.

  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.

  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.

Share:

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

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.

Share:

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.


  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

    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.

Share:

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


Share:

Days in Month Function

Function to Calculate Number of Days.

The User-Defined Function DaysM() given below is useful in calculations that use the number of days in a particular month. Copy and paste the following code into a Global Module and save it.

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 use the 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 to add an extra day 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 considering the 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 0.0078 days each year. Over 400 years, this accumulates to 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 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:

Share:

Finding Consecutive Workdays with Query

Finding Consecutive Workdays with a 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 NameData TypeDescription
empShort TextEmployee code (e.g., E001, E002)
workdateDate/TimeThe 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.

Solution

  1. Query1:

  2. Create a Totals query grouped by employee, and use the minimum value of WorkDate to determine each employee’s work-start date. Then, create a new column that adds 6 days to the work-start date to calculate and store the 7th day of work for each employee. They must work from the work-start date to this date without a break for a special eligibility incentive.
  3. SQL: Query1

    SELECT Table1.emp,
     Min(Table1.workdate) AS min_date,
     Min([workdate])+6 AS Day7Date
    FROM Table1
    GROUP BY Table1.emp;
  4. 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]));
    
  5. 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.

The data file contains records spanning different periods, and the records are evaluated for a specific period; the data is filtered to include only those relevant records. 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 were 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 extensively tested these methods 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.

Share:

Transparent Command Button

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.

  1. Design a Table using the Structure shown below and enter a few sample records as given below.

  2. Use the Form Wizard to design a Tabular Form for the above table and save it with the name DataFiles.

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

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

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

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

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

  8. Select the Command Button and display the Property Sheet. Change the following Property Values:

    • Name = cmdMenu

    • Transparent = Yes

    • Height = 0.1771"

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

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

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


    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.

  12. Click on the Sub-Form, display the Property Sheet, and change the following Property Values:

  • Special Effect = Flat

  • Border Style = Transparent

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.

  1. Command Button Animation
  2. Double Action Command Button
  3. Colorful Command Buttons
  4. Transparent Command Button
  5. Command Button Animation-2
  6. Creating an Animated Command Button with VBA
  7. Command Button Color Change on Mouse Move

Share:

Colorfull Command Buttons

Gradient-Colored Command Buttons.

We have seen the Double-Action Command Button in an earlier Post and Command Button Animation before, and now it is time to add some color to the Command Buttons. Take a look at some different Command Buttons in action on a Control Screen (Main Switchboard) of a sample MS-Access Application.

Would you like to use these colorful buttons in your own application?

You might be expecting a long lecture from me about their setup — maybe a hundred lines of VBA code tucked away at the end of this article for you to copy and paste. Or perhaps you’re bracing yourself to download some MS Access add-ons, link a couple of library files, and wrestle with complex installations.

Well… none of that is needed.

In fact, you can create one of these buttons in just two minutes — maybe three the first time you try. Once you’ve made one, you can produce variations in under a minute.

Here’s the twist: these buttons are not created inside MS Access at all. Instead, we make them in MS Word (or Excel, if you prefer), then copy and paste them straight into an Access form. After that, just tweak a few settings in the Property Sheet, add maybe one or two lines of VBA for animation, and you’re ready to roll. 

Designing the Button.

  1. Open your MS Access project and open a form in Design View.

  2. Launch MS Word and check whether the Drawing Toolbar is visible at the bottom, just above the status bar.

    • If it’s not visible, go to View → Toolbars → Drawing.

    • If the Drawing Toolbar button is already visible, then click it to display the toolbar.

  3. On the Drawing Toolbar, click AutoShapes → Basic Shapes → Rounded Rectangle.

  4. Draw a Rounded Rectangle on the Word document.

  5. Right-click the shape you just created and choose Format AutoShape from the shortcut menu.

  6. In the Format AutoShape dialog box, go to the Colors and Lines tab.

  7. Under the Fill group, open the Color drop-down list to select your preferred fill color.

  8. Click on the Yellow color on the Color Palette, and then click on the Fill Effects. Control.

  9. In the Fill Effects dialog box, select the Gradient tab.

    • Under Shading Styles, choose Vertical.

    • In the Variants section, click the shape at the bottom right corner.

    • The selected gradient will appear under the Sample preview on the right.

  10. Click OK, and then click OK again in the Format AutoShape dialog to apply the chosen gradient to the Rounded Rectangle.

  11. Right-click the shape and select Edit Text from the shortcut menu.

  12. Type the button caption—for example, Data Files. Highlight the text and apply the desired formatting using Word’s standard tools:

    • Align Center

    • Bold

    • A font color that provides good visibility against the gradient background.

  13. Note: Once you copy and paste the button into an MS Access form, you cannot change its caption or formatting there. It’s a good practice to keep a copy of the original buttons in the Word document. If you need changes later, edit them in Word, then copy and paste the updated version into Access.

    You can make copies of this Button and follow from Step 5 above to change the Gradient Color and Caption.

  14. Tips: You can easily change the shape of your created buttons:

    • On the Drawing toolbar in Word, open the Draw menu.

    • Select Change AutoShape, then choose any shape you prefer for your button.

    To insert the button into Access:

    • Right-click the button in Word and choose Copy from the shortcut menu.

    • Minimize Word and return to the Access form you have open in Design View.

    • Right-click in the form’s Detail or Footer section where you want the button placed, and choose Paste.

    • Drag and position the button to the desired location.

      Adjusting the button display in Access:

    • Right-click the pasted button in Access and select Properties.

    • Move the Property Sheet so it does not overlap the button.

    • Locate the Size Mode property and experiment with the available settings:

      • Clip

      • Zoom

      • Stretch

    • When Zoom is selected, the button resizes proportionally, maintaining the height-to-width ratios. However, it may leave a gap around the image. You can fill this background area with the Form’s background color so the gap is not visible when the form opens in Normal View.

  15. If the button is in the Footer Section of your Form, then click on the Footer Section of the Form to select it and display the Property Sheet.

  16. Copy the Back Color Property Value of the Form.

  17. Click on the button to display the Property Sheet of the Button.

  18. Paste the value in the Back Color property copied from the Form.

  19. Change the Special Effect Property to Raised.

  20. Change the Border Style Property to Transparent.

  21. Now you can program your Colorful Command Button like a normal MS-Access Command Button.

    If you look at the Property Sheet of this Command Button, you can see that all the essential Properties and Events are available for Event Procedures, Hyperlinks, Macros, etc.

    I will give you one line of code to give some life (Animation) to this Button so that it will respond when the mouse moves over it.

  22. Click on the Button's Mouse Move Property and select Event Procedure. Copy the following Code between the Empty Subroutine lines.

     Me.OLEUnbound257.SpecialEffect = 2

  23. Copy the correct name of the Button from its Name Property and paste it to replace OLEUnbound257. Mind the dot separator after the word Me. And at the beginning of SpecialEffect, while pasting the Button Name.

  24. Select the Form's Section (Detail or Footer, or Header) where you have placed the Command Button and display the Property Sheet.

  25. Click on the Mouse Move Property and select Event Procedure, copy and paste the above line of Code with the Button Name changed, and paste between the empty Subroutine lines.

  26. Change the Value 2 to 1.

  27. Animating the Button.

    Initially, the Button will be in a Raised Style. When the mouse is moved over the Button, it will be pushed-in (Sunken). When the Mouse is moved out of the Button, it will restore to the raised state. If you repeat this action in quick succession, the Animation will be more evident.

    We will learn some more tricks with Command Buttons.

     What do you think about them?

     

    1. Command Button Animation
    2. Double-Action Command Button
    3. Colorful Command Buttons
    4. Transparent Command Button
    5. Command Button Animation-2
    6. Creating an Animated Command Button with VBA
    7. Command Button Color Change on Mouse Move

Share:
Listen to this article
Ready to read.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

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