LEARN MS-ACCESS TIPS AND TRICKS

Missing Lines in Line-Chart

a.p.r. pillaiAugust 14, 2012Graph Charts No comments

Introduction.

You spent several hours preparing the data for your line chart. Designed the Line-Chart on a Report with 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 on 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, resulting in the Graph Chart ignoring these cell values and not connecting other values with lines, without breaks in between. While preparing data (source Table/Query) for the Graph Chart, ensure that none of the cells end up with a Null value. 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, resulted in connecting 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.

Open the Report with the Graph Chart in Design View.
Click on the Chart’s outer frame to select it.
Display the Property Sheet.
Click on the build (...) button on the Row Source Property to open the Graph Chart Source Query in Design View.

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;

Save and close the Query.
Open the Report with the Graph Chart in Print Preview mode to view the effect of the change.

Back Tracking Open Forms

a.p.r. pillaiJuly 29, 2012Forms 2 comments

Introduction

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 delay during startup, 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 shown 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.

Import the Employees Table and Employees Form from Northwind.mdb (or Northwind) sample database.
Check the sample image given below.
Right-click to open Large Image in New Tab/Window.
Rename the Employees form as Employees1.
Open the 'Employees1' Form in Design View.
Add a label control in the Header of the Form and change the Caption value to 1, change the font size to 16, and the foreground color to White or any other bright color suitable for the background.
Expand the Footer of the Form.
Add two Command Buttons in the Form Footer as shown above.
Click on the left side Command Button to select it.
Display its Property Sheet (F4).
Change the Name property value to Back and change the Caption property value to << (two less than symbols).

Two Button-Click Event Sub-Routines.
Select the OnClick Event property and select [Event Procedure] from the drop-down list, and click on the build (...) Button to open the VBA editing window with the empty Sub-routine stub: Private Sub Back_Click() . . . End Sub.
Copy the following Code and paste it, overwriting the sub-routine 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.
Repeat steps 7 to 10 for the right-side Command Button by changing the Name property value to Forward() and the Caption property value to >> (two greater than symbols).
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
```
Save and close the Form.

The Move ForwardBack() Function

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

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 to distinguish one from the other.
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 define any one of the form’s Window Mode as Normal to make that form visible in the Application Window, while all other forms stay hidden.
Save the Macro with the name 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.

Open Forms Employees1 to Employees5 manually, one by one, from the Navigation Pane.
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), and in the same way, access other properties of the form. 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 these internal 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 on the Command Button with the >> (Go Forward) symbols on it to move forward to the next form, but nothing will happen because this is the last form in the opened Forms Collection now.
Click on the Command Button with the << (Go Back) symbols to make the Employees4 form visible and to place the current form Employees5 in the hidden state in memory.
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 the other forms remain in memory in a hidden state, since our main program is designed to keep them that way.
Try to move forward by clicking on 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).

Stretching Controls when resizing Form View

a.p.r. pillaiJuly 14, 2012Forms No comments

Introduction

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, in maximized Application Windows, shows an empty stretch of space to the right of the data fields. The Form Title Employees stay where it is placed in a Label control, centralized over 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 with the Employees Table from the sample database Northwind.

Import the Employees Table from the Northwind sample database.
Create a form with a similar Design shown at the top of this page. You can use the Form Wizard to create the form in Column format and rearrange the controls.
Select all the controls of 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.
Select Fax Number and Address Fields, and right-click on them to display the shortcut menu.
Select Cut from the menu to remove both text boxes and their child labels from the first column controls group.
Right-click somewhere in the Detail Section and select Paste to paste them back into the detail section.
Move the pasted controls and place them on top of the second column of controls.
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 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.

Save the Form and open it in Form View to check what the current design looks like.

Implementing the Trick.
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 width of the form 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.
Click on the heading label to select it.
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.
Place the Form back into Design View.
Highlight and select all the second column TextBoxes.
Select Arrange ->Anchoring -> Top Right or right-click on the selected controls and select Anchoring -> Top Right from the shortcut menu.
Change the form in Form View and preview the effect of our setting. Change the form back into Design View again.
Select the TextBoxes in the left column, except the Note field.
Right-click on the controls and select Anchoring -> Stretch Across Top.
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.

Defining Pages on Form

a.p.r. pillaiJune 28, 2012Defining Pages, Form, Forms 2 comments

Introduction.

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 pages side by side, 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 spans 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 located 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 vertically on the second physical page (i.e., 5 inches down from the top of the form).
The fourth logical page (second segment of the second physical page) starts at 10 inches from the left and 0 inches vertically 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 on the first Command Button moves the focus to the second page at the top-right corner. The Command Button on this page will send the focus to the bottom-left page, and the button there will move the focus to the bottom-right page. Finally, the Command Button on 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 will automatically move from one page to the next without needing to click a Command Button.

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

Create Macros.

Select Macro from the Create menu to open a new Macro in the design view.
Click on the Macro Names control to open the Name column of the macro.
Select the first row in the Name column and type Page1-1.
Select GotoPage from the drop-down control in the Action column.
Select the Arguments column.
Type 1 in the Page Number Action Arguments property below.
Type 0 in the Right property and 0 in the Down property.
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.
Save and close the Macro with the name MacPages.

The Form Design.

Open a new Form in Design View.
Display the Property Sheet (F4).
Click on the top left corner of the Form (Access2003) to select the Form’s Property Sheet or select Form in the Selection Type box (Access2007) of the Property sheet.
Change the Width Property value of the Form to 20 inches.
Click on the Detail Section to display its property sheet.
Change the Height Property Value to 10 inches.
Select the Page-break Control from the Toolbox.
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.
Create a Textbox control, about half an inch down on the top left corner of the Form, and change the child label Caption value to Page-1.
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.
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.
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
Create a Textbox below the Page-break control and change the child label Caption to Page-3.
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
Create a Textbox after the 10-inch location to the right and change the child label Caption to Page-4.
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
Save the Form with a name.

Test Run the Form.

Open the Form in normal view and click on 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.
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.

Accounting Year Week Calculations

a.p.r. pillaiJune 12, 2012Accounting Year, Functions 1 comment

Introduction.

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.

We have the built-in Function DatePart() in Microsoft Access to calculate Week numbers based on the activity date provided to the function as a parameter. Let us try an example of the DatePart() function directly on the Debug Window.

The date will be passed in dd-mm-yyyy format, and Sunday is taken as 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, etc.) 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: AccWeek() Function is not 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:

Deleting Folders with DOS Command

a.p.r. pillaiMay 27, 2012DOS Commands, Functions No comments

Introduction.

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 as commonly used as MkDir(). Typically, folders or sub-folders are created to organize files for easier access when needed. Folder removal becomes necessary only when files are relocated or deleted, and freeing up disk space is required.

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.

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.
If the folder is not empty, then the message ‘Path/File access error’ is displayed.

Through Windows Explorer, we can remove a folder with all its sub-folders and files in one clean sweep. If this is done by mistake, then 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, recovery cannot be done 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.

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 to do that. VB Script is mostly used in Web Pages for server-side actions. VB Script 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:

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 VB Script 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"


Private Sub cmdRun_Click()
    FolderDeletion txtFolderPath
End Sub

Earlier Post Link References:

ChDir and IN Clause of Access Query

a.p.r. pillaiMay 14, 2012ChDir, Functions No comments

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 earlier updating/Appending data into external Database Tables (external Tables of 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 into them, like 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 it can be accessed by several front-end databases from different client machines. This situation raises important considerations of issues 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 omit the lengthy Pathname 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 change the PathName in all SQLs of Queries on location change of your application. The downside is that we need to ensure that the Default Database Folder location is the active database's folder; otherwise, the Queries will look for the external database in the old 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:

Microsoft DOS Commands in VBA-2

a.p.r. pillaiMay 06, 2012DOS Commands, Functions No comments

Continued from Last Week.

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 constant values as Path, and with a few 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 above and earlier examples, we have provided the full path of the existing location, where the new folder is to be created, with the new folder name at the end. If you are sure where the current location is (or the active path of the current database on disk), then you can issue the MkDir() command with the new folder name alone, like the following example:

MkDir "Projects"

Finding out the Current Folder.

As far as VBA is concerned, the current location is not what 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" like a command without knowing where it is going to be created, then type Dir and press Enter Key to display a list of files and directory names with the label <Dir> to indicate they are folders.

That doesn’t mean the above method is the only option to check the Default Database Folder location. Select Access Options from the Office Button, and select the Popular Option Group(Access 2007), and the Default Database Folder settings. You can change it if it becomes necessary. Check the image given 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 on 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 (like the Startup Screen or Main Switchboard) to change control to the active database folder.

Microsoft DOS Commands in VBA

a.p.r. pillaiApril 24, 2012DOS Commands, Functions 1 comment

Continued from Last Week.

Continued from last week’s Article: Disk Operating System Commands in VBA.

Once we determine the presence of a file in a folder with the Dir() Command, we can do certain operations on the file, like opening that file in its parent application through the Shell() Command or making a copy of that file to a different location with the FileCopy() Command or deleting it with the Kill() Command.

Example-1:

Check for the presence of a text file in a folder, and if found, open it in Notepad.exe

Public Function OpenTextFile()
Dim txtFilePath As String
Dim NotePad As String

   txtFilePath = "C:\msaccesstips\htaccess.txt"
   NotePad = "C:\Windows\System32\Notepad.exe"

If Dir(txtFilePath, vbNormal) = "htaccess.txt" Then
   Call Shell(NotePad & " " & txtFilePath, vbNormalFocus)
Else
   MsgBox "File: " & txtFilePath & vbcr & "Not Found...!"
End If

End Function

Example-2:

: Make a copy of the file with the FileCopy() Command.

Public Function CopyTextFile()
Dim SourcefilePath As String
Dim TargetFilePath As String

   SourcefilePath = "C:\msaccesstips\htaccess.txt"
   TargetFilePath = "C:\New Folder\htaccess.txt"

If Dir(SourcefilePath, vbNormal) = "htaccess.txt" Then
   FileCopy SourcefilePath, TargetFilePath
   MsgBox "File copy complete."
   
Else
   MsgBox "File Not Found...!"
End If

End Function

Example-3: Find and Delete a File from a specific location on the Hard Disk.

Public Function DeleteFile()
Dim FilePath As String, msgtxt As String

   FilePath = "C:\New Folder\htaccess.txt"

If Dir(FilePath, vbNormal) = "htaccess.txt" Then
   msgtxt = "Delete File: " & FilePath & vbCr & vbCr
   msgtxt = msgtxt & "Proceed...?"
   If MsgBox(msgtxt, vbYesNo + vbDefaultButton2 + vbQuestion, "DeleteFile()") = vbNo Then
      Exit Function
   End If
   Kill FilePath
   MsgBox "File: " & FilePath & vbCr & "Deleted from Disk."
   
Else
   MsgBox "File: " & FilePath & vbCr & "Not Found...!"
End If

End Function

Check for a Folder Name:

Dir() Function can also be used to inspect the presence of a folder in preparation for creating a new folder in a particular location on the Hard Drive.

The following Command checks for the presence of a particular folder on the C: drive:

strOut = Dir("C:\Developers\Projects", vbDirectory)

The second parameter, vbDirectory, asks the Dir() command what to look for in the specified Path. If the folder Projects is found under the C:\Developers folder, then the folder name Projects is returned in the strOut variable; otherwise, it returns an empty string.

The MKDIR Command

The MkDir() Command can be used for creating a new folder if the Projects folder doesn't exist.

Let us write a small program to check the presence of the Projects folder. If it doesn’t exist, then let us create the folder.

Public Function CreateFolder()
Dim folderPath As String
Dim msgtxt As String

folderPath = "C:\Developers\Projects"

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
   If Dir(folderPath, vbDirectory) = "Projects" Then
      msgtxt = folderPath & vbCr & "Created successfully."
      MsgBox msgtxt
   Else
      msgtxt = "Something went wrong," & vbCr & "Folder creation was not successful."
      MsgBox msgtxt
   End If
Else
   msgtxt = folderPath & vbCr & "Already exists."
   MsgBox msgtxt
End If

End Function

The Dir() Command can check the volume label of the Disk Drive.

The following command, run directly from the Debug window, gets the Volume Label of the Hard Drive if it exists; otherwise, it returns an empty string:

? Dir("D:", vbVolume)

Result: RECOVERY

Earlier Post Link References:

Disk Operating System Commands in VBA

a.p.r. pillaiApril 13, 2012Disk, Functions, Operating System No comments

Introduction.

The Microsoft Disk Operating System (MS-DOS 1.0) was launched in 1982. The first edition of Microsoft Windows 1.0 Operating System, Disk Operating System with Graphical User Interface (GUI), was released on November 20, 1985 – Source: www.wikipedia.org. The Disk Operating System Version under Windows 7 is 6.1.7600.

Disk Operating System Commands (both Internal and External) are directly used under the Command Prompt for managing Files and folders on Disks, and for retrieving information on them.

DIR Command.

For example, the Directory Command (Dir /S/B/P) provides a list of all files with full Path Names (like 'C:\My Documents\New Folder\Resume.doc') from your C: Drive and displays them on screen, one page at a time.

Let us try an example.

Click on the Start Menu.

Type cmd and press Enter, the DOS Window will open up with the Command Prompt C:\>. Type the following command to display a list of Folders/Files from your C: drive, one page at a time. You must press a key to advance the list from one page to another.

Warning: The Folders/Files listing on your C: drive may run into hundreds of pages. Press Ctrl+C (break the command) to terminate the list from displaying further.

C:\> Dir /S/B/P

C:\>> is the command prompt

Dir (command stands for Directory)

The Command switch /S includes Files in Sub-directories also in the output.

The Command switch /B provides a bare-formatted list of files, i.e., gives only the file path names without creation date, file sizes, or any other information about the files.

The Command switch /P displays the output on Screen one Page (one screen full) at a time. Needs to press a key on the keyboard to advance the list of files to the next Page.

If you want to save the entire list into a text file, without page breaks, issue the following command with the output redirection symbol (>) with a text file name. The redirection symbol will send the output of the Directory command to a specified text file, without displaying it on the screen.

C:> Dir /S/B > myDirList.txt

Note: Leave a space on either side of the > symbol.

TYPE and MORE Command.

Open the myDirList.txt file in Notepad and check the contents. You can display the contents of the myDirList.txt file with the following DOS Command:

C:\> TYPE myDirList.txt

Press Ctrl+C to stop the runaway display. The TYPE command displays the contents of a text file on screen. But it will not display the output one screen full at a time. To do that, we can seek the help of another DOS command: MORE, with the use of the piping symbol (|).

C:\> TYPE myDirList.txt | MORE

In the above command, the piping symbol (|) is used to join the TYPE filename.txt | MORE commands to get the required output. The TYPE command reads the text file contents and passes it on to the next command, MORE, without directly sending the output to the Screen. The MORE Command takes its input from the TYPE command, through the pipe, and displays it one screen-full at a time. Press SPACEBAR to display the next screen-full of text.

C:\> MORE < myDirList.txt

If the > (greater than) symbol is known as a redirection symbol in DOS, then the < (less than) symbol is known as a Source symbol for the MORE command. The MORE command reads data from the filename given immediately after the Source Symbol (<) and displays one screen-full at a time.

DIR Command in VBA.

The Dir Command is available in VBA also. But it is used for a different purpose. We can use this command to check the presence of a particular file or the existence of any file in a folder. The usage of this command is shown below:

strOutput = Dir("C:\My Documents\Resume.doc", vbNormal)

The Dir command checks for the presence of Word File Resume.doc in Folder C:\My Documents. If found, then the file name 'Resume.doc' is returned in the strOutput Variable; otherwise, it will return an empty string.

strOutput = Dir("C:\My Documents\*.*", vbNormal)

This command gets the first file name from the specified folder, and returns it in the strOutput Variable. You may try out this command in the Debug Window directly, like:

? Dir("C:\My Documents\*.*")

This will print the first file name found in the folder C:\My Documents in the Debug Window. To get subsequent file names from the same folder, you can run the command without any parameters to the function, like:

? Dir()

Note: The First time you run this Command, you should provide a Path as a parameter; otherwise, it will end up with an error.

Place the insertion point on the Dir() Command and press F1 to display the details of this Command in Access Help Documents.

There are other interesting Disk Operating System Commands, like ChDrive, ChDir, MkDir, RmDir, etc., and we will learn their usage in VBA Next week.

Earlier Post Link References:

Easy Reference: Access2003 Commands in Access2007
IIF vs Switch function in MS-Access
Preparing Rank List
Restoring Disabled Full Menus in Access 2007
Running Workgroup Admin in Access 2007

Introduction.

Tracking Down the Real Issue.

Modifying the Chart Data Source Query.

Introduction

Two Button-Click Event Sub-Routines.

The Move ForwardBack() Function

Test Run our Creation and Program.

Closing All Open Forms - The CloseAllForm() Function

Introduction

Implementing the Trick.

Testing the Heading Label Behavior

Moving On

Introduction.

Page Usage Plan.

The Page-Break control.

The Page Navigation Plan.

The Form Design.

Test Run the Form.

Introduction.

Function: AccWeek().

Usage Example-1:

Usage Example-2:

Usage Example-3:

Earlier Post Link References:

Introduction.

Validation Checks.

The Function VBA Code:

VBScript Function: FolderCreation()

Earlier Post Link References:

ChDrive() and ChDir() Commands.

Earlier Post Link References:

Continued from Last Week.

Creating a Folder.

Finding out the Current Folder.

ChangeDir() Command.

Continued from Last Week.

Example-1:

Example-2:

Check for a Folder Name:

The MKDIR Command

Earlier Post Link References:

Introduction.

DIR Command.

TYPE and MORE Command.

DIR Command in VBA.

Earlier Post Link References:

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

Post Feed

Get new posts by email:

Search

USEFUL LINKS

Popular Posts

About Me

Blog Archive

Labels

Statcounter