Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Time Value Formatting Beyond Twenty Four Hours

Introduction.

Time Format: hh:nn:ss, returns time values up to 23 hours 59 minutes and 59 seconds (23:59:59) and restarts with the value 00:00:01 for the next day, 1 second after midnight 00:00:00.  With the built-in Format() Function, we cannot format time values beyond 24 hours, like 45 Hours 30 minutes, and 15 seconds (45:30:15).

Then how do we format time values beyond twenty-four hours for applications, like formatting total hours worked by an employee for a week: 40:30:15 or an IT Firm employee's total internet browsing time for the month: 115:47:12, extracted from Server Logs.

We need a function of our own for this purpose.  The following VBA Function: FormatTime() accepts time values in a different form, and will do the job.  I  have created this function for you and you may Copy and Paste the VBA Code into a Standard Module of your project.

The FormatTime() Function Code.

Public Function FormatTime(ByVal StartDateTime As Variant, Optional ByVal EndDateTime As Variant = CDate(0)) As String
Dim v1 As Double, hh As Integer, nn As Integer, ss As Integer
'-------------------------------------------------------------------------
'Remarks: Time-Format function for time value beyond 23:59:59
'Author : a.p.r.pillai
'Date   : August-2016
'Rights : All Rights Reserved by www.msaccesstips.com
'
'Input options
'1. StartDatetime & EndDateTime (both date/time values or both Time Values)
'2. StartDateTime = Date/Time Value and EndDateTime value=0
'3. StartDateTime = Time Value in decimal format – e.g. 0.999988425925926) for TimeValue("23:59:59") or less
'4. StartDateTime = Time Value in Seconds – e.g. 86399 for Timevalue("23:59:59")*86400
'-------------------------------------------------------------------------
'VALIDATION SECTION

'If Input Value is in whole seconds (TimeValue*86400)
'for applications like internet Server log time etc.
'Option4

On Error GoTo FormatTime_Err

If TypeName(StartDateTime) = "Long" Then
   GoTo Method2
End If

If EndDateTime > 0 And EndDateTime < StartDateTime Then
'End-Date is less than Start-Date
   MsgBox "Error: End-Date < Start Date - Invalid", vbCritical, "Format_Time()"
ElseIf (EndDateTime > StartDateTime) And (StartDateTime > 0) Then
'option 1
   If TypeName(StartDateTime) = "Date" And TypeName(EndDateTime) = "Date" Then
     v1 = EndDateTime - StartDateTime
     GoTo Method1
   End If
ElseIf StartDateTime > 0 And EndDateTime = 0 Then
'option 2
'Is it Today's date & Time
   If Int(StartDateTime) = Int(Now()) Then
      'Remove Date number and take only time value
      v1 = StartDateTime - Int(StartDateTime)
   Else
   'Option 3
      'Assumes Value is Time-Value in decimal format
      v1 = StartDateTime
   End If
End If

'Option 1 to 3
Method1:
hh = Int(v1 * 24)
nn = Int((v1 - hh / 24) * 1440)
ss = Round((v1 - (hh / 24 + nn / 1440)) * 86400, 0)
FormatTime = Format(hh, "00:") & Format(nn, "00:") & Format(ss, "00")
Exit Function

'Time Input in Seconds
'Option 4
Method2:
v1 = StartDateTime
hh = Int(v1 / 3600)
nn = Int((v1 - (hh * 3600#)) / 60)
ss = v1 - (hh * 3600# + nn * 60)
FormatTime = Format(hh, "00:") & Format(nn, "00:") & Format(ss, "00")

FormatTime_Exit:
Exit Function

FormatTime_Err:
MsgBox "Error: " & Err & " - " & Err.Description, , "FormatTime()"
Resume FormatTime_Exit

End Function

Trial Run of Code.

Let us try a few examples so that you will know how the function parameter values are correctly inputted into the function.  Try the following sample runs, by typing them directly in the VBA Debug Window.

The FormatTime() function accepts Date &Time or Time input parameter values to the function, The second parameter is optional.  The following conditions apply when parameter values are passed to the function:

  1. When both parameters are entered both should be either Date/Time Values or both Time Values only.  The difference in time will be calculated by subtracting the first parameter value from the second parameter.
  2. The second parameter is optional. If omitted, then the Date value will be ignored when the Date/Time value is passed in the first parameter. The time value will be formatted and displayed.
  3. The first parameter can be a time value in decimal format (e.g. 0.999988425925926 ). Omit the second parameter.
  4. The first parameter is acceptable as a time value in Seconds (e.g. 86399). Omit the second parameter.

Example-1:

Both Parameter values are Date/Time Values. The second Parameter value should be greater than the first parameter value.

StartDT = DateAdd("d",-2,Now()) = 8/27/2016 7:38:22 PM 

EndDT = Now() = 8/29/2016 7:41:13 PM

? FormatTime(StartDT,EndDT)

Result: 48:02:51

Example-2:

First parameter Date/Time Value, second parameter optional, and omitted.

SDateTime=Now() or DateVallue("08/29/2016")+TimeValue("18:30:15")

? FormatTime(SDateTime)

Result: 18:30:15

Example-3:

First parameter Time Value input as a number (2.00197916667094), the decimal value equal to the first example Result: 48:02:51). When we subtract Start-Date/Time Value from End-Date/Time Value you will get the difference as a decimal number, equal to the number of days and time value.  This time number can be the result of a summary of the time values of several records from a Table or Query. The whole number represents the number of days (i.e. 2*24=48 hrs.) + the fractional part in hours:minutes:seconds.

StartDT = 2.00197916667094 

? FormatTime(StartDT)

Result: 48:02:51

The whole number 2 on the left side of the decimal point is the number of days and it will be ignored, when the second parameter is omitted.

You can create a time number similar to the one above for testing this function. To do that we will look at a few basics on the time value to understand them better.

1 Day = 24 hours. 1 Hour = 60 Minutes. 1 Minute = 60 Seconds. So 1 Day = 24 * 60 * 60 = 86400 Seconds.

1 second before mid-night = 86400-1 = 86399 seconds = 23:59:59

To convert the time 23:59:59 (86399) into the internal representation of time value, divide 86400 into 86399 (86399/86400). Result: 0.999988425925926. The time 23:59:59 is stored in computer memory in this form. When combined with the current Date-number it will be like 42612.999988425925926 for 08/30/2016 23:59:59

Let us input this time number alone into our function and try out the result.

? FormatTime(0.999988425925926)

Result: 23:59:59

Example-4:

So, if you want to convert a Time number into Seconds then multiply it by 86400. 0.999988425925926 * 86400 = 86399

You can input the number of seconds as a time number as the first parameter to get it formatted in Hours:Minutes:Seconds.

? FormatTime(86399)

Result: 23:59:59

If you want larger values for testing this function, then try the following examples.

SD = 86399+86400+86000
   = 258799 seconds

SD = SD/86400
   = 2.9953587962963 time number

? FormatTime(SD)

Result: 71:53:19

Let us input the time value in seconds (let us assume that this value is the summary of Internet browsing time value in seconds).

SD= 258799

? FormatTime(SD)

Result: 71:53:19

If you have a Library Database then you can move this Function into that database so that you don't have to copy the code into all your other databases.

A library database is a common database that you have created with all your custom functions or custom wizards that you can attach to your other Projects. This method will enable you to use these functions in your other projects without duplicating codes in all of them. For a detailed discussion on this subject, visit the page MS-Access and Reference Library.

Please leave your comments or suggestions for improvement of the FormatTime()  function in the Comments Section, at the end of this post.

Share:

Opening Multiple Instances of Form in Memory

Introduction.

For the last few weeks, we have been through learning the usage of dot (.) separator and exclamation symbol (!) in VBA object references.  Now, we will explore some interesting tricks with Forms in VBA.  How to Call a Function Procedure embedded in a Form Module (Class Module), from a program outside the Form?

We will explore two different aspects of this particular topic.

  1. How do we open several instances of a single Microsoft Access Form in memory, displaying different information on each of them?

    A sample screenshot of two instances of the Employees Form is given below for reference.  The first form is behind the second instance of the form, displaying employee details of ID: 4 & 5.   Click on the image to enlarge the picture. 


    Calling the Form's Class Module Public Function.

  2. How to call a Function Procedure on the Form's Class Module, from outside the Form?

    Call the Function from a Standard Module,  from the Module of another form, or from the VBA Debug Window (Immediate Window).  The target form must be opened in memory in order to call the function procedure of the form from outside.

Function Procedures in a Form module are helpful to avoid duplication of code. It can be called from subroutines in different locations on the same Form, from a command button click, or from some other Event Procedures of the Form.  The function procedure in a Form Module can be anything that does some calculation, validation check, updating the information, or a stand-alone search operation procedure, like the one we are going to use on our sample Employees Form.

All the Event Procedures on a Form Module are automatically declared as Private Subroutines and they all will start with the beginning and end Statements, like the sample statements given below.   Our own VBA code that does something will go within this block of codes:

Private Sub Command8_Click()
.
.
End Sub

The scope of Private declared Subroutine/Function stays within that module and cannot be called from outside.  The private declaration is absolutely necessary to avoid a procedure name clash with the same name in another Class Module or Standard Module.  The Form's Function Procedure must be declared as Public in order to call it from outside the Form.

To perform a trial run of the above trick you need the Employees Table and a Form.

  1. Import Employees Table from Northwind sample database.
  2. Click on the Employees Table to select it.
  3. Click on Create Ribbon.
  4. Select the Form option and create a Form, for Employees Table, in the format shown above.
  5. Save the Form with the name frmEmployees.
  6. Open the frmEmployees Form in Design View.  Set the Form Property Has Module Value to Yes.
  7. Select the Design Menu and select VBA Code from the Tools button group, to open the Form Module.
  8. Copy the following VBA code and Paste them into the VBA Module of the Form.

    Public Function in Form ClassModue.

    Public Function GetFirstName(ByVal EmpID As Integer) As String
    Dim rst As Recordset, crit As String
    Dim empCount As Integer
    
    'get total count of employees in the Table
    empCount = DCount("*", "Employees")
    
    'validate employee code
    If EmpID > 0 And EmpID <= empCount Then
    
        crit = "ID = " & EmpID
        Set rst = Me.RecordsetClone
        rst.FindFirst crit
        
        If Not rst.NoMatch Then
              
         	Me.Bookmark = rst.Bookmark
            GetFirstName = rst![First Name]
        End If
        
           rst.close
           Set rst = Nothing
        
      Else
        MsgBox "Valid Employee IDs: 1 to " & empCount
    End If
    
    End Function
    
  9. Save and Close the Form.

Have you noticed the starting line of the above Function, which is declared as Public?

The Function GetFirstName() accepts a parameter EmployeeID value, finds that record and will make that record current on the form. The Function returns the First Name of the Employee to the calling program if the search was successful.  If the search operation fails, then it gives a warning message, saying that the employee ID code passed to the function is not within the range of ID codes available in the Employees table.

Now, we need another program, in the Standard Module, to run the search function GetFirstName() from the frmEmployees Form Module.  Besides that this program demonstrates how to create more than one instance of a Microsoft Access Form and open them in memory, to access their properties, methods, or control contents.

  1. Open VBA Editing Window (Alt+F11).
  2. Select the Module option from Insert Menu and add a new Standard Module.
  3. Copy and paste the following VBA Function code into the new Module.

    Call GetFirstName() from Standard Module.

    Public Function frmInstanceTest()
    
          Dim frm As New Form_frmEmployees '1st Form instance
    
          Dim frm2 As New Form_frmEmployees '2nd instance declaration
    
          Dim Name1 As String, Name2 As String
    
      frm.Visible = True 'make the instance visible in Application Window
      frm2.Visible = True '2nd instance visible
    
      Name1 = frm.GetFirstName(4) 'Call the GetFirstName of Employee ID 4
      
      Name2 = frm2.GetFirstName(5) ''Call the GetFirstName of Employee ID 5
    
    'pause execution of this code to view
    'the Employees Form instances in Application Window.
    
    Stop
    
      MsgBox "Employees " & Name1 & ", " & Name2
      
    End Function

Trial Run of Function frmInstanceTest()

Let us run the code and view the result in Application Window.

  1. Click somewhere within the body of the frmInstanceTest() function and press the F5 key to run the code.

    The program will pause at the Stop statement and this will facilitate the viewing of the Application window, where the frmEmployees Form instances are open in normal view mode, one overlapping the other.

  2. Press Alt+F11 to display the Application Window displaying both instances of the Form, the second form overlapping the first one.
  3. Click and hold on to the title bar area of the top form and drag it to the right, to make part of the form behind visible.

    Check the employee records on both forms, they are different, one with employee code 4 and the other is 5.  Check the title area of the forms, both are showing frmEmployees titles.  Now, let us come back to the program and continue running the code to complete the task.

  4. Press Alt+F11 again to switch back to the VBA Window and press the F5 key one more time to continue executing the remaining lines of code.

    The Message Box appears in the Application Window displaying the Employee names Mariya and Steven together.  When you click the OK MsgBox Button, the frmEmployee form instances disappear from the Application Window.

  5. Click the OK button on the MsgBox.

Note: I would like to draw your attention to the Stop statement above the MsgBox() function, at the end part of the code. The Stop statement pauses the execution of the VBA code on that statement.  Normally, this statement is used in a program for debugging code, to trace logical errors and corrections.  Here, it is required to pause the execution of code so that we can go to the Application Window and view both instances of the frmEmployees Form there.  The MsgBox() will pause the code, but we will see only the topmost instance of the form. We cannot drag the top form to the right side while the MsgBox is displaced.

If we don't create a pause in the code execution, both instances of the form are closed immediately, when the program ends.  In that case, we will not be able to view the forms.  Since it is a trial run we would like to know what is happening in the program. It is not necessary to make the Form instances visible, before calling the Function GetFirtName().

The VBA Code Line by Line.

Let us take a closer look at each line of code of the frmInstanceTest() function.  Even though hints are given on each line of code, explaining a few things here will make them more clear to you.  We will start with the first two Dim Statements.

Dim frm As New Form_frmEmployees
Dim frm2 As New Form_frmEmployees

In the above Dim statement, you can see that the New keyword is followed by the object reference. The object name is our frmEmployees prefixed by the direct Object Class name FORM followed by an underscore character separation (Form_) to the frmEmployees Form name (Form_frmEmployees).  These Dim statements themselves open two instances of the frmEmployees in memory.   Form instances opened in this way are not immediately visible in the Application Window.  If we need them to be visible, then make them visible with another statement.

Next, we declared two String Variables: Name1 & Name2 to hold the names returned by the GetFirstName() method.

Next two statements: frm.Visible=True and frm2.Visible=True, makes both instances of the frmEmployees Form visible in the Application Window, for information purposes only.

In the next two lines of code, we are calling the GetFirstName() method of the first and second instances of the frmEmployees to search, find and return the First Names of employee codes 4 and 5.

Default Instance and Other Instances.

The default instance of a Form is opened, in the following manner in programs, for accessing their Properties, Methods, and Controls.  These styles of statements are always used to open a form in programs. The default instance of the Form will be automatically visible, in the Application Window.

Dim frm as Form 'define a Form class object
DoCmd.OpenForm "frmEmployees", vbNormal 'open frmEmployees in Memory
Set frm3 = Forms!frmEmployees ' attach it to the frm3 object

Assume that we have opened frm & frm2 instances first in memory before the default instance through the above code.  How do we address those three instances in a program to do something?  Let us forget about the frm, frm2, frm3 object references, for now, we will go with the straight method, like the one given below:

name3 = Forms![frmEmployees].GetFirstName(5) 'target form in memory is the default instance
'OR
name3 = Forms("frmEmployees").GetFirstName(5) 
'OR
name3 = Forms(2).GetFirstName(5) ' this is the third and default instance

The other two instances in memory cannot be referenced like the first two default methods, using the name of the form. You have to use only the index number of the Forms collection to address the other two instances.

name1 = Forms(0).GetFirstName(3)
name2 = Forms(1).GetFirstName(6)

A Shortcut Method.

There is a shortcut method you can use to run the GetFirstName() Method of the frmEmployees Form from the debug window (Ctrl+G).  Type the following command on the Debug Window and press Enter Key:

? form_frmEmployees.GetFirstName(5)
'Result: Steven
'OR
X = form_frmEmployees.GetFirstName(5)

What happens when we execute the above command?  It opens an instance of the frmEmployees in memory, Calls the Function GetFirstName() with the employee Code 5. The GetFirstName() runs and finds the record and returns the First Name of the employee and closes the form.

Tip: Even after closing the Form, after the execution of the above command, the current record, of Employee ID 5, remains as current on the closed Form.

You can check this by executing the following shortcut command by typing it in the debug window and pressing Enter Key.

? form_frmEmployees![First Name]
'Result: Steven

A Fancy Approach.

In the above command, we didn't run the GetFirstName() method, but the current record's First Name field value is printed. If you want to get a little fancy with the command, then try this by typing it in the debug window and pressing the Enter Key:

MsgBox "First Name: " & form_frmEmployees.GetFirstName(8)
'OR
MsgBox "First Name: " & form_frmEmployees![First Name]

Or try the above command from a Command Button Click Event Procedure from another Form's Module, as given below.

Private Sub Command8_Click()
  MsgBox "First Name: " & Form_frmEmployees.GetFirstName(8)

End Sub
Share:

Dots and Exclamation Marks Usage with Objects in VBA3

The Final Post and Continued from Last Week's Post.

MS-Access Application Objects (Table, Query, Forms, Text box, Label, etc.) need some meaningful names when we create them. If we don't, then MS-Access assigns default names, like Form1, Form2, Text1, Label1, and so on. These default names don't give any clue as to what those names represent. We are free to assign appropriate names in relation to what we are building in the Database. That way it is easy to remember those names (if not all of them), when we need them in calculations,  in VBA, or in wherever they are referenced. We need these names to address them easily in VBA, like Forms!Employees!Salary rather than the usage  Forms("Employees").Controls("Salary").Value. 

Last week we started with a simple example, where we can use the symbol! , to shorten the length of the object addresses, when dot separators are used, giving you enough insight into what it is all about. When we have the Form’s name and Control name the expression can be written in a short form with the symbol!  This is true when the Recordset fields are referenced, like rset!LastName to retrieve its default Property VALUE's contents, instead of rset.Fields("LastName").Value.

If you are a new visitor on this page and topic, then please visit the earlier two pages and continue from here. The links are given below:

Referencing Form's Controls.

I will repeat the first example here, introduced on the first page of this three-page series, to go further in this discussion.

? Forms!frmMain!frmSubForm.Form!frmInsideSubForm.Form!Text7
'
'The above command without the use of the ! Symbol, you must write it in the following manner to get the same result.
'
? Forms("frmMain").Controls("frmSubForm").Form.Controls("frmInsideSubForm").Form.Text7.value

Note: The frmSubForm when placed as SubForm on the frmMain becomes a control (with its own controls and properties) of the Main Form and is listed among the Controls list of the Main Form. If we make a list of controls of the Main Form, we can see that the frmSubForm is listed among them.

Open a Form with a SubForm  then type the following command in one line, with changes in the Debug Window, and press Enter Key to get a listing of control names of the main form

for j=0 to forms!frmMain.controls.Count-1:? j, forms!frmMain.controls(j).name:next
'Result of the above command, on my Form.
'
 0            Label0
 1            Text1
 2            Label2
 3            Text3
 4            Label4
 5            frmSubForm
 6            Label5

The frmSubForm is listed as a Control of the frmMain with index number 5

Now, about the example given at the beginning, we have three open Forms: frmMain, frmSubForm & frmInsideSubForm, layered one inside the other. We are trying to print the Text7 Text Box contents, from the innermost form in the debug window.  Look at the command given below:

? Forms!frmMain!frmSubForm.Form!frmInsideSubForm.Form!Text7
The actual address of Text7 Textbox, all elements are joined with the symbol!, except the .Form after the name frmSubForm and frmInsideSubForm. This command will work without the .Form part, try the command given below.

? Forms!frmMain!frmSubForm!frmInsideSubForm!Text7

If the address works without the .form part why do we need it in the address and what it means? It works without an explicit reference because the system knows that it is a Sub-Form control by default.

When you drag and drop a Sub-Form onto the Main Form Microsoft Access creates a container control on the main form and inserts the Sub-Form into it. To be more specific, if you select the outer edge of the sub-form control you can select this container control. Display its Property Sheet(F4) and check the Source Object Property setting. You can see that the subform's name is inserted there. This is the control where we set the Link Master Fields and Link Child Fields properties to set the relationship between data on the master form and sub-form.

You can re-write this property value with any other form's name to load another form into it, in real-time. When you do that, consider the relationship change, if the new form's source data is not related.

Coming back to the point, i.e., what is the .Form part in the above address means? It means that the Sub-Form Control created by Access is a control for loading a Form into it and it will be always a form control, whether you explicitly add the .Form part in the address or not.

Loading Table or Query in a Sub-Form Control.

But, the interesting part is that you can insert a Table or a Query (not an Action Query) into this control as well.

Try that, if you have a Form with a sub-form, open it in design view.

  1. Click on the outer edge of the Sub-Form to select the Sub-Form control.
  2. Display the Property Sheet (F4) and select the Source Object Property.
  3. Click on the drop-down control to the right of the property to display the available forms, Tables, and Queries.

    On top of the list, all forms will appear, after that the list of Tables and then the Queries list. All the Tables are listed in Table.TableName format and queries with Query.QueryName format indicates the category of object you can insert into the Source Object Property of the Form control.

  4. Select a Table or Query to insert into the Source Object Property of the Sub-Form control.
  5. Save the Form and open it in Form View.
  6. You will find the Table or Query result displayed in the Sub-Form control.

  7. Try to print the value of one of the fields displayed in the debug window.

    Tip: It will print the value of the active record in the sub-form, if selected, otherwise the first record field value.

Is this the command you have typed in the Debug Window?

? Forms!frmMain!frmSubForm.Table!LastName

Then you are wrong, it is not a Table Control, still, it is a Form control only. When you set the Source Object Property Value with a Table's name the system already added the category name to the object's name (Table.TableName or Query.QueryName) to identify what type of object is loaded into the sub-form control.

So the correct command is:

? Forms!frmMain!frmSubForm.Form!LastName
'
'OR
'
? Forms!frmMain!frmSubForm!LastName
Share:

Dots and Exclamation Marks Usage with Objects in VBA2

Continued from Last Week's Topic

Last week we started with a few examples of the usage of dots (.)  and exclamation marks (!) in memory and loaded Form/Report Objects.  We will continue exploring this topic further.  If you have not gone through the earlier page,  please visit that page and then continue from here. Earlier article link: Dots and Exclamation Marks usage with Objects in VBA

After going through last week's article, I am sure you are slightly confused, about which Syntax of the Command is the easiest one to use because we have tried different ones for addressing form & control in VBA.

For the time being, we will leave it there and try a few things differently here so that you will be better informed about the dot separator usage of built-in objects.  The exclamation (!) symbol is not at all valid for referencing these objects.  You will see the hierarchy set up of some of the library objects visually and how to address them to view their property values or call their methods directly from the debug window. 

Object Library View.


Tools -->Options-->Editor-->AutoList Members.

  1. Open your Access Database.
  2. Open VBA Editing Window (Alt+F11).
  3. Open Debug Window (Ctrl+G).
  4. Display Object Browser Window(F2).
    • Select Options from Tools Menu.
    • On the Editor Tab put a check-mark on the Auto List Members item if it is not already selected.
  5. Select Access from the <All Libraries> Control's drop-down list.
  6. Move the scrollbar of the Classes window down, find the item with the name CurrentProject and select it.

    Check the right side window listing of  CurrentProject’s Properties and Methods.

  7. If the Object Browser Window is small, then drag the right and bottom edges to make it large enough to display all the Properties and Methods of the CurrentProject Object in the right-side window.
  8. Click and hold on to the Title area of the object browser window and drag it to the right, if it overlaps the Debug Window area.

When you select a Class Object or Public Constant definition from the Classes Window (left panel), the selected object members are displayed in the right-side window.

Let us display information stored in some of these objects and how we address those object properties/methods and in which order we specify them?

We will display the full Pathname (.FullName property value) of the active database with the following command, by typing it in the Debug Window and pressing Enter Key:

? Access.CurrentProject.FullName
'Result: G:\New folder\pwtracker.accdb

The .FullName Property Value of the CurrentProject Object, from the Access Library of Objects, is displayed.  When you open a database from a particular location the FullName Property value is assigned from the full pathname of the Database.  We have joined the object elements in the correct sequence separated by dots to specify the FullName property at the end. The Name property value will display the database name part only.

Let us see how many Forms we have in our database, by reading the Count Property Value of AllForms Object.

? Access.CurrentProject.AllForms.Count
'Result: 75 – There are 75 user created Forms in my active database.
'
? Access.CurrentProject.AllForms.item(50).Name
'Result: frmEmployees 
'
'The 50th index numbered item (i.e. 51st item)in my database is frmEmployees. 
'This Form is not opened in memory, but it is taken from the Database’s .AllForms Collection. 
'
? Access.CurrentProject.BaseConnectionString
'
'Result: PROVIDER=Microsoft.ACE.OLEDB.12.0;DATA SOURCE=G:\New folder\pwtracker.accdb;PERSIST SECURITY INFO=FALSE;Jet OLEDB:System database=G:\mdbs\BACAUDIT_1.MDW
'

In the above examples, we have displayed the CurrentProject Object's few Property Values assigned by the System. We have used Forms Collection Object to address the open forms in memory, in last week's examples.

Note: You may explore some of the other objects yourself.

From the above few examples, you can see that we have used the dot separator Character only to join each object/property. You cannot use ! symbol to address predefined objects, methods, or properties.

When we reference a User-Defined object (it should have a name) we can use ! symbol followed by the object/control Name to access that Control's default Property Value's contents or other controls/properties, eliminating the lengthy syntax. To make this point very clear we will try out one simple example below.

The TempVars Object.

  1. Scroll down to the bottom of the Classes Window.
  2. Select the TempVars Class Object. You can see it's Methods (.Add(), .Remove() & .RemoveAll()) and Properties in the right side window.
  3. Above the TempVars Class, you can see another object name TempVar Class, click on that and check the right-side window. You will find only two properties: Name & Value.
  4. Type the next line of code in the Debug Window and press Enter Key.
TempVars.Add "website", "MsAccessTips.com"

We have called the Add() method of the TempVars Collection Object to instantiate the TempVar Class, to assign the Name property with the text: website and the Value property with the text: MsAccessTips.com. The new Tempvar Variable website is added to the TempVars Collection with index number 0, because this is the first TempVar variable we have defined in memory so far.

The TempVar data type is Variant Type, i.e., whatever data type (Integer, Single, Double, Date, String, etc.) you assigned to it, it will automatically adjust to that data type.

We can display the website variable contents in two different ways.  First method using dot separators, second with ! symbol.

'1st method
? TempVars.item(0).value
'Result: MsAccessTips.com
'OR
? TempVars.item("website").value
'Result: MsAccessTips.com
'
'OR
X="website"
? TempVars.item(X).value
'Result: MsAccessTips.com
'

'2nd method
'the above lengthy syntax can be shortened
'with ! symbol and the user-defined name:website
'point to note:after ! symbol Name property value should follow.
? TempVars!website
'Result: MsAccessTips.com
'
'next command removes the website variable from memory.
TempVars.Remove "website"
'
'the .RemoveAll method clears all the user-defined Temporary Variables from memory.
'TempVars.RemoveAll

The TempVars Variables are Global in nature, which means you can call this variable (Tempvars!website) in Queries, Textbox (=TempVars!website) on Forms or on Reports and in expressions like: ="Website Name is: " & TempVars!website. If the Value property is assigned with numerical values (like Exchange Rates or some common factor) it can be used in Calculations.

Tip: Try defining a few more TempVar variables assigning different data types: Integer, Double, Date, etc., with appropriate Name Property Values.

The Tempvar variable with the name website is our own creation and it is in memory.  Objects (Form/Report) should be loaded into memory with their controls (Textbox, Combobox, Labels, Sub-Form/Sub-Report, and so on) to address them with the use of ! symbol followed by the name of the control.

We have used the item collection object in the first three examples.  This is used immediately after the TempVars Collection Object.  When we address text boxes, labels, and other controls on an open Form/Report we must use the keyword Controls.

You may explore other objects by selecting them in the left-side panel and inspecting their properties, Methods, and Events when you are in doubt about something.

The Data Access Object (DAO).

Tables & Queries are part of the DAO library. You may select DAO in the top control, replacing Access, and explore DAO-related Classes, their Properties, and Methods.

Hope you are now comfortable with the period (.) and (!) symbols in object references. We will look into a few more things in the next session before we conclude the discussion on this topic.

Share:

Dots and Exclamation Marks Usage with Objects in VBA

Introduction.

Beginner VBA enthusiasts often get confused as to how to address control on a Form or on Sub-Forms. It becomes more difficult when there is another Form within a Sub-Form. Specifying the address of the control on the inner sub-form correctly will only help to access the Text control's contents or to set the control's value directly through VBA. How do we join each element of the address? Can we join each element of the Object address with a dot (.) or with an exclamation mark (!) or with both of them?

Let us start with the simple usages of dots and exclamation marks in object address specifications.

I have designed a sample Main Form and two Sub-Forms. The second sub-form is inside the first Sub-Form. The Forms design view and normal view images are given below for reference.

Sample Form in Normal View

Dots and Exclamation Symbols.

General Usages of the dot (.) and exclamation symbol (!) in object references.

  • A dot (.) -  after an object name to access its methods or properties.
  • The exclamation mark (!) - after an object name, refers to the sub-object or control of the Top-level Object.

Sample Forms:

Main Form: frmMain

Sub-Form of Main Form: frmSubForm

Inner Sub-Form on frmSubForm:  frmInnerSubForm

Text Control on frmInnerSubForm:  Text7

Another Unbound Text Control on frmInnerSubForm:  Text3

All three forms are designed without linking to any Table (i.e. The Form's Record Source property is empty). All the Text controls on the Form are unbound.

You may design three sample forms with a few unbound text boxes and set them up one inside the other and open it in the normal view. Open VBA Editing Window and open the Debug Window (Ctrl+G). Now you are ready to try out the following examples after typing the code directly in the Debug Window and pressing Enter Key.

Practical Exercises.

First, we will try to read the contents of the Text7 textbox (i.e. 'Innermost Form') from frmInnerSubForm and display the result in the Debug Window.

? Forms!frmMain!frmSubForm.Form!frmInsideSubForm.Form!Text7

Printed Result is: Innermost Form

I have written an expression ="Innermost Form" inside the Text7  Text box, that is displayed in the Debug Window.

Forms!frmMain!frmSubForm.Form!frmInsideSubForm.Form!Text3 = "ms-access"

Result: ms-access

The Text3 textbox is in the Innermost Form and its Conttrol Source Property is loaded with the text: ms-access.

Check on your opened Form after executing the above statement on the Debug Window.

Let us examine each part of the above command joined by an exclamation mark or dot.  I call this a command because we directly execute this program statement in the Debug Window, with the? (Print VBA command).

  • Forms: Collection of all Forms loaded in memory. All Forms opened in memory are indexed in the order in which they are loaded into memory, i.e., the first form opened will have the index number 0, the second form is 1, and so on. If your main form has two more forms on it as Sub-Forms they are treated as controls of the Main-form, like any other control (Combobox, labels, textbox, and others). They will not appear as separate forms in the Forms collection. We can display the name of the first open Form in memory with the following command using zero as its index number and printing its Name Property Value.
    ? Forms(0).Name

    Result: frmMain - Form's Name property value is printed.

    ? Forms(0).Width

    Result: 21225 (you will get a number similar to the one given to the left) - the width of the form given in the measure of Twips. 1 Inch = 1440 in Twips. VBA converts the value you set in the Width Property of the Form, in Inches or Centimeters, or in any other regional value, it will be converted internally into Twips.

    If you know the Form's name, then the above command can be given as below:

    ? Forms("frmMain").Width 

    In this form of the command, the Form's name can be given as a string in parenthesis.

    OR

    ? Forms!frmMain.Width

    In this command, immediately after the Forms Collection name, we have used the symbol (!)  to give the name of the next level of the object of the Forms collection object, not a property or method. The next item Width is a property of the frmMain object, so a dot is required, not the other symbol. The exclamation ! symbol is given in parenthesis for legibility only.

    Note: You cannot use the symbol (!) in place of the dot (.) to address the Width or Name or any other property of the Form. There are over 200 properties for a form. You can display the Name of all the properties by typing the following Code, on one line, on the Debug Window and pressing Enter Key:

    For j=0 to Forms(0).Properties.count-1:? j,Forms(0).Properties(j).Name:Next

    In the above statement, we have called the Count() Method of the Forms Property, to take a Count of the first form Properties, and print the Name of each one.

    Take a Listing of the Property Names.

    OR

    For j=0 to Forms("frmMain").Properties.count-1:? j,Forms("frmMain").Properties(j).Name:Next

    OR

    For j=0 to Forms!frmMain.Properties.count-1:? j,Forms!frmMain.Properties(j).Name:Next

    Note the usage of Forms("frmMain") and !frmMain, two different ways to refer to the frmMain object in the open Forms Collection.  In both cases, the form's name is given immediately after the open Forms collection name. But,  if the frmMain form is not open, at the time of executing this statement, then it will end up with an Error.  Forms(0) refer to any form that is open first in memory. It will fail only when no form is open at the time of executing this statement.

    ? Forms.Count

    The Count() method of the open Forms Collection Object will print the count of open Forms

We will explore this topic further in the next Post.

Share:

Combining Active and Backup Database Records in Union Query

Introduction.

Microsoft Access database can be a maximum of 2GB in size. We can back up old Records (like previous year transactions) into a Backup database safely for later use if the need arises. After the safe backup of these transactions, they can be deleted from the active database and Run Compact & Repair Utility to optimize the database performance in day-to-day activities.

There are times that we need the old data for analysis purposes, like next year's budgeting, sales target setting, and so on. At this point we will need old records to combine with the existing data of the active master-table, in the current database, to do various analysis runs.

The Union Query Solution.

We can merge records of both the active table and backup table (with the same name) in a Union Query.

A simple example is given below:

SELECT Export.* 
FROM Export
UNION ALL SELECT Export.*
FROM Export IN 'G:\NEW FOLDER\DB1.MDB';

From the above example, you can see that the name of the table Export in an active database and backup database(DB1.MDB) are the same. No need to link the table to the active database to access the data from the backup database.

Share:

ROUNDUP Function of Excel in Ms-Access

Introduction.

Microsoft Access doesn't have this function built-in. I have made an attempt to write the code for this function and you may use it at your own risk. Before going to the code take a look at the usage examples of this function in Excel.

ROUNDUP() function in Microsoft Excel.

The Rules.

Rounds a number up, away from 0 (zero).

Syntax: ROUNDUP(number,num_digits) Number is any real number that you want to be rounded up.

Num_digits is the number of digits to which you want to round off the number.

Remarks ROUNDUP behaves like ROUND, except that it always rounds a number up.

If num_digits is greater than 0 (zero), then the number is rounded up to the specified number of decimal places.

If num_digits is 0, then the number is rounded up to the nearest integer.

If num_digits is less than 0, then the number is rounded up to the left of the decimal point.

Examples: 

=ROUNDUP(3.2,0) Rounds 3.2 up to zero decimal places (4)

=ROUNDUP(76.9,0) Rounds 76.9 up to zero decimal places (77)

=ROUNDUP(3.14159, 3) Rounds 3.14159 up to three decimal places (3.142)

=ROUNDUP(-3.14159, 1) Rounds -3.14159 up to one decimal place (-3.2)

=ROUNDUP(31415.92654, -2) Rounds 31415.92654 up to 2 decimal places to the left of the decimal (31500)

Courtesy: Microsoft Excel Help Documents.

Copy and Paste the following VBA Code into a Standard Module of your Database and try it out. The examples given above have successfully passed on my testing of the code.

The ROUNDUP() Function.

Public Function ROUNDUP(ByVal dblNum As Double, ByVal intprecision As Integer) As Double
'-------------------------------------------------
'ROUNDUP() Function of Excel Redefined in MS-Access
'Author: apr pillai
'Date  : June 2016
'Rights: All Rights Reserved by www.msaccesstips.com
'-------------------------------------------------
On Error GoTo ROUNDUP_Err
Dim sign As Integer
    sign = Sgn(dblNum)
    dblNum = Abs(dblNum)
    dblNum = dblNum * 10 ^ intprecision
ROUNDUP = (Int(dblNum + (1 - IIf((dblNum - Int(dblNum)) <> 0, (dblNum - Int(dblNum)), 1))) / 10 ^ intprecision) * sign

ROUNDUP_Exit:
Exit Function

ROUNDUP_Err:
MsgBox Err & ": " & Err.Description, , "ROUNDUP()"
Resume ROUNDUP_Exit
End Function

suggestions for improvement of the above VBA Code are welcome.

Share:

Uploading Comma delimited Text Data into Access Table-2

Introduction.

Last week we learned how to upload a simple list of names (separated with commas) from a text file into Microsoft Access Table.

Now, we will improve that program a little bit more to add a few fields (Name, Birthdate, Height, and Weight) to each record. The sample text file image is given below for reference.

The text file has a fixed number of items on a single line. All four items belong to a single record. In last week's example, we used a single column in the Access Table output, and the number of items on each line in the text file was different. Since, all the items belong to a single output column on Access Table we need to determine how many items are there in the x_Names Array, put by the Split() Function from a single line of text and we have used Ubound() Function to take the count of items in the output Array.

In this example, we have an output table with a fixed number of fields: Name, Birth Date, Height, and Weight. A sample image of the output table is given below:


The VBA Code.

VBA Code that uploads the text file into the access table is given below:

Public Function NamesList2()
'-----------------------------------------------------
'Utility: Creating Access Table from
'       : comma separated text data.
'Author : a.p.r.pillai
'Date   : May 2016
'Rights : All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------
Dim db As Database, rst As Recordset, tdef As TableDef
Dim strH As String, fld As Field, j As Integer
Dim x_Names As Variant, tblName As String, fldName As String
Dim txtfile As String

On Error GoTo NamesList2_err
tblName = "NamesList2"
txtfile = "d:\mdbs\Names2.txt" 'Make required changes 

'create the NamesList2 table
Set db = CurrentDb
Set tdef = db.CreateTableDef(tblName)
With tdef
  .Fields.Append .CreateField("Name", dbtext, 50)
  .Fields.Append .CreateField("BirthDate", dbDate)
  .Fields.Append .CreateField("Height", dbInteger)
  .Fields.Append .CreateField("Weight", dbInteger)
End With
db.TableDefs.Append tdef
db.TableDefs.Refresh

'Open the NamesList table to write names with the text file
Set rst = db.OpenRecordset(tblName)

'Open the Names2.txt file to upload data into the table
Open txtfile For Input As #1
'setup a loop to read the data till the end-of-file reached
Do While Not EOF(1)
'read the first line of items separated with commas and
'terminated with carriage return (Enter key)into variable strH
Line Input #1, strH
'extract each item separated with comma and load into the Array variable x_Names
x_Names = Split(strH, ",")

'Read each item from array elements
'and write into the NamesList2 table fields
With rst
    .AddNew
    ![Name] = x_Names(0)
    ![BirthDate] = x_Names(1)
    ![Height] = x_Names(2)
    ![Weight] = x_Names(3)
    .Update

End With
'Repeat till the End-Of-Text File is reached
Loop

NamesList2_Exit:
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
Exit Function

NamesList2_err:
If Err = 3010 Then 'Table already exists
  'continue executing from the next line onwards
  Resume Next
Else
  MsgBox Err & ": " & Err.Description, "NamesList2()"
  Resume NamesList2_Exit
End If
End Function

How IT Works.

As we did in the earlier example, first we are attempting to create a new access table with four fields. If the table creation process ends up with error code 3010 then the table already exists and continues to execute from the next line, skipping the table creation process. If it ends up with any other error code, then the program terminates. Opens the text file for reading the text data, each line at a time. The Split() function breaks up items in the line and loads them into the x_Names Array.

The next step is to add a new record into the access table, take each item from the array element and load it into the corresponding data field before updating them on the table. We know that each text line has four items and they are in which order. Only we need to know the array index numbers to read the items and load them into the fields. Split Function uses a zero-based index system to store the items in the array in memory. Since, we have only four items on a line the index numbers will be 0,1,2,3 to address each item in the array in sequence, Name, Birth Date, Height, and Weight shown in the program snippet given below.

With rst
    .AddNew
    ![Name] = x_Names(0)
    ![BirthDate] = x_Names(1)
    ![Height] = x_Names(2)
    ![Weight] = x_Names(3)
    .Update

End With

We can use the following code in place of the above code snippet:

With rst
    .AddNew
For j = 0 To UBound(x_Names)
    
    .Fields(j).Value = x_Names(j)
    
Next
    .Update
End With

The first code snippet is good for beginners and easy to understand. But, it is not an efficient method when more items are there on a text line to upload. The second code is more compact and doesn't address the field names directly. If any of the field names change at a later stage the first code snippet will run into an error but the second code will work without errors. The number of items on a line is also taken care of automatically. 

The first code cannot be used for a different table, but the second code snippet works for any table without change.

Share:

Uploading Comma Separated Text Data into Access Table

Introduction.

Converting Access table contents into a comma-delimited text form makes it easier to transport the data through the Internet. It can be uploaded into the Access table or into any database system at the receiving end. We have already tried the data exporting procedure earlier. You can find the link to this method combined with the usage of the GetRows() Function of Microsoft Access here. The GetRows() Function copies the entire table contents into memory in a two-dimensional Array, in one swift operation.

Microsoft Access already has inbuilt data Import and Export options.

Here, we will learn how to read a Text File containing the names of Employees, each separated with a comma, and write them into a new Access table. An Image of a sample text file is given below:

An Image of the output Access Table, with a single column, is given below for reference.

  1. First, create a text file, using Notepad or any other Text Editor Program, with some sample data (a few names of people or products), separated with a comma. You may create names on separate lines with different sets of items on each line as shown above. Don't put a comma at the end of the line.

  2. Save the Text file with the name: Names.txt, in your database folder, or in any folder you like.

  3. Open your Access Database to try out the Program given below.

  4. Open VBA Editing Window and Insert a new Standard Module.

  5. Copy and Paste the following VBA Code into the Module and save it:

    Creating Table From Comma Separated Text File.

    Public Function NamesList()
    '-----------------------------------------------------
    'Utility: Creating Access Table from
    '       : comma separated text data.
    'Author : a.p.r.pillai
    'Date   : April 2016
    'Rights : All Rights Reserved by www.msaccesstips.com
    '-----------------------------------------------------
    Dim db As Database, rst As Recordset, tdef As TableDef
    Dim strH As String, fld As Field, j As Integer
    Dim x_Names As Variant, tblName As String, fldName As String
    Dim txtfile As String
    
    On Error GoTo NamesList_err
    
    tblName = "NamesList"
    fldName = "Names"
    txtfile = "d:\mdbs\Names.txt" 'Change this line to correctly point where your text file is saved.
    
    'create the NamesList table with a single field: Names
    Set db = CurrentDb
    Set tdef = db.CreateTableDef(tblName)
    With tdef
      .Fields.Append .CreateField(fldName, dbtext, 50)
    End With
    db.TableDefs.Append tdef
    db.TableDefs.Refresh
    
    'Open the NamesList table to write names with the text file
    Set rst = db.OpenRecordset(tblName)
    
    'Open the Names.txt file to read text data
    Open txtfile For Input As #1
    'setup a loop to read the data till the end-of-file reached
    Do While Not EOF(1)
    'read the first line of names, separated with commas and
    'terminated with carriage return (Enter key),into String variable strH
    Line Input #1, strH
    'extract each name separated with comma and load into the Array variable x_Names
    x_Names = Split(strH, ",")
    
    'Read each name from array elements
    'and write into the NamesList table
    With rst
    For j = 0 To UBound(x_Names)
        .AddNew
        !Names = x_Names(j)
        .Update
    Next
    End With
    'Repeat till the End-Of-Text File is reached
    Loop
    close #1
    
    NamesList_Exit:
    rst.Close
    db.Close
    Set rst = Nothing
    Set db = Nothing
    Exit Function
    
    NamesList_err:
    If Err = 3010 Then 'Table already exists
      'continue executing from the next line onwards
      Resume Next
    Else
      MsgBox Err & ": " & Err.Description, "NamesList()"
      Resume NamesList_Exit
    End If
    End Function
  6. Find this line in the vba code: txtfile = "d:\mdbs\Names.txt" and make the change in the correct location of your text file. Click somewhere in the middle of the Code and press the F5 Key to Run the Code.

    Note: If everything went on well, then you will find the Table NamesList in the Navigation Pane. If you could not find it, then right-click on the top bar of the Navigation Pane and select Search Bar. Type NamesList in the Search Bar to bring up the table in view. Click on it to open and view the data.

How This Works.

  • When the program runs for the first time, it creates the NamesList Table, with a single field: Names. Subsequent runs of the program ignore the table creation action and simply Appends the data to the existing table from the text file.

  • The Program reads the first text line (three names separated with commas) into the string variable strH. Here, we have used the Line Input Statement, rather than Input, to read the entire line of text, the Carriage Return character (Enter Key) at the end of the line is considered as a line terminator. The Input statement will only read the first name because the comma after the name is considered as an input terminator character.

  • The Split() Function will break up the names separately and load them into the Variant Array Variable: x_Names.

  • The Array variable x_Names will be automatically dimensioned/re-dimensioned, by the Split() Function, for the number of items on the input line, and each item is loaded into the elements of the array.

  • In the next step, a new record is added to the Access Table for each item loaded into the array and written to the table one by one from array elements.

  • This process is repeated for all the lines in the text file. When the end of the text file is reached all files are closed and stops the function.

Next week we will learn how to work with text files having several fields of data in a record.

Share:

DIR TREE DOS COMMANDS

Introduction.

Dir Disk Operating System (DOS) Command is available in Microsoft Access VBA too. The usage of this command in VBA is for checking the presence of Files / Folders on the Disk. But, do you ever thought of taking a listing of all the folders/files with their complete pathname, starting from its root (C:\folder\folder\folder.., or C:\folder\folder\folder\Filename), for future reference or simply for reviewing your disk usage and maintenance. 

Way back in 1996-97, when it was time to change our Corporate LAN Software from Novell Netware to Windows NT System all the User Departments are asked to review their Server folder structure and advised to remove unused or unwanted folders/files so that old Server contents can be migrated to the new Windows NT Server.  This requirement encouraged me to take a closer look at the DIR command. With the help of combining a few optional parameters of the command, I was able to prepare a listing of all the folders and sub-folders of our department Server, taken from Windows 95 Client Machine’s mapped Server Drive. This listing helped a lot to review the folders and remove obsolete folders/files.

You can take a listing of folders using either the DIR command or the TREE command. Both commands produce different styles of listings. I prefer the DIR command. The TREE command creates a graphical listing, showing the hierarchical placement of each folder. DIR command displays all the folders and sub-folder(s) separated with back-slashes in one line. A sample listing of both types is given below, one after the other:

DIR Command has several optional parameters to prepare listings in different ways depending on your requirements. Most of the time we ignore these options because their usage is not common.

DOS Command Help.

You can get a list of all optional parameters with a simple help command parameter (/?). The Usage is as given below. First, let us open the DOS Command Window.

  1. Right-click on Windows Start Button and click on the Run command.
  2. Type cmd in the Open control and click OK to open the DOS Command Prompt.
  3. If the prompt appears as something like C:\Users\User> then Type:
    Cd \ then press Enter key to set the prompt to C:\>

    Cd stands for Change Directory command. The \ is the name of the Root Folder. This will set the Disk Drive C root folder as current.

    Tip: Type the Command Exit and press the Enter key to close the DOS Window, any time you want.

  4. Type the following command to display a list of optional parameters of the DIR command:
    DIR /?
    

You can display the details of any DOS command and their usage in this way, by typing the command followed by /?, in the DOS Command prompt.

Display Folders in C: Drive.

Now, let us display the listing of all the folders in C: drive on the screen.

Warning: Don't say I didn't warn you that this will be a lengthy list, and may take a few minutes to display all of them on the screen.

Tip: You may terminate the listing at any point by pressing Ctrl+C Keys.

Type the following command in the DOS Prompt and press Enter Key.

C:/>DIR /A:D/S/B/P

Let us take a look at each parameter given with the DIR command.

DIR Command and its few Options.

  • /A - Display files with specific Attributes. Specific attributes are given, separated by a colon like /A:D D - for directories.
  • /S - include Sub-folders also in the listing.
  • /B - take a Bare-format listing and exclude summary information.
  • /P - display the listing Page-wise (Pause the listing when a screen full of information is displayed. Press any key to display the next page).

If you need a listing of a particular folder and its sub-folders only then include the folder name in the command as given below:

C:/>DIR "\RADIO" /A:D/S/B/P

Saving the DIRectory Listing to File.

By default, DOS Command's output is directly sent to the screen for viewing. If you need a printout on paper, then we must save the output into a text file. This method uses an output re-direction symbol (>) followed by a text file name. The following command saves the output to the FolderList.txt file.

C:/>DIR "\RADIO" /A:D/S/B > FolderList.txt

NB: If you are taking a listing of all the folders and sub-folders/files of a disk then it will take some time to save all the details in the text file and complete the command. It may look like the computer hanged, have patience and wait for the DOS prompt C:/> to re-appear. If you run out of patience and would like to terminate the command then press Ctrl+C Keys.

You may open the text file FolderList.txt in any plain text editor program to take printouts.

The TYPE of Command.

You may use the following DOS command to display the contents of the text file:

Type FolderList.txt | More

Type - Displays the contents of the text file on the screen.

| (The vertical bar) this symbol is known as Piping Symbol. The piping symbol ensures that the output of the Type command is passed to the next command More. The More command displays the output one screen-full at a time, like the /P parameter to the DIR command. Press a key to display the next page.

The TREE Command.

The TREE command displays the folder list in a hierarchical structure.

C:/>TREE | More

Display folder structure listing page-wise.

C:/>TREE/F | More

/F parameter display folder names followed by Filenames.

Hope you have enjoyed doing something different and useful.

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