Command Button Color Change on Mouse Button-down Action

Command Button Color Change on Mouse Button-down Action.

After working with the Command Button and Message Box Controls in MS Access, I really got bored with their styles. So, I set out to do something about it and created some animation styles for the command buttons and used Office Assistant (valid only for Access2003 and earlier versions) for Message Boxes. The Links to those Articles and free Demo Database download links are given below:

Command Button Styles

1. Command Button Animation
2. Command Button Animation-2
3. Colorful Command Button

   Message Box Styles (Access2003 or earlier versions only)

4. Message Box using the Office Assistant
5. MsgBox with Options Menu
6. MsgBox with Checkbox Menu

Some New Trick on Command Button.

Here is a new trick for you to use on Command Button Control. Command Button will change the color on the mouse-down action and restore the original color on the mouse-up action.  You can do this with a Label control that has the same dimensions as the Command Button, filled with the color you like, and position it behind the Command Button Control.  You need two lines of VBA Code on the Form Module as well.

Design the New Animation Style.

Let us go through with this simple design and enjoy the new animation style of the Command Button Control.

1. Open a new Form.

2. Create a Command Button Control on the Detail Section of the Form.

3. Display the Command Button’s Property Sheet (F4).

4. Change the Name Property Value to cmdClose.

5. Change the Caption Property Value to Close.

6. Create a Label Control with some caption text near the Command Button.

7. Select the Command Button and the Label Control together.

8. Right-click it, highlight the Size option on the Shortcut Menu, and select To Tallest.

9. Repeat step 8 above and select To Widest.

10. Click on the Label Control alone and display its Property Sheet (F4).

11. Remove the text from the Caption property.

12. Change the Special Effect Property value to Raised.

13. Select the Back Color Property and click the Build ( . . .) button to display the Color Palette.

14. Select the color you like the most to fill the background of the label control.

15. Move the Label control behind the CommandButton control and position it completely hidden.

16. If the label control is above the CommandButton, then display the Shortcut Menu by right-clicking and selecting Send to Back from the Position group.

17. Click on the Command Button and display the property sheet (F4).

18. Click the Event Tab, select the Mouse Down Event, select [Event Procedure] from the drop-down list, and click the Build (. . .) button to open the Form’s VBA Module Window.

19. Copy and paste the following Code in the Form Module, overwriting the existing empty subroutine stubs:

    Private Sub cmdClose_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
      Me.cmdClose.BackStyle = 0 'transparent
    End Sub

20. Repeat step 18 for Mouse Up [Event Procedure] and copy and paste the following Code into the Form Module:

    Private Sub cmdClose_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single) 
        Me.cmdClose.BackStyle = 1 'Normal 
    End Sub

Test Run.

Now, it is time for the test run of our design. 

1. Save the Form as frmButton.

2. Open frmButton in Form View.

3. Click the CommandButton and hold the mouse button down.  You will see the CommandButton’s color change to the background fill color you selected for the label control, and the CommandButton caption text Close becomes visible on it.

4. Release the mouse button.  The original CommandButton view is restored.

The trick works by changing the CommandButton Back Style to transparent, forcing it to display the Label control color and changes to Normal view when the mouse button is released. 

You can learn another trick from an earlier Article on Transparent Command Button, here.

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

Read More

Time-bound Form Mode Change

Time-bound Form Mode Change.

Sometimes, you may want to allow data entry or editing only during specific time periods, and other times keep the form locked in read-only mode. This requirement usually arises in time-bound workflows, where users are permitted to update records only within fixed working slots.

A similar request was once raised in a Microsoft Access discussion forum, and I decided to explore a practical solution.

In the following example, we will enable data entry and editing on a form only during these time periods:

• 06:00 – 07:00 Hours

• 11:00 – 13:00 Hours

• 17:00 – 19:00 Hours

At any other time, the form will remain restricted to data view only. The Data Entry Control Function.

The function written for this task is given below. Copy it into the Standard Module:

Public Function Data_Entry(ByVal frmName As String)
Dim T1S, T1E, T2S, T2E, T3S, T3E
Dim frm As Form

Set frm = Forms(frmName)

D = Date
T1S = TimeValue("06:00:00")
T1E = TimeValue("07:00:00")

T2S = TimeValue("11:00:00")
T2E = TimeValue("13:00:00")

T3S = TimeValue("17:00:00")
T3E = TimeValue("19:00:00")

Select Case time
      Case T1S To T1E, T2S To T2E, T3S To T3E
          With frm
            If .AllowAdditions = False Then
               .AllowAdditions = True
               .AllowEdits = True
               .lblMsg.Visible = False
'change .subFrmName to match the control (window) name of the sub-form
               .subFrmName.Enabled = True
            End If
          End With
          frm.Refresh
      Case Else
          With frm
            If .AllowAdditions = True Then
                .AllowAdditions = False
                .AllowEdits = False
                .lblMsg.Visible = True
 'change the next line to set focus on any field on the main form
                .EmployeeID.SetFocus
'change .subFrmName to match the control (window) name of the sub-form
                .subFrmName.Enabled = False
            End If
          End With
          frm.Refresh
End Select

Set frm = Nothing
End Function

NB: You must make changes wherever applicable to point the code to correct control names on your Form, which I have marked with comments.

Some Changes to the Form.

1. Open your Form in Design View.

2. Add a Label control on the main Form where you want to display 'Entry not allowed', change the Name property to lblMsg, and write the message in the Caption property.

3. Display the Form Property Sheet.

4. To implement this feature, we can make use of the Form’s Timer event.

   1. Set the Timer Interval property of the form to 60000 (i.e., 60,000 milliseconds = 1 minute).

      • This means Access will automatically check the current system time once every minute.

      • If you want more frequent checks, increase the interval in 1000 millisecond increments (1,000 = 1 second).

   2. In the Form_Timer event procedure, we can write a simple VBA routine to check whether the current time falls within the allowed edit periods. If it does, the form will be set to data entry/edit mode. Otherwise, it will automatically switch to view-only mode by disabling edits.

   This way, the form will self-adjust, ensuring users can enter or edit data during their specific allotted time slot. Select the On-Timer() Event, select [Event Procedure] from the drop-down control, and click the build (...) Button to open the VBA module.

5. Copy and paste the following lines of Code, replacing the existing two lines displayed there:

   Code:

   Private Sub Form_Timer()
      Data_Entry Me.Name
   End Sub

6. Save and Close the Form.

Tracking the Time for Form Mode Change.

The Timer setting ensures that the program checks every minute to determine whether the current time falls within the allowed time slots specified in the code. If the condition is true, both the main form and its subform will be enabled for data entry and editing; otherwise, they will remain locked in read-only mode.

When the form is opened, however, there will be an initial one-minute delay before Access performs the first check. To avoid this delay, you can call the Data_Entry() function from the Form_Current event procedure. This ensures that the time-check routine runs immediately upon opening the form, rather than waiting for the first timer tick.

Earlier Post Link References:

• Prepare a List of Procedure names from a Module
• VBA Module Object and Methods
• Writing VBA Code with VBA
• Control Setfocus on Tab-Page Click
• Adding Data directly into an external Database

Read More

Missing Lines in Line-Chart

Missing Lines in Line Chart.

You spent several hours preparing the data for your line chart.  Designed the line chart on a Report with a title, Data Labels, and Legends, and it looks nice except for one big problem with the Profit/Loss line.  The Line doesn’t show up on the graph except for two marker points of Qrtr1 and Qrtr3 Value points, and nothing shows on Qrtr2 and Qrtr4 value locations.

Check the sample Graph Chart Image shown below with the points marked with yellow color on the Profit/Loss line:

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

Take a look at the following Graph Chart Image with the Source Table displayed:

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

Tracking Down the Real Issue.

Did you notice where the actual problem is?  In the Profit/Loss row, in Qrtr2 and Qrtr4 cells have Null values in the table, and the Graph Chart ignores these Cell values, resulting in no continuity with other connecting value lines. While preparing data (source Table/Query) for the Graph Chart, ensure that no cells have Null values. If there are Cells with Null values, then fill them with Zeros.

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

The corrected Chart Table, filled with zero values in empty cells, connected the points with the line correctly on the Graph Chart image shown above. 

You can modify the Chart Source Value by modifying the Row Source Property SQL value, without directly updating zeroes on the Source Table.

Modifying the Chart Data Source Query.

1. Open the Report with the Graph Chart in Design View.

2. Click on the Chart’s outer frame to select it.

3. Display the Property Sheet.

4. Click on the Build (...) button on the Row Source Property to open the Graph Chart Source Query in Design View.

5. Modify the Query Columns to get the SQL modified as shown below:

   

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

   SELECT Chart.Desc, Val(nz([qrtr1],0)) AS [Qrtr-1], 
     Val(nz([qrtr2],0)) AS [Qrtr-2],
     Val(nz([qrtr3],0)) AS [Qrtr-3],
     Val(nz([qrtr4],0)) AS [Qrtr-4] FROM Chart;
   

6. Save and close the Query.

7. Open the Report with the Graph Chart in Print Preview mode to view the effect of the change.

1. MS-Access and Graph Charts
2. MS-Access and Graph Charts-2
3. Working With Chart Object in VBA
4. Column Chart and VBA
5. Pie Chart Object and VBA
6. Missing Lines in Line Chart
7. Pie Chart and Live Data on Form
8. Scaling Chart Object
9. Cross-Tab Union Queries for Chart

Read More

Back Tracking Open Forms

Back Tracking Open Forms.

On the Internet, when we browse from one webpage to another, the browser provides Back and Forward buttons to move through previously visited pages one at a time.

A similar approach can be applied in Microsoft Access. During startup, we can open several forms sequentially and keep them hidden in memory. The question is: how do we move back and forth between these forms—just like navigating web pages? Fortunately, there is a way to achieve this.

When multiple forms are required for day-to-day operations, it is often a good practice to open them all at once (immediately after the application starts) and keep them hidden. Although this may introduce a slight startup lag, it significantly improves performance later, since forms are displayed directly from memory rather than being opened and closed repeatedly. All hidden forms can also be closed easily with a short VBA routine before shutting down the application.

If navigation from one form to another is needed in a predictable sequence, you can automate the process with a simple macro by arranging the forms in the desired order. The sample macro below demonstrates how to open multiple forms in sequence.

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

In the sample macro, the first OpenForm action opens the Employees4 form in Normal View mode, while the other forms are opened in Hidden mode so they remain in memory. When needed, any of these hidden forms can be made visible, while the currently active form can be set to hidden at the same time. This ensures that only one form is visible at a time, preventing the application window from becoming cluttered with multiple open forms.

Prepare for a Trial Run.

Let us try an example before exploring other aspects of this interesting method.

1. Import the Employees Table and Employees Form from Northwind.mdb (or Northwind) sample database.

   Check the sample image given below.

   

   Right-click to open the large image in a New Tab or Window.

2. Rename the Employees form as Employees1.

3. Open the 'Employees1' Form in Design View.

4. Add a label control in the Form Header, and set its Caption to 1, change the font size to 16, and the foreground color to White or a bright background color.

5. Expand the Footer of the Form.

6. Add two Command Buttons in the Form Footer as shown above.

7. Click on the left-side Command Button to select it.

8. Display its Property Sheet (F4).

9. Change the Name property value to Back and change the Caption property value to << (two less- than symbols).

   Two Button-Click Event Sub-Routines.

10. Select the OnClick Event property, set [Event Procedure] from the drop-down list, and select the Build (...) Button to open the VBA editing window with the empty Sub-routine stub: Private Sub Back_Click() . . . End Sub.

11. Copy the following Code and paste it, overwriting the subroutine lines in the VBA Module:

    Private Sub Back_Click()
      ForwardBack "B", Me.Name
    End Sub

    Note: ForwardBack() is a Function we will write and add to the Standard Module.

12. Repeat steps 7 to 10 for the right-side Command Button, changing the Name property value to Forward() and the Caption property value to >> (two greater than symbols).

13. Copy the following Code and paste it, overwriting the sub-routine starting and ending lines in the VBA Module:

    Private Sub Forward_Click()
      ForwardBack "F", Me.Name
    End Sub

14. Save and close the Form.

    The Move ForwardBack() Function

15. Copy and paste the following Code into a Standard Module in your Database and save it:

    Public Function ForwardBack(ByVal strStatus As String, ByVal strForm As String)
    Dim frmCount As Integer, j As Integer
    
    On Error GoTo ForwardBack_Err
    
    'get count of open forms in memory
    frmCount = Forms.Count - 1
    For j = 0 To frmCount
    Select Case strStatus
          Case "B" 'Move Back
            If Forms(j).Name = strForm And j - 1 >= 0 Then
               DoCmd.SelectObject acForm, Forms(j - 1).Name, False
               Forms(strForm).Visible = False
               Forms(j - 1).Visible = True
               Exit For
            End If
         Case "F" 'Move Forward
            If Forms(j).Name = strForm And frmCount > j Then
               DoCmd.SelectObject acForm, Forms(j + 1).Name, False
               Forms(strForm).Visible = False
               Forms(j + 1).Visible = True
               Exit For
            End If
    End Select
    Next
    
    ForwardBack_Exit:
    Exit Function
    
    ForwardBack_Err:
    MsgBox Err & ":" & Err.Description, , "ForwardBack()"
    Resume ForwardBack_Exit
    End Function

    The ForwardBack() Function needs two parameters when called:

    The first parameter can be either "B" or "F".

    • Use “B” as the first parameter value when called from the << (Go Back) labeled Command Button Click Event Procedure and "F" for >> (Go Forward) labeled Command Button Click Event Procedure.

    • The second parameter is the active Form's name, which can be passed with the 'Me.Name' statement.

16. Make 4 more copies of the Employees1 Form and name them as Employees2 to Employees5. The index in the header labels should also change to 2, 3, 4, and 5 on their respective Forms.

    Since all the forms are copies of the same form, this number will help us distinguish one from the other.

17. Create a Macro similar to the sample image shown at the top of this page to open the forms, and keep them hidden in memory except for one.  You may set one of the Form Window Modes as Normal to make that Form visible in the Application Window, while all other forms stay hidden.

18. Save the Macro as Macro1.

Test Run our Creation and Program.

Now, it is time to test our Project.  First, let us test our Project manually without using Macro1.

1. Open Forms Employees1 to Employees5 manually, one by one, from the Navigation Pane.

2. You now have all the Forms opened in the Application Window.  The Employees5 form is on top, with the form header label displaying the number 5.

   When multiple forms are opened in this manner, they are stored in memory within the Forms Collection Object, arranged in the order they were opened. The first form opened can be accessed using index 0 (e.g., Forms(0) or Forms("Employees1") in VBA). You can retrieve the form’s name with the Name property (Forms(0).Name) in the same way and access other Form properties. The second form opened will have index 1, the third will have index 2, and so on.

   Keep in mind that the suffix numbers we added to the Employees forms (e.g., Employees1, Employees2, …) have nothing to do with the internal form opening sequence (Index) numbers. Forms can be opened in any order you like; you don’t need to start with Employees1 and end with Employees5. However, following a simple naming sequence at the beginning can make it easier to test and understand the program.

   Click the Command Button with the >> (Go Forward) symbols to move to the next form, but nothing will happen because this is the last form in the Forms Collection.

3. Click the Command Button with the << (Go Back) symbols to make the Employees4 form visible and the current form, and to hide the Employees5 Form.

4. Repeat step 2 to make the Employees3 form visible and continue doing this till you reach Form Employees1. 

   At this stage, clicking the Back button (<<) on this form will not produce any response because it is the first form we opened. However, if the forms were opened in a different order, the button would work as expected. When you arrive at the Employee1 form, all other forms remain in memory in a hidden state, since our main program is designed to keep them that way.

5. Try to move forward by clicking the >> button to make the other forms become visible one by one, hiding the earlier forms.

Closing All Open Forms - The CloseAllForm() Function

Tip: If you want to make changes to any of these forms while they are hidden in memory, simply right-click the form’s name in the Navigation Pane and select Design View. The form will open directly in Design View.

To close all the open forms (both hidden and visible) in one go while shutting down the application, you can use a simple VBA routine named CloseAllForms(). This routine can be called from a command button’s Click event procedure, right before executing the DoCmd.Quit statement. You may also run the program directly from the VBA window while testing the procedure.

Copy and paste the following code into a Standard Module of your project:

Public Function CloseAllForms()

Dim j
'when no forms are in open state
'then forms.count -1 returns -1 and
'the For...Next loop is not executed

'When a form is closed
'other forms in memory are re-indexed automatically
For j = 0 To Forms.Count - 1
  DoCmd.Close acForm, Forms(0).Name
Next
End Function

Click anywhere inside the code and press F5 to run it. This will close all the open forms.

In the earlier test, we opened all the forms manually. Instead, you can run the macro (Macro1) we created earlier to open all the forms at once, keeping all of them hidden except one.

If you want this macro to run automatically when the database opens—so that all forms are loaded into memory and hidden—rename Macro1 to AutoExec.

During normal operations, users can open forms in any order, and they will remain in memory in the sequence in which they were opened. Users can then navigate through the open forms by clicking command buttons: >> (Go Forward) or << (Go Back).

• VBA Module Object and Methods
• Writing VBA Code with VBA
• Control Setfocus on Tab-Page Click
• Adding Data directly into an external Database
• Users and Groups Listing from Workgroup Information File

Read More

Stretching Controls when resizing Form View

Stretching Controls when resizing to Form View.

A sample image of an Employee's Form Design is given below:

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

When you view this screen in a maximized Application Window, the view will be something like the following image:

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

The normal view of the Employees Form, when displayed in a maximized application window, shows an empty area to the right of the data fields. The Form Title, Employees, remains in its designated position within a Label control, centered above the data field controls.

Now, look at the following image taken after the Anchor Settings to the controls, which respond dynamically to move or stretch across the screen based on the resizing of the screen:

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

Compare the two images shown above. Notice how the layout changes automatically when the form window is maximized:

• The second column of controls shifts neatly to the right edge of the screen.

• The controls on the left side expand horizontally, filling up unused space and giving more room for data entry or viewing.

• The form heading (“Employees”) realigns itself to the center of the screen.

• The Note field (a memo field) stretches both downward and across, making it much easier to view and edit larger amounts of text.

Curious to know how this neat effect works in MS Access 2007? If you already have a form with a design similar to the one shown in the first image, you can try it out yourself — and here’s the best part: this trick works with any form!

Design a Form.

Let us design a Form for the Employees Table in the NorthWind Database.

1. Import the Employees Table from the Northwind sample database.

2. Design a Form similar to the one shown at the top of this page.  You can use the Form Wizard to create the form in Column format and rearrange the controls.

3. Select all the controls in the second column together and move them down to get enough space for the Fax Number and Address fields, which we will bring from the first column, and place them on top of the second column.

4. Select Fax Number and Address Fields, right-click them to display the shortcut menu.

5. Select Cut from the menu to remove both text boxes and their child labels from the first column controls group.

6. Right-click somewhere in the Detail Section and select Paste to paste them back into the detail section.

7. Move the pasted controls to the top of the second column controls.

8. Select and cut the Note Field and its child label from the second column and place them below the first column of controls.

Up to this point, everything we did was part of a standard form design process — arranging controls into columns as usual. However, there’s one important exception: the Note field. Instead of keeping it in the second column’s control group, we deliberately moved it down below the first column as a standalone control. This way, it is no longer tied to the group’s layout behavior and can be assigned a different Anchoring property, allowing it to stretch independently when the form is resized.

1. Save the Form, and reopen it in Form View to check how the current design looks.

   Implementing the Trick.

2. Change the Form in Design View.

   Let’s begin applying these tricks to the controls on our form, starting from the top.

   Step 1: Centering the Heading Label
   We want the form heading to remain horizontally centered whenever the form is resized. If the text “Employees” in the header label is not already centered within its current width, select the label and click the Center button on the Design tab of the ribbon.

   Step 2: Making the Heading Responsive
   Next, we want the label itself to stretch across the available Form width so that the caption “Employees” always stays centered—whether the form is maximized or manually resized. To achieve this:

   1. Select the heading label control.

   2. Open the Property Sheet (if not already open).

   3. Locate the Anchoring property.

   4. Change the setting to Stretch Across Top.

   With this setting, the label automatically resizes with the form, and its caption remains centered no matter how the window is adjusted.

3. Click on the heading label to select it.

4. Select Arrange ->Anchoring -> Stretch Across Top.

   Testing the Heading Label Behavior.

   1. Open the form in Normal View.

   2. Maximize the window and check whether the form title Employees moves to the center across the expanded screen.

   3. Next, manually resize the form:

      • Move the mouse pointer to the right edge of the form until it changes to the horizontal sizing arrow.

      • Hold down the left mouse button and slowly drag the edge inward.

      • Watch how the heading label automatically adjusts its width and keeps the caption centered.

   4. For a quicker test, minimize the Navigation Pane and then display it again. The form window will expand and shrink instantly, showing the anchoring effect in action.

   Moving On

   Now that the header label behaves as expected, let’s play some anchoring tricks with the other controls in the Detail Section. This is where we’ll make the second column “stick” to the right edge and stretch the first column controls for better usability when resizing.

5. Place the Form back into Design View.

6. Highlight and select all the second column TextBoxes.

7. Select Arrange -> Anchoring -> Top Right or right-click the selected controls and select Anchoring -> Top Right from the shortcut menu.

8. Change the form to Form View and preview the effect of our setting. Change the form back to Design View again.

9. Select the TextBoxes in the left column, except the Note field.

10. Right-click on the controls and select Anchoring -> Stretch Across Top.

11. Select the Notes field, right-click on the control, and select Anchoring -> Stretch Down and Across.

All the anchoring settings are now complete. Save the form and switch to Normal View. Try resizing the form window manually—dragging the edges slowly—to watch how each control responds in slow motion. You’ll see the title label, the right-side controls, and the Note field all adapt smoothly to the changing form size.

• Writing VBA Code with VBA
• Control Setfocus on Tab-Page Click
• Adding Data directly into an external Database
• Users and Groups Listing from Workgroup Information File
• Access Security Key Diagram

Read More

Defining Pages on Form

Define Pages on Form.

When designing a form, we usually place controls within the visible area of the screen for ease of use. However, an Access form can actually be as large as 22 x 22 inches, giving you plenty of real estate to work with. The challenge is not in placing controls on this large surface but in arranging them in a well-organized way. Without careful planning, the form may feel cluttered and inconvenient for users to navigate.

Page Usage Plan.

Let’s plan a four-page layout on a sample form to experiment with control placement and resizing. Assuming the visible area of the form is approximately 10 inches wide, we can define two logical adjoining pages, each about 10 inches wide. Then, leaving about 5 inches from the top of the form, we can define another two pages below the first row. The diagram below illustrates this four-page layout for quick reference in our project.

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

We have planned four logical page areas as shown above. Physically, however, there are only two pages:

• The first-page area is at the top, divided into two segments.

  • The first segment (logical page 1) starts at coordinates (0,0) — 0 inches from the left and 0 inches from the top of the form.

  • The second segment (logical page 2, top right) starts at coordinates (10,0) — 10 inches from the left and 0 inches from the top.

Each logical page is approximately 10 inches wide and 5 inches tall. For the macro that moves controls between pages, we only need to specify the top-left corner coordinates of each logical page.

• The second logical page area (top-right) begins at (10,0).

• The command buttons at the bottom-right corner of each logical page will run a macro to jump between pages.

The Page-Break control.

The dotted line on the left at the 5-inch vertical position is a Page Break control, inserted from the Toolbox, to define the second physical page area (which contains the third and fourth logical pages).

• The third logical page (first segment of the second physical page) starts at 0 inches from the left and 0 inches on the second physical page (i.e., 5 inches down from the top of the form).

• The fourth logical page (the second segment of the second physical page) starts at 10 inches from the left and 0 inches on the second physical page.

The 10-inch width and 5-inch height for logical pages are arbitrarily chosen. You may adjust them if the pages overlap on your screen. Using the same rules, you can define additional logical pages on the form if needed.

The Page Navigation Plan.

Clicking the first CommandButton moves the focus to the second page at the top-right corner. The CommandButton on this page moves focus to the bottom-left page, and the button there moves the focus to the bottom-right page. Finally, the Command Button at the bottom-right page will bring the focus back to the top-left page, completing the loop.

Tip: You can also connect the macro to the Lost_Focus() event of the last textbox or control on each logical page. This way, when the user presses the Tab key, the focus moves from one page to the next without using the CommandButton.

Now that the master plan and diagram are ready, we can implement them on a form. Let’s start by writing the macros to control the CommandButtons.

Create Macros.

1. Select Macro from the Create menu to open a new Macro in the design view.

2. Click on the Macro Names control to open the Macro Name column.

3. Select the first row in the Name column and type Page1-1.

4. Select GotoPage from the drop-down control in the Action column.

5. Select the Arguments column.

6. Type 1 in the Page Number Action Arguments property below.

7. Type 0 in the Right property and 0 in the Down property.

8. Create the other three lines with the Argument values as shown in the image below:

   

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

9. Save and close the Macro as MacPages.

The Form Design.

10. Open a new Form in Design View.

11. Display the Property Sheet (F4).

12. Click the top-left corner of the Form (Access2003) and select the Form’s Property Sheet.

13. Change the Width Property value of the Form to 20 inches.

14. Click on the Detail Section to display its Property Sheet.

15. Change the Height Property Value to 10 inches.

16. Select the Page-break Control from the Toolbox.

17. Find the 5-inch position from the left side Scale, and click near the left border on the Detail Section to place the Page-break control there.

18. Create a TextBox control, about half an inch down in the top-left corner of the Form, and type the child label Caption Value to Page-1.

19. Create a Command Button below the Textbox and change the following property values as shown below:

    • Caption:  Go to 2
    • On Click:  MacPages.Page1-2

      Tip: You may select the Macro Name from the drop-down list to avoid typing mistakes.

20. Create a second Textbox to the right of the first one, about half an inch to the right of the 10-inch scale location on top of the Form, and change the child label Caption to Page-2.

21. Create a Command Button below the Text and change the following property values as shown below:

    • Caption:  Go to 3
    • On Click:  MacPages.Page2-1

22. Create a Textbox below the Page-break control and change the child label Caption to Page-3.

23. Create a Command Button below the Textbox and change the following property values as shown below:

    • Caption:  Go to 4
    • On Click:  MacPages.Page2-2

24. Create a Textbox after the 10-inch location to the right and change the child label Caption to Page-4.

25. Create a Command Button below the Textbox and change the following property values as given below:

    • Caption:  Go to 1
    • On Click:  MacPages.Page1-1

26. Save the Form with a name.

Test Run the Form.

27. Open the Form in normal view, and click the Command Button to jump to the second page to the right.

    You will find that the form jumps to the second logical page to the right, and the Textbox on this page is the active control.

28. Try clicking on other Command Buttons to transfer control from one page to the next.

In all cases, you will find that the textbox on the active logical page is the active control.

• Control Setfocus on Tab-Page Click
• Adding Data directly into an external Database
• Users and Groups Listing from Workgroup Information File
• Access Security Key Diagram
• Updating Sub-Form Recordset from Main Form

Read More

Accounting Year Week Calculations

Accounting Year Week Calculations.

Many companies maintain their Accounting Year from April 1 to March 31 of the following year. If you need a weekly analysis of sales or other activities, the challenge is that the standard DatePart() function in Microsoft Access calculates week numbers starting from January 1, not from April 1. For accounting purposes, April 1–7 should be considered Week 1, April 8–14 Week 2, and so on, regardless of the calendar week number.

The built-in Function DatePart() in Microsoft Access calculates the week numbers based on the activity date provided as a function parameter.  Let us try an example of the DatePart() function directly in the Debug Window.

The date will be passed in dd-mm-yyyy format, and Sunday is the first day of the week. The VBA Constant vbSunday represents the numeric value 1.

dt = DateValue("01-04-2011")
? DatePart("ww",dt,vbSunday)

Result: 14

The built-in DatePart() Function returns week number 14 instead of 1 for the accounting week period of April 1–7. This result can fluctuate between 13 and 14, depending on the first day of the week (the second parameter in the function, vbSunday in this case) and the year. For example, using DatePart() April 1, 2006, returns week 13, not week 1, which shows why an adjustment is needed to calculate accounting week numbers accurately starting from April 1.

To create a weekly Sales Graph for the first quarter of the accounting year (the first 13 weeks covering April, May, and June), we first need to convert the sales dates into their corresponding week numbers. This allows us to aggregate the sales values into weekly totals for the chart.

In short, the DatePart() function cannot be used directly to calculate Accounting months or weeks without some modifications. We can use the DatePart() function within our own Code to modify the output we want.

Function: AccWeek().

I have created a function, AccWeek(), to calculate week numbers for an accounting year running from April 1 to March 31. This function is primarily designed to be called from a query column, with an activity date (such as a sale date, payment date, and so on) as the parameter, to return the corresponding accounting week number. You can also use this function in VBA code, on a form, report, or anywhere else you need to determine the accounting week.

Copy and paste the following Code into a Standard Module in your Database and save it:

Public Function AccWeek(ByVal accDate As Date) As Integer
'--------------------------------------------------------------
'Author : a.p.r.pillai
'Date   : June 2012
'All Rights Reserved by www.msaccesstips.com
'--------------------------------------------------------------
Dim wkdayStart As Integer, wk As Integer, wkout As Integer
Dim accStart As Date, wksave As Integer, accStart1, accStart2

On Error GoTo AccWeek_Err
'First day of the week is taken Sunday as default
'If change is needed in your area please change the next line
wkdayStart = vbSunday
'calculate week number with built-in function
wk = DatePart("ww", accDate, wkdayStart)
'modify the week number according to accounting period
wksave = IIf(wk = 13 And ((Year(accDate) - 1) Mod 4) = 0, wk + 1, 13)
Select Case wk
      Case Is <= 13, 14
         wkout = DatePart("ww", DateValue("31-12-" & Year(accDate)), vbSunday) - wksave + wk
      Case Is > wksave
         wkout = wk - wksave
End Select

accStart1 = "01-04-" & Year(accDate)
accStart2 = "08-04-" & Year(accDate)

'Overlapped Week days check and reset week to 1
If (accDate >= accStart1) And (accDate < accStart2) Then
   wk = DatePart("ww", accDate, vbSunday)
   If wk > 1 Then
     wkout = 1
   End If
End If
AccWeek = wkout

AccWeek_Exit:
Exit Function

AccWeek_Err
MsgBox Err.Description, , "AccWeek()"
Resume AccWeek

End Function

Usage Example-1:

Calling AccWeek() Function from a Query Column:

SaleWeek:AccWeek([SaleDate])

Usage Example-2:

Use it in a Text Box on a Form or Report:

=AccWeek([SaleDate])

Usage Example-3:

Call from within your own Code:

intSaleWeek = AccWeek(dtSaleDate)

Note: The AccWeek() function has not been extensively tested in field conditions and may be used at your own risk. If you find any logical errors in the code, please share them with me too.

Technorati Tags: Microsoft

Earlier Post Link References:

• Adding Data directly into an external Database
• Users and Groups Listing from Workgroup Information File
• Access Security Key Diagram
• Updating Sub-Form Recordset from Main Form
• Easy Reference: Access2003 Commands in Access2007

Read More

Deleting Folders with DOS Command

Deleting Folders with DOS Command.

We have already seen how to create folders with the MkDir() DOS command and how to change the default folder to the active database’s folder using the ChDir() command.

Conversely, if we can create folders, we should also be able to remove them. The RmDir() command serves this purpose, though it is not frequently used as MkDir(). Typically, folders or subfolders are created to organize files for easier access when needed. Folder removal becomes necessary when files are relocated or deleted to free up disk space.

To understand its usage, you can try running the RmDir() command directly from the Immediate Window (Debug Window), as shown below:

RmDir "C:\MyFolder"
Validation Checks.

1. The RmDir() command has a built-in safety check. When executed, it first verifies whether the specified folder is completely empty—containing no files or subfolders. If the command runs successfully, it completes silently without displaying any message. However, if the folder is not empty or cannot be removed, one of the following two error messages will appear:

   If the specified folder path does not exist, it will show a 'Path Not Found' Error Message.

2. If the folder is not empty, then the message ‘Path/File access error’ is displayed.

Using Windows Explorer, we can remove a folder with all its subfolders and files in one clean sweep.  If this is done by mistake, we can always restore them from the Recycle Bin, also before emptying it.

Important Note: Folders or files deleted from a network location using Windows Explorer are not sent to the Recycle Bin. If you have the necessary access rights to delete items on the network, you cannot recover them locally. In such cases, you must contact your Network Administrator to restore them from the most recent LAN backup. As a safeguard, many organizations allow users to create folders but restrict deletion rights to administrators, reducing the risk of accidental data loss.

A Custom Function.

Let’s create a small function named FolderDeletion() that will execute the RmDir() DOS command with proper validation checks. You can add this function to your common utilities library for reuse.

1. Before physically removing a folder, the function will perform the following validations:

   1. Check if the folder exists – Ensure the specified path is valid.

   2. Confirm the folder is empty – The folder should not contain any subfolders or files.

   3. Validate permissions – Confirm the user has rights to delete the folder.

   4. Error handling – Provide meaningful messages if deletion fails.

The Function VBA Code:

Public Function DeleteFolder(ByVal strFolder As String)
On Error Resume Next

If Len(Dir(strFolder, vbDirectory)) > 0 Then
  'Folder exists, ask for permission to delete the folder
  If (MsgBox("Deleting Folder: '" & strFolder & "', Proceed...?", vbOKCancel + vbDefaultButton2 + vbQuestion, "DeleteFolder()") = vbNo) Then
     'User says not to delete the folder, exit program
     GoTo DeleteFolder_Exit
  Else
     'Delete Folder
     RmDir strFolder
     
     If Err = 75 Then 'folder is not empty, have sub-folders or files
        MsgBox "Folder: '" & strFolder & "' is not empty, cannot be removed."
        GoTo DeleteFolder_Exit
     Else
        MsgBox "Folder: '" & strFolder & "' deleted."
        GoTo DeleteFolder_Exit
     End If
  End If
Else
  MsgBox "Folder: '" & strFolder & "' Not found."
End If

DeleteFolder_Exit:
On Error GoTo 0
End Function

If you want an alternative to the same result, we can use VBScript in Microsoft Access.  VBScript is used in Web Pages for server-side actions.  VBScript uses the FileSystemObject to manage Drives, Folders, & Files.  We have used it for creating Text, Word, and Excel Files before.

You can find those examples in the following links:

• Create Text File Access.
• Create Excel and Word Files.

VBScript Function: FolderCreation().

First, let us write a VBScript Function to create a Folder -  C:\MyProjects.

Public Function FolderCreation(ByVal strFolder As String)
Dim FSysObj, fldr
  
  On Error Resume Next 'arrange to capture the error so that it can be check
  'Create the File System Object
  Set FSysObj = CreateObject("Scripting.FileSystemObject")
  'Call the Create Folder Method of the File System Object with Folder Path as parameter
  Set fldr = FSysObj.CreateFolder(strFolder)
  'if this action ended up with error code 58 then the folder already exists
  If Err = 58 Then
     MsgBox "Folder: '" & strFolder & "' already exists."
     GoTo FolderCreation_Exit
  Else
     MsgBox "Folder: " & strFolder & " created successfully."
  End If
  
FolderCreation_Exit:
On Error GoTo 0
End Function

Copy and paste the above function into the Standard Module of your database.  You can try the function by calling it from the Debug Window with a folder name as shown below:

FolderCreation "C:\MyProjects"

VBScript Function: FolderDeletion().

After the sample run, use Windows Explorer to look for the folder name c:\MyProjects. The following VBScript function, FolderDeletion(), can be used for removing a folder:

Public Function FolderDeletion(ByVal strFolder As String)
  Dim FSysObj, fldr
  
  On Error Resume Next
  Set FSysObj = CreateObject("Scripting.FileSystemObject")
  Set fldr = FSysObj.GetFolder(strFolder)
  If Err = 76 Then
     MsgBox "Folder: '" & strFolder & "' No found!"
  Else
     If MsgBox("Delete Folder: '" & strFolder & "' Proceed...?", vbOKCancel + vbDefaultButton2 + vbQuestion, "FolderDeletion()") = vbNo Then
         GoTo FolderDeletion_Exit
     Else
         fldr.Delete 'call the Delete Method of the Folder Object
         MsgBox "Folder: '" & strFolder & "' Deleted."
     End If
  End If
FolderDeletion_Exit:
On Error GoTo 0
End Function

Copy and paste the above code into the Standard Module of your database.  You can run the above code either from the Debug Window or call it from a Command Button Click Event Procedure.

Sample Run from Debug Window:

FolderDeletion "C:\MyProjects"

OR

Private Sub cmdRun_Click()
    FolderDeletion txtFolderPath
End Sub

Earlier Post Link References:

• Users and Groups Listing from Workgroup Information File
• Access Security Key Diagram
• Updating Sub-Form Recordset from Main Form
• Easy Reference: Access2003 Commands in Access2007
• IIF vs Switch function in MS-Access

Read More

ChDir and IN Clause of Access Query

ChDrive() and ChDir() Commands.

Last week, we learned how to change the Directory Path using VBA to the 'CurrentProject.Path' (active database’s location) using ChDrive() and ChDir() Commands, without altering the Default Database Folder settings under Access Options.

If you don’t like to use DOS commands, then you can change the Default Database Folder setting with the following VBA Statement:

Application.SetOption "Default Database Directory", "C:\Developers\Project"

The above statement will change the Default Database Folder to the location specified in the second parameter. The next example changes the Default Database Folder to the active database's location:

Application.SetOption "Default Database Directory", CurrentProject.Path

You can execute the above commands directly in the Debug Window. After running any of the above commands, open the Access Options from the Office Buttons and check the Default Database Folder control value under the Popular options group.

We have already discussed updating/Appending data to external Database Tables (Tables in Access, dBase, etc., not linked to the active Access Database) by using the IN Clause in Queries.  You will find the Article here to refresh your memory.

Caution: If you have Queries in your Databases that reference Tables in external databases to update or Append data to them, the sample SQL given below, it is time to review them to avoid unexpected side effects.

INSERT INTO Employees (EmployeeID, LastName ) IN 'C:\Developers\Projects\Northwind.mdb' 
SELECT 100221 AS EID, "John" AS LN; 

An external or back-end database is stored in a common location, on the Local Area Network (LAN), and can be accessed by several front-end databases on client machines.  This situation raises important considerations of its own. We will address those issues separately, probably next week, as I don’t want to mix them in here and cause any confusion.

Coming back to the IN Clause in the above SQL, if the external Database and the current database are in the same Folder, then you can skip the lengthy path name in the external database reference, like the modified SQL given below:

INSERT INTO Employees (EmployeeID, LastName ) IN 'Northwind.mdb' 
SELECT 100221 AS EID, "John" AS LN;

The main advantage of writing the IN Clause in this way is that you don't have to modify the PathName in all SQL queries when your Application's location changes. The downside is that we need to ensure that the Backend and Front-end Databases are stored in the same Folder; otherwise, the Queries will look for the external database in the earlier location for updating/appending data.  You can do this either using the SetOption method or using the ChDir() Command. Both methods are given below for reference:

SetOption Method:

SetOption "Default Database Directory", CurrentProject.Path

This method permanently changes the Default Database Folder control value in the Access Options area and remains active till it is changed again.  This is a global change in Access Options and may affect other databases when they are open.

ChDir() Method:

Public Function ChangeDir()
Dim vDrive As String * 1, sysPath As String

'get current database Path
  sysPath = CurrentProject.Path

'extract the drive letter alone
'vDrive Variable is dimensioned to hold only one character
  vDrive = sysPath 

'change control to the Drive
  ChDrive vDrive 

'change current location to the database path
  ChDir sysPath 

End Function

This method is harmless because the change is temporary, and the Default Database Folder global setting remains intact. You can use the above Code in databases that require this Function. 

One of these methods must run immediately on opening the database, either through an Autoexec Macro with the RunCode Action or through the Form_Load() Event Procedure of the first Form opened.

Earlier Post Link References:

• Access Security Key Diagram
• Updating Sub-Form Recordset from Main Form
• Easy Reference: Access2003 Commands in Access2007
• IIF vs Switch function in MS-Access
• Preparing Rank List

Read More

Microsoft DOS Commands in VBA-2

Microsoft DOS Commands in VBA-2.

With the MkDir() Command, we were able to create a folder on disk with a small VBA routine that we wrote last week.  We don’t even need a separate program to do this; we can directly execute this command from the Debug Window, like the following example:

MkDir "C:\Developers\Projects"

The only disadvantage of this method is that we cannot perform a validation check before executing this command.  In the VBA program, we have included the validation checks.  That program uses a constant Path, and with some modifications, this program can be further improved to accept the Path string as a Parameter to the CreateFolder() Function.  The Code with improvements is given below:

Creating a Folder.

Public Function CreateFolder(ByVal folderPath As String)

Dim msgtxt As String, folderName As String

'extract the new Folder Name from the folderPath Parameter
folderName = Right(folderPath, Len(folderPath) - InStrRev(folderPath, "\"))

'check for the new folder name, if not found proceed to create it
If Dir(folderPath, vbDirectory) = "" Then 
   msgtxt = "Create new Folder: " & folderPath & vbCr & "Proceed ...?"
   If MsgBox(msgtxt, vbYesNo + vbDefaultButton1 + vbQuestion, "CreateFolder()") = vbNo Then
      Exit Function
   End If
   MkDir folderPath 'try to create the new folder

'check whether the folder creation was successful or not
   If Dir(folderPath, vbDirectory) = folderName Then
      msgtxt = folderPath & vbCr & "Created successfully."
      MsgBox msgtxt
   Else
'if the code execution enters here then something went wrong
      msgtxt = "Something went wrong," & vbCr & "Folder creation was not successful."
      MsgBox msgtxt
   End If
Else
  'the validation check detected the presence of the folder
   msgtxt = folderPath & vbCr & "Already exists."
   MsgBox msgtxt
End If

End Function

In all the preceding examples, we provided the full pathname of the existing location where the new folder is to be created, with the new folder name specified at the end. However, if you are certain of the current location (that is, the active path of the current database on disk), you can issue the MkDir() command using only the new folder name, as shown in the following example:

MkDir "Projects"

Finding out the Current Folder.

As far as VBA is concerned, the current location is not the one you have selected using Windows Explorer. Or the one selected using DOS Command ChDir(), which runs directly under the DOS Command Prompt.

But with a small trick, we can find out which is the current folder that VBA is aware of; that is, by running the Shell() command directly from the Debug Window to invoke the DOS Command Prompt from VBA, like the example given below:

Call Shell("cmd.exe")

The above command will open the DOS Command Prompt (if it is minimized on the taskbar, then click on it to make that window current), and the Cursor will be positioned in the current folder. Check the sample image given below:

If you have used MkDir "Projects" without knowing where the folder will be created, type Dir and press the Enter Key. This will display a list of files and directory names, with the

label indicating which entries are folders.

However, the above method is not the only way to check the Default Database Folder location. Select Access Options from the Office Button, select the Popular option group in Access 2007, and locate the Default Database Folder setting. You can change this location if necessary. Refer to the image below:

Try to open a Database from another location on disk, but this setting will not change, and the Default Database Folder will remain active as per this setting.  Without touching the above Access default setting, we can change the active folder to the newly opened database’s parent directory with the use of the following DOS Commands from VBA (you can try this by typing these commands directly in the Debug Window):

? CurrentProject.Path

This is not a DOS Command, but the above VBA statement retrieves the active database's Path. Let us assume that the retrieved location of the current database is: C:\MDBS

Using the above information in the next two DOS Commands, we can change the control to the active database's location, without changing the Default Database Path setting, which we have seen earlier:

ChDrive "C" 'change control to C: Drive.  This is necessary if the control was on a different drive
ChDir CurrentProject.Path 'change control to the active database's folder

ChangeDir() Command.

By combining the above statements, we can write a very useful Function ChangeDir() to change the control to the current Database Folder.  Copy and paste the following Code into a Standard Module of your Database and save it:

Public Function ChangeDir()
Dim vDrive As String * 1, sysPath As String

'get current database Path
  sysPath = CurrentProject.Path

'extract the drive letter alone
'vDrive Variable is dimensioned to hold only one character
  vDrive = sysPath 

'change control to the Drive
  ChDrive vDrive 

'change current location to the database folder
  ChDir sysPath 

End Function

Call the above Function from an Autoexec macro with the RunCode Action or from the Form_Load() Event Procedure of the first Form open (the Startup Screen or Main Switchboard) to change to the active database folder.

• Updating Sub-Form Recordset from Main Form
• Easy Reference: Access2003 Commands in Access2007
• IIF vs Switch function in MS  Access
• Preparing Rank List
• Restoring Disabled Full Menus Access 2007

Read More