Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Word Mail-Merge with Ms-Access Table

Introduction.

Form Letters.

Form Letters are prepared from a standard template merged with addressee related information filled in on each letter.  Appointment letters, call letters for interviews or Employment Contracts etc. are some of the examples and easier to prepare them in Ms-Word Mail-Merge mode, rather than composing them individualy for each addressee.

These type of Letters will have some common body text, applicable to all the recipients and will have specific areas where personalized information can be inserted, like Name and Address, interview dates or nearest location where the candidates can attend the interview and so on.

If the interview calls should go to hundreds of prospective candidates then we need to prepare only one letter with standard body text applicable to all receipients.  Other personalized information can be automatically inserted from the attached data table, at appropriate places on each letter, at printing time.  This process is known as Mail-Merging.

We have already made an attempt to mimic this process in Microsoft Access itself with the help of VBA, some time back, and the links of those Posts are given below for your reference:

  1. Ms-Access and Mail-Merge
  2. Ms-Access and Mail-Merge-2
  3. Ms-Access and Mail-Merge-3

The Names, Addresses and other related information of each individual will be kept in an external Table.  The external Table can be prepared in any of the following Applications:

  1. MS-Word Document with Table Grid.
  2. MS-Excel Data Table.
  3. MS-Access Table.

The Table can be linked to the Form Letter so that Ms-Word can pick the required information from the Table and insert them where Table Field-Codes are placed on the body of the letter.

Sample MS-Word Table.

We have prepared a small Table in Word Document, within the Table Grid, as an example  in case if you prefer to prepare one in Word.  Here is an image of the data table with only 3 entries, similar to the one we have in Northwind.accdb Employees Table.

Click on the Image to Enlarge.


MS-Access Employees2 Table.

But, we will be using a modified Employees Ms-Access Table prepared with few new fields, for our Mail Merge Demo Run, from the original Employees Table of Northwind.accdb sample database.  The  image of the Employees2 Access Table is given below:

Click on the Image to Enlarge.

Create the Employees2 Table from the Employees Table of Northwind.accdb, with the selected fields in your Database.  Create three more fields and fill it up with sample data as shown in the above image.

Sample Letter Specimen.

A Specimen image of the Letter, without the individual Employee details is given below:

Click the Image to Enlarge it.

Create a Letter in MS-Word similar to the specimen given above and save it with the name: Employees.

Sample Merge-Printed Letter.

When we merge print the personal information on the Document, from Access Table, it will look like the sample image given below:

Click the Image to Enlarge.

Inserting the Merge Fields on Document.

Let us prepare a Mail-Merge Document in Word, with the above Employees2 Table as Source Data,  matching the above merged letter model. 

We assume that the above Employees of our Organization is awarded with an Annual increment, in appreciation of their commendable contribution to the Company’s overall growth and encourage them to do better by taking up future challenges of the company.

  1. Open the Employees Word Document, as per specimen given above, created and saved earlier.  If you have not yet created one so far, then let us do it now.
  2. Click on Start Mail-Merge from Mailings Menu and select Letter.
  3. Click on Select Recepients and select Use Existing List.
  4. Browse and find the Database where you have the above Employees2 Table is and Click Open.
  5. The list of Tables are displayed, select Employees2 Table and Click OK to select it.  Now, the Employees2 Table is attached to the Document.
  6. Next, if you click on the Edit Recepient List it will display all the records in the Employees2 Table with check-marks indicating that all the employee records are selected for Merge-Printing the Letters.  If any employee is to be excempted from the letter then remove the check-mark on that item.

    First we will insert the Address Block and Greeting Line information on the Letter, from the Merge-Fields selected from the attached Table.  Even though we can insert these to sections in two simple steps, we will do it the hard way to make things to our liking and less complicated, if the automatic method is not properly understood, for the first time user.  You may try them later by selecting Address Block and Greeting Line buttons and their optional settings to modify the output on the document.

  7. Click some where below the Reference information (Ref:ABC/HRD/2020/1234-) and above the word Dear on the Letter.
  8. Click on Insert Merge Field option in Mailings Menu and click on First Name Field.  Open the same list again and select Last Name.
  9. Click in the middle of both field-codes and tap the space bar to insert a space between First-Name and Last-Name segments of the Employee Name and position the insertion point on the next line.
  10. Select Address from the Merge-Field list for the second line.
  11. Select City for the third address-line.
  12. Insert State/Province and Country/Region on the next line, with a space in between.
  13. Position the Insertion point after the word Dear with a space, insert First Name and Last Name  fields with a space in between.

    After inserting the merge fields it will look like one of the images given below, based on the Field-Code display option setting ALT+F9.

    OR

    The second image is the expanded form of Merge Field Codes. You can toggle between these two states by pressing ALT+F9 Keys.  see that the Preview Result button is in de-selected state.  If the Preview Result button is on then the actual data will be displayed.

  14. Position the insertion point on the Designation line and insert the Job Title Merge-field.
  15. On next three lines insert Pay, Grade and Wef (stands for with-effect-from) Fields.

    When completed it will look like the following image:

    We need two more pieces of data for Reference Sequence Number and Date of the Letter.

  16. Position the insertion point at the end of the existing reference number, immediately after the dash character (Ref: ABC/HRD/2020/1234-).
  17. Select Page Number from Insert Menu and select Current Position from the displayed list.
  18. Position the insertion point after the word Date:.
  19. Click on Date & Time option, under Insert Menu and select one of the Date-format options displayed.
  20. Press Ctrl+S Keys to save the Word Document with the changes.

Merge-Printing the Letter.

Now, we are ready to Merge-Print the Document for each selected employee on the Employees2 Table.

  1. Click on Preview Result button, with the Zoom Glass image, under Mailings Menu. 
  2. If it is still on Field-Code display, then Press ALT+F9 Keys to Fill-in the first Employees data, wherever we have inserted the Merge-Fields on the Document.
  3. Now, you are viewing the sample Document you will get when Merge-Print the Document on the Printer,  one Document for each employee.
  4. Now, Click on Finish & Merge button and select Edit Individual Document. . .  and select All.

All the Documents are merged with Employees information and you are allowed to edit the Pages, if needed.  Can insert spaces between lines or inserted information can be formatted in Bold letters.  If Letter head is not appearing properly then insert a line and so on.

Once you are finished editing, select Print from Office Button, then Preview or Print the Document on Printer.

If you have selected Send EMail Message at Step-4 above then you can send the Document as Email Message.

  1. MS-Access and E-Mail
  2. Invoke Word-Mail Merge from Access2007
  3. Automated Email Alerts

Share:

Auto-Numbers in Query Column Version-2

Introduction

During January 2010 I have published a Function: QrySeq() - Auto-Numbering in Query Column  on this website and well-received by the readers all through these years.  While going through it again I thought it can be written with less Code and improve it’s performance by using a better approach, other than Variant Array.

When the function QrySeq() is called from a query record the program searches through the Array of Unique Keys and look for the matching key, passed from the record as parameter, finds it and returns the sequence number from Array-Element to the calling record.

If the Query have large Volume of records this process may take more time, because every time the program looks for the Key Value from the beginning of the Array.

The New Version is with the Name: QryAutoNum()

Using Collection Object  instead of Array.

You can find a detailed discussion on Collection Object in Ms-Access and Collection Object Basics Page.

Here we will have a brief introduction to know what it is and how it is used in VBA.  The Collection Object is a versatile Object that can hold, in general terms, any Values, Numeric or String Values, Class Module Objects or collection of other Objects.  The Collection Object is instantiated in VBA programs in the following manner:

'declare a Collection Object. Dim ABC as Collection 'create an instance of Collection Object in Memory Set ABC = New Collection 'We can Add built-in data types: Numeric, Strings etc ‘or Objects like Class Module Objects, ‘or other Collection Object as Items to the Collection Object.

'Use the Add method to add a Collection Item to the Object. ABC.Add 25 ABC.Add "ms-accesstips" 'When Collection Object Items added this way, ‘it can be retrieved only in the added order. For j = 1 to ABC.Count 'gets the count of Items Debug.Print ABC(J)’ retrieve in Item index Order. Next 'When ADDed an Item with a String Key 'we can use the Key value to retrieve the Items Randomly. 'But, usage of Key is optional. ABC.Add 25, "1" ABC.Add "ms-Accesstips", "2" x = "2" Debug.Print ABC(x) Result: ms-accesstips

So, we will use Collection Object to add the Query Auto-Numbers with the Unique Key Values as Collection Object Key.  With this approach we can retrieve the Auto-Numbers directly, rather than struggling with Arrays and it’s complicated storing/retrieving steps.

The QryAutoNum() Function Code.

Option Compare Database
Option Explicit

Dim C As Collection

Public Function QryAutoNum(ByVal KeyValue As Variant, ByVal KeyfldName As String, ByVal QryName As String) As Long
'-------------------------------------------------------------------
'Purpose: Create Sequence Numbers in Query Column Ver.-2
'Author : a.p.r. pillai
'Date : Dec. 2019
'All Rights Reserved by www.msaccesstips.com
'-------------------------------------------------------------------
'Parameter values
'-------------------------------------------------------------------
'1 : Column Value - must be UNIQUE Numeric/String Type Values from Query Column
'2 : Column Name  - the Field Name in Quotes from where Unique Values taken
'3 : Query Name   - Name of the Query this Function is Called from
'-------------------------------------------------------------------
'Limitations - Function must be called with Unique Field Values
'            - as First Parameter
'            - Need to Save the Query, if changes made, before opening
'            - in normal View.
'-------------------------------------------------------------------
Static K As Long, Y As Long, fld As String
On Error GoTo QryAutoNum_Err

Y = DCount("*", QryName) ' get count of records for control purpose

'If KeyfldName Param is different from saved name in variable: fld
'or Value in K more than count of records in Variable: Y
'then it assumes that the QryAutoNum() is called from a different Query
'or a repeat run of the same Query. In either case the Control Variable
'and Collection Object needs re-initializing.
If KeyfldName <> fld Or K > Y Then
'initialize Control Variable
'and Collection Object
    K = 0
    Set C = Nothing
    'save incoming KeyfldName
    fld = KeyfldName
End If

'if KeyValue parameter is Numeric Type then convert
'it to string type, Collection Object needs it's Key as String Type.
If IsNumeric(KeyValue) Then
    KeyValue = CStr(KeyValue)
End If

K = K + 1
If K = 1 Then
Dim j As Long, db As Database, rst As Recordset
Dim varKey As Variant

Set C = New Collection

Set db = CurrentDb
Set rst = db.OpenRecordset(QryName, dbOpenDynaset)

'Add recordlevel AutoNumber with Unique KeyValue
'to Collection Object, in AutoNumber, KeyValue Pair
While Not rst.BOF And Not rst.EOF
    j = j + 1 ' increment Auto Number
    
    'Get key value from record
    varKey = rst.Fields(KeyfldName).Value
    
    'if numeric key convert it to string
    If IsNumeric(varKey) Then
      varKey = CStr(varKey)
    End If
    
    'Add AutoNumber, KeyValue pair to Collection Object
    C.Add j, varKey
    
    rst.MoveNext
Wend
    rst.Close
    Set rst = Nothing
    Set db = Nothing

'Retrieve AutoNumber from Collection Object
'using the KeyValue.  Works like Primary Key of Table
    QryAutoNum = C(KeyValue)
Else
    QryAutoNum = C(KeyValue)
End If

If K = Y Then 'All record level AutoNumbers are Returned
    K = K + 1 ' increment control variable
End If

QryAutoNum_Exit:
Exit Function

QryAutoNum_Err:
MsgBox Err & " : " & Err.Description, , "QryAutoNum"
Resume QryAutoNum_Exit

End Function

Sample Source Query SQL.

With the NorthWind Products Table.

SELECT Products.ID, 
Products.Category, 
Mid([Product Name],18) AS PName, 
Sum(Products.[Standard Cost]) AS StandardCost, 
QryAutoNum([ID],"ID",
"Product_AutoNumQ") AS QrySeq
FROM Products
GROUP BY Products.ID, Products.Category, Mid([Product Name],18)
ORDER BY Products.Category, Mid([Product Name],18);

Review of VBA Code Line-By-Line.

On the Global Area of the Module we have declared a Collection Object with Object Variable C.

The QryAutoNum() Function declaration is the same as our earlier QrySeq() with three Parameters.

  1. Unique Key Value either Numeric or String as first Parameter.
  2. The Key Value Field’s Name in String Format.
  3. The Query Name in String Format.

The returned Auto-Number is in Long Integer format.

Three Static Variables,  K and Y declared as Long Integers and fld declared as String Variable.

All the three Variables controls the Code execution paths and determines when to initialize Collection Object and control variables.

The DCount() Function takes a count of records in the Query in Variable Y.

If the KeyFldName is different from the saved name in Variable fld then it assumes that the function call is from a new Query Record.  If the field name is same but the value in variable K is greater than Y  then the earlier Query is calling the function QryAutoNum() for a repeat of the earlier run.   In either case the control Variable K is reset to zero, the Collection Object with existing Items are cleared from memory.  The new Key field name received in KeyFldName variable is saved in fld variable for later validation check.

Next, if the KeyValue parameter value is numeric then it is converted to String format in the statement: KeyValue = Cstr(KeyValue). The Collection Object Item Key must be in string format.

Next, the variable K is incremented by one.  When the value in K=1 it assumes that this is the first call of this function, from the first record of a Query.  When this is the case the main process of this function starts.

The local temporary Variables are declared here and their values are not preserved between calls of this function from different records of the query.

The Collection Object, declared on Standard Module’s  Global area is instantiated in memory, with the statement Set C = New Collection.

The Query recordset is opened to read records one-by-one. The local variable J to create Auto-Numbers and Add it to the Collection Object for each record.  The Unique Key Value, read from the recordset,  into variable varKey, is added to the Collection Object as it’s Key Value.

If the varKey Value is Numeric Type then it is converted to String format.

The Auto-Number Value in Variable J and the string value in variable varKey are added to the Collection Object in the following statement, as it’s Item, Key pair:

C.Add J, varKey

This process is repeated for all the records in the Query.  The Auto-Numbers are generated for all the records and added them into the Collection Object, one after the other.  All this work is done during first call of the function from the first record of the query.

Did you notice that we are reading the Unique Key value of each record directly from the recordset within the While . . . Wend Loop to add them to the Collection Object.  After adding Auto-Numbers for all records the recordset and Database Objects are closed.

Remember, we are still at the first call of the function from the first record of the query and the first parameter variable KeyValue still holds the first record Key Value.

The next statement QryAutoNum = C(KeyValue) retrieves Collection Object’s first Item Auto-Number Value 1, using the Unique Key Value in parameter variable KeyValue, and returns it to the function calling record. This will happen only once because the variable K will be greater than one on subsequent calls of this function.

So, the Function calls from second record onwards will take the ELSE path of the If K=1 Then statement and retrieves the Auto-Numbers from Collection Object, using the KeyValue passed as Parameter, and returns it to respective records in the Query.

It works very fast because we can directly pick the item value, using the Collection Object Key, rather than searching for the Key, through the Array from the beginning to find the one we want.

When all the record Auto-Number values are returned the value in control variable K = Y. We have already taken the count of records of the Query, in Variable Y in the beginning of the program. At this point we increment the value in variable K by 1, to make it more than the value in Variable Y.  Since, K and Y are Static Variables their values are not lost after the last record call is over and remains in memory.  If the same Query is run a second time the test on these variable values can determine whether we need to reset the variable values and clear the earlier Collection Object from memory for a fresh start of the process all over again.

If the QryAutoNum() function is called from the same Query again the Static Variables and Collection Object is cleared from memory, preparing for a fresh run of the Function for the same Query or for a different Query.

The sample Report Image using the above Query as Source is given below for reference

You can use the Query as Source for Report or Form. 

A sample demo database, with all the Objects and VBA Code, is attached for Downloading and for trying out the Code.


  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:

Diminishing Balance Calc in Query

Introduction.

This is an offshoot of the earlier Function Running-Sum in Query Column.  With few changes in the earlier Function RunningSum() we can easily calculate and find the loan balance-to-pay amount, after deducting the monthly paid amount, at each record level.

The loan amount is payable in monthly instalments.   Our simple task is to show the diminishing balance of loan amount against each record-level installment amount in a separate Column of the Query.  The last record will have the remaining balance amount pending to pay.

Let us pretend that the Loan Repayable Total Amount is 1000.

Sample Query Recordset.

The sample installment payment detail records are taken from the earlier Post: Running-Sum in Query Column as given below.

The Query SQL that calls the DiminishingBal() Function.

The SELECT Query SQL that calls the DiminishingBal() Function, in a separate Column of the Query.

SELECT Table_Units.ID, Table_Units.Units, DiminishingBal([ID],"ID","Units","DiminishingQ1") AS DiminishingBalance
FROM Table_Units;

The Query Recordset Image, with the result in the last column,  is given below:

We are using the same Query recordset used as source for the earlier RunningSum() Function and used here also for demonstration purposes.  The Recordset should have a Unique value (Numeric or String) field and used as first parameter to the Function.

Total Repayable Loan Amount kept in a separate Table.

The Total Amount to be repaid to the Bank (1000) is kept in a separate Table with the following structure:

The DiminishingBal() Function VBA Code.

The VBA Code of DiminishingBal() Function is given below:

Option Compare Database
Option Explicit

'Declare a Generic Object
Dim D As Object

Public Function DiminishingBal(ByVal IKey As Variant, ByVal KeyFldName As String, ByVal SumFldName As String, ByVal QryName As String) As Double
'-----------------------------------------------------------
'Function: DiminishingBal()
'Purpose : Calculate Diminishing Balance in a separate Column
'The Query can be used as source for other Processing needs,
'for Form View or Report
'-----------------------------------------------------------
'Author  : a.p.r. pillai
'Date    : December 2019
'Rights  : All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------------
'Parameter List, in the Order of it's placement
'1. Key Value Data Field
'2. Key-Field Name in String Format
'3. Field-Name for Calcuating Running Sum in String Format
'4  Query-Name in String Format
'-----------------------------------------------------------
'Remarks: The Key-Value Field should have Unique Numeric or
'String Values.
'-----------------------------------------------------------
Static K As Long, X As Double, fld As String, y As Long
Dim p As Variant

On Error GoTo DiminishingBal_Err

y = DCount("*", QryName)
'If the Function is not called by the same Query
'then initialize Dictionary Object and Variables
If SumFldName <> fld Or K > y Then
   fld = SumFldName
   Set D = Nothing
   K = 0
   X = 0
End If


K = K + 1
If K = 1 Then 'The major process of the function starts here
    Dim DB As Database, rst As Recordset
    
    'Create and instantiate the Dictionary Object
    Set D = CreateObject("Scripting.Dictionary")
    
    'Get Loan Repayable Amount
    X = DLookup("[LoanAmt]", "tblRepay", "[id] = 1")
    
    'Open the EMI Recordset
    Set DB = CurrentDb
    Set rst = DB.OpenRecordset(QryName, dbOpenDynaset)
    'Calculate cumulative record-level summary and
    'add the value into Dictionary Object as it's Item
    While Not rst.EOF And Not rst.BOF
    'read the record summary field value and add it to total
         X = X - rst.Fields(SumFldName).Value
    'read current record key field value
         p = rst.Fields(KeyFldName).Value
    'add the total value to dictionay object
    'as Key, Item pair
         D.Add p, X
    ' repeat this process for all records
         rst.MoveNext
    Wend
    
    'close recordset and remove the database objects
    rst.Close
    Set rst = Nothing
    Set DB = Nothing
    
    'Retrieve the first item from Dictionary,
    'using the first Key passed as parameter,
    'and return to the function calling record in the Query
    
   DiminishingBal = D(IKey)
Else
   'Subsequent calls with the record Key passed as parameter
   'will retrieve other record values from Dictionary and
   'returns to their corresponding records in the Query.
   
   DiminishingBal = D(IKey)
End If

'A control forcing to initialize the static variables
'when the program is rerun for the same query.
   If K = y Then
      K = K + 1
   End If

DiminishingBal_Exit:
Exit Function

DiminishingBal_Err:
MsgBox Err & ":" & Err.Description, vbOKOnly, "DiminishingBal()"
Resume DiminishingBal_Exit
End Function

How the Function Works.

In the Global declaration area of the VBA Module the Variable D is declared as an Object.

The DiminishingBal() Function needs four parameters:

  1. A Unique Value Field (Numeric or String Values) as first parameter.  The parameter data Type is declared as Variant.
  2. The Unique Value’s Field Name as second parameter in String format.
  3. The Loan Installment Value Field Name.
  4. The Query Name as fourth Parameter.

Four Static Variables K, X, fld and y are declared.  They must be Static Variables to retain their values between repeated calls of the Function, from each record of the Query.  The Variable p is declared as a Variant Type normal variable, to hold the Key Value (either Numeric or String) of each record.

The DCount() Function takes the record count of the Query in Variable y. The Value in this Variable is used as a control to check when to Reset the Static Variable Values to their initial Values and to remove the Dictionary Object from memory.  This control is necessary if the same Query is run more than once, consecutively.

  1. If the value in control Variable K is more than the Query record count in variable y then resets the Static variables to their initial values and the Dictionary Object is deleted from memory.
  2. Or, If the installment value Field Name is different, from the field name saved in Variable fld  during the last call of the function,  then it assumes that the Function is called from a different Query Column and resets the Static Variable Values. The Dictionary Object is deleted from memory.

Next, Variable K is incremented by 1.  When K=1 the main action of the Function starts.  The Database and Recordset Objects are declared in DB and rst Variables respectively.

In the next executable statement Set D = CreateObject("Scripting.Dictionary") creates the Dictionary Object  with the CreateObject() method and assigns it to the Object variable D, that was declared in the Global Area of the Module.

There are other ways to declare and use this Object by adding the Microsoft Scripting Runtime File to the Microsoft Access Reference Library List.  After that you can create an instance of the Dictionary Object in the following way:

Dim D As Dictionary
Set D = New Dictionary

If you are new to the Dictionary Object and it’s usage visit the Post: Dictionary Object Basics.  There are five Posts on this Topic and you can find the links at the end of this Page.

Next we need the value of repayable total Loan Amount and retrieves it from it’s Table tblRepay with the Dlookup() Function in the statement: X = DLookup("[LoanAmt]", "tblRepay", "[id] = 1").  There is only one record in the Table with ID number 1 and you may omit the criteria part.

The Query Recordset is open to read records one by one. The first record’s amount, paid to the bank, is deducted from the Loan Amount (1000) in Variable X.  The Unique Key value of the record is retrieved from the record and saves it in variable p, in the next statement.

The balance loan amount, calculated after deducting the repaid Amount, is added to the Dictionary Object, with Dictionary Object’s  Add Method, as it’s Item Value, with the Unique Key field value in variable p as Dictionary-Key in the statement: D.Add p, X. The Dictionary’s Add method always adds it’s Item value in Key, Item pairs.

Note: If the Key Value is not Unique then the Add method fails and will end up with Errors.

With the rst.MoveNext statement takes the next record for the same sequence of processing and adds the result value to the Dictionary Object.

This way individual record value is deducted from the remaining balance loan amount at that level and added to the Dictionary Object as it’s Item.

Note: Here, you may ask why the Dictionary Object is chosen to hold all the calculated values rather than in an Array.  Yes, It can be done but that method needs more statements to store and retrieve the values in a two Dimensional Array. It will become more complicated when the Query Record’s Unique Key Value is in String form.  The Dictionary Object allows the value retrievel in either sequential or random order based on it’s Key.  Here, the Random method works fine with the Key Value Type in Numeric or String form.

When all the record processing is complete the recordset and Database Objects are closed.

What you have to keep in mind at this point is that still the value in variable K=1 and the first Parameter IKey retains the first records Unique Id Value.  At the first record level call of the function DiminishingBal() itself we have calculated all the record level balance loan amount values, one by one and added the result to the Dictionary Object as it’s Items. The function parameter IKey still holds the first record’s Unique ID value. That is the reason why we have used a separate variable p for individual record key values, while processing all the records.

So, the entire record level processing is done during the first call of the function, initiated from the first record of the Query and all the record level result values are held in a temporary storage in the Dictionary Object.

The next statement DiminishingBal = D(IKey) retrives the first value added to the Dictionary using the unique parameter value IKey  and returns the value to the calling first record of the Query.

The next call from the second record of the Query increments the variable K by 1 (now K=2) and the program takes the ELSE path of the IF. . .Then statement and retrieves the second Item value from the Dictionary Object, using the IKey parameter and returns it to the respective record of the Query.

The rest of the DiminishingBal() function call from the remaining records will route the program control  through only the ELSE  path, because the value in Variable K is greater than one, retrieves the values from Dictionary Item and returns it to the function calling record.

The Next If . . . Then statement checks whether the value in variable K = y or notVariable y holds the total record count of the Query.  If it is found True then it assumes that the last call of the DiminishingBal() function has arrived.  At this point the K Variable is made greater than the value in variable y.

This is necessary to initialize the Static Variables during the rerun of the same Query. In case of any change made on the Source Data before rerun it will not reflect on the balance amount calculated earlier because it will keep taking the ELSE path of the If . . . Then statement and  retrieve the old value from Dictionary Object.

The Demo Database, with all the necessary Objects and the Code Module, is attached for your convenience to download and try it out straight away.


Dictionary Object Links.

  1. Dictionary Objects Basics
  2. Dictionary Object Basics- 2
  3. Sorting Dictionary Object Keys and Items
  4. Display Records from Dictionary
  5. Add Class Objects as Dictionary Items
  6. Update Class Object Dictionary Item
Share:

Opening Access Objects from Desktop

Frequently Used Methods.

  1. Set the Form Name in Display Form Option of Current Database in Access Options.

    BIG DEAL!, this is the first trick any novice learns, when he/she start learning Microsoft Access.

  2. Create an Autoexec Macro with FormOpen Action with required Form Name in the Property.
  3. Both the above options opens a Form, always we wanted to open first, when the database is launched.

Opening Form Directly, without any Changes in Database.

We would like to launch a particular Form automatically tomorrow, to continue updating data on that form, without making any changes in the database for that.

Or, You would like to print a particular Report, first thing in the morning without fail, then here is a simple trick.

Note: Your Database's Navigation Pane must be accessible.

  1. Open the Database.
  2. Click the Restore Window Control-Button to reduce the Application Window size, so that the empty area of the Desktop is visible.
  3. Click and hold the Mouse Button on the required Form Name, in the Navigation Pane, drag and  drop it on the Desktop.
  4. Close the Database.
  5. Double-Click on the Desktop-Shortcut. The Form will be in open state, when the Database is open.

    You can open the following Objects directly, with Desktop-Shortcuts:

Try it out yourself.

  1. MS-Access Class Module and VBA
  2. MS-Access and Collection Object Basics
  3. Dictionary Objects Basics
  4. Withevents and All Form Control Types
Share:

Running Sum in MS-Access Query

Introduction.

We are familiar with creating Auto-Numbers in Query Column, through an earlier Function created with the Name QrySeq(), published on this Website with the Post Title: Auto-Numbering in Query Column, during January 2010. Hope you have come across that Post, if not you may visit the Page by following the above link.

The RunningSum() Function is written somewhat on similar logic, with few parameters matching the QrySeq() Function.

Before going into details let us take a look at some sample Images, before and after run of the new Function in a test Query Column.

A small Table with two Fields: Table_Units and with few records.

The SELECT Query: RunningSumQ1 recordset in datasheet view, with summary values in a separate Column, with the column name RunningSum, from where the RunningSum() Function is called from.

The SQL Code of RunningSumQ1 Query.
SELECT Table_Units.ID, Table_Units.Units, RunningSum([ID],"ID","Units","RunningSumQ1") AS RunningSum
FROM Table_Units;

A Report Designed using RunningSumQ1:

The Query Preparation Note.

Before diving deep into the VBA Code I want you to check the above sample data, to draw your attention to an important point while preparing the data for the RunningSum() Function.

  1. A unique ID Field, like PrimaryKey required in the Query, with either Numeric or String Data and strictly no duplicates in them.
  2. If this is not readily available in the Source Data, you may join (concatenate) two or more field values together, to create unique values in a separate Column, as a Key Field in the Query.
  3. If this method is followed then create a Test Query similar to the sample one given below, using the first Query as source, to find out whether any duplicates still exists in the Source Query or not.
  4. Sample ID Field Record-Count Test Query:

    SELECT RunningSumQ2.ID2, Count(RunningSumQ2.ID2) AS CountOfID2
    FROM RunningSumQ2
    GROUP BY RunningSumQ2.ID2;
    
    

    The CountOfID2 Column result should be like the sample Image give below, with all Count values are showing as one.

  5. When all the ID Field values are unique then the CountOfID2 Column will have the value 1 in all records.  Greater  than one in any record means that those records have duplicate key values and needs to join some other field to eliminate duplicates.
  6. Once you are sure that all records have unique ID values then you may add other required fields in the first Query for the purpose you plan to use it, like Form or Report Source Query.
  7. Once you are ready with the Query data then it is time to add the function in a new Column in the Query, like: Summary:RunningSum([ID],”ID”,”Units”,”MyQuery”).

The RunningSum() Function VBA Code.

Option Compare Database
Option Explicit

Dim D As Object

Public Function RunningSum(ByVal IKey As Variant, ByVal KeyFldName As String, ByVal SumFldName As String, ByVal QryName As String) As Double
'-----------------------------------------------------------
 'Function: RunningSum()
 'Purpose : Creates Running-Sum Value of a Field.
 'The Query can be used as Source for other Processing needs.
 '-----------------------------------------------------------
 'Author  : a.p.r. pillai
 'Date    : November 2019
 'Rights  : All Rights Reserved by www.msaccesstips.com
 '-----------------------------------------------------------
 'Parameter List, in the Order of it's placement
 '1. Key Value Data Field
 '2. Key-Field Name in String Format
 '3. Field-Name for Calculating Running Sum in String Format
 '4  Query-Name in String Format
 '-----------------------------------------------------------
 'Remarks: The Key-Value Field should have Unique Numeric or
 'String Values.
 '-----------------------------------------------------------
Static K As Long, X As Double, fld As String, y as Long
Dim p As Variant

On Error GoTo RunningSum_Err

y = DCount(“*”,QryName)

If SumFldName <> fld OR K > y Then
    fld = SumFldName
    Set D = Nothing
    K = 0
    X=0
 End If

K = K + 1
 If K = 1 Then
     Dim DB As Database, rst As Recordset

    Set D = CreateObject("Scripting.Dictionary")
 
    Set DB = CurrentDb
    Set rst = DB.OpenRecordset(QryName, dbOpenDynaset)
     
    While Not rst.EOF And Not rst.BOF
          X = X + rst.Fields(SumFldName).Value
          p = rst.Fields(KeyFldName).Value
          
          D.Add p, X
          
          rst.MoveNext
     Wend
     
     rst.Close
     Set rst = Nothing
     Set DB = Nothing
     
     RunningSum = D(IKey)
 Else
    RunningSum = D(IKey)
 End If

If K = y then
   K = K + 1
End If

RunningSum_Exit:
Exit Function

RunningSum_Err:
MsgBox Err & ":" & Err.Description, vbOKOnly, "RunningSum()"
Resume RunningSum_Exit
End Function

VBA Code Line by Line.

On the Global area of the Standard Module an Object Variable is declared with the name D. 

The function RunningSum() is declared with four parameters.

  1. The Unique Key Field Value.
  2. The Key-Field Name in String format.
  3. The Summary Field Name in String format.
  4. The Query-Name in String format.

The returned value from function is Double precision number.

Four Static Variables are declared:

  1. K – is a control variable.
  2. X – to hold the Summary Values, added to it at record level.
  3. fld – A control Variable to keep the Summary Field Name as a flag to ensure that the function runs for the same Query.

The Static Variables will retain their values during repeated calls of the Function.

Variable p is to hold the IDKey-value retrieved from the record.  It is declared as Variant Type to accept either Numeric or String Key Value.

The Working Logic of the Function.

The statement If SumFldName <> fld Then checks whether the Key-Field Name passed to the function is different from last call of the Function.  If it is different then it assumes that a different Query is passed to the function.

The Dictionary Object D is erased from memory and other variables are initialized.

In the next step the K Variable is incremented by one. When K=1 the function’s main task is initiated.  

The Database and Recordset Objects are declared.

The D Object variable is instantiated as a new Dictionary Object, with the Object creation statement: Set D = CreateObject(“Scripting.Dictionary”).

By default, the Dictionary Object Reference is not added to the list of Microsoft Access Library Files. If you add it manually then you can declare and instantiate a Dictionary Object, like the Class Object of Access or Collection Object.

Note: If you are not familiar with Dictionary, Class Object or Collection Object, then we have all the information you need to learn the fundamentals about them, in this Website.  The links are given at the end of this page. You may visit them to learn with sample code and Demo databases, as  working models to download.

Adding Dictionary Object Reference File.

To add the Dictionary Object to your Database’s Library Files List do the following:

On the VBA Window, select Tools - - >References… and look for the file: Microsoft Scripting Runtime in the displayed list and put checkmark to select it.

Once you do this you can declare and instantiate a Dictionary Object as given below.

Dim D As Dictionary
Set D = New Dictionary

If you do this you have an added advantage of displaying it's Properties and Methods, when you type a dot (D.) after it's Object name, by intellisense.

Next, the database object DB is set with the active database and the Query is opened as recordset in rst.

Within the  While. . .Wend Loop the summary field and the unique key Field values are read from each record. The Summary field value is added to the Variable X.  The Key value of record is written as Key Value of Dictionary Object and the current Value in X is written as Dictionary Object Item, as Key, Item pair.

The Dictionary Object Items are always written in this way.  The Item can be a single value, an Array, Objects or Collection of Objects. All of them should have a Unique Key Value to retrieve the Item values later.

The purpose of Key in Dictionary  Object is similar to the function of Primary Key in a Table.  We can retrieve any value Randomly or Sequentially from the Dictionary Object using the Key,  like A = D(Key) or  A = D.Item(Key).

In this way the cumulative summary value, at each record level, is added to the Dictionary Object as it’s Item, with unique Key. When all the record level processing is complete, the first record summary field value is returned to the function calling record by execuring the RunningSum = D(IKey) statement, from the first Dictionary Item.  All the above actions are taking place when the  control Variable K=1.  

Subsequent Calls of the function with the Key Value parameter of each record retrieves the corresponding summary value of that record from Dictionary Item and returns it to the Query Column, that’s how it works.

Some Images of a sample Run done on the Products Table of NorthWind.accdb are given below.

Sample Query Run (Key Values are String Type) Data on Form.

SELECT Trim(Str([ID])) & [Product Code] AS ID2, Products.[Product Code], Products.[Product Name], Products.[List Price], RunningSum([ID2],"ID2","[List Price]","RunningSumQ2") AS RunningSum
FROM Products;

The RunningSumQ2 Query is the Record Source of the Form.

Sample Run Data on Report.

The RunningSumQ2 Query is the Record Source of the Report.

Download Demo Database.

CLASS MODULE

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object and Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA-Base Class and Derived Object-2
  5. Base Class and Derived Object Variants
  6. MS-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Wrapper Class Functionality


COLLECTION OBJECT

  1. MS-Access and Collection Object Basics
  2. MS-Access Class Module and Collection Objects
  3. Table Records in Collection Object


DICTIONARY OBJECT

  1. Dictionary Objects Basics
  2. Dictionary Object Basics-2
  3. Sorting Dictionary Object Keys and Items
  4. Display Records from Dictionary
  5. Add Class Objects as Dictionary Items
  6. Update Class Object Dictionary Item


MS-ACCESS EVENT HANDLING

  1. Withevents MS-Access Class Module
  2. Withevents and Defining Your Own Events
  3. Withevents Combo List Textbox Tab
  4. Access Form Control Arrays And Event
  5. Access Form Control Arrays And Event-2
  6. Access Form Control Arrays And Event-3
  7. Withevents in Class Module for Sub-Form
  8. Withevents in Class Module and Data
  9. Withevents and Access Report Event Sink
  10. Withevents and Report Line Hiding
  11. Withevents and Report-line Highlighting
  12. Withevents Texbox and Command Button
  13. Withevents Textbox Command Button
  14. Withevents and All Form Control Types


Share:

Date2Text and Text2Date Functions

Introduction.

We already have some frequently used Report Footer formatting simple functions, like Report Page Number formatting function =PageNo([page],[pages]),  output: Page: 1/20 –> Page: 20/20, Report Period Function =Period([StartDate], [EndDate]), output : Period: 15/09/2007 To 30/09/2007 and =Dated()  Function, output: Dated: 15/09/2007 on Report Footer.  Even though they are simple Report Header/Footer formatting functions it saves report design time.  Check the following Links, if you have not yet come across those Functions earlier:

Useful Report Functions.

Continued on Page 2/- an indicator Label on Report Page Footer, on multi-page reports.

Our new Function formats the Date Value into the following sample Text form:

  Sunday, 27th October 2019.

The Date2Text() Function Code.

Public Function Date2Text(ByVal dt As Date) As String
Dim txt As String, num As Integer

num = Day(dt)

   Select Case num
       Case 1, 21, 31
          txt = "st "
       Case 2, 22
          txt = "nd "
       Case 3, 23
          txt = "rd "
       Case 4 To 20, 24 To 30
          txt = "th "
   End Select
   
   Date2Text = WeekdayName(Weekday(dt)) & "," & Day(dt) & txt & MonthName(Month(dt)) & " " & Year(dt)
   
End Function

Copy and paste the above Code into the Standard VBA Module, save and compile the Code.

Let us try out the Code directly from Debug Window. Press Ctrl+G to display the Debug Window, if it is not already visible on the VBA editing Window.

Sample Test Runs.

D = #27-10-2019#

? Date2Text(D)
Result: Sunday, 27th October 2019

D=Cdate("22/10/2019")

? Date2Text(D)
Result: Tuesday, 22nd October 2019

D=DateValue("11/10/2019")

? Date2Text(D)
Result: Friday, 11th October 2019

? Date2Text(Date)
Result: Thursday, 31st October 2019

Weekday 1 to 7 is Sunday to Saturday. This depends on your Computer's Regional Settings. If not correct in your case then change it on the Regional settings on your Computer.

Scope of this Function.

The Date2Text() Function can be placed in Text-Box on Report Header, use it on Query’s Date-Field Column, or on the Main Form (Main Switchboard or Control Screen etc.) as general info.

The Text2Date() Function.

The Date2Text() Function’s complementary Function Text2Date() VBA Code is given below.

Public Function Text2Date(ByVal txtDate As String) As Date
Dim S, dt As String
    
    S = Split(txtDate, " ")
    dt = Str(Val(S(1))) & "-" & S(2) & "-" & S(3)
    Text2Date = DateValue(dt)

End Function

The date converted into Text form can be changed back into valid date format with the Text2Date() Function. There is no validation check performed on the input value for errors and expected to pass the parameter value in the correct input format, same as the Date2Text() Function output.

Example:

? Text2Date("Thursday, 31st October 2019")

Result: 31-10-2019

Caution: If the parameter value is entered manually then there should not be more than one space between each segment of the date text.

  1. Days in Month Function
  2. Custom Calculator and Eval Function
  3. Rounding Function MRound() of Excel
  4. Proper Function of Excel
Share:

Call Function From MouseMove Event Property

Introduction.

This is about running a User-Defined Function (say myFunction(Parameter)) on the Mouse Move Event Property of Text Boxes.  When the Mouse Move Event occurs the Text-Box name must be passed as Parameter to the function dynamically.

This question was asked in an Access User's Forum (www.accessforums.net), in the Forms Category of Posts, by a member, seeking suggestions for a solution.  A demo database was posted by me there, twice on page-5, but the last one is the final version. 

I thought it will be useful to my readers and presented here for you, with details of this difficult requirement and how this Object Oriented Programming approach solved the puzzle.

Manual Option.

This is easy to set up, if it is manually entered =myFunction("Text1")  on each Text Box's Mouse Move Event Property

But, the requirement is to pass the Text-Box Name as parameter dynamically to the Function.  It means that we should somehow get the Text Box Name from the Mouse Move Event and pass it as parameter to the function, placed on the same Mouse Move Event Property..

To get to know the real situation that demands this method, digest the following requirement of an Access Application:

Complexity of Requirements.

Assume that you are developing a database for a movie ticket booking Application and needs around 350 or more text boxes on the Form, for a graphical design of the seating arrangement.  Each Text-Box represent a single seat in the cinema hall, in an arrangement of several rows & Columns (i.e. each row have several seat positions) and each Seat is having a unique identity number (that is text box name), indicating it's position in the auditorium, like Row-A, Seat No.5 (A5) or B1 etc. The text-box text will show Booked or Vacant  depending on it's current status.

The idea is, when the mouse moves over the textbox (Seat) it should display the Seat Number (A5, or B1 etc.) on a dedicated Label on the Header or Footer Section of the Form, to help the customer to look for his choice of Seat Numbers and book the Seat(s).

A simple Form with several Text Boxes and a label on the top is given below to get an idea of sample arrangement of Text Boxes, to try out methods to solve this problem.

PS: The technical details presented above may have some lapses or may form suggestions in the minds of the reader.  That is not important, the core point is how do we manage to get the Text-Box Name on the Mouse Move Event and pass the name as a string parameter to the =myFunction() Function, placed in the Mouse Move Event Property.

Why Manual Method not Acceptable.

So, writing =myFunction("A5") or =myFunction("B1") etc. in each one of  350 Text Box's Event Property is lots off work.  Besides that, if any change of arrangement of Seats or reworking of the Seat Numbering scheme become necessary then all the text box Properties have to undergo manual changes. 

Another option available is to set the Control Tip Text Property with the Text Box Name. When the mouse-pointer rests on the Text-Box, after a brief delay (the delay is not acceptable), the Seat Number is displayed from Control tip text property by the System.  Modifying the Control Tip Text Property is easy and can be done dynamically on the Form_Load() Event Procedure. 

But, the database designer insists on passing the Text Box Name as parameter to the Function.  Besides displaying the Text-Box Name on the designated Label Caption and the Function may have other issues in the program to take care off as well, on the Mouse Move Event. 

The Difficult Question.

Even though it sounds like a simple issue, the difficult question is how do we get the Text-Box name, say Text1, from the Name Property, when the mouse moves over that Text-Box and pass the name as parameter to the Calling Program?   Remember, the Mouse Move Event fires repeatedly, at every mouse-point coordinates on the text box (or on any other control it moves)  and this Event have some default parameters: Button, Shift, X and Y coordinates of the Mouse Pointer on the Control.  But not the Control Name among them.

The Programming Road-Blocks.

There are times that we face road-blocks on solving issues, when conventional programing approaches doesn't give the correct solutions.  But such issues can be easily handled by few lines of Code through Object Oriented Programming.  This is a classic example, easy to understand and does the job with few lines of code.

Access Class Module Objects.

We have already covered earlier the fundamentals of Access Class Modules and Objects based programming.  If you are not familiar with stand-alone Access Class Modules and Objects then  the links are given at the bottom of this page for you to start learning the basics.

The Easy Solution.

To solve the above narrated issue we have used few lines of Code in the Access Class Module Objects (both Form and stand alone Class Modules) and used Collection Object to organize several instances of the Class Module Objects, rather than using Arrays.

The General purpose Text-Box Object Class Module: ClsTxt Code:

Option Compare Database Option Explicit Private WithEvents txt As Access.TextBox Private frm As Access.Form Public Property Get pFrm() As Access.Form Set pFrm = frm End Property Public Property Set pFrm(ByRef vNewValue As Access.Form) Set frm = vNewValue End Property Public Property Get pTxt() As Access.TextBox Set pTxt = txt End Property Public Property Set pTxt(ByRef vNewValue As Access.TextBox) Set txt = vNewValue End Property Private Sub txt_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single) '------------------------------------------------ 'The first MouseMove Event, of each TextBox, 'comes into this sub-routine. 'The MouseMove Event Property is set with the Function: '"=RunMouseOver('Textbox_Name','Form_Name')" 'with the TextBox & Form Names as Parameters. 'Subsequent MouseMove Events Calls the Function 'directly from Standard VBA Module1, 'control will not come into this sub-routine, any more. '------------------------------------------------

txt.OnMouseMove = "=RunMouseOver('" & txt.Name & "')" End Sub

Two Class-Module Properties, the Access.TextBox  and Access.Form Object are declared in txt and frm object Variables with Private scope, respectively.  The txt Property is declared with WithEvents key word to capture Events originating from Text Boxes on Form. The next twelve lines of Code are for assigning and retrieving objects in Text Box and Form Properties with Set/Get  Property Procedures.  This will prevent direct access to the Class Module Properties txt and frm from outside.  Up to this point it is the Text Box Object's common feature for assigning and retrieving values to and from the Object Variables.  The frm Property is not used here.

The sub-routine part is what we are interested in.  Any number of Text-Box based Event Procedure sub-routines can be written here rather than directly on the Form's Class Module.

The txt_MouseMove() Event.

The Text Box's first Mouse Move Event transfers control into the txt_MouseMove() Subroutine.  There is only one executable statement in the sub-routine, that over-writes the Text Box's Mouse Move Event Property Value. 

txt.OnMouseMove = "=RunMouseOver('" & txt.Name & "')"

We can get the Text-Box name from the txt Property.  The Text-Box Mouse Move Event Property (initially set as "[Event Procedure]" in the Form_Load() Event Procedure) is replaced with the Function "=RunMouseOver('" & txt.name & "')" and passes the Text-Box name as string Parameter.  The subsequent Mouse Move Events will call the RunMouseOver() Function in Standard Module, from the Mouse Move Event Property and never comes back to the above sub-routine txt_MouseMove() any more. 

So, the first Mouse Move Event on any Text Box will do the trick and other Text Boxes will wait for their turn for a Mouse Move Event to take place.

The simple RunMouseOver() Function Code will be presented later in this page.

Form3 Class Module Code.

The Form's (Form3) Class Module VBA Code is given below:

Option Compare Database
Option Explicit

'Declare Class ClsTxt as Object F
Private F As ClsTxt
'Declare Collection Object as C
Private C As Collection

Private Sub Form_Load()
Dim ctl As Control

Set C = New Collection 'instantiate Collection Object

For Each ctl In Me.Controls 'scan through the controls
   If TypeName(ctl) = "TextBox" Then ' Take only Text Boxes
        Set F = New ClsTxt 'instantiate ClsTxt Class Object
        
            Set F.pFrm = Me 'Assign Form to pFrm Property
            Set F.pTxt = ctl 'Assign TextBox to pTxt property
            'enable mouse move event
            F.pTxt.OnMouseMove = "[Event Procedure]"
            
        C.Add F 'add ClsTxt Object to Collection
        
        Set F = Nothing 'remove the ClsTxt object instance from memory
    End If
Next

End Sub

Private Sub Form_Unload(Cancel As Integer)
    'Erase the Collection Object when Form3 is closed.
    Set C = Nothing
End Sub

In the global declaration area Class Module ClsTxt is declared as Object F and Collection Object as C.

In the Form_Load() Event Procedure we scan through Form3 controls and takes only Text Box controls.  The Form Object and Text-Box controls are assigned to the F.pFrm and F.pTxt Properties of ClsTxt Object.

The F.pTxt Object's OnMouseMove() Event Procedure is enabled, so that when it happens the control goes to the txt_MouseMove() sub-routine of Class Module instance of ClsTxt for the first time. In the next step ClsTxt Object instance F is added to the Collection Object, as it's Item.  In the next step the ClsTxt Object instance F is cleared from memory.  A new F object instance is created for the next Text Box.  This is necessary to identify each instance of Text Box Object, with a different internal reference, related to each Text Box added to the Collection Object as it's Item.

This process repeats for all the Text Boxes on Form3.

When the Form is closed the Form_Unload() Event executes and the Collection Object is cleared from Memory.

When Form3 is open these initializing steps are performed and all the Text Box Controls are enabled with the Mouse Move Event, added to the Collection Object and stays in memory till Form3 is closed.  Each Text Box's Mouse Move Event is handled by their respective ClsTxt Object instance added in the Collection Object.

The RunMouseOver() Function Call.

When the User moves the mouse over a Text Box (say Text-box name A1) for the first time the Mouse Move Event executes and calls the txt_MouseMove() Event Procedure in the ClsTxt Object instance, for that Text Box, in the Collection Object item.  In this procedure the TextBox's MouseMove Event Property is modified and inserted with the =RunMouseOver("A1")  Function with the Text Box name A1 as Parameter.

The second Mouse Move Event onwards it calls the RunMouseOver() Function from the Standard Module1.  The VBA Code of this Function is given below.

Option Explicit

Public Function RunMouseOver(strN As String)
    Screen.ActiveForm.Controls("Label0").Caption = strN

End Function

The RunMouseOver() Function receives the text-box name as parameter.  The statement addresses the Label0 control, directly through the Screen Object ActiveForm route  and changes the Label's Caption with the Mouse Moved Text-box Name.

The RunMouseOver() Function can be modified to pass the Form's Name as second parameter and can be used to address the Label0 control as Forms(strForm).Controls("Label0").Caption = strN.  This is avoided to keep the parameter expression simple.

When the Mouse is moved over other Text Boxes the same procedure is repeated for that Text Box Object instances in the Collection Object.

When Form3 is closed the Collection Object instance C, containing all Text Box's ClsTxt Class Object instances, is cleared from memory.

The Function RunMouseOver() assigned to Text Box's Mouse Move Event Properties are cleared (as they are assigned dynamically) and the Property will remain empty.

Next time when Form3 is open everything falls into place again and ready for action.  So everything controlled by the Object oriented Programming and happens dynamically.  This sample database is uploaded as solution to the Access User's Forum Page 5, where several alternative options are suggested by other members of the Forum.  You may visit this Group for suggestions to solve your issues and for help on matters related to Queries, Reports, Forms etc.

The Demo Database is attached and may Download and try it out yourself.


Class Module Tutorials.

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Objects-2
  5. Base Class and Derived Object Variants
  6. Ms-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Wrapper Class Functionality Transformation
  9. Ms-Access and Collection Object Basics
  10. Ms-Access Class Module and Collection Object
  11. Table Records in Collection Object and Form
  12. Dictionary Object Basics
  13. Dictionary Object Basics-2
  14. Sorting Dictionary Object Keys and Items
  15. Display Records from Dictionary to Form
  16. Add Class Objects as Dictionary Items
  17. Update Class Object Dictionary Item on Form
Share:

ROUNDDOWN Function of Excel

We have introduced ROUNDUP() Function of Excel in Microsoft Access earlier.  It works like ROUND() Function except that the number is always rounded up.  When number of digits is greater than 0 (zero), then the number is rounded up to the specified number of decimal places. If number of digits specified is 0 (zero), then the number is rounded up to the nearest integer.

Visit the following link for more details on Roundup() Function:

The ROUNDUP() Function of Excel in Access.

The ROUNDDOWN() Function of Excel does the opposite of ROUNDUP() Function.  When the number of digits specified is greater than 0 (zero) then the number is rounded down to the specified number of decimal Places.  If the number of digits specified is 0 (zero) then the number is rounded down to the nearest integer.

Syntax:

ROUNDDOWN(Number, num_digits)

Number: RequiredAny Real Number.

num_digits: Required, Number of Digits the Number to Round Down to.

Public Function ROUNDDOWN(ByVal Num As Double, ByVal num_digits As Integer) As Double
'-------------------------------------------------
'ROUNDDOWN() Function of Excel Redefined in MS-Access
'Author: apr pillai
'Date  : Sept 2019
'Rights: All Rights Reserved by www.msaccesstips.com
'-------------------------------------------------

Dim S1 As Integer, S2 As Integer

On Error GoTo ROUNDDOWN_Err
S1 = Sgn(Num)
S2 = Sgn(num_digits)

Select Case S1
    Case 0
        ROUNDDOWN = 0
        Exit Function
    Case 1
    Select Case S2
        Case 0
            ROUNDDOWN = Int(Num) * S1
        Case 1
            ROUNDDOWN = (Int(Num * (10 ^ num_digits)) / 10 ^ num_digits) * S1
        Case -1
            num_digits = Abs(num_digits)
            ROUNDDOWN = Int(Num / (10 ^ num_digits)) * 10 ^ (num_digits) * S1
    End Select
    Case -1
    Select Case S2
        Case 0
            ROUNDDOWN = Int(Abs(Num)) * S1
        Case 1
            ROUNDDOWN = (Int(Abs(Num) * (10 ^ num_digits)) / 10 ^ num_digits) * S1
        Case -1
            num_digits = Abs(num_digits)
            ROUNDDOWN = (Int(Abs(Num) / (10 ^ num_digits)) * 10 ^ num_digits) * S2
    End Select
End Select

ROUNDDOWN_Exit:
Exit Function

ROUNDDOWN_Err:
MsgBox Err & " : " & Err.Description, , "ROUNDDOWN()"
Resume ROUNDDOWN_Exit
End Function


The ROUNDDOWN() Function is not field tested for accuracy, use it at your own risk.

The Function is developed based on the sample output given in the Microsoft Help Document. The Microsoft Excel Help Document extract is reproduced below for your information.

FormulaDescriptionResult
=ROUNDDOWN(3.2, 0)Rounds 3.2 down to zero decimal places.3
=ROUNDDOWN(76.9,0)Rounds 76.9 down to zero decimal places.76
=ROUNDDOWN(3.14159, 3)Rounds 3.14159 down to three decimal places.3.141
=ROUNDDOWN(-3.14159, 1)Rounds -3.14159 down to one decimal place.3.1
=ROUNDDOWN(31415.92654, -2)Rounds 31415.92654 down to 2 decimal places to the left of the decimal point.31400
  1. Roundup Function
    of Excel in MS-Access
  2. Proper Function of
    Excel in Microsoft Access
  3. Appending Data from
    Excel to Access
  4. Writing Excel
    Data Directly into Access
  5. Printing MS-Access
    Report from Excel
  6. Copy Paste Data
    From Excel to Access2007
  7. Microsoft Excel Power
    in MS-Access
  8. Rounding Function
    MROUND of Excel
  9. MS-Access Live Data in
    Excel
  10. Access Live Data in Excel-
    2
  11. Opening Excel Database
    Directly
  12. Create Excel Word
    File from Access
Share:

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

Search

Popular Posts

Empty Gadget

Blog Archive

Powered by Blogger.

Follow by Email

Labels

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

Featured Post

Word Mail-Merge with Ms-Access Table

Introduction. Form Letters. Form Letters are prepared from a standard template merged with addressee related information filled in on each l...

Labels

Blog Archive

Recent Posts