Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Conditional Formatting in Continuous Form


Conditional formatting is a magical option to change color (background/foreground) of textbox controls on Forms or Report.  The color change is possible when one or more field values meets a specific condition.  The specific condition can be tested on the field, where we want to set color on or can be based on values in some other field.

The color change has three different possible choices and their variations:

  1. Color change when the field has focus.
  2. Color change when the field value meets a specific condition.
  3. Color change depending on conditional values in some other field.

Changing the BackGround/ForeGround Color.

We will try out an example, to learn how to change the background/foreground colors of all fields of certain records, which meets a specific condition on a single field value, on a continuous form.

A sample image of a Form with conditional formatting is given below for reference:

The records are formatted based on values in the UnitPrice field.  The background color is set on records with UnitPrice value is greater than $30.  The font is set with Red color on records with UnitPrice value falls between 20 and 30.

Design a Continuous Form.

  1. Import the Order Details and Products Tables from the Northwind.accdb sample database.
  2. Design a continuous form for the Order Details Table, like the sample image below:

    Setting up the Conditions.

  3. Open the Order Details Form in Design View.
  4. Select the OrderID field and open the Conditional Formatting dialog control.
  5. Select Expression Is under Condition 1 and type [UnitPrice]>30 in the right-side control.
  6. Select Fill/Back Color to display the color palette and select a light color for the background.
  7. Click on the Add>> button to display options for a second condition for the same field.
  8. Select Expression Is in Condition 2 control.
  9. Type the expression [UnitPrice]>=20 And [UnitPrice]<=30 in the next text control.
  10. Select Red Font Color for formatting from the color palette and click OK to close the formatting dialog box.
  11. Repeat Steps-4 to Step-10 for ProductID, Quantity and Discount fields.
  12. Select UnitPrice field and display the Conditional Formatting dialog control.
  13. Select Field Value Is in the Condition 1 control.
  14. Select the same background color selected, for other fields for formatting, from the Color Palettes.
  15. Click on the Add>> Button to add a new condition for the same field.
  16. Select Field Value Is in Condition 2 control.
  17. Select Between from the drop-down list in the next control.
  18. Type 20 in the next control and type 30 in the last control.
  19. Select the Red Font Color for formatting from the color palettes and click OK to close the conditional formatting dialog box.

    Test Run your Creation.

  20. Save the Form and open it in normal view.

You should see the form view similar to the sample image shown above.  Scroll the form down to view more records with conditional formatting.


Autonumber with Date and Sequence Number-2


Perhaps you may be wondering, whey we need something different when there is an Autonumber feature already built-in into Microsoft Access?  Well, the built-in feature may not be suitable for all situations, like Patient's Unique Registration Numbers in hospitals, with change of date and sequence numbers. We have created a function earlier for generating auto-numbers with date and sequence numbers and you can have that from the first link given below. You may visit the other related links for some different approaches for generating Auto-numbers in Queries too.

We are now going to take a different approach in generating auto-numbers with date and sequence numbers. Let us take a re-look at the last method we have created with date & sequence numbers, and how we are going to reformat the same thing in the new method with lesser number of digits as follows:

Sample Data Image

Sample Dates: 30-10-2012 and 31-10-2012

The Auto-number generated for patient's registration looks like the following, the format used in the earlier article (first link above):

Format: yyyymmdd-999

Autonumber-with-Date method-1
Saved Number Display with Inputmask
20121030001 20121030-001
20121030002 20121030-002
20121030003 20121030-003
20121030004 20121030-004
20121030005 20121030-005
20121031001 20121031-001
20121031002 20121031-002
20121031003 20121031-003

The dash in the number is inserted using the Input-mask for better readability in the display control. In the above example it uses eight digits for displaying the date part and three digits for serial numbers. This method requires a total of 11 digits for the auto-number.

In the following new method, we are going to create date-wise changing auto-numbers, which takes only eight digits, like the example shown below:

Sample Date: 30-10-2012 and 31-10-2012

New display format: yyddd-999

First two digits (yy) stores the year (12), next three digits (ddd) are the day number from 1st January (is 001) onwards, October 30th, 2012 is 304th day from 1st January 2012.

Autonumber-with-Date method-2
Saved Number Display with Inputmask
12304001 12304-001
12304002 12304-002
12304003 12304-003
12304004 12304-004
12304005 12304-005
12305001 12305-001
12305002 12305-002
12305003 12305-003

The sequence numbers resets to 001 when the date changes.  The new method result is somewhat compact in size and takes only eight digits to store the auto-number in place of eleven digits in the earlier method.

DaysAsOnMonth() Function.

Copy and paste the following VBA Code into a Standard Module of your Database:

Public Function DaysAsOnMonth(ByVal dt As Date) As Long
Dim i As Integer, j As Integer, tdays As Long, d As Long

On Error GoTo DaysAsOnMonth_Err
i = Month(dt)
d = DatePart("d", dt)

For j = 1 To i - 1
tdays = tdays + Choose(j, 31, 28 + IIf(Year(dt) / 4 = Int(Year(dt) / 4), 1, 0), 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)
If (Year(dt) Mod 400) = 0 And Month(dt) > 2 Then
  tdays = tdays - 1
End If
tdays = Val(Right(Year(dt), 2)) * 10 ^ 3 + tdays
tdays = tdays + d
DaysAsOnMonth = tdays

Exit Function

MsgBox Err & " : " & Err.Description, , "DaysAsOnMonth()"
Resume DaysAsOnMonth_Exit

End Function

The AutoNumber() Function.

Public Function AutoNumber(ByVal strField As String, ByVal strTable As String) As String
Dim dmval As String, dt1 As String, dt2 As String, Seq As Integer, dv As String

On Error GoTo AutoNumber_Err

'get the highest existing value from the table
dmval = Nz(DMax(strField, strTable), 0)

'if returned value is 0 then the table is new and empty
'create autonumber with current date and sequence 001
If Val(dmval) = 0 Then
   dv = Format(DaysAsOnMonth(Date) * 10 ^ 3 + 1)
   AutoNumber = dv
   Exit Function
End If

'format the number as an 11 digit number
dv = Format(dmval, "00000000")
'take the 3 digit sequence number separately
Seq = Val(Right(dv, 3))
'take the date value separately
dt1 = Left(dv, 5)
'get today's date
dt2 = Format(DaysAsOnMonth(Date))
'compare the latest date taken from the table
'with today's date
If dt1 = dt2 Then 'if both dates are same
   Seq = Seq + 1 'increment the sequence number
   'add the sequence number to the date and return
   AutoNumber = Format(Val(dt1) * 10 ^ 3 + Seq)
   Exit Function
Else 'the dates are different
   'take today's date and start the sequence with 1
   AutoNumber = Format(Val(dt2) * 10 ^ 3 + 1)
End If

Exit Function

MsgBox Err & " : " & Err.Description, , "AutoNumber()"
Resume AutoNumber_Exit

End Function

How It works.

The first function DaysOfMonth() is called from the AutoNumber() Function to calculate numbers days from January 1st to the date passed as parameter to the function.  The input date 30-10-2012 will return the result value 304, i.e. 31+29+31+30+31+30+31+31+30+30 = 304.

The trial run procedure for the new method is already published in an earlier Article. I will take you there to the exact point in that Article, from where you can continue reading and prepare yourself for the demo. All that you should do is to change the Function name Autonum(), appearing in those sample run lines, to AutoNumber().

Click to continue...

Command Button Color Change on Mouse Button-down Action


After working with the Command Button and Message Box Controls of 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 of those Articles and free downloads 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 with Office Assistant
  5. MsgBox with Options Menu
  6. MsgBox with Checkbox Menu

Some New Trick on Command Button.

Here is some new trick for you to use on Command Button Control. Command Button will change color on the mouse-button down action and will restore original color on the mouse-up action.  You can do this with a Label control, with the same dimension of the Command Button, filled with the color you like and by placing 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 on it and highlight 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 it’s 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 on the Build ( . . .) button to display the Color Palette.
  14. Select the color you like to fill the background of the label control.
  15. Move the Label control behind the Command Button control and place it completely hidden.
  16. If the label control is overlapping the Command Button then display the Shortcut Menu by right-clicking on it and select Send to Back from the Position group.
  17. Click on the Command Button and display the property sheet (F4).
  18. Click on the Event Tab and select the Mouse down Event, select [Event Procedure] from the drop-down control and click on the build (. . .) button to open the Form’s VBA Module Window.
  19. Copy and Paste the following Code into the Form Module, over-writing the existing two empty lines appeared there:
    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 with the name frmButton.
  2. Open frmButton in Form View.
  3. Click on the Command Button and hold the mouse button down.  You will see the Command Button’s Color changes to the background fill color you have selected for the label control and Command Button caption text Close appearing on it.
  4. Release the mouse button.  The original state of the Command Button is restored.

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

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

  1. Command Button Animation
  2. Double Action Command Button
  3. Colorfull 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


Time-bound Form Mode Change


Allow data entry or editing on certain period of time otherwise lock the form for data view alone.  The reason to do this may be for time-bound work schedules or some other reason.  Request for a solution to this kind of  action was raised in a Microsoft Access Discussion Forum on the web and I thought I will give it a try and written a program to enable the form for Data Entry/Edit between 06:00 to 07:00 Hours, 11:00 to 13:00 Hours and 17:00 to 19:00 Hours otherwise disable the form and allow only data view mode.

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
      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
'change .subFrmName to match the control (window) name of the sub-form
                .subFrmName.Enabled = False
            End If
          End With
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 value to lblMsg and write the message in the Caption property.
  3. Display the Form Property Sheet.
  4. Set Timer Interval value to 60000 (i.e. 1 minute).  The Form’s current mode is checked in one minute interval apply changes automatically.  You may increase the time interval by adding 1000 to the above value for each second increase.
  5. Select On timer Event, select [Event Procedure] from the drop-down control and click on the build ( . . .) button to open the VBA module.
  6. Copy and Paste the following lines of Code replacing the existing two lines displayed there:


    Private Sub Form_Timer()
       Data_Entry Me.Name
    End Sub
  7. Save and Close the Form.

Tracking the Time for Form Mode Change.

The Timer setting will run the above program and will check every minute, whether the current time falls within the time slots given in the program, if true then the Main-form and a Sub-Form on the main form will be enabled for Editing/Entry purposes, otherwise they will be locked.

When you open the Form there will be a one minute delay before Access starts checking. You may call the Data_Entry() function from the Form_Current() event procedure to start checking immediately after opening to avoid the one minute initial delay.


Missing Lines in Line-Chart


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

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

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

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 them resulting the Graph Chart to ignore 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 Null value. If there are Cells with Null values then fill them up with Zeroes.

The corrected Chart Table, filled with zero values in empty cells resulted in connecting the points with 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:
    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

Back Tracking Open Forms


On Internet we may open several web pages, by visiting links on other web pages. When we do that we will be provided with buttons on the browser to move from the current page to the previously opened pages or move forward one page at a time from earlier visited pages.

Similarly, if we need to keep several forms opened at the same time in Microsoft Access, to work with them by moving back or Forward form one to the other open form, then there is a way to do it.

When there are several forms to open for normal operations then it is a good idea to open all those forms once, immediately after the application is open, and keep them in memory in hidden state.  This may cause a slight delay at the time of opening the application to become ready for normal operations but later on they will be best performed when displaying them from memory, rather than opening and closing them every time.  All the opened forms in memory can be closed easily with a small VBA program before shutting down the application.

If jumping from one form to the other is required in a predictable sequence then all the forms can be opened through a simple macro by arranging the opening sequence in the required order.  See an image of a sample macro given below that opens several forms in the required order.

The first OpenForm Action opens the first Form (Employees4) in Normal View mode, others are in hidden mode to keep them in memory.  The required form can be made visible, at the same time the active form can be kept hidden in memory too.  By doing this only one form will be in visible state rather than crowding all the forms in the application window. 

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.accdb) sample database.

    Check the sample image given below.

  2. Rename the Employees form as Employees1.
  3. Open the Employees1 Form in Design View.
  4. Add a label control on the Header of the Form and change the Caption value to 1, change the Font-size to 16 and the fore-color to White or any other bright color suitable for the background.
  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 Button to select it.
  8. Display it’s 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 On Click 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.
  11. Copy the following Code and Paste them over-writing the sub-routine lines in the VBA Module:

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

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

  12. Repeat step-7 to step-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).
  13. Copy the following Code and Paste them over-writing 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
    Exit Function
    MsgBox Err & ":" & Err.Description, , "ForwardBack()"
    Resume ForwardBack_Exit
    End Function

    ForwardBack() Function needs two parameters when called:

      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, 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 number in the header labels also change to 2,3,4 and 5 in their respective Forms.

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

  17. Create a Macro similar to the sample image shown on top of this page, to open the forms and keep them hidden in memory except 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 stays hidden.
  18. 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.

  1. Open Forms Employees1 to Employees5 manually, one by one from the Navigation Pane.
  2. You have now all the Forms opened in the Application Window.  The Employees5 form is on top, with the form header label showing number 5.

    When you open several forms this way they are placed in memory in the opened FORMS Collection Object in an indexed order.  The first form opened is addressable with zero index number as Forms(0) or Forms(“Employees1”) in VBAForm’s Name can be retrieved from the Name property of the opened form, like Forms(0).Name and similarly other properties of the form can be addressed as wellThe second Form opened will have index number 1 and so on.   The suffix numbers we have added to the Employees Form have nothing to do with this internal index numbers.  You can open those Forms in any order you like, not necessary that you should start with Employees1 and end with 5.  It is better that way initially to test the program.

  3. 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.
  4. Click on the Command Button with the << (Go Back) symbols to make Employees4 form visible and to place the current form Employees5 in hidden state in memory.
  5. Repeat step-2 to make Employees3 form visible and continue doing this till you reach the Form Employees1. 

    At this stage clicking on the back button (<<) on this Form will not show any respons because this is first form we have opened.  But, if you have opened these forms in different order then it will work.  When you reach the Employee1 form all the other Forms are in hidden state in memory because our main program is made to do that.

  6. Try to move forward, by clicking on the >> button, to the other forms and make them visible one by one, hiding the earlier forms.

Closing All Open Forms - The CloseAllForm() Function

Tip: If you want to make some changes on any of these forms, while the form is in hidden state in memory, you may right-click on the Form name in the navigation pane and select Design View.  The Form will appear in Design View.

You can easily close all the opened forms (both hidden as well as visible ones) with a single click, while shutting down the Application with a simple VBA routine CloseAllForms(). The CloseAllForms() program can be called from a Command Button click Event Procedure and before executing the DoCmd.Quit statement.  You may even run the program directly from the VBA window, while testing the above procedures.  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
End Function

Click in the middle of the Code and press F5 to run it and close all the open forms.

For the above test we have opened all Forms manually.  You may run the macro (Macro1), we have created earlier, to open all the forms at once and keep all of them hidden except one.

If you want to run this macro automatically, immediately on opening the database, to open all the forms and keep them hidden then rename the macro (Macro1) as Autoexec.

Opening of Forms can happen during normal operations by Users and they will be in the order of their opening sequence in memory.  Users can work with the open Forms by clicking on the Command Button with the symbols >> (to Go Forward) or by clicking on the Command Button with the symbols << (to Go Back) Command Buttons on the Form.


Stretching Controls when resizing Form View


Sample image of an Employees Form Design is given below:

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

The normal view of the Employees Form in maximized Application Window shows that an empty stretch of space to the right of data fields.  The Form Title Employees stays 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 responds dynamically to move or stretch across the screen based on re-sizing of the screen:

Compare both images given above.  The second column of controls automatically moved to the right edge of the screen, when the screen is maximized.  Left side controls stretched to the right filling empty area giving more space for viewing or keying in information.  The heading Employees moved to the center of the screen.  The  Note field (memo field type) stretched down and across to display or edit more text into the field very easily.

Want to find out how to do it in MS-Access2007?  If you have a form with the design similar to the one shown on top of this page you may use that (this works with any form) to try this sample trick.

Design a Form.

Let us design a Form with the Employees Table from the sample database Northwind.accdb.

  1. Import Employees Table from the Northwind.accdb sample database.
  2. Create a form with similar design shown on 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 of second column together and move it down, to get enough space for Fax Number and Address fields, which we are going to bring from the first column and place them on top of the second column.
  4. Select Fax Number and Address Fields, right-click on 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 on the Detail Section and select Paste to paste them back into the detail section.
  7. Move the pasted controls and place them on top of the second column of controls.
  8. Select and cut the Note Field and it’s child label from the second column and place them below the first column of controls.

So far, what we did was a normal form design by re-arranging controls as we normally do.  But, the Note field we have removed from the second column controls group and placed below the first column, as an independent control (not part of any controls group), to apply a different Anchoring action.

  1. Save the Form and open it in Form View to check how the current design look like.

    Implementing the Trick.

  2. Change the Form in Design View.

    Let us start implementing the tricks one by one on the controls on the Form from top.  First, let us start with the header Label. We want this Form heading to get centralized within the available width of the form, when the Form View expands or shrinks.  If the text Employees in the header title label is not centralized within the current label width then select the label, select Center Ribbon button from Design Menu.  Now, we are ready to implement the magic settings on the controls.

    When the screen is maximized we want the heading label Stretch Across to the width of the screen so that the heading text Employees automatically move and center horizontally on the enlarged screen.  If the Form is resized manually to make it shrink or expand the label should respond accordingly. To make it happen do the following:

  3. Click on the heading label to select it.
  4. Select Arrange - - >Anchoring - - >Stretch Across Top.

    To test the effect of this setting you may open the Form in Normal View, maximize the form, check whether the form title ‘Employees’ moves across the screen to the center in the expanded screen or not.  You may try to manually resize the form window from right edge of the form, by displaying the sizing control on the mouse, by clicking and holding the left mouse button and dragging the right edge to left slowly and watch the heading label responds to the manual resizing action.  Or minimize the Navigation panel and display it again so that the form display window expands and shrinks quickly.  Now let us play few tricks with other set of controls on the Detail Section.

  5. Place the Form back into Design View.
  6. Highlight and select all the second column Text Boxes.
  7. Select Arrange - - >Anchoring - - >Top Right or right-click on the selected controls and select Anchoring - - > Top Right from the shortcut menu.
  8. Change the form into Form View and preview the effect of our setting. Change the form back into Design View again.
  9. Select the Text Boxes in the left column, except the Note field.
  10. Right-click on the controls and select Anchoring - - > Stretch Across Top.
  11. Select Notes field, right-click on the control and select Anchoring - - > Stretch Down and Across.

It is all done and you may save the form and open it in Normal View for testing by resizing the form view manually to view the action in slow motion.


Defining Pages on Form


When we design a Form we will limit the placement of form controls within the visible area on the screen.  A Form’s dimension is 22 x 22 inches.  So you have plenty of real estate to work with, if you know how to use it.  Placing controls on this big surface is not difficult, but if not in a well organized manner then users may not find it convenient to use it.

Page Usage Plan.

Let us make a plan to create a four page layout on a sample Form to try them out.  If the visible area of the form on the screen is approximately ten inches wide then we can define two pages across the screen of about ten inches each.  We can define another two pages, after leaving about 5 inches from the top of the form, below the first two logical pages.  The Form Pages layout diagram is given below for a quick reference of our project.

There are four logical Page areas planned as shown above.  But, on physical terms there are only two pages, the top first page area with two segments across, first segment starting at co-ordinates at 0,0 (0 inch horizontal starting point at left and 0 inch vertical starting point at top or top left corner).  The second segment of the first page (or second logical page) starts at 10 inch location from left of the form and 0 inch vertical position.

Size of a logical page is about 10 inch wide and 5 inch high. But, we need to specify only the left top corner coordinates of each Page, in our Macro for transferring control from one page to the other.  The second logical page area (top right) is starting at 10 inch position from left of the form and 0 inch top position vertically (10,0).  The Command Buttons shown at the bottom right corner of the logical pages will run a macro to jump from one page to the other.

The Page-Break Control.

The dotted line at the left side at 5 inch position is a page-break control, inserted from the Toolbox, to define the second physical page area. The form area below the page-break control is the second physical page area.  This also divided into two logical pages like we did on the first page.  The first logical page (or the third logical page on the form) of the second physical page starts at 0 inch from left and at 0 inch position on the second page vertically (i.e. 5 inch down from top of the form).  Second logical page on the second physical page (or the fourth logical page on the form) starts at 10 inch position from left of the form and 0 inch position of the second physical page.

Ten inch width and five inch height logical page values are arbitrarily selected, you may change them if you find the pages are overlapping on your screen.  Following the same rules you can define more pages on the form, if you need them.

The Page Navigation Plan.

A Click on the first Command Button the control will jump to the second page at right top corner, the Command Button there will send the control to the left bottom page and pushing the command button there will pass the control to the right bottom corner page.  The right bottom corner command button click will make the top left corner page visible. 

Tip: The macro can be connected to the last Textbox/control’s (on the logical page) Lost_Focus() Event Procedure so that when the user presses the Tab Key the control will jump from one page to the next, without the help of a Command Button Click.

Now that we have everything in theory and have our master plan drawn out in a diagram, let us try it out on a form.  We will write the Macros for driving our Command Buttons first.

Create the Macros.

  1. Select Macro from the Create Menu to open a new Macro in design view.
  2. Click on the Macro Names control to open the Name column of the macro.
  3. Select the first row in 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:
  9. Save and close the Macro with the name MacPages.
  10. The Form Design.

  11. Open a new Form in Design View.
  12. Display the Property Sheet (F4).
  13. 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.
  14. Change the Width Property value of the Form to 20 inch.
  15. Click on the Detail Section to display it’s property sheet.
  16. Change the Height Property Value to 10 inch.
  17. Select the Page-break Control from the Toolbox.
  18. 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.
  19. Create a Textbox control, about half an inch down on top left corner of the Form and change the child label Caption value as Page-1.
  20. 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.

  21. Create a second Textbox to the right of the first one, about half an inch to the right of 10 inch scale location on top of the Form, and change the child label Caption to Page-2.
  22. 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
  23. Create a Textbox below the Page-break control and change the child label Caption as Page-3.
  24. 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
  25. Create a Textbox after the 10 inch location to the right and change the child label Caption as Page-4.
  26. 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
  27. Save the Form with a name.
  28. Test Run the Form.

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

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


Let us get straight into an issue in computerized calculations.  Most companies maintain their Books of Accounts for the Accounting Year starting from April 1st and ending on March 31st, next year.  So, April is the first month of the Accounting Year and 12th month is March next year. 

Here, our focus is on the Accounting Weeks calculations. If we need Weekly analysis of Sales or some other activities of the company how do we calculate week numbers starting from April 1-7 as Week 1 instead of January 1-7?  January 1 may sometimes end up as week 53 or 54, overlapping to next year based on the start of the week day.

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

Date is in dd-mm-yyyy format and Sunday is taken as 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 DatePart() built-in function returns the week number 14 instead of 1, for accounting-week calculation period April 1-7. This may fluctuate between 13 and 14, based on the first day of the Week being Sunday (used as second parameter to the above function) and the year. You will get the result 13 for April 1st, 2006.

If we want to create a weekly Sales Graph Chart for the first Quarter (first 13 weeks during April, May and June) of the Accounting Year we must convert the Sales date into their corresponding Week numbers in order to summarize the Sales values into weekly totals.

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

Function: AccWeek().

I have written such a function AccWeek() to calculate week numbers from 01-04-yyyy to 31-03-yyyy.  This function mainly intended to call from the query column with activity-date as parameter (like Sale date, Payment date and so on) to return it’s corresponding week number.  You can use the function in your Code, on Form or Report or anywhere else you need it.

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

Exit Function

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

End Function

Usage Example-1:

Calling AccWeek() Function from a Query Column:


Usage Example-2:

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


Usage Example-3:

Call from within your own Code:

intSaleWeek = AccWeek(dtSaleDate)

Note:AccWeek() Function is not extensively tested in field conditions and may use it at your own risk. If you find any logical errors in the code please share it with me too.

Technorati Tags:

Deleting Folders with DOS Command

We have seen how to create folders with MkDir() DOS Command and learned how to change default folder control to the active database’s parent folder with ChDir() Command.

If we can create a folder then we must be able to remove the folder too.  But, RmDir() Command usage is not as common as the MkDir() Command.  We create new folders or sub-folders to organize files into them so that we can easily locate them when needed.  The removal of those folders become necessary only when need arises for relocation/removal of those files and release the disk space.

We can run RmDir() command directly from the Immediate Window (Debug Window) as shown below, to learn it’s usage:

RmDir "C:\MyFolder"

This command have a safety-check built into it.  When you run the above command it will check whether the folder is totally empty of any sub-folders or files.  If the Command execution was successful then it will not show any message, otherwise it will one of the following two messages:

  1. If the Path specified is not correct then it will show ‘Path Not found’ Error Message.
  2. 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 it’s sub-folders and files in one clean sweep.  If this is done by mistake then we can always restore them from the Recyclebin also, before emptying it.

Network Folders or Files deleted through Windows Explorer are not transferred to Recyclebin.  If you have access rights to delete Network Folders/Files then you must approach the Network Administrator to restore the Folder/File from the latest LAN Backup.  Normally, some users may be allowed to create folders but access rights to delete folder is kept with the Network Administrator, as a safety measure.

Let us write a small function FolderDeletion() to run the RmDir() DOS Command with proper validation checks so that you can add this to your other common functions library.  The function will have the following validation checks before the folder is physically removed:

  1. It checks whether the folder name passed to the function exists, if not displays a message in that effect and aborts the program.
  2. If the folder exists then asks for confirmation from the user to delete the folder.
  3. if the user’s response is not to delete the folder then aborts the program, otherwise attempt to delete the folder.
  4. If the delete action fails then the folder has sub-folders or files in it, abort the program, otherwise delete the folder and show a message.

The VBA Code is given below:

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
     '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
        MsgBox "Folder: '" & strFolder & "' deleted."
        GoTo DeleteFolder_Exit
     End If
  End If
  MsgBox "Folder: '" & strFolder & "' Not found."
End If

On Error GoTo 0
End Function

If you want something different to get the same work done then we can use VB Script in Microsoft Access to do that.  VB Script mostly used in Web Pages for Server Side actions.  VB Script uses 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:

First let us write a VB Script 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
     MsgBox "Folder: " & strFolder & " created successfully."
  End If
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"

After the sample run, open Windows Explorer and check 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!"
     If MsgBox("Delete Folder: '" & strFolder & "' Proceed...?", vbOKCancel + vbDefaultButton2 + vbQuestion, "FolderDeletion()") = vbNo Then
         GoTo FolderDeletion_Exit
         fldr.Delete 'call the Delete Method of the Folder Object
         MsgBox "Folder: '" & strFolder & "' Deleted."
     End If
  End If
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


ChDir and IN Clause of Access Query

Last week we have learned how to change the Directory Path control of VBA to the CurrentProject.Path (active database’s location) with the use of 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 executing any of the above commands you may open Access Options from Office Buttons and check the Default Database Folder control value under the Popular options group.

We have already discussed earlier about 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 that Article here to refresh your memory.

If you have Queries in your Databases that references Tables in external databases to Update or Append data into them, like the sample SQL given below, it is time to take a relook at them to avoid unexpected side effects.

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

If external or backend database is on a common location on Local Area Network (LAN) Server, serviced by several front-end databases from client machines that itself is asking for separate treatment of the whole issue which we will look at them later, probably next Week. I don't want to mix them up here and confuse you.

Coming back to the IN Clause in the above SQL, if the external database and the current database is on 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 down side is that you have to ensure that the Default Database Folder location changes to 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 intact till it is changed again.  This is a global change to 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 requires this Function. 

One of these methods must be 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.


Microsoft DOS Commands in VBA-2

Continued... from Last Week.

With the MkDir() Command we were able to create a folder on disk, with the help of a small VBA routine that we have written 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 few modifications this program can be further improved to accept the Path string as 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
'if the code execution enters here then something went wrong
      msgtxt = "Something went wrong," & vbCr & "Folder creation was not successful."
      MsgBox msgtxt
   End If
  '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 we need the new folder to be created, with the new folder name at the end.  If you are sure where the current location is (or active location 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 the Windows Explorer. Or the one you have selected using DOS Command ChDir() run 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 task bar then click on it to make that window current), the Cursor will be positioned in the current folder. Check the sample image given below:

If you have used MkDir "Projects" like 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 that the above method is the only option to check the Default Database Folder location.  Select Access Options from Office Button and select the Popular Option Group(Access2007) and you can see the Default Database Folder settings there. You may change it, if you need to change it.  Check the image given below:

Try to open a database from some other 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 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, we have seen earlier:

ChDrive "C" 'change control to C: Drive. This is necessary if 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

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 it’s parent application through the Shell() Command or make a copy of that file to a different location with the FileCopy() Command or delete it with the Kill() Command.


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)
   MsgBox "File: " & txtFilePath & vbcr & "Not Found...!"
End If

End Function


: 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."
   MsgBox "File Not Found...!"
End If

End Function

Example-3: Find and Delete a File from specific location on 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."
   MsgBox "File: " & FilePath & vbCr & "Not Found...!"
End If

End Function

Check for a Folder Name:

Dir() Function also can be used for checking 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 C: drive:

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

The second parameter vbDirectory tells the Dir() command what to look for and if the folder Projects found under C:\Developers folder, then the folder name Projects returned in the strOut variable, otherwise returns an empty string.

The MKDIR Command

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

Let us write a small program to check the presence of Projects folder and if it doesn’t exists 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
      msgtxt = "Something went wrong," & vbCr & "Folder creation was not successful."
      MsgBox msgtxt
   End If
   msgtxt = folderPath & vbCr & "Already exists."
   MsgBox msgtxt
End If

End Function

Dir() Command can be used to check the Volume label of a Disk Drive.

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

? Dir("D:", vbVolume)




Subscribe in a reader
Your email address:

Delivered by FeedBurner


Popular Posts

Blog Archive

Powered by Blogger.

Follow by Email


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