Auto-Numbers in Query Column Version-2

Introduction

In January 2010 I published a Function: QrySeq() - Auto-Numbering in Query Column on this website and was 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 its performance by using a better approach, other than a variant Array.

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

If the Query has a 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 of Collection Object, on 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 a 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 the 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 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 the 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 the 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 a Long Integer format.

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

All three Variables control the Code execution paths and determine when to initialize Collection objects 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 fld, then it assumes that the function call is from a new Query Record.  If the field name is the 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, and the Collection Object with existing Items is cleared from memory.  The new Key field name received in the KeyFldName variable is saved in the 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 in Standard Module’s  Global area is instantiated in memory, with the statement Set C = New Collection.

The Query record set is opened to read records one by one. The local variable J is for creating Auto-numbers and adding 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 its Key Value.

If the varKey variable 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 its Item value, Key pairs:

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 to the Collection Object, one after the other.  All this work is done during the 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 record set within the While . . . Wend Loop to add them to the Collection Object?  After adding the Auto-Numbers for all records, the record set and Database Objects are closed.

Remember, we are still on 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 the second record onwards will take the ELSE path of the If K=1 Then statement and retrieve 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 the control variable K = Y. We have already taken the count of records of the Query, in Variable Y at 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 for 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.

Download AutoNumQuery2007.zip

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.

Read More

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 installments.   Our simple task is to show the diminishing balance of the loan amount against each record-level installment amount in a separate Column of the Query.  The last record will have the remaining balance payment amount.

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 record set used as a 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 be used as the first parameter to the Function.

The Total Repayable Loan Amount is 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, Variable D is declared as an Object.

The DiminishingBal() Function needs four parameters:

1. A Unique Value Field (Numeric or String Values) is the first parameter.  The parameter is declared as a Variant data Type.
2. The Unique Value’s Field Name is the second parameter in String format.
3. The Loan Installment Value Field Name.
4. The Query Name is the 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 the 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 the DB and the 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, which 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 its 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 saved 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 its Item Value, with the Unique Key field value in the variable p as Dictionary-Key in the statement: D.Add p, X. The Dictionary’s Add method always adds its 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 its 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 retrieved in either sequential or random order based on its Key.  Here, the Random method works fine with the Key-Value Type in the Numeric or String form.

When all the record processing is complete the record set 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 its 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 temporary storage in the Dictionary Object.

The next statement DiminishingBal = D(IKey) retrieves the first value and 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 to the ELSE path of the IF. . .Then statement, 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 only through 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 not.  Variable 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 route of the If . . . Then statement and retrieves 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.

Download DiminishingBalance.zip
Download DiminishingBalance2003.zip




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

Read More

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 TextBoxes.  The difficult part is that when the Mouse Move Event occurs the Event running TextBox 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 TextBox Name as a 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 a 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:

The complexity of Requirements.

Assume that you are developing a database for a movie ticket booking Application and need around 350 or more text boxes on the Form, for a graphical design of the seating arrangement.  Each TextBox represents a single seat in the cinema hall, in an arrangement of several rows & Columns (i.e. each row has several seat positions) and each Seat is having a unique identity number (that is the text box name), indicating its 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 its 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 look for his choice of Seat Numbers and book the Seat(s).

A simple Form with several TextBoxes and a label on the top is given below to get an idea of textbox arrangements and try out this method to solve the 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 we manage to get the TextBox Name on the Mouse Move Event and pass the name as a string parameter to the =myFunction() Function, placed on 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 of work.  Besides that, if any change of arrangement of Seats or reworking of the Seat Numbering scheme becomes 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 TextBox, after a brief delay (the delay is not acceptable), the Seat Number is displayed from the Control tip text property of 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 a parameter to the Function.  Besides displaying the TextBox Name on the designated Label Caption and the Function may have other issues in the program to take care of 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 TextBox name, say Text1, from the Name-Property of the running form, when the mouse moves over that TextBox, and pass the name as a parameter to the Calling Program?   Remember, the Mouse Move Event fires repeatedly, at every mouse-point coordinate on the text box (or on any other control it moves)  and this Event has 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 Roadblocks.

There are times that we face roadblocks to solving issues when conventional programming approaches don't give the correct solutions.  But such issues can be easily handled by a few lines of Code through Object-Oriented Programming.  This is a classic example, easy to understand, and does the job with a 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 a 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 TextBox 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 Objects are declared in txt and frm object Variables respectively, with Private scope, respectively.  The txt Property is declared with the WithEvents keyword to capture Events originating from TextBoxes 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 TextBox Object's common feature of 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 overwrites the Text Box's Mouse Move Event Property Value. 

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

We can get the TextBox name from the txt Property.  The TextBox 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 TextBox 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() anymore. 

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

The simple RunMouseOver() Function Code will be presented later on 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 take only TextBox controls.  The Form Object and TextBox 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 the Class Module instance of ClsTxt for the first time. In the next step ClsTxt Object instance, F is added to the Collection Object, as its 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 TextBox Object, with a different internal reference, related to each Text Box added to the Collection Object as its Item.

This process repeats for all the TextBoxes 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 stay 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 Textbox 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, 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 the event 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 textbox 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 Textbox Name.

The RunMouseOver() Function can be modified to pass the Form's Name as a 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 TextBoxes the same procedure is repeated for that Text Box Object instance 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 is ready for action.  So everything is controlled by Object-oriented Programming and happens dynamically.  This sample database is uploaded as a 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.

Download MouseMoveEvent.zip

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

Read More

Animated Floating Calendar for Multiple Date Fields

Moving the single Calendar control to different active Date fields.

The Calendar ActiveX Control is a convenient tool for quickly inserting date values into fields with a simple click, eliminating the need for manual typing.

The only drawback of the Calendar ActiveX Control is its large size—it occupies considerable space on the form, especially when multiple date fields are involved. This can interfere with the placement of other controls and compromise the overall layout. As a result, despite its convenience, developers may hesitate to include it in their design and instead fall back on the traditional approach of manually typing the Date value—after all, it’s the user, not the developer, who will be doing the typing.

We’re going to use the Calendar Control—because we don’t accept defeat. The concerns mentioned earlier won’t stop us from using this powerful tool wisely. The method we’ll implement here may seem a bit complex at first, but once set up, it becomes very easy to reuse across other forms within the same project.

Microsoft Access Floating Calendar Project.

Open your database and select a form you've already designed, or create a new one with at least two Date Fields. Let’s begin with the easy part of our project. Copy and paste the following VBA code into a standard (global) module, then save it:

Option Compare DatabaseOption Explicit
'Global Declarations
Public Const twips As Long = 1440
Dim mm_actctl As Control

Public Function Calendar()
Dim sngStart As Single, CalCtrl As Control
Dim ctl As Control, frm As Form, t_height As Long
Dim m_left As Long, m_top As Long, i As Double
Dim w As Long, h As Long, y As Double
Dim caltop As Long, calheight As Long
Dim secHeight As Long
Dim frmWidth As Long, t_width As Long

On Error GoTo Calendar_Err

Set ctl = Screen.ActiveControl
Set mm_actctl = ctl
Set CalCtrl = Screen.ActiveForm.Controls("Cal1")
Set frm = Screen.ActiveForm

CalCtrl.Width = 0.1458 * twips ' 0.1458"
CalCtrl.Height = 0.1563 * twips ' 0.1563"
m_left = ctl.Left + ctl.Width
m_top = ctl.Top + ctl.Height
caltop = m_top
calheight = ctl.Height + (15 * twips * 0.106) '0.105"

secHeight = frm.Section(acDetail).Height
frmWidth = frm.Width
t_height = caltop + calheight
t_width = m_left + (15 * twips * 0.17) '0.17"

If t_height > secHeight Then
    m_top = secHeight - (calheight + (0.106 * twips))
End If

If t_width > frmWidth Then
   m_left = frmWidth - (15 * twips * 0.17) ' 0.17"
End If

CalCtrl.Left = m_left
CalCtrl.Top = m_top
CalCtrl.Visible = True

sngStart = Timer
i = 0.05: y = i
Do While Timer < (sngStart + 0.75)

If Timer >= sngStart + y Then
    y = y + i
    w = CalCtrl.Width + (0.17 * twips) ' 0.17"
    CalCtrl.Width = w
    h = CalCtrl.Height + (0.106 * twips) ' 0.105"
    CalCtrl.Height = h
    DoEvents
End If
Loop

Calendar_Exit:
Exit Function

Calendar_Err:
MsgBox Err.Description, , "Calendar"
Resume Calendar_Exit
End Function

'Insert Date into the active Field

Public Function Cal1Click()
Dim m_cal As Control, m_ctl As Control

On Error GoTo Cal1Click_Err

Set m_cal = Screen.ActiveForm.Controls("Cal1")
mm_actctl.Value = m_cal.Value
m_cal.Width = 0.1458 * twips ' 0.1458"
m_cal.Height = 0.1563 * twips ' 0.1563"
mm_actctl.SetFocus
DoEvents
m_cal.Visible = False

Cal1Click_Exit:
Exit Function

Cal1Click_Err:
MsgBox Err.Description, , "Cal1Click"
Resume Cal1Click_Exit
End Function

Note: If you encounter any errors while running this code, please refer to my earlier post titled Command Button Animation. It explains how to link the necessary library references to your project—an essential step for this code to work correctly.

MS-Access Calendar Control.

1. Open the Form in Design View.

2. Select Microsoft Access ActiveX Control from the Insert Menu.

3. Scroll through the Displayed List and find Calendar Control as shown in the image below:

   

   Right-click to open a Large Image in New Tab/Window

4. Select it and click OK. A Calendar Control is inserted into your Form.

   

   Right-click to open a Large Image in New Tab/Window

5. Temporarily position the control anywhere on the form where it's convenient.

6. Next, click on the control to select it, open its Property Sheet, and update the following property values as indicated below:

MS Access Calendar Control Properties

1. Name = Cal1
2. Visible = False
3. Special Effect = Raised
4. Border Color = 0
5. Back Color = 11139322
6. Month Length = System (Medium) - Access 2003, e.g.: Jul 2007
7. Grid Lines Color = 2147483632
8. Grid Font Color = 10485760
9. Title Font Color = 10485760
10. Width = .1458"
11. Height = .1563"

After adjusting the Width and Height properties, the Calendar control becomes a small rectangle that you can easily position anywhere on the form, wherever it's most convenient.

Next, we need to copy a few additional lines of code into the Form's code module. While the form is in Design View, either click the Code button on the toolbar or choose View → Code from the menu. Then, paste the following code into the form’s module:

Private Sub Cal1_Click()
   Cal1Click
End Sub

Private Sub Detail_Click()
   Me.Cal1.Visible = False
End Sub

Private Sub Form_Load()
   Me.Cal1.Value = Date
End Sub

When you click on a date in the Calendar, the Cal1Click() routine is triggered, inserting the selected date into the currently active field and then hiding the Calendar.

However, if you decide not to select a date and simply want to hide the Calendar, clicking anywhere on the empty area of the form’s Detail section will do so. This behavior is handled by the Detail_Click() routine.

Additionally, when the form opens, the Calendar is automatically initialized with the system's current date, thanks to the Form_Load() event procedure. This ensures the Calendar always starts with today’s date selected.

If you're new to customizing Microsoft Access menus and toolbars, the next section might seem a bit unfamiliar. But don’t worry—we're just going to explore features that already exist within Access. It’s something every developer eventually needs to learn, and getting familiar with it now will save you time later.

Creating an MS Access Toolbar Button.

We'll now create a new toolbar button and link it to the procedure you copied into the global module earlier. For added convenience, we'll also add a copy of this toolbar button to the shortcut menu. This setup allows you to easily launch the animated floating Calendar control directly from your MS Access form.

Select the following Menu Option:

1. View - - > Toolbar - - > Customize. Click New to create a new Toolbar and name it ToolCal, then click OK. A new small empty Toolbar will show up above.

   

   Right-click to open a Large Image in New Tab/Window

2. Select the Commands Tab. Click and drag the New button, and place it on the new Toolbar.

3. Right-click the new toolbar button, and point the cursor at the Change Button Image option in the displayed menu to show several Button Images. You may select one of the Images you prefer to use.

   

   Right-click to open a Large Image in New Tab/Window

   If you'd like to create a custom button image, you can do so by choosing the Edit Button Image option from the menu. In this example, I selected a fish image, fitting since it's for a Floating Calendar. After selecting the image, right-click the button again to bring up the menu and enter &Calendar the Name field. You can choose the button style as Image only, Text only, or Image and Text. The default style (Image and Text) is suitable for now, so you can keep that selected.

4. Next, we need to link our Calendar() program to the toolbar button. Right-click on the toolbar button and select Properties from the context menu. In the On Action field, type:

   =Calendar()
   

   (Be sure to include the equal sign at the beginning.)

   Click Close to save the changes.

   Our toolbar button is now ready and can be used to launch the calendar on the form. If that’s all you need, you can stop here. However, we’ll take it a step further by adding a copy of this button to the form view’s shortcut menu. This will allow users to simply right-click on a date field and select the calendar option from the shortcut menu, making it even more convenient. The calendar remains hidden until the button is clicked.

5. Select the Toolbars Tab in the Customize Dialog Control and put a tick mark for Shortcut Menus.

   Before copying the button, let’s first locate the target area where we’ll place it. In the Shortcut Menus options, locate and expand the Form section. You’ll see a long list of sub-menu groups—find and select Form View Control. This group contains standard options like Filter by Selection, Cut, Copy, Paste, and more. These are the default items that appear when you right-click on a text control in Form View. Refer to the image below for guidance.

   

   Right-click to open a Large Image in New Tab/Window

   The shortcut menus vary depending on where you right-click—whether on an empty area of the form, the record selector, or a control. Therefore, it's essential to place a copy of the new button specifically on the Form View Control shortcut menu, which appears when you right-click on a text control in Form View.

   Be aware that this method has a few side effects. For instance, if you click the new toolbar button on a form that doesn’t contain the Calendar control, or if the control isn't named Cal1 You may encounter error messages.

   We have more articles that explore this topic in greater detail, including how to design custom Microsoft Access menubars, toolbars, and shortcut menus—and how to use them effectively on forms. Relevant links are provided at the end of this page for your reference.

6. Now, let’s proceed with placing the button. Hold down the Ctrl key with one hand, then click and drag the new toolbar button with the mouse. (Make sure to keep the Ctrl key pressed—otherwise, the button will be moved rather than copied.)

   While dragging, hover over the Form menu in the Shortcut Menu Bar to reveal its submenu groups. Then move the pointer to Form View Control to display its shortcut menu options. Drop the button at the left edge of the menu, aligning it with the existing icons for a clean look.

   Finally, click Close to exit the customization mode.

Trial Run.

We are ready to try out the Animated Floating Calendar. Open your Access Form in Form View and right-click on a Date Field. The new button on the Shortcut Menu, with the Fish Icon, and the Calendar Caption should be visible as shown below:

Right-click to open a Large Image in New Tab/Window

Click the Calendar button from the shortcut menu. The ActiveX Calendar control will unfold smoothly with a yellow background, appearing just below and to the right of the field you right-clicked. While it's typically used with date fields, you can activate it from any field on the form.

Right-click to open a Large Image in New Tab/Window

If we want to restrict calendar-based date selection to only date-type fields, we’ll need to validate the Control Source of the active control against the field type defined in the underlying table or query. Based on that, we can decide whether to display the calendar or show a warning and prevent it from appearing. While this refinement is possible, it requires additional code. For now, we’ll keep things simple and revisit those enhancements later.

Click on a date in the Calendar to insert it into the field you previously right-clicked. If you decide not to insert a date and simply want to close the Calendar instead, just click on any empty area in the detail section of the form. The Calendar will then disappear.

Normally, the Calendar appears to the right and below the control you right-clicked. If the Date field is located near the bottom or right edge of the form, the Calendar automatically repositions itself to fit within the visible area, typically as close as possible to the field. In such cases, it may slightly overlap the field, but you can still click on the Calendar to insert a date without any issue.

NB: The Program may not work correctly if you attempt to use this method on a Sub-form.

Download

Download AnimatedCalender.zip

1. Calendar and Toolbars
2. Custom Menus and Toolbars
3. Custom Menus and Toolbars 2
4. Startup Screen Design

Read More

Startup Screen Design

Introduction

A Startup Screen offers a great first impression for your application. Beyond showcasing your design skills, it also provides an opportunity to run essential daily routines—such as automatically backing up the database or verifying the connection status of linked tables from external sources—all quietly in the background.

Sample Design.

Let’s explore a simple design for a Startup Screen. The completed layout is shown below, featuring an animated Website Address Image prominently displayed at the center.

Right-click to open a Large Image in New Tab/Window

We’ll now examine the form-level and control-level settings individually.

Note: All object dimensions and specifications on this site are provided in U.S. measurement units. If you use the metric system, please convert the values accordingly or change your PC’s regional settings to U.S. standards via the Control Panel.

Form Property Values.

Open a new Form and set the Detail Section & Form level Properties as given below:

1. Detail Section Height = 2.85"
2. Back Color = 16777215 (white)
3. Form Width = 5.45"
4. Default View = Single Form
5. Allow Form View = Yes
6. Allow Datasheet View = No
7. Allow Edits = No
8. Allow Deletions = No
9. Allow Additions = No
10. Data Entry = No
11. Record Selectors = No
12. Navigation Buttons = No
13. Dividing Lines = No
14. Auto Resize = Yes
15. Auto Center = Yes
16. Pop Up = Yes
17. Border Style = None
18. Control Box = No
19. Min Max Buttons = None
20. Close Button = No
21. What's This Button = No
22. Allow Design Changes = Design View only

Create a Label with the Caption as shown at the top of the design. Display its Property Sheet and change the following Properties:

1. Forecolor = 12632256 (gray color, good for a shadow-like effect)
2. Border Style = Transparent
3. Font Name = Times New Roman
4. Font Size = 18
5. Font Weight = Bold
6. Text Align = Center

To create a shadow effect for the label text:

Copy the existing label and paste it again on the form. Position the new label slightly above and to the right of the original, so the original text appears as a shadow behind it. Then, set the ForeColor property of the new label to 0 (black) or any other bold color you prefer for the shadow effect.

Create another label below it as shown in the design with the caption indicating the Version Number of your Application.

Create a Text Box at the bottom of the design and write the text as shown to display the Copyright information. Set the Text Align property value to Center.

To create a double-lined border effect:

1. Select the Box control from the Toolbox and draw a box around your design.

2. Set the box’s Border Color to Red.

3. Copy the box, then click outside it to deselect the original.

4. Paste the copied box, and move it slightly up and to the right, creating a double-line border effect that adds depth to the layout.

Now we come to the centerpiece of the design.

At this stage, you can insert any visual element you like—such as your company logo, WordArt, or ClipArt—to enhance the look and feel of the form.

If you're inserting objects, like WordArt or graphics copied from other applications (e.g., Word or PowerPoint), they must be converted into static images after pasting them into the form.

To do this:

1. Select the inserted object.

2. Go to the Format menu and choose Change To > Image.

3. A warning message will appear, informing you that the object cannot be edited after conversion.

4. Click Yes to confirm and proceed.

This ensures better compatibility and consistent display within your Access form.

In our design, I used a 3D text Design, which we explored in an earlier topic.

To incorporate it into the form:

• I displayed the 3D text on a white background,

• Captured it using Alt + Print Screen,

• Pasted the screenshot into MS Paint,

• Cropped the relevant portion,

• Saved it as a .jpg file,

• And finally, inserted the image into the form design.

Whatever object you insert, change the following properties to the values given below:

Image Properties.

1. Name = OLE1
2. Size Mode = Zoom
3. Picture Alignment = Center
4. Picture Tiling = No
5. Back Style = Transparent
6. Back Color = 16777215 (White color or change to your design background color)
7. Special Effect = Flat
8. Border Style = Transparent

Save the form with the name “Startup”.

When viewed in Form View, it will resemble the sample layout shown once the programmed animation completes. At first, the center image remains hidden. After a brief delay, it smoothly zooms in from the center and settles into its original position and size.

Right-click to open a Large Image in New Tab/Window

Animation Code.

Reopen the “Startup” form in Design View.

To access the form’s code module, click the Code button on the toolbar or go to Tools → Macro → Visual Basic Editor. Once the editor opens, copy and paste the required VBA code into the form’s module, then save and close the form.

Option Compare Database
Option Explicit
'Global declarations
Dim i As Integer, h As Long

Private Sub Form_Load()
   Me.OLE1.Visible = False
   h = Me.OLE1.Height / 10
   Me.OLE1.Height = h
   Me.TimerInterval = 100
End Sub

Private Sub Form_Timer()
Dim x As Long
i = i + 1
Select Case i
   Case 1 To 5
      'do nothing
   Case 6
      Me.OLE1.Visible = True
   Case 7 To 15
      x = Me.OLE1.Height
      x = x + h
      Me.OLE1.Height = x
   Case 40
      Me.TimerInterval = 0
      i = 0
      DoCmd.Close
 End Select
End Sub

Private Sub Form_Unload(Cancel As Integer)
    DoCmd.OpenForm "Control", acNormal
End Sub

From the Tools menu, choose Startup. In the Display Form/Page dropdown, select your “Startup” form. To customize your application further, enter a title in the Application Title field—this will replace the default “Microsoft Access” title bar text. You can also assign a custom Application Icon, such as your company logo in .ico, or a small one .bmp format, to replace the default Access icon. If you prefer to hide the database window when the application runs, uncheck Display Database Window.

When the Startup Screen is closed, the last three lines in the VB Code will attempt to open the Application's "Control" Screen. You must have a Screen named Control (Switchboard), or you can change the name of the form you would like to open in the following code segment:

Private Sub Form_Unload(Cancel As Integer)
    DoCmd.OpenForm "Control", acNormal
End Sub

Try out your design. Close and reopen your application and see that your design works as planned.

Download Demo Database.

Download Demo Database

• Opening Access Forms
• Control Screen Menu Design

Read More