Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Conditional Formatting in Continuous Form

Introduction.

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

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

  1. Color changes when the field has focus.
  2. Color changes when the field value meets a specific condition.
  3. The Color changes 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 meet 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 Unit Price field.  The background color is set on records with a Unit Price value greater than $30.  The font is set with Red color on records with Unit Price value falling between 20 and 30.

Design a Continuous Form.

  1. Import the Order Details and Products tables from the Northwind sample database.

  2. Design a continuous form on 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 the Unit Price 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 the 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.

Share:

Autonumber with Date and Sequence Number-2

Introduction.

Perhaps you may be wondering, why 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 the 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 to generate auto-numbers with the 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 a lesser number of digits as follows:

Sample Data Image.

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

The Auto-number generated for patient 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 Input mask
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

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

Autonumber-with-Date method-2
Saved Number Display with Input mask
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 reset 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)
Next
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

DaysAsOnMonth_Exit:
Exit Function

DaysAsOnMonth_Err:
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

AutoNumber_Exit:
Exit Function

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

Download Demo Database and Modify.


  1. Auto-Numbering in Query Column
  2. Product Group Sequence with Auto-Numbers.
  3. Preparing Rank List.
  4. Auto-Number with Date and Sequence Number.
  5. Auto-Number with Date and Sequence Number-2.
Share:

Command Button Color Change on Mouse Button-down Action

Introduction.

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 to 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 a new trick for you to use on Command Button Control. Command Button will change the color on the mouse button down the action and will restore the original color on the mouse-up action.  You can do this with a Label control, with the same dimension as 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 the Size option on the Shortcut Menu and select To Tallest.

  9. Repeat step 8 above and select To Widest.

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

  11. Remove the text from the Caption property.

  12. Change the Special Effect Property value to Raised.

  13. Select the Back Color Property and click 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 selecting 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 that 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 it and changing to a Normal view when the mouse button is released. 

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

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

Share:

Time-bound Form Mode Change

Introduction.

Allow data entry or editing for a certain period of time otherwise lock the form for data view alone.  The reason to do this may be 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 write 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
          frm.Refresh
      Case Else
          With frm
            If .AllowAdditions = True Then
                .AllowAdditions = False
                .AllowEdits = False
                .lblMsg.Visible = True
 'change the next line to set focus on any field on the main form
                .EmployeeID.SetFocus
'change .subFrmName to match the control (window) name of the sub-form
                .subFrmName.Enabled = False
            End If
          End With
          frm.Refresh
End Select

Set frm = Nothing
End Function

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

Some Changes to the Form.

  1. Open your Form in Design View.

  2. Add a Label control on the main Form where you want to display 'Entry not allowed', change the Name property 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 at a one-minute interval and applies changes automatically.  You may increase the time interval by adding 1000 to the above value for each second increase.

  5. Select the 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:

    Code:

    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.

Earlier Post Link References:

Share:

Missing Lines in Line-Chart

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:

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 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 up with Zeroes.

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.

  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
Share:

Back Tracking Open Forms

Introduction

On the 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, we can open several Forms, one after the other, during startup time and keep them hidden.  In that case, how do we move from one form to the other, going back or forward, one by one? Yes, 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 a 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 the 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 remain visible at a time 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) 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 its Property Sheet (F4).

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

    Two Button-Click Event Sub-Routines.

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

  16. Make 4 more copies of the Employees1 Form and name them as Employees2 to Employees5. The number in the header labels also needs to 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 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.

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

    When you open several forms this way they are placed in memory in the opened FORMS Collection Object in indexed order.  The first form opened is addressable with zero index number as Forms(0) or Forms(“Employees1”) in VBA.  The Form’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 these internal index numbers.  You can open those Forms in any order you like, not necessarily 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 the Employees4 form visible and to place the current form Employees5 in a hidden state in memory.

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

    At this stage clicking on the back button (<<) on this Form will not show any response because this is the first form we have opened.  But, if you have opened these forms in a different order then it will work.  When you reach the Employee1 form all the other Forms are in a 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 in any of these forms, while the form is in a 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
Next
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 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.

Share:

Stretching Controls when resizing Form View

Introduction

A 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 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 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 areas on the form, 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 this trick works in MS-Access 2007?  If you have a form with a design similar to the one shown on top of this page you may use that (this works with any form) to try this simple trick.

Design a Form.

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

  1. Import Employees Table from the Northwind sample database.

  2. Create a form with a 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 the second column together and move it down, to get enough space for the 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, and 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 in 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 its child label from the second column and places them below the first column of controls.

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

  1. Save the Form and open it in Form View to check what the current design looks 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 the 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, and select the 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, and check whether the form title ‘Employees’ moves across the screen to the center of the expanded screen or not.  You may try to manually resize the form window from the right edge of the form, by displaying the sizing control of the mouse, clicking and holding the left mouse button, and dragging the right edge to the left slowly, watching 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 a few tricks with other sets of controls on the Detail Section.

  5. Place the Form back into Design View.

  6. Highlight and select all the second column TextBoxes.

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

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

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

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

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

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.

Share:

Defining Pages on Form

Introduction.

When we design a Form we will limit the placement of form controls within the visible area on the screen.  Access 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 to our project.

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

The size of a logical page is about 10 inches wide and 5 inches in height. 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 the 10-inch position from the 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 on the left side at the 5-inch vertical position is a page-break control, inserted from the Toolbox, to define the second physical page area (the third logical page area). This is also divided into two logical pages like we did on above the page-break control.  The third logical page of the form, or the second physical page starts at 0 inches from the left and at the 0-inch position on the second page vertically (i.e. 5 inches down from the top of the form).  The second logical page on the second physical page (or the fourth logical page) starts at the 10-inch position from the left of the form and the 0-inch position on the second physical page.

Ten-inch wide 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 the 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 Macros.

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

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

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

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

  5. Select the Arguments column.

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

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

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

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

  15. Click on the Detail Section to display its property sheet.

  16. Change the Height Property Value to 10 inches.

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

Share:

Accounting Year Week Calculations

Introduction.

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 the 12th month is March next year. 

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

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 DatePart() built-in function returns the week number 14 instead of 1, for the 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 the second parameter to the above function) and the year. You will get 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 Sale dates 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 or weeks without some modifications. We can use the DatePart() function within the 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 is mainly intended to call from the query column with an activity date as a parameter (like Sale date, Payment date, and so on) to return its 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

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 use at your own risk. If you find any logical errors in the code please share them with me too.

Technorati Tags:

Earlier Post Link References:

Share:

Deleting Folders with DOS Command

Introduction.

We have seen how to create folders with MkDir() DOS Command and learned how to change default folder to the active database’s 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 becomes necessary only when the need arises for relocation/removal of those files and increase the available disk space.

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

RmDir "C:\MyFolder"

Validation Checks.

This command has a safety-check mechanism 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 a 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 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.

Network Folders or Files deleted through Windows Explorer are not transferred to the Recycle bin.  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 folders are kept with the Network Administrator, as a safety measure.

A Custom Function.

Let us write a small function with the name FolderDeletion() to run the RmDir() DOS Command with proper validation checks so that you can add this to your other common function 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 it doesn't display a message to that effect, and aborts the program.

  2. If the folder exists, then ask for confirmation from the user to delete the folder.
  3. If the user’s response is negative, then abort 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 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 something different to get the same work done, then we can use VB Script in Microsoft Access to do that.  VB Script is 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:

VBScript Function: FolderCreation()

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
  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, 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!"
  Else
     If MsgBox("Delete Folder: '" & strFolder & "' Proceed...?", vbOKCancel + vbDefaultButton2 + vbQuestion, "FolderDeletion()") = vbNo Then
         GoTo FolderDeletion_Exit
     Else
         fldr.Delete 'call the Delete Method of the Folder Object
         MsgBox "Folder: '" & strFolder & "' Deleted."
     End If
  End If
FolderDeletion_Exit:
On Error GoTo 0
End Function

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

Sample Run from Debug Window:

FolderDeletion "C:\MyProjects"

OR

Private Sub cmdRun_Click() FolderDeletion txtFolderPath End Sub

Earlier Post Link References:

Share:

ChDir and IN Clause of Access Query

ChDrive() and ChDir() Commands.

Last week we 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 that 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 the Access Options from 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 that Article here to refresh your memory.

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 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 an external or back-end database is on a common location on the Local Area Network and serviced by several front-end databases from client machines, then 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 are 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 downside 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 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 requires 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:

Share:

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 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 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
   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 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 the 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 Windows Explorer. Or the one you have selected using DOS Command ChDir(), 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 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 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.

Share:

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

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