LEARN MS-ACCESS TIPS AND TRICKS: Command Button

Showing posts with label Command Button. Show all posts

WithEvents and Defining your own Events

a.p.r. pillaiApril 25, 2019Class Module, Command Button, Controls, Event, WithEvents 1 comment

Defining Custom Event and Class Module.

I hope you have reviewed last week’s introduction to WithEvents, Event, and RaiseEvent, experimented with the sample forms, and understood how the VBA code in both forms interacts. This time, we will try a similar example with one form and a class module, and see what changes are needed in the class module to capture events from the form module.

From this point onwards, we will use the Form and Class Module combination to capture built-in Events from the Form and execute the required code within the Standalone Class Module.

Our ultimate goal is to capture all the commonly used built-in events raised by various controls on a form—such as Command Buttons, Text Boxes, Combo Boxes, List Boxes, Option Group buttons, and others—and handle them using VBA code in a Class Module, an array of class modules, or a collection object. This approach requires only a few lines of code in the form module to pass the form object reference to the class module, allowing you to write the event-handling code in the class module instead of directly in the Form Module.

There’s a long way to go from here, and I hope you will follow each week’s posts and try out the sample exercises provided to track the progressive changes in the code and understand their relevance at each stage.

If you are not yet familiar with class modules, I recommend starting with the earlier articles, beginning with “MS Access Class Module and VBA.”

Last week’s example was simply a demonstration of user-defined events and how to capture them in the target module. The RaiseEvent action was triggered from within a built-in event procedure: Qty_AfterUpdate().

A re-run of last week's Demo with changes.

We will run last week’s example here one more time, with some changes in the setup of related Objects.

Example 2: In this demo, we will use a single form (Form_Form3Custom) and a class module (ClsCustomEvent). The event will be raised from the form, captured in the Standalone Class Module, and the required code will be executed from there.

The sample Form: Form3Custom image is given below:

Right-click to open the large image in a New Tab or Window.

Create a Form named Form3Custom with the following Controls. Copy and Paste the VBA Code given below into the Form's Code Module.

The following Controls are on the Form.

Text Box Name: Qty
Command Button Name: cmdClose
The label above the Text Box. – Form heading.

Form Module Code.

VBA Code behind Form3Custom is given below:

Option Compare Database
Option Explicit

Public ofrm As ClsCustomEvent

Public Event QtyLess(mQty As Single)
Public Event QtyMore(mQty As Single)
Public Event Closing()

Private Sub Form_Open(Cancel As Integer)
  Set ofrm = New ClsCustomEvent
  Set ofrm.mfrm = Me
End Sub

Private Sub Qty_AfterUpdate()
  If Qty < 1 Then RaiseEvent QtyLess(Qty)
  If Qty > 5 Then RaiseEvent QtyMore(Qty)
End Sub

Private Sub cmdClose_Click()
  RaiseEvent Closing
  DoCmd.Close
End Sub

Create a Class Module

Create a Class Module with the Name: ClsCustomEvent

Copy and paste the following VBA code into the Class Module. Compile the database and save it.

Option Compare Database
Option Explicit

Private WithEvents frm As Form_Form3Custom

Public Property Get mfrm() As Form_Form3Custom
  Set mfrm = frm
End Property

Public Property Set mfrm(ByRef obj As Form_Form3Custom)
  Set frm = obj
End Property

Private Sub frm_QtyLess(Q As Single)
Dim msg As String
    msg = "Order Quantity < 1 is Invalid. " & Q
    MsgBox msg, vbInformation, "ClsCustomEvent"
End Sub

Private Sub frm_QtyMore(ByRef Q As Single)
Dim msg As String
    msg = "Quantity: [ " & Q & " ] is above Order Limit 5."
    MsgBox msg, vbInformation, "ClsCustomEvent"
End Sub

Private Sub frm_Closing()
  MsgBox "Form will be Closed Now!"
End Sub

Testing the User-Defined Events

Open Form3Custom in Normal View.
Enter a value greater than 5 in the TextBox and press the Tab Key. If everything went well, you will see an error message.
Try entering a negative value (say –2) into the TextBox and press the Tab key. An error message will be displayed from the Class Module.
If you enter any value in the range of 1 to 5, then no error message will appear.
- Note: Take a closer Look at the ClsCustomEvent VBA Code.
- In the Global declaration area, the Form Object is declared as Private WithEvents frm as Form_Form3Custom (the Form’s specific Class Module Name) rather than the normal declaration Private WithEvents frm as 'Access.Form'.
- The Property Get and Set Property Procedures also use the same Object Type declarations as Form_Form3Custom. The specific Form module name is used as the source of the Event firing.
- In the Custom Event Procedures: QtyLess(), QtyMore(), the parameter type declaration is ByRef.

Important Points to Note.

When you try this out in your own Project, keep the above points in mind; otherwise, it will not work.

Now, we will try to capture built-in events—such as AfterUpdate or Click—from form controls in a class module, and execute the appropriate code for validation checks, calculations, and other tasks, rather than running them within the Form’s class module.

For built-in events fired by controls on a Form, we don't need to define Event and RaiseEvent statements in the Form.

However, the WithEvents declaration is required in a class module to capture events from Form controls such as TextBoxes, CommandButtons, ComboBoxes, ListBoxes, and others.

Built-in Event Capturing in a Class Module.

With this background knowledge, we will now use a Form—similar to Form2 from the earlier example—with a few modifications, along with a Class Module to capture the built-in events.

An Image of the sample Form is given below:

Right-click to open the large image in a New Tab or Window.

One TextBox and two CommandButtons are on the Form. The CommandButton with the caption Exit closes the Form. The Label at the top is for information purposes only.

The Control names are given below:

TextBox Name: Text1
CommandButton: cmdRaise
CommandButton 2: cmdClose
Top Label for information only

The value entered in the TextBox is validated in the AfterUpdate built-in Event, and a message is displayed. The acceptable range of valid values is from 1 to 5. Any value outside this range will trigger an error message.

When the CommandButton, immediately below the TextBox, is clicked, it displays the current value in the TextBox.
The bottom CommandButton, when clicked, displays a message indicating that the Form is closing.

These actions are not handled in the Form’s code module; instead, they are captured and executed in the class module.

Form Module Code.

The VBA Code behind the Form’s (ClassTestForm) Module is given below.

Option Compare Database
Option Explicit

Dim m_obj As New ClsEventTest

Private Sub Form_Load()
Set m_obj.mFrm = Me
End Sub

Private Sub Text1_AfterUpdate()
  'comment
End Sub

Private Sub cmdRaise_Click()
   'comment
End Sub

Private Sub cmdClose_Click()
  'comment
End Sub

The Dim statement at the top declares a ClsEventTest Class Module instance named m_obj.

Within the Form_Load() event procedure, the current form object is passed to the Class Module object’s m_obj.mFrm property. In other words, the current form object is assigned to the Class Module object Instance m_obj.

The Text1_AfterUpdate(), cmdRaise_Click(), and cmdClose_Click() event procedures serve only as placeholders and contain no executable VBA code. A comment line is added inside each procedure to prevent the compiler from removing these empty event procedures.

These empty event procedures must remain in the Form’s Module to trigger the events and allow them to be captured in the Class Module object, where the actual code is executed. Although we can remove them later with some modifications in the class module, for now, we’ll progress one step at a time.

This approach works similarly to the RaiseEvent action used in our earlier user-defined event procedure.

Likewise, the TextBox built-in AfterUpdate Event (triggered when you enter a value and press the Tab key) is captured in the class module, and the corresponding VBA code is executed there.

The TextBox also has other built-in events, such as BeforeUpdate, LostFocus, GotFocus, and more. To capture these events in the class module as well, their corresponding empty event procedures must exist in the form’s module, while the actual handling code should be written in the class module’s subroutines.

Naturally, a question may arise: if these empty event procedures are mandatory (at this stage, yes) in the form’s module, why not just write the entire code there? If this thought crosses your mind, it means you’re on the right track to understanding this technique. We will soon explore a way to eliminate these empty procedures from the form module altogether.

**The Class Module: ClsEventTest Code**

Option Compare Database
Option Explicit

Private WithEvents frm As Access.Form
Private WithEvents txt As TextBox
Private WithEvents btn As CommandButton
Private WithEvents btnClose As CommandButton

Public Property Get mFrm() As Access.Form
  Set mFrm = frm
End Property

Public Property Set mFrm(ByRef vNewValue As Access.Form)
  Set frm = vNewValue
  Call class_init
 
End Property

Private Sub class_init()
'btn object in global declaration area
'is initialized with form Command Button cmdRaise
  Set btn = frm.Controls("cmdRaise")
  
'txt Object is initialized with Form Text1 TextBox
  Set txt = frm.Controls("Text1")

'like btn, btnClose Object is initialized
  Set btnClose = frm.Controls("cmdClose")

End Sub


’Event Handling section

Private Sub btn_Click()

   MsgBox "Current Value: " & Nz(txt.Value, 0), , "btn_Click()"

End Sub

Private Sub txt_AfterUpdate()
Dim lngVal As Long, msg As String
lngVal = Nz(txt.Value, 0) 'Text1 TextBox value

msg = "Order Qty [ " & lngVal & " ] Valid." ‘default message
'perform validation check
Select Case lngVal
    Case Is < 1
       msg = "Quantity <1 is Invalid: " & lngVal
    Case Is > 5
       msg = "Quantity [ “ & lngval & “ ] > Order Limit 5."
End Select
   MsgBox msg, vbInformation, "txt_AfterUpdate()"
End Sub

Private Sub btnclose_Click()
  MsgBox "Form: " & frm.Name & " will be closed."
  DoCmd.Close acForm, frm.Name
End Sub

Private Sub class_terminate()
  Set txt = Nothing
  Set btnClose = Nothing
  Set btn = Nothing
  Set frm = Nothing
End Sub

Class Module VBA Code Line by Line

Let us check what we have in the Class Module.

In the Global Declaration Area of the Module, four Object variables are declared with the WithEvents statement.

In the first line, a Form Object named frm. This Object will be assigned to the Form Object ClassTestForm (or any other form that uses this Class Module) on the Form_Load() Event of the Form.

One TextBox and two CommandButton Controls are declared, with the WithEvents statement, in the Global Area of the Class Module.

These controls will be assigned references to the TextBox and CommandButton controls on the form.

The Public Property Set mFrm() procedure accepts the form object passed from the form’s module.

The Class_Init() subroutine (not to be confused with Class_Initialize()) is called from within the Set Property Procedure, and initializes the TextBox and CommandButton controls by linking them to the corresponding controls on the Form.

For example, the statement:

Set btn = frm.Controls("cmdRaise")

sets a reference to the CommandButton control named cmdRaise on the form object frm.

Similarly, the remaining statements in the Init() procedure set references to the other controls (declared with WithEvents), such as Text1 and btnClose, on the same Form.

Try out the sample Form and Code.

In next week’s post, we will explore how to remove the empty event procedures from the Form’s code module.

Links to WithEvents Tutorials.

Display Records from Dictionary to Form

a.p.r. pillaiMarch 10, 2019Add, Array, Collection Object, Combo Boxes, Command Button, Dictionary Object, Forms 2 comments

Employee Records from Dictionary to Form.

We will now perform a similar exercise to what we did earlier with the Collection Object—displaying Table records on a form based on a key value selected from a ComboBox.

This time, we will use a Dictionary Object to store Employee Records, using the Last Name as the Key.

Design a sample form:

Create a ComboBox in the Header Section (to list all employees' last names).
Create a few TextBox controls in the Detail Section (to display the selected employee’s information).

We then write the Form-based event procedures in the Form Class Module.

Load data records from a Table or Query into the Dictionary Object.
Retrieve a specific record from the Dictionary, based on the selected key (Last Name) from the Combo Box.
Populate the TextBoxes on the form with the retrieved record values.

Note: You can download a Demo Database, with the Form and VBA Code, from the bottom of this page.

Let us start with the preparation steps so that you will know what it takes to complete this Project. You will be better informed of the whole process if you plan to implement this method in one of your own projects.

The Employees Table for Sample Data.

We need some data to load into the Dictionary Object.

Import the Employees Table from the Northwind sample database.
Copy and paste the following SQL String into the SQL editing window and save it with the name: EmployeesQ
```
SELECT Employees.[Last Name], Employees.[First Name], Employees.[E-mail Address], Employees.[Job Title], Employees.[Business Phone], Employees.[Home Phone]
FROM Employees;
```
The Sample Form with a Few Textboxes and a Combo Box.
We will use only a few fields of data from the Employees table. We can quickly design a Form with the field names from EmployeesQ, but will not attach EmployeesQ to the Form as a record source. The next few steps will add TextBoxes with the correct data field names, without typing them into the Name Property of the Text Boxes.
NB: You may assign any name to the Text Boxes; it works with any name.
Select the Design Form option from the Forms Group of the Create Menu. It will open a Blank Form.
Right-click on the Form and select Form Header/Footer to insert Header and Footer sections into the Form.
The Sample Design of the Form is given below.
Right-click to open the large image in a New Tab or Window.
Click the Form Detail Section to make it the active Section.
Now, we will add six Text Boxes from the Employee record field names as the Text Box Names.
Click on the 'Add Existing Field' Button from the Tools Buttons Group in the Design Menu.
Find the Employees Table and click on the [+] Symbol to show the Employees Table Fields.
Double-click on the following list of Fields, one by one, to insert them into the Detail Section of the Form:
- Last Name
- First Name
- E-mail Address
- Job Title
- Business Phone
- Home Phone
NB: This is an Unbound Form, and the inserted Field controls also must be Unbound Text Boxes.

Keep the Text Box's Name Property Value (Field Name) and remove the Control Source Property Value.
Click on the first Text Box to select it.
Display the Property Sheet (F4) of the selected Text Box.
Remove the data Field Name from the Control Source Property to make the text box Unbound. Ensure that the Name Property Value remains intact.
Remove other TextBoxes Control Source Property Values.
In the Form Header Section, we need the Employees LastNames list in a ComboBox.
Select the ComboBox control from the Controls Group Menu, in the Design Menu, and create it in the Form Header Section. If the Control Wizard is active, then follow steps 14 to 18, and go to step 19.
If the Control Wizard is on, then select the first option and click Next.
Select the Query Option in the next screen, select EmployeeQ, and click Next.
Double-click the Last Name to select and insert it into the right panel, and click Next.
Select the Last Name in the first text box to sort Last Names in Ascending Order, and click Next.
On the next screen, click Finish.
Change the Name Property Value of the ComboBox to cboLastName.
Find the Limit to List Property of the ComboBox and change the Value to Yes.
Copy and paste the following SQL into the Row Source Property of the ComboBox:
```
SELECT EmployeesQ![Last Name] FROM EmployeesQ;  
```
Insert a CommandButton in the Form Footer Section.
Change the Caption of the CommandButton to Exit and the Name Property value to cmdClose.
Select the View Code Button from the Tools group to display the Form's Class Module.

Highlight the entire VBA Code below, copy and paste it into the Form’s Class Module, overwriting the existing lines of Code:

The Form's Class Module VBA Code.

Option Compare Database
Option Explicit

Private D As Object
Dim txtBox() As String

Private Sub Form_Load()
Dim db As Database
Dim rst As Recordset
Dim Rec() As Variant
Dim fldCount As Long, ctl As Control
Dim k As Long, frm As Form, Sec As Section
Dim strKey As String

'Restore the Form to it's actual design size
DoCmd.Restore

'instantiate Dictionary Object
   Set D = CreateObject("Scripting.Dictionary")
   

'Open Recordset Source to save in Dictionary
   Set db = CurrentDb
   Set rst = db.OpenRecordset("EmployeesQ", dbOpenDynaset)
'get recordset fields count
   fldCount = rst.Fields.Count - 1

'Redimension Field Names Array (Rec) for number of fields in Table
   ReDim Rec(0 To fldCount) As Variant

   'Add records to Dictionary Object
   Do While Not rst.EOF
     'Get current record field values into Rec Variant Array
     For k = 0 To fldCount
        Rec(k) = rst.Fields(k).Value
     Next
     'Last Name as Dictionary Key
     strKey = rst.Fields("[Last Name]").Value
     
    'Add record to Dictionary Object with 'Last Name' Key
     D.Add strKey, Rec
     rst.MoveNext
   Loop
   
   'Set current Form
   Set frm = Me
   
'Set Detail Section of Form to look for Text Boxes
   Set Sec = frm.Section(acDetail)
   
'Redim txtBox Array to save Textbox Names on the Form
'To display field values
   ReDim txtBox(0 To fldCount) As String
  
  'Get Text Box Names,from Detail Section of Form, and save them into Array.
  'this will be used in the ComboBox AfterUpdate Event
  k = 0
  For Each ctl In Sec.Controls
     If TypeName(ctl) = "TextBox" Then
        txtBox(k) = ctl.Name
        k = k + 1
     End If
  Next
   
   rst.Close
   Set rst = Nothing
   Set db = Nothing
End Sub


Private Sub cboLastName_AfterUpdate()
  Dim strD As String, R As Variant
  Dim j As Long
  Dim L As Long
  Dim H As Long

'Get Selected Key from ComboBox
 strD = Me![cboLastName]
  
  'Retrieve the record from Dictionary
  'using KEY and load the field
  'Values into the Variant Array
  R = D(strD)
  L = LBound(R)
  H = UBound(R)
'Read Field Values from Array and display
'them into it's corresponding Textbox names on the Form
  For j = L To H
    Me(txtBox(j)) = R(j)
  Next
  Me.Refresh

End Sub

Private Sub cmdClose_Click()
  DoCmd.Close
End Sub


Private Sub Form_Unload(Cancel As Integer)
  'Clear Dictionary Object from Memory
  Set D = Nothing
End Sub

Save the Form named Dict_Employees or any other name you prefer.

How it All Works Together.

There are four Subroutines in the above Code.

Private Sub Form_Load() Event Procedure.
Private Sub cboLastName_AfterUpdate()
Private Sub cmdClose_Click()
Private Sub Form_Unload(Cancel As Integer)

In the Declaration area of the Module, an Object variable D is defined for the Dictionary Object. The txtbox() array variable is declared for the TextBox Names from the Form’s Detail Section and will display the selected record’s field values.

The Dictionary Object is created in the Form_Load Event Procedure,

Immediately after instantiating the Dictionary Object, the EmployeesQ Recordset is opened for adding records to the Dictionary Object. A Select Query is created to select a few fields of the Employees Table.

The Rec() Variant Array is re-dimensioned for the number of fields in the record. Each field value is added to the Rec Variant Array element, and the whole array is inserted into the dictionary Object as a single Item (a record), with the Last Name field value as a unique Dictionary Key.

In the next stage of the code, the Dict_Employees Form’s Detail Section area is scanned to identify text boxes, and their names are saved into the textbox() array for use in the cboLastName_AfterUpdate() Event Procedure. The txtbox() Array was declared in the Module Global area.

When the user selects a name from the ComboBox, the Private Sub cboLastName_AfterUpdate() Event Procedure is executed. The Form's Normal View image is given below:

Right-click to open the Large Image in a New Tab or Window.

When the user selects Last Name from the cboLastName ComboBox, it is saved into the String Variable strD. The statement R=D (strD) reads the corresponding employee record field values array into the Variant Variable R.

We have not explicitly defined the Variant Variable R as an Array. But when we read an Array of values from a Dictionary Object Item into it, it automatically re-dimensions itself with the required number of elements and loads each field value into them.

The next two steps determine the Array Index range values.

Within the For ... Next Loop, the record values are displayed in the Form TextBoxes, using the TextBox names saved in the Form_Load() Event Procedure.

You may select other names from the ComboBox to display their details on the Form. When you are ready to close the Form, click on the Exit CommandButton.

When the user selects the Exit CommandButton, the Form closes. Before closing the Form, the Form_Unload Event is triggered, and the Dictionary Object is cleared from Memory.

Download the Demo Database.

Download Dictionary2003.zip

Download Dictionary2007.zip

MS-ACCESS CLASS MODULE

Command Button Color Change on Mouse Button-down Action

a.p.r. pillaiSeptember 14, 2012Command Button, Command Buttons No comments

Command Button Color Change on Mouse Button-Down Action.

After working with the Command Button and Message Box Controls in MS Access, I really got bored with their styles. So, I set out to do something about it and created some animation styles for the command buttons and used Office Assistant (valid only for Access 2003 and earlier versions) for Message Boxes. The Links to those Articles and free Demo Database download links are given below:

Command Button Styles

Command Button Animation
Command Button Animation-2
Colorful Command Button
Message Box Styles (Access2003 or earlier versions only)
Message Box using the Office Assistant
MsgBox with Options Menu
MsgBox with Checkbox Menu

Some New Trick on Command Button.

Here is a new trick usable on the CommandButton Control. CommandButton changes the color on the mouse-down action and restores the original color on the mouse-up action. You can do this with a Label control that has the same dimensions as the CommandButton, filled with the color you like, and position it behind the CommandButton Control. You need two lines of VBA Code in the Form Module as well.

Design the New Animation Style.

Let us go through creating this simple design and enjoy the new CommandButton animation Style.

Open a new Form.
Create a Command Button Control on the Detail Section of the Form.
Display the Command Button’s Property Sheet (F4).
Change the Name Property Value to cmdClose.
Change the Caption Property Value to Close.
Create a Label Control with some caption text near the Command Button.
Select the Command Button and the Label Control together.
Right-click it, highlight the Size option on the Shortcut Menu, and select To Tallest.
Repeat step 8 above and select To Widest.
Click on the Label Control alone and display its Property Sheet (F4).
Remove the text from the Caption property.
Change the Special Effect Property value to Raised.
Select the Back Color Property and click the Build (...) button to display the Color Palette.
Select the color you like the most to fill the background of the label control.
Move the Label control behind the CommandButton control and position it so that it is completely hidden.
If the label control is above the CommandButton, then display the Shortcut Menu by right-clicking and selecting Send to Back from the Position group.
Click on the Command Button and display the property sheet (F4).
Click the Event Tab, select the Mouse Down Event, select [Event Procedure] from the drop-down list, and click the Build (...) button to open the Form’s VBA Module Window.

Copy and paste the following code into the Form Module, overwriting the existing empty subroutine stubs:

Private Sub cmdClose_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
  Me.cmdClose.BackStyle = 0 'transparent
End Sub

Repeat step 18 for Mouse Up [Event Procedure] and copy and paste the following Code into the Form Module:

Private Sub cmdClose_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single) 
    Me.cmdClose.BackStyle = 1 'Normal 
End Sub

Test Run.

Now, it is time for the test run of our design.

Save the Form as frmButton.
Open frmButton in Form View.
Click the CommandButton and hold the mouse button down. You will see the CommandButton’s color change to the background fill color you selected for the label control, and the CommandButton caption text Close becomes visible on it.
Release the mouse button. The original CommandButton view is restored.

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

You can learn another trick from an earlier Article on the Transparent CommandButton trick here.

Creating Animated Command Button with VBA

a.p.r. pillaiJune 08, 2011Animation, Animations, Command Button, Command Buttons, Forms, VBA No comments

Creating an Animated CommandButton with VBA.

My very first blog post on this website, published back in September 2006, was about the CommandButton Animation method. I have used this technique in almost all of my projects since then. The animated response of CommandButtons to mouse movements gave the Forms a sense of life, making them feel interactive rather than rigid and boring.

The design consists of a CommandButton control and a Rectangle control configured with the Shadow special effect. The CommandButton is manually positioned over the Rectangle control to create the desired visual appearance. The animation is implemented in the Mouse_Move() event procedures in the Form Module, which invoke a common function to perform the Animation.

If you haven’t read the earlier post yet, I recommend reviewing it first to get a general understanding as how it works before continuing. [Click here to go to the earlier article.]

Now, let’s take this a step further and see how to create animated CommandButtons, with their corresponding animation procedures written into the Form module, all at design time, with just a single click of a button.

Animated Button Creation Code.

With two VBA routines, we can do this very easily.

Open one of your databases.
Display the Standard VBA Module.
Create a new Standard Module (Insert -> Module).
Copy both of the following Functions into the Standard Module and save the code:

CommandButton Animation Code:

Public Function BAnimate(ByVal strForm As String, ByVal mode As Integer, ByVal lblName As String)
'---------------------------------------------------------------------------
'Author : a.p.r. pillai
'Date   : July 1998
'Remarks: Animates the Command Button
'Rights : All Rights Reserved by www.msaccesstips.com
'---------------------------------------------------------------------------
Dim Frm As Form, l As Long, t As Long

On Error GoTo BAnimate_Err

Set Frm = Forms(strForm)

l = Frm.Controls(lblName & "Box").Left
t = Frm.Controls(lblName & "Box").Top

If (mode = 1) And (Frm.Controls(lblName & "Box").Visible = False) Then
    Frm.Controls(lblName & "Box").Visible = True
        Frm.Controls(lblName).Left = l - (0.0208 * 1440)
        Frm.Controls(lblName).Top = t - (0.0208 * 1440)
        Frm.Controls(lblName).FontWeight = 700
ElseIf (mode = 0) And (Frm.Controls(lblName & "Box").Visible = True) Then
    Frm.Controls(lblName & "Box").Visible = False
        Frm.Controls(lblName).Left = l
        Frm.Controls(lblName).Top = t
        Frm.Controls(lblName).FontWeight = 400
End If

BAnimate_Exit:
Exit Function

BAnimate_Err:
Err.Clear
Resume BAnimate_Exit

End Function

Animated CommandButton Creation Code:

Public Function CreateButton(ByVal intSection As Integer, strName As String)
'---------------------------------------------------------------------------
'Author : a.p.r. pillai
'Date   : Aug. 2000
'Remarks: Creates animated command button controls and Programs on a Form
'       : at design time of the form.
'Rights : All Rights Reserved by www.msaccesstips.com
'---------------------------------------------------------------------------
'Program Parameter Values : intSection
'     0 =  Form Detail Section
'     1 =  Form Header Section
'     2 =  Form Footer Section
'                         : strName
'Command Button Name Property Value
'The Name will be prefixed with the text 'cmd'
'Example: Edit will be modified as cmdEdit
'Sample Run : CreateButton 0,"Edit"
'Remarks    : If more than one Form is opened in Design View then the
'           : Command Button will be created on the first Form
'---------------------------------------------------------------------------
Dim Frm As Form, Ctrl As Control, txt As String
Dim vLeft As Long, vTop As Long, vWidth As Long, vHeight As Long
Dim i As Long, dif As Double, x As Long, Y As Long, z
Dim frmViewStatus As Integer, frmsCount As Integer, j As Integer
Dim hfd As String
On Error Resume Next

i = 1440
dif = 0.0208 * i
vLeft = 0.25 * i
vTop = 0.0521 * i
vWidth = 1.25 * i
vHeight = 0.2375 * i

frmsCount = Forms.Count - 1
frmViewStatus = False
For j = 0 To frmsCount
    If Forms(j).CurrentView = 0 Then
      Set Frm = Forms(j)
      frmViewStatus = True
      Exit For
    End If
Next
If frmViewStatus = False Then
   MsgBox "Open a Form in Design View then try again." & vbCr & vbCr & "Program CreateButton() Aborted."
   Exit Function
End If

  Set Ctrl = CreateControl(Frm.Name, acRectangle, intSection, , , vLeft, vTop, (vWidth - dif), (vHeight - dif))
  
  With Ctrl
    .Name = "cmd" & strName & "Box"
    .SpecialEffect = 4
    .BorderWidth = 3
  End With

  Set Ctrl = CreateControl(Frm.Name, acCommandButton, intSection, , strName, vLeft, vTop, vWidth, vHeight)
  
  With Ctrl
    .Name = "cmd" & strName
    .Caption = strName
  End With
   
  x = Frm.Module.CreateEventProc("MouseMove", Ctrl.Name)
  
  txt = "BAnimate me.name, 1, " & Chr(34) & "cmd" & strName & Chr(34)
  Frm.Module.InsertLines x + 1, txt

  hfd = Choose(intSection + 1, "Detail", "FormHeader", "FormFooter")

  z = Frm.Module.ProcBodyLine(hfd & "_MouseMove", vbext_pk_Proc)

  If z = 0 Then
      x = Frm.Module.CreateEventProc("MouseMove", hfd)
  Else
      x = z + 1
  End If

  txt = "BAnimate me.name, 0, " & Chr(34) & "cmd" & strName & Chr(34)
  Frm.Module.InsertLines x + 1, txt


CreateButton_Exit:
Exit Function

CreateButton_Err:
Resume CreateButton_Exit
End Function

Press Ctrl+S to save the programs. Keep the VBA Window open.

Do the Test Run.

Now that you have the main functions for creating and animating Command Buttons, let us do a quick run to see how easy it is.

Close all open forms, if any.
Open a new Form in Design View.
Display the VBA window, if it was closed or minimized (Alt+F11).
Display the Debug Window (Immediate Window) - Ctrl+G.
Type the following statement in the Debug Window and press the Enter Key:
CreateButton 0, "Edit"
- The first parameter value (0) represents the section of the form where the CommandButton will be placed.
  - 0 = Detail Section
  - 1 = Form Header
  - 2 = Form Footer
- The second parameter ("Edit") serves two purposes: it defines the commandbutton’s Name (which will be modified to cmdEdit and assigned to the Name property) as well as its Caption.
  
  Important: The second parameter must always be a single word. This is essential for correctly creating the MouseMove event procedure in the form’s module. Once the button is created, you can always change its Caption later if needed.
Minimize the VBA window to show the Form in Design View. You can see the CommandButton is created in the Detail Section of the Form with the Caption: Edit.
Change the Form into Form View.
Move the Mouse over the CommandButton. The button moves slightly up and to the left to display a shadow beneath it and to the right of the CommandButton.
Move the mouse out from the button. The CommandButton goes back to its original position, hiding the shadow.

Repeat this action in quick succession. You can see the button movement more clearly.

The VBA Code is Written by the Program.

If you display the Form's VBA Module, you can see that the following VBA Program lines are written into it:

Private Sub cmdEdit_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
     BAnimate Me.Name, 1, "cmdEdit"

End Sub

Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
    BAnimate Me.Name, 0, "cmdEdit"

End Sub

Change the Form mode to Design View.
Click on the CommandButton to select it.
Drag the CommandButton alone to the right and down to show the Rectangle Control with the special effect shadow underneath it.

Right-click to open the Large Image in a New Tab/Window.
The Command Button and the Rectangle Control work together to create the animation effect. To move them as a pair, click near the CommandButton and drag the mouse over both controls to select them together. Then, drag the selection to the desired location on the form.

It is important to do this before creating another CommandButton in the same form section. Otherwise, the new button will be placed directly on top of the previous one. By default, all newly created CommandButtons are positioned in the same location within their section.
Right-click on the Detail Section of the Form and select Form Header/Footer to display them on the Form.

Creating a Button on the Header or Footer Section.

You can now try the CreateButton() function, with the first parameter value set to 1 or 2 to create CommandButtons in the Form Header or Form Footer sections, respectively.

The second parameter, which specifies the CommandButton name, must be unique for each button on the same form.

Below are sample statements demonstrating how to create animated CommandButtons on the header and footer sections of the form:

CreateButton 1, "PrintPreview"

CreateButton 2, "Exit"

Note: Make sure the Header and Footer sections are visible before attempting to create CommandButtons on them.

Also, ensure that only one form is open in Design View. If multiple forms are open, the first form found in Design View is selected for creating the button controls.

During form design time, running the CreateButton() function from the Debug Window is the most convenient method.

However, if you prefer a different approach, such as launching the program from a CommandButton click, that can also be implemented easily.

Button Creation from a CommandButton Click.

A CommandButton Click will prompt for the required parameter values and quickly create the Animated CommandButton on the form, currently in Design View. It generates its associated subroutines in the form’s module.

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

Open a new Form in Design View.
Create a Command Button as you normally do.
Display its Property Sheet when the CommandButton is in selected state (F4).
Change the Name property value to cmdCreate and set the Caption property value to Create Button.
Display the Form's VBA Module (Alt+F11).

Copy and paste the following Code into the Module and press Ctrl+S to save the Form with the name frmCreateButton:

Private Sub cmdCreate_Click()
Dim intSection As Integer, strButtonName As String
Dim msg As String
msg = "Form Section (0,1 or 2)" & vbCr
msg = msg & "0 - Form Details" & vbCr
msg = msg & "1 - Form Header" & vbCr
msg = msg & "2 - Form Footer"

intSection = 3
Do While intSection < 0 Or intSection > 2
    intSection = Nz(InputBox(msg, "cmdCreate_Click()"), 0)
Loop

strButtonName = ""
Do While Len(strButtonName) = 0
    strButtonName = InputBox("Command Button Name", "cmdCreate_Click()")
Loop

   If (intSection >= 0 And intSection < 3) And Len(strButtonName) > 0 Then
       CreateButton intSection, strButtonName
   End If

End Sub

Perform a Demo Run.

Open a form in Design View and display the Form’s Header/Footer Sections.
Open the frmCreateButton in Normal View.
Click the Command Button to enter the program parameter values.
Input one of the values (0, 1, or 2) suggested by the Prompt to select a Form Section area and press the Enter Key or click the OK button.

Right-click to open the Large Image in a New Tab/Window.
Enter a Name for the CommandButton in the second prompt (it should be a single word, eg. PrintPreview) and press the Enter Key or click OK.
The CommandButton control is created on the selected Form Section in the design view.

If you want to use this method across all your projects, you can transfer the first two functions, BAnimate() and CreateButton(), into a common Library Database. Then, attach this library as a Reference Library File to your projects. (Refer to the blog post CommandButton Animation to learn how to do this.)

CommandButton Animation
Double Action CommandButton
Colorful CommandButtons
Transparent CommandButton
CommandButton Animation-2
Creating an Animated CommandButton with VBA
CommandButton Color Change on Mouse Move

LEARN MS-ACCESS TIPS AND TRICKS

WithEvents and Defining your own Events

Defining Custom Event and Class Module.

A re-run of last week's Demo with changes.

Form Module Code.

Create a Class Module

Testing the User-Defined Events

Important Points to Note.

Built-in Event Capturing in a Class Module.

Form Module Code.

The Class Module: ClsEventTest Code

Class Module VBA Code Line by Line

Links to WithEvents Tutorials.

Display Records from Dictionary to Form

Employee Records from Dictionary to Form.

The Employees Table for Sample Data.

The Sample Form with a Few Textboxes and a Combo Box.

The Form's Class Module VBA Code.

How it All Works Together.

Download the Demo Database.

MS-ACCESS CLASS MODULE

COLLECTION OBJECT

DICTIONARY OBJECT

Command Button Color Change on Mouse Button-down Action

Command Button Color Change on Mouse Button-Down Action.

Some New Trick on Command Button.

Design the New Animation Style.

Test Run.

Creating Animated Command Button with VBA

Creating an Animated CommandButton with VBA.

Do the Test Run.

The VBA Code is Written by the Program.

Creating a Button on the Header or Footer Section.

Button Creation from a CommandButton Click.

Perform a Demo Run.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

Post Feed

Get new posts by email:

Search

USEFUL LINKS

Useful-Links

Popular Posts

About Me

Blog Archive

Labels

Report Abuse

Statcounter

**The Class Module: ClsEventTest Code**