January 2009 ~ LEARN MS-ACCESS TIPS AND TRICKS

Combo-Box Column Values

a.p.r. pillaiJanuary 30, 2009Combo Boxes, msaccess controls 6 comments

Introduction

The Text Boxes and Command Buttons are the most frequently used controls on a Microsoft Access form. They serve as the primary means of user interaction — Text Boxes for data input or display, and Command Buttons for triggering specific actions through macros or VBA procedures.

We have explored several techniques involving these controls in earlier articles, including how to enhance their functionality, improve their visual behavior, and automate form operations. For readers who have not encountered those posts before, the links to those articles are provided below for easy reference. Review them to gain a better understanding of how these controls can be customized and utilized effectively in your own applications.

Next in line is the most preferred and familiar control on Forms: the Combo Box control. This versatile control permits users to select a value from a predefined list or type in a new one. The Combo Box can be created not only on Forms but also directly in Tables, providing an easy and efficient way to maintain data integrity and consistency.

If you would like to see a few examples of how Combo Boxes are used in Tables, you already have them on your PC. Open the Northwind.mdb sample database located at:

C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb
(for Microsoft Office 2003 — the path may vary in later versions)

Now open the following Tables in Design View and observe the Field Properties:

Employees Table
Orders Table
Order Details Table

You will find several fields that use lookup lists, where the Display Control property is set to Combo Box. This helps restrict data entry to valid values while still allowing flexibility through a user-friendly dropdown list.

Employees Table
- Field: Title of Courtesy
Note: Select the Lookup Tab from the Field Properties below. Inspect the Row Source Type and Row Source Property Values. Inspect the other related Property settings as well.
The Row Source Property Values are keyed in as Constants separated by semicolons after setting the Row Source Type value as Value List. This is a single-column list.
To enter values in a two-column list, the Column Count Property value must be 2, and based on that, the values entered into the Row Source property will be read in pairs to display.
When using the constant values (Dr., Mr., Miss, Mrs., Ms.) as Source items for the ComboBox, it is assumed that values other than these are not likely to enter this field, but if necessary, you may enter them manually into the target field. The Limit To List Property Value setting of No suggests this.
- Field: ReportsTo
Note: The Row Source Property value EmployeelD is taken in the ReportsTo Field from the same Employees Table.
The Row Source Type Value is Table/Query, and the Row Source Value is an SQL statement, which pulls data from selected fields from the Employees table itself.
Here, the Limit To List property value is set to Yes, indicating that values other than what is appearing in the ComboBox (or in the Employee ID field) will not be accepted in the Combobox Text Box to select or enter. In other words, you cannot enter a value manually into this field other than what is appearing in the combo box.
The Combo-Boxes created in the Table Structure have more control over the data going into the target field than an external Combo-Box created on a Form. A Form or Query designed with this Table Column will have the Combo-Box attached to it when the Field is dragged from the Field-List to the Form or Query, or when you create a Form or Report with the built-in Wizards.
If it is absolutely necessary to add a new value to the ComboBox, then that should be added to the Source Table either manually or through a VBA Program first. After that, refresh the ComboBox contents so that the newly added value appears in the ComboBox's List.
Orders Table
- CustornerlD
- EmployeelD
- Ship via
Open the Orders Qry in normal view and click on one of the rows of the Customer Column to see the Combo-Box in action on the Query.
Order Details Table
- ProductID

The Bound Column Property

Normally, a Combo Box has one or more columns of information — for example, Employee ID and Employee Name. The value that is actually stored in the target field depends on the Bound Column property setting.

If the Bound Column is set to 1, then the first column value (usually the unique key, such as Employee ID) is saved into the field. The second column (for example, Employee Name) is displayed in the Combo Box for clarity and user convenience, but is not stored in the table.

This dual-column approach provides both efficiency and readability — users can easily identify records by name, while the database continues to work with the key values needed for relational integrity.

The Column Width Property must be set with Values like 0.5"; 1.5" for each column, and the List Width Property Value is equal to the value of all Column Widths added together.

Assume that our Employee Combo Box has three columns: EmployeeID, Employee Name, and Designation. When the user selects an employee from the Combo Box, not only should the EmployeeID be stored in the ReportsTo field, but the Designation should also be displayed automatically in another control (for example, a text box named txtDesignation) on the same Form.

This can be done with a single line of VBA code in the AfterUpdate or On Click event procedure of the Combo Box:


Me!txtDesignation = Me!cboEmployee.Column(2)

Here’s what’s happening in this line:

Me!cboEmployee refers to the Combo Box control on the Form.
.Column(2) refers to the third column (remember: column numbering starts from 0, so Column(0) = EmployeeID, Column(1) = Employee Name, and Column(2) = Designation).
The value from that column is assigned to the txtDesignation text box.

This method can be extended to extract and use values from multiple columns of the Combo Box to populate different fields or text boxes in the Form, all with just a few lines of code.

Private Sub EmployeeCombo_Click()
     Me![Designation] = Me!EmployeeCombo.Column(0,2)
 End Sub

The Row/column index numbers of combo boxes are 0-based, and the third column has an index value of 2.

The value 0 points to the First row in the Combo Box Value List, and the value 2 picks the third column value.

Earlier Post Link References:

Drill-Down Inquiry Screen-2

Continuation of Last Week's Post.

Designing the Employee-Wise Order Summary Form

We will start by creating the top-level summary form that displays employee-wise order totals. This form will serve as the entry point for drilling down into the details of each employee’s sales performance.

Create a New Form
- Open your database and create a new blank form in Design View.
- Set its Record Source property to the query or table that summarizes orders by Employee — for example:
  qryEmployeeOrderSummary
  This query should include fields such as:
  - EmployeeID
  - EmployeeName
  - TotalOrders
  - TotalSales
Add Controls
- Add a Combo Box (cboEmployee) to allow selection of an Employee.
- Add Text Boxes to display summary values:
  - txtTotalOrders
  - txtTotalSales
- Add Labels with appropriate captions (e.g., “Employee”, “Total Orders”, “Total Sales”).
Form Properties
- Set the Default View property to Single Form.
- Set Allow Additions, Allow Deletions, and Allow Edits to No (this is a summary form, not for data entry).
- Set the Border Style to Dialog and Pop Up to No.
Add a Refresh Button
- Insert a small Command Button (cmdRefresh) with the caption “Refresh Summary”.
- Add the following VBA code in its Click event to requery the form data:
```
Private Sub cmdRefresh_Click()
    Me.Requery
End Sub
```

This completes the Employee Summary layer — the top level of your drill-down screen.
Next, we’ll design the second-level subform to display Order-Wise Details for the selected employee and link it dynamically to the summary form.

03_Employee_Summary Form

Designing the Employee Summary Form
1. Select the Source Query
  - Open the Database Window (or Navigation Pane in newer versions).
  - Locate and select the query named 03_Employee_Summary.
    This query should contain summary data for each employee, such as:
    - Employee ID
    - Employee Name
    - Number of Orders
    - Total Sales Amount
    - Region or City (if applicable)
2. Create the Form Using the Form Wizard
  - From the Insert menu, choose Form Wizard.
  - Select 03_Employee_Summary as the record source.
  - Add the following fields in this order (or as per your data):
    - EmployeeID
    - EmployeeName
    - OrdersCount
    - TotalSales
  - When prompted for the form layout, choose Tabular Form (Continuous Form).
  - Complete the wizard and give the form a suitable name, such as Employee_Summary.
3. Clean Up the Design
  The Form Wizard may add unnecessary design elements, like background images, grid lines, and shadowed controls.
  - Open the form in Design View.
  - Remove any background image (set the Picture property to None).
  - Select all controls and set:
    - Special Effect → Flat
    - Back Color → White or Transparent
    - Border Style → Solid
  - Adjust the Form Header to include a descriptive title such as “Employee Summary” in a bold label.
4. Enhance Readability
  - Align the text boxes neatly in columns.
  - Set the Alternate Back Color of the Detail section (e.g., a light grey) for easier row reading.
  - Format the TotalSales field as Currency.
  - Set OrderCount as a Number, with no decimal places.
5. Save and Test the Form
  - Save the form as Employee_Summary.
  - Switch to Form View to verify that multiple employee records appear in a continuous list, as shown in the sample.
✅ Next Step:
In the following section, we will design the Order Details Subform that will display order-level data for the employee selected in this summary form.

Right-click to open Large Image in New Tab/Window.
Select all the data fields together by clicking on the left border of the Detail section. Alternatively, click anywhere within the Detail section and drag the mouse over all the controls to select them simultaneously.
Display the Property Sheet (View ->Properties).
Change the following Property Values:
- Enabled = No
- Locked = Yes
- Tab Stop = No
- Back Color = 12632256
- Special Effect = Raised
Expand the Detail Section of the Form down to get enough room to draw a lengthy Command Button.
Draw a Command Button as wide as the full length of all the Fields above, so that we can cover the fields by placing it over them.
Display the Property Sheet of the Command Button.
Change the following Property Values:
- Name = cmd1
- Transparent = Yes
- Hyperlink SubAddress = #
We must make the Command Button's height the same as the Fields above. Click on the Command Button to select it, hold the Shift Key, and click on any one of the Fields above to select it along with the Command Button. Alternatively, you can click and drag over the Command Button and any one of the fields above.
Select Format -> Size and select Shortest, if the Command Button's height is more than the field, or select Tallest to make the Command Button as tall as the fields above.
Drag the Transparent Command Button and place it over the Fields above.
To make sure that the Transparent Command Button stays above all the data fields, select Format -> Bring to Front.
Now, reduce the Detail Section height, but leave a little gap above and below the Data Fields.
Draw a Text Box in the Form Footer Section below in the same position as the TORDERS field in the Detail Section and write the following expression in the Control Source Property:
```
=Sum([TORDERS]) 
```
Change the Caption of the child label to Total Orders.
Create a Label at the Header of the Form and change the Caption value to EMPLOYEE-WISE ORDERS SUMMARY. Change the font size to 12.
Display the Code Module of the Form (View -> Code), copy and paste the following VBA lines into the Module:
```
Private Sub cmd1_Click()
Me.Parent.Refresh
Me.Parent.Tabctl0.Pages(1).SetFocus
End Sub
```
Save the Form with the Name: 03_Employee_Summary.

04_Order_ListQ Form

Select the Query 04_Order_ListQ and create a Tabular Form (continuous Form) as we did at the top.
Change the design to look like the sample Image given below:

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

Click to Enlarge

Select all the fields as we did earlier and change the following Property Values:
- Enabled = No
- Locked = Yes
- Tab Stop = No
- Back Color = 16777215
- Special Effect = Flat
Follow Steps 6 to 8 given above.
Change the following Properties of the Command Button:
- Name = cmdOrder
- Transparent = Yes
- Hyperlink SubAddress = #
Follow Steps 10 to 13 as explained above. Reduce the height of the Detail Section without leaving the gap above and below the fields.
Create a Command Button at the Footer Section of the Form.
Display the Property Sheet of the Command Button and change the following Property Values:
- Name = cmdMain
- Caption = Goto Main
Expand the Header Section of the Form and drag the Field Headings down to get enough room to create a Heading for the Form.
Add a Text Box above the Field Headings and write the following expression in the Control Source Property of the Text Box:
```
= "Order List of " & [EmpName]
```
Display the Code Module of the Form (View -> Code), copy and paste the VBA Code given below into the Module, and save the Form with the name 04_Order_ListQ.

Private Sub cmdMain_Click()
    Me.Parent.Tabctl0.Pages(0).SetFocus
End Sub

Private Sub cmdOrder_Click()
   Me.Parent.Refresh
   Me.Parent.Tabctl0.Pages(2).SetFocus
End Sub

05_Order_DetailQ Form

Select the Query 05_Order_DetailQ and Create a Tabular Form.

Here, we don't need the Transparent Command Button; change the Form design to look like the sample image below.

Right-click to open Large Image in New Tab/Window.
Create a Text Box in the Form Footer below the Quantity field and write the following expression in the Control Source Property:
```
=Sum([Quantity])
```
Create another Text Box in the Form Footer below the ExtendedPrice Column and write the following expression in the Control Source Property:
```
=Sum([ExtendedPrice])
```
Create a Command Button in the Form Footer below the TextBoxes.
Change the following Property Values of the Command Button.
- Name = cmdBack
- Caption = << Back
Display the Code Module of the Form (View -> Code), copy and paste the following lines into the Module:
```
Private Sub cmdBack_Click()
    Me.Parent.TabCtl0.Pages(1).SetFocus
End Sub
```
Save the Form with the Name 05_Order_DetailsQ.

Now, we are ready to design the Main Form Inquiry. Main to assemble all three Sub-Forms on a Tab Control and make the Tab Control Pages invisible.

Inquiry_Main Form

Select the Parameter Table Date_Param, select Form from the Insert Menu, and select Design View from the displayed list.
Select the Tab Control Tool from the Toolbox and draw a Tab Control on the Detail Section of the Form.
Check the sample image given below. The Tab Control will have only two pages, but we need one more page.

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

Click to Enlarge

Click on the Tab Control to select it and select Tab Control Page from the Insert Menu.
You may select each Page of the Tab Control, display its Property Sheet, and change the Caption Property value of Page1, Page2, and Page3 as Summary, Orders, and Order Detail, respectively, if needed. It is used for reference purposes only.
The next step is to drag and drop the Sub-Forms (03_Employees_Summary, 04_Order_ListQ, and 05_Order_DetailQ) one by one, on the Tab Control Pages.
Position the Database Window with the Forms Tab active and the Inquiry_Main Form with the Tab Control side by side.
Drag and drop the 03_Employee_Summary Form on the Tab Control Page 1.
You may delete the child label attached to the subform. Resize both the subform and the tab control to ensure that the contents are displayed properly on the screen. Save the form with the name Inquiry_Main, and open it in Normal View to verify how the information appears in the subform. Check whether any adjustment to the size of the form or tab control, either an increase or a decrease, is required or not. Leave some space between the tab control and the top of the Detail section of the form to insert a few text boxes for the StartDate and EndDate fields, along with two additional text boxes for control purposes. Also, make sure to leave some space for a heading above these controls.
Once you are satisfied with the subform’s dimensions and overall design, click on the subform and open the Property Sheet. Take note of the following property values on a piece of paper — you will need them later to resize and position the other two forms that will be inserted into Page 2 and Page 3 of the Tab Control.
- Top
- Left
- Width
- Height
Right-click on the Sub-Form and select Copy from the displayed Shortcut Menu.
Select Tab Control Page2, press and hold the Shift Key, right-click on the Tab Control Page2, and select Paste from the displayed menu.
The pasted control will be an empty form displaying the Source Object Name of the copied Form with a white background.
Display the Property Sheet of the Form and change the Source Object Property value to 04_Order_ListQ. After this change, the Form will appear on the Tab Control Page 2.
Change the dimension property values to match the ones you noted earlier. Since you copied the form (rather than dragging and dropping it from the Database Window), you only need to update the Top and Left property values — the Width and Height values should already be the same. If they differ, adjust them accordingly.
Follow Steps 9 to 12 above to bring in the 05_Order_DetailQ Form to the third Page of the Tab Control.
Display the Field List (View -> Field List) if it is not visible.
Drag and drop StartDate and EndDate fields above the Tab Control, create labels above and left, and change their Caption values as shown in the design above.
Create a Command Button to the right of the EndDate field and change its property values as given below:
- Name = cmdRefresh
- Caption = <<Refresh Screen
The Date fields we created, along with two additional text boxes that we will add, will be referenced in the queries we designed earlier to filter the data displayed on the forms. Although the refresh action is not strictly required—since the data is refreshed automatically before the results are displayed—it serves as an additional feature that allows the user to manually refresh and update any recent changes.
Create a Text Box to the right of the Command Button and display its property sheet, and change the following Property Values:
- Name = EID
- Control Source = =[03_Employee_Summary].Form!EmployeeID
- Visible = No
Create another Text Box below the earlier one and change the property values as given below.
- Name = OID
- Control Source = =[04_Order_ListQ].Form!OrderID
- Visible = No
Create a heading on top of the Form with the Caption Value SALES PERFORMANCE INQUIRY.
Create a Command Button below the Tab Control and change the following property values:
- Name = cmdQuit
- Caption = Quit
Now, we make the Tab Control disappear, and the Tab Control's appearance will change. It will not look like a Tab Control after the change.
Click on the Tab Control, display the Property sheet, and change the following property values:
Caution: After changing the property values, make sure to click the Save button on the toolbar or choose Save from the File menu to preserve your changes immediately. In Microsoft Office 2000, attempting any other action before saving may cause the form to lock up.
- Back Style = Transparent
- Multirow = No
- Style = None

Display the Code Module of the Form, copy, and paste the following few lines of code into the Module:

Private Sub cmdQuit_Click()
  DoCmd.Close acForm, Me.Name
End Sub

Private Sub cmdRefresh_Click()
   Me.Refresh
End Sub

Private Sub EndDate_LostFocus()
Me.Refresh
End Sub

Private Sub Form_Load()
Me.TabCtl0.Pages(0).SetFocus
End Sub

We will finish the design by setting the Property Values of the Inquiry_Main Form. Display the Property Sheet of the Form and change the following Property Values:
- Allow Edits = Yes
- Allow Deletions = No
- Allow Additions = No
- Data Entry = No
- Scroll Bars = Neither
- Record Selectors = No
- Navigation Buttons = No
- Dividing Lines = No
- Auto Re-size = Yes
- Auto Center = Yes
- Pop up = Yes
- Modal = No
- Border Style = Dialog
- Control Box = Yes
- Min Max Buttons = None
- Close Button = Yes
- Allow Design Changes = Design View Only
Save the Form, open it in Normal View, and try out your creation.
Note: If you encounter any issues while running your design, refer to the downloaded sample database. Compare your design with it to identify and correct any mistakes.

Drill-Down Inquiry Screen

a.p.r. pillaiJanuary 17, 2009Forms, msaccess forms 3 comments

Introduction

Following is an image of a three-layer Inquiry Screen, designed for the Management, to keep track of the Northwind Traders' staff-level Sales Performance:

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

The top section of the Form displays a salesperson-level summary of orders along with the percentage of total orders. The Form footer shows the combined total of all orders. When you click on a specific employee's record, the individual order-level summary for that employee will appear on the main screen, overlaying the previous summary view. Refer to the image below for illustration.

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

Several items can be ordered under a particular Order, and details of all items can be displayed by clicking on one of the Order records. Check the image given below.

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

The Form displays order-level details along with summary information in the footer, showing the total quantity of all orders and the total net value of all items after discounts. The command buttons at the footer of each sub-form allow you to switch back to the upper layer of the Form.

The date parameter values at the top can be adjusted to display information for a different period.

I want to assure you that there is no complex VBA code driving this design—only one or two lines are used here and there to refresh controls or switch between form layers. Beyond that, the entire screen operates using Queries and Forms.

Designing the Forms

We need several tables from the Northwind.mdb sample database to create six simple queries, three subforms, and a main form to organize them and present the information effectively to the user.

Note: If you would like to see the Inquiry Screen in action before designing it, you can download the demo at the bottom of this post. If you find it difficult to understand how it works or how to assemble all the components, you can return here and follow the step-by-step design instructions. This will help you see how simple or complex it is and understand how each element interacts with the others.

In this tutorial, we will also revisit the use of transparent command buttons, which were covered in an earlier article with the same title.

Due to the complexity of the design, this topic will be split across multiple posts. Although I could show you how to build it in just a few minutes, explaining the process with images and property settings requires more detail.

Downloading and exploring the demo database first will help you better understand the functionality and make the step-by-step design task more engaging.

The Design Task

So, let us start with the first part.

Download the following Tables from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb (MSOffice2003 address, you can replace Office in place of Office11 if your Version of Access is 2000):
- Employees
- Orders
- Order_Details
- Products
A Parameter Table.
Create a Parameter Table with the following name and Structure:
Parameter Table: Date_Param

Srl. Field Name Data Type Size

1. StartDate Date/Time

2. EndDate Date/Time
Open the Table and create a single record with the following StartDate and EndDate values, and save the record:

Parameter Table: Date_Param

StartDate EndDate

01-01-1996 31-12-1996
Open new Queries and copy the following SQL Strings into the queries' SQL Editing Module surface, and save them with the exact Name given for each one of them. Create the Queries in the same order as shown below, as they have dependencies on Queries created first.

Parameter Table: Date_Param
Srl.	Field Name	Data Type	Size
1.	StartDate	Date/Time
2.	EndDate	Date/Time

Parameter Table: Date_Param
StartDate	EndDate
01-01-1996	31-12-1996

Query Name: 00_Orders_MainQ

SELECT Orders.*
FROM Orders, Date_Param
WHERE (((Orders.OrderDate) Between [StartDate] And [EndDate]));

Query Name: 01_OrderCount1

SELECT Employees.EmployeeID,
  [FirstName] & " " & [LastName] AS EmpName,
   Count([00_Orders_MainQ].OrderID) AS TORDERS
FROM Employees INNER JOIN 00_Orders_MainQ ON Employees.EmployeeID = [00_Orders_MainQ].EmployeeID
GROUP BY Employees.EmployeeID, [FirstName] & " " & [LastName];

Query Name: 02_OrderCount2

SELECT Count([00_Orders_MainQ].OrderID) AS TOTALORDERS
FROM 00_Orders_MainQ;

Query Name: 03_Employee_Summary

SELECT [01_OrderCount1].*,
      [TORDERS]/[TOTALORDERS] AS PCNT
FROM 01_OrderCount1, 02_OrderCount2;

Query Name: 04_Order_ListQ

SELECT [00_Orders_MainQ].OrderID,
        UCase([FirstName] & " " & [Lastname]) AS EmpName,
       [00_Orders_MainQ].OrderDate,
        [00_Orders_MainQ].RequiredDate
FROM Employees INNER JOIN 00_Orders_MainQ ON Employees.EmployeeID = [00_Orders_MainQ].EmployeeID
WHERE ((([00_Orders_MainQ].EmployeeID)=[Forms]![Inquiry_Main]![EID]));

Query Name: 05_Order_DetailQ

SELECT [FirstName] & " " & [LastName] AS EmpName,
        [Order Details].OrderID,
        [Order Details].ProductID,
        [Order Details].Quantity,
        [Order Details].UnitPrice,
        [Order Details].Discount,
        (1-[Discount])*[UnitPrice]*[Quantity] AS ExtendedPrice
FROM (Employees INNER JOIN 00_Orders_MainQ ON Employees.EmployeeID = [00_Orders_MainQ].EmployeeID) INNER JOIN [Order Details] ON [00_Orders_MainQ].OrderID = [Order Details].OrderID
WHERE ((([Order Details].OrderID)=[Forms]![Inquiry_Main]![OID]));

Continued Next Week.

Download

Download Demo InquiryDemo00.zip

Command Button Animation-2

a.p.r. pillaiJanuary 10, 2009Animations, Command Buttons, msaccess animation No comments

Introduction.

A screen with animated text or controls adds a lively, dynamic touch to an application, making it more engaging for both the user and the observer. The Command Button Animation article was the very first post I published on this website.

Another enhancement to screen design was the introduction of 3D headings for forms and reports, using a combination of different fonts, sizes, and styles. Initially, these headings were created manually, which eventually led to the development of the 3D Text Wizards.

You can find the details of 3D Text Styles in the following posts.

The IBM AS/400 (iSeries) screens inspired me to design MS Access interfaces with a dark background, light-shaded data labels, and green text. Although those old text-based screens may seem dated, their clear and highly legible presentation of information remains their greatest strength.

However, when I began designing Access forms in this style, I faced a challenge: the Command Button Animation I had been using wasn’t built for dark backgrounds. This led me to develop a new animation technique—one that complemented the darker design while remaining simple to create and easy to implement without complicated VBA code.

So, here it is for you. I hope you’ll enjoy it as much as I do. We’ll first explore a simple, easy-to-understand version of this method and then move on to a more versatile, reusable routine that can drive the animation on any form with just one or two lines of code.

Command Button Design.

Open a new Form or an existing one.
Select the Footer of the Form. If it is not visible, select Form Header/Footer from the View Menu.
Display the Property Sheet (View -> Properties).
Change the Back Color Property Value to 0.
Select the Command Button Tool from the Toolbox and draw a Command Button in the Footer Section of the Form.
Display the Property Sheet of the Command Button.
Change the Name Property Value to cmdExit and the Caption Property Value to Exit.
Select the Rectangle Tool from the Toolbox and draw a rectangle around the Command Button as shown in the sample design below:
Right-click to open Large Image in New Tab/Window.
Give a little more gap, between the button and the rectangle at the bottom and the right side than above and left, giving it a feel that the Command Button is in a raised state.
Click on the Rectangle and display its Property Sheet.
Change the Name Property Value to ExitBox and the Visible Property Value to No.

Animating the Command Button
Now, it is time to implement the animation trick. This time, we will not animate the button like we did earlier on the Command Button Animation; instead, the Box around it will be made visible or hidden based on the Mouse movement over the Command Button.
We will track the mouse movement in Code. When the mouse is over the Command Button, the rectangle becomes visible, and when the mouse moves out, it is hidden. When this action is repeated, it will appear as if the command button rises and then returns to its original position. It has a better look and feel in a dark background, rather than remaining flat all the time.
We need to track the Mouse movement at two positions: the On Mouse Move Event of the Command Button and the On Mouse Move Event in the Form Section.
Select the Command Button.
Display the Property Sheet of the Command Button (View -> Properties).
Select [Event Procedure] in the On Mouse Move property and click on the build button (...).

Copy and paste the code given below between the sub-routine skeleton. You can ignore the first and last lines while copying, as these will be present in the Module.

Private Sub cmdExit_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
     If Me.ExitBox.Visible = False Then
           Me.ExitBox.Visible = True
     End If
End Sub

Click anywhere within the Form Footer to select that area, display the Property Sheet, and repeat Step 14 above.
Copy and paste the following code into the empty skeleton of the sub-routine, as you did above:
```
Private Sub FormFooter_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
   If Me.ExitBox.Visible = True Then
      Me.ExitBox.Visible = False
   End If
End Sub
```
Actually, the IF... The statement is not required in the routine. This is introduced to avoid changing the value repeatedly during mouse movements and to avoid flickering.
Trial Run of Animation
Save the Form and open it in Normal view.
Move the Mouse over and out of the Command Button repeatedly, which will give the button a sense of up and down movement every time.
The AnimateFrame() Function
When we implement this animation for several Command Buttons, duplicating the above code everywhere is not good programming. A common routine that can be called with a one-line code, so that it is easy to implement anywhere and for any number of buttons.
Copy and paste the code given below into a Global Module of your database and save it.

Public Function AnimateFrame(ByVal OnOff As Boolean, ByVal x_Box As String)
Dim frm As Form, ctrl As Control
On Error GoTo AnimateFrame_Err

Set frm = Application.Screen.ActiveForm
Set ctrl = frm.Controls(x_Box)

Select Case OnOff
    Case False
        If ctrl.Visible = False Then Exit Function
             frm.Controls(x_Box).Visible = False
    Case True
        If ctrl.Visible = True Then Exit Function 
        frm.Controls(x_Box).Visible = True
End Select

AnimateFrame_Exit:
Exit Function

AnimateFrame_Err:
Resume AnimateFrame_Exit
End Function

Now, we can replace the code we have written earlier with a single-line Code to display and hide the rectangle.

If Me.ExitBox.Visible = False Then

    Me.ExitBox.Visible = True

End If

The above code can be replaced with the statement

AnimateFrame True, "ExitBox"

in the On Mouse Move of the Command Button and

If Me.ExitBox.Visible = True Then
    Me.ExitBox.Visible = False

End If

can be replaced with the statement

AnimateFrame False, "ExitBox"

in the FormFooter_MouseMove event procedure.

Cardinal Text Format In Access

a.p.r. pillaiJanuary 02, 2009Functions, msaccess functions No comments

Introduction

The other day, a colleague asked me to open Microsoft Word, type the expression =Rand() on a blank line, and press Enter. What happened next felt like magic — something I hadn’t known until that moment. Instantly, Word replaced the expression with a block of sample text, repeating the following sentence fifteen times (arranged in three rows and five columns):

The quick brown fox jumps over the lazy dog.

Open a Word document and try it yourself: type =Rand() on its own line and press Enter. Word will replace the expression with a block of sample text — the sentence below repeated fifteen times (arranged in three rows and five columns). The sentence contains every letter of the alphabet. You can control the output by passing parameters to the function; for example, it =Rand(5,1) prints the sentence five times in one column. It’s a built-in Word feature that looks like a randomizer but isn’t — it’s just a quick way to insert sample text, and it only works when the expression is on a separate line.

There’s another Word feature I wish Access had natively: formatting numeric values as cardinal text. In Word mail merge fields, you can use switches such as \* CardText or \* DollarText — for example, the mail-merge expression { = 9.20 + 5.35 \* CardText } produces fourteen and 55/100 when merged. Adding the \* Caps switch (e.g., { = 9.20 + 5.35 \* DollarText \* Caps }) capitalizes the first letter of each word, which is handy for printing amounts on invoices.

That’s exactly the kind of output I wanted for Access invoices, so I wrote a VBA function to convert numeric values to cardinal text.

The CardText() Function

Copy and paste the following Code into a Global Module of your Database and save it:

Public Function CardText(ByVal inNumber As Double, Optional ByVal precision As Integer = 2) As String
'------------------------------------------------------------------------
'Author : a.p.r. pillai
'Date   : December 2008
'URL    : www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
'------------------------------------------------------------------------
Dim ctu, ctt, bmth
Dim strNum As String, j As Integer, k As Integer, fmt As String
Dim h As Integer, xten As Integer, yten As Integer
Dim cardseg(1 To 4) As String, txt As String, d As String, txt2 As String
Dim locn As Integer, xfract As String, xhundred As String
Dim xctu As String, xctt As String, xbmth As String

On Error GoTo CardText_Err

strNum = Trim(Str(inNumber))
locn = InStr(1, strNum, ".")
'Check Decimal Places and rounding
If locn > 0 Then
  xfract = Mid(strNum, locn + 1)
 strNum = Left(strNum, locn - 1)
    If precision > 0 Then
        If Len(xfract) < precision Then 
        	xfract = xfract & String((precision - Len(xfract)), chr(48))
        ElseIf Len(xfract) > precision Then
            xfract = Format(Int(Val(Left(xfract, precision + 1)) / 10 + 0.5), String(precision, "0"))
        End If
        xfract = IIf(Val(xfract) > 0, xfract & "/" & 10 ^ precision, "")
    Else
        strNum = Val(strNum) + Int(Val("." & xfract) + 0.5)
        xfract = ""
    End If
End If

h = Len(strNum)
If h > 12 Then
'if more than 12 digits take only 12 (max. 999 Billion)
'extra value will get truncated from left.
   strNum = Right(strNum, 12)
Else
   strNum = String(12 - h, "0") & strNum
End If

GoSub initSection

txt2 = ""
For j = 1 To 4
    If Val(cardseg(j)) = 0 Then
       GoTo NextStep
    End If
    txt = ""
    For k = 3 To 1 Step -1
      Select Case k
       Case 3
            xten = Val(Mid(cardseg(j), k - 1, 1))
            If xten = 1 Then
                txt = ctu(10 + Val(Mid(cardseg(j), k, 1)))
            Else
                txt = ctt(xten) & ctu(Val(Mid(cardseg(j), k, 1)))
            End If
        Case 1
            yten = Val(Mid(cardseg(j), k, 1))
            xhundred = ctu(yten) & IIf(yten > 0, bmth(1), "") & txt
            Select Case j
                Case 2
                      d = bmth(2)
                Case 3
                    d = bmth(3)
                Case 4
                    d = bmth(4)
            End Select
            txt2 = xhundred & d & txt2
    End Select
   Next
NextStep:
Next

If Len(txt2) = 0 And Len(xfract) > 0 Then
    txt2 = xfract & " only. "
ElseIf Len(txt2) = 0 And Len(xfract) = 0 Then
    txt2 = ""
Else
  txt2 = txt2 & IIf(Len(xfract) > 0, " and " & xfract, "") & " only."
End If

CardText = txt2

CardText_Exit:
Exit Function

initSection:
xctu = ", One, Two, Three, Four, Five, Six, Seven, Eight, Nine, Ten, Eleven, Twelve,"
xctu = xctu & " Thirteen, Fourteen, Fifteen, Sixteen, Seventeen, Eighteen, Nineteen"
ctu = Split(xctu, ",")

xctt = ", Ten, Twenty, Thirty, Fourty, Fifty, Sixty, Seventy, Eighty, Ninety"
ctt = Split(xctt, ",")

xbmth = ", Hundred, Thousand, Million, Billion"
bmth = Split(xbmth, ",")
k = 4
For j = 1 To 10 Step 3
    cardseg(k) = Mid(strNum, j, 3)
    k = k - 1
Next
Return

CardText_Err:
CardText = ""
MsgBox Err.Description, , "CardText()"
Resume CardText_Exit
End Function

The CardText() or the DollarText.

Sample Demo Runs

The Function name CardText() is derived from MS Word Number Format Switch \* CardText. The CardText() Function accepts a maximum value of 10^12-1 or up to 999 Billion. For most applications, this will be sufficient. Passing a Value greater than this will get truncated from the left.

The CardText() Function accepts two parameters, and the second one is optional. The second parameter controls the number of digits after the decimal point.

By default, the CardText() Function will round off the fractional part, if present, to two decimal places when the second parameter value is omitted.

To try out the Code, you may open the VBA Window (Alt+F11) and open the Immediate Window (Ctrl+G) and type the following statement or a similar one with a different value or Expression:

Example:? CardText(1234.5678,3) will produce the result shown below.

Result: One Thousand Two Hundred Thirty-Four and 568/1000 only.

The first parameter can be a Number or an Expression that evaluates to a Numeric Value. If the second parameter is zero, then the Number is rounded to the next highest Integer.

Example:? CardText(1234.5678,0)

Result: Thousand Two Hundred Thirty-Five only.

To change the output to upper-case or lower-case letters, enclose the CardText() Function in UCase() or LCase() built-in function, respectively.

Example:? UCase(CardText(1234.5678))

Result: ONE THOUSAND TWO HUNDRED THIRTY-FOUR AND 57/100 ONLY.

To prefix a Currency Description, use the following example:

Example:? "Dollars" & CardText(1234.5678)

="Dollars" & CardText([UnitPrice]) on Forms or Reports.

Result: Dollars One Thousand Two Hundred Thirty-Four and 57/100 only.

Try the Function on Form or Report with the data field Value as input.

The CardText() Function is not extensively field-tested, and if you find bugs, please let me know. Use it at your own risk.

Any suggestions for improvement are welcome.

CardTextV2.zip

LEARN MS-ACCESS TIPS AND TRICKS

Combo-Box Column Values

Introduction

The Bound Column Property

Earlier Post Link References:

Drill-Down Inquiry Screen-2

Designing the Employee-Wise Order Summary Form

03_Employee_Summary Form

Designing the Employee Summary Form

04_Order_ListQ Form

05_Order_DetailQ Form

Inquiry_Main Form

Drill-Down Inquiry Screen

Introduction

Designing the Forms

The Design Task

A Parameter Table.

Download

Command Button Animation-2

Introduction.

Command Button Design.

Animating the Command Button

Trial Run of Animation

The AnimateFrame() Function

Cardinal Text Format In Access

Introduction

Sample Demo Runs

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