Combo-Box Column Values

Introduction

The Text Boxes and Command Buttons are the most commonly used controls on a Form. We have seen them in action in different ways and with different designs. For those who have not come across those Articles before then links to them are given below; you may take a look at them.

1. Command Button Animation
2. Command Button Animation-2
3. Double-Action Command Button
4. Colorful Command Buttons
5. Transparent Command Button

Next in line is the most preferred and familiar control on Forms; the Combo-Box Control. This can be created not only on Forms but also on Tables as well.

If you would like to see a few examples for the usage of Combo-Boxes in Tables then you have them on your PC itself. Open the Northwind.mdb, sample database from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb (MS-Office2003 pathname) and open Employees Table or Orders Table or Order Details Table in Design View and look at the Fields and Properties given under each Table listed below:

1. Employees Table
   • Field: TitleofCourtesy

   Note: Select the Lookup Tab from the Field Properties below. Inspect the Row Source Type and Row Source Property Values. Check the other related Property settings as well.

   The Row Source Property Values are keyed-in as Constants separated by semi-colons 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.

   While using the constant values (Dr.; Mr.; Miss; Mrs.; Ms.) as Source items for the Combo-Box it is assumed that values other than these are not likely to enter into 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 Combo-Box (or in the EmployeelD field) will not be accepted in this field. In other words, you cannot enter a value manually into this field, other than what is appearing in the Combo-Box.

   With this setting, an automatic validation check is performed by the Combo-Box to prevent invalid values from creeping into the target data field. If the new value is required, then that must be entered into the source Table/Field of the Combo-box first before that can be inserted into the target field.

   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.

2. Orders Table
   
   • CustornerlD
   • EmployeelD
   • Ship via

   Open the Orders Qry in normal view and click on one of the rows of Customer Column to see the Combo-Box in action on Query.

3. Order Details Table
   • ProductID

The Bound Column Property

Normally, Combo-Box will have one or more columns of information like EmployeelD and Employee Name. When clicked the first column value is inserted (this depends on the Bound Column Property Value setting) into the target field and the description appearing in the second Column is shown for information purposes only.

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.

In our above example, the EmployeelD is the value inserted into the ReportsTo field when clicked. The EmployeelD is a number, but the descriptive name is important to the User because it is more meaningful and easy to remember. So in the Combo-Box, the EmployeelD number is kept hidden by setting the first Column Width Value as 0", thereby showing only the name of the Employee in the Combo-Box.

We will go back to the first sentence of the above Paragraph and proceed further on that point. There are times that we need information from other Columns also to insert into other target fields with one click.

Assume that our Employee Combo-Box has one more column for Designation (besides EmployeelD and Name) and when clicked this information also must be inserted into another control on the Form. You can do this with a one-line VBA Code in the On Click Event Procedure in the Form Module like:

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:

• Sum() Min() Max() ParamArray
• Text Box and Label Inner Margins
• Multiple Parameters for Query
• Form Menu Bars and Toolbars
• Seriality Control - Missing Numbers

Read More

Drill-Down Inquiry Screen-2

Continuation of Last Week's Post.

This is the second part of the earlier Article: Drill-Down Inquiry Screen. If you have landed straight on this page, then please visit the above link and go through the first part of this article before continuing..

I hope you have downloaded the sample database from the first part of this article published last week and tried it out. Then you have a general idea of what we are going to explore further on this topic.

Here, we will design three small Forms, assemble them on the Main Form on a Tab Control, and use a few lines of code to refresh the Main Form to update the contents of the Sub-forms before bringing them into view.

First, we will design the top layer of the Form for the Employee-wise Order Summary Information.

03_Employee_Summary Form

1. Select the Query 03_Employee_Summary.

2. Design a Tabular Form (continuous form) like the sample given below. You may use the Form Wizard from Insert Menu to create the Form quickly, but it may insert background images and the controls may be created with shadows and other settings. In that case, you have to modify the design to look like the sample given below.

   
   

   Click to Enlarge

3. Select all the Data Fields together by clicking on the left side border of the Detail Section or click on the Detail Section somewhere and drag the mouse over all the controls to select them together.

4. Display the Property Sheet (View - ->Properties).

5. Change the following Property Values:

   • Enabled = No
   • Locked = Yes
   • Tab Stop = No
   • Back Color = 12632256
   • Special Effect = Raised

6. Expand the Detail Section of the Form down to get enough room to draw a lengthy Command Button.

7. 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.

8. Display the Property Sheet of the Command Button.

9. Change the following Property Values:

   • Name = cmd1
   • Transparent = Yes
   • Hyperlink SubAddress = #

10. We must make the Command Button's height the same as of 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.

11. 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.

12. Drag the Transparent Command Button and place it over the Fields above.

13. To make sure that the Transparent Command Button stays above all the data fields, select Format - - > Bring to Front.

14. Now, reduce the Detail Section height, but there must be a little gap above and below the Data Fields.

15. Draw a Text Box in the Form Footer Section below in the same position of the TORDERS field in the Detail Section and write the following expression in the Control Source Property:

    =Sum([TORDERS]) 

16. Change the Caption of the child label to Total Orders.

17. 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.

18. 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

19. Save the Form with the Name: 03_Employee_Summary.

04_Order_ListQ Form

1. Select the Query 04_Order_ListQ and create a Tabular Form (continuous Form) as we did at the top.

2. Change the design to look like the sample Image given below:



Click to Enlarge

3. 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

4. Follow Step-6 to 8 given above.

5. Change the following Properties of the Command Button:

   • Name = cmdOrder
   • Transparent = Yes
   • Hyperlink SubAddress = #

6. Follow Step-10 to 13 explained above. Reduce the height of the Detail Section without leaving the gap above and below the fields.

7. Create a Command Button at the Footer Section of the Form.

8. Display the Property Sheet of the Command Button and change the following Property Values:

   • Name = cmdMain
   • Caption = Goto Main

9. Expand the Header Section of the Form and drag the Field Headings down to get enough room to create a Heading to the Form.

10. 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]

11. 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

1. Select the Query 05_Order_DetailQ and Create a Tabular Form.

   Here, we don't need the Transparent Command Button. Change the design of the form to make it like the sample image given below.

   

2. Create a Text Box in the Form Footer below the Quantity field and write the following expression in the Control Source Property:

   =Sum([Quantity])

3. Create another Text Box in the Form Footer below the ExtendedPrice Column and write the following expression in the Control Source Property:

   =Sum([ExtendedPrice])

4. Create a Command Button in the Form Footer below the TextBoxes.

5. Change the following Property Values of the Command Button.

   • Name = cmdBack
   • Caption = << Back

6. 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

7. Save the Form with the Name 05_Order_DetailsQ.

Now, we are ready for the Main Form Inquiry_Main to assemble all the three Sub-Forms on a Table Control and make the Tab Control invisible.

Inquiry_Main Form

1. Select the Parameter Table Date_Param, select Form from Insert Menu, and select Design View from the displayed list.

2. Select the Tab Control Tool from the Toolbox and draw a Tab Control on the Detail Section of the Form.

3. Check the sample image given below. The Tab Control will have only two pages when created, but we need one more page.



Click to Enlarge

4. Click on the Tab Control to select it and select Tab Control Page from Insert Menu.

   You may select each Page of the Tab Control, display their 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 and will not use those names anywhere or display them either.

   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.

5. Position the Database Window with the Forms Tab active and the Inquiry_Main Form with the Tab Control side by side.

6. Drag and drop the 03_Employee_Summary Form on the Tab Control Page1.

7. You may delete the child label attached to the Sub-Form. Re-size the Sub-Form and Tab Control to display the contents properly on the Screen. You may save the Form with the name Inquiry_Main and open it in Normal View to check as to how the Information is displayed on the Sub-Form. Check whether the Form and Tab control size need size increase or decrease is necessary or not. Leave some gap between the Tab control and the top of the Detail Section of the Form to insert a few TextBoxes for StartDate and EndDate fields and two more Text Boxes for control purposes. We need space for a heading above these controls as well.

8. When you are satisfied with the Sub-Form dimension of the design, click on the Sub-Form, and display the Property Sheet. Take a piece of paper and note down the following Property Values for resizing and positioning the other two forms we are going to insert into Page2 and Page3 of the Tab Control.

   • Top
   • Left
   • Width
   • Height

9. Right-click on the Sub-Form and select Copy from the displayed Shortcut Menu.

10. Select Tab Control Page2, press and hold 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 white background.

11. 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 Page2.

12. Change the dimension property values to the same value you have noted down. Since you have copied the Form (no drag and drop from Database Window); you need to change only the Top and Left Property Values, Width, and Height values will be the same. If not, then change it.

13. Follow Step-9 to 12 above to bring in the 05_Order_DetailQ Form to the third Page of the Tab Control.

14. Display the Field List (View - ->Field List), if it is not visible.

15. Drag and drop StartDate and EndDate fields above the Tab Control, create labels above and left, and change their Caption values as shown on the design above.

16. 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 have created and another two TextBoxes, which we are going to create, will be referenced in the Queries we have created earlier to filter the data for the Forms to display. Even though this refresh action is not necessary, as we do that before we display the results, it is an additional feature to refresh the changes manually by the User.

17. 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

18. 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

19. Create a heading on top of the Form with the Caption Value SALES PERFORMANCE INQUIRY.

20. Create a Command Button below the Tab Control and change the following property values:

    • Name = cmdQuit
    • Caption = Quit

    Now, we are going to make the Tab Control disappear, well the Tab Control will not look like a Tab Control after the change, to be exact.

21. Click on the Tab Control, display the Property sheet, and change the following property values:

    Caution: Immediately after you change the Property values make a point to click the Save Toolbar Button or select Save from File Menu to save the changes. There is a tendency to lock up the Form in Office2000 if you attempt to do anything else.

    • Back Style = Transparent
    • Multirow = No
    • Style = None
22. 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

23. 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
24. Save the Form, open it in Normal View, and try out your creation.

    Note: If you face any difficulty in running your design normally, use the downloaded database as a reference, find the mistake and correct it.

• Text Box and Label Inner Margins
• Multiple Parameters for Query
• Form Menu Bars and Toolbars
• Seriality Control - Missing Numbers
• Wave-Shaped Reminder Ticker

Read More

Drill-Down Inquiry Screen

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:

The top layer of the Form shows the Salesperson level summary of Orders and the Percentage of Total Orders. The Footer of the Form shows the Total of all Orders put together. When clicked on a particular Employee's record the individual Order-level Summary information will be visible on the main screen overlaying the earlier view. Check the image given below.

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.

The above Form shows order-level details and summary information at the Footer of the form with the Total Quantity of all Orders and the Total Net Value of all items after discount. The Command Buttons at the footer of each form are used to switch the view back to the upper layer of the form.

The Date Parameter values at the top can be changed to display information for a different period.

First of all, I would like to assure you that there is no complicated VBA Code to drive this design except one or two lines of them here and there to refresh the controls and for switching from one layer of the form to the other. Other than that, the entire Screen works on Queries and Forms.

Designing the Forms

We need a few Tables from the Northwind.mdb sample database, to create six simple Queries, three Sub-Forms, and the Main Form to organize all of them together to present the information to the User.

Note: If you would like to see the Inquiry Screen in action before you design it, you can download it from the bottom of this post and perform a demo run. If you find it difficult to figure out how it works or how to put all of them together, then come back and go through the design task step by step so that you will know how simple or difficult it is and how each element of this trick works together with others.

Here, we will see the usage of Transparent Command Buttons again, which we have already seen in an earlier article with the same title.

We will have to split this topic into more than one Blogpost so that we can do the design task with ease. Even though I can show you how to do it in a few minutes, explaining that on paper with images and Property settings takes a lot more than that.

I know that, if you download the database and take a look at it first, you will be better prepared to find out more about it and ready to go through the design task with more interest.

The Design Task

So, let us start with the first part.

1. 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.

2. 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

3. 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

4. Open new Queries and Copy the following SQL Strings into Query's SQL Editing Window and save them with the exact Name given for each of them. Create the Queries in the same order as given here as they have dependencies on Queries created first.
5. Query Name: 00_Orders_MainQ

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

6. 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];
   

7. Query Name: 02_OrderCount2
   

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

8. Query Name: 03_Employee_Summary

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

9. 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]));
   

10. 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

1. Multiple Parameters for Query
2. Form Menu Bars and Toolbars
3. Seriality Control - Missing Numbers
4. Wave Shaped Reminder Ticker
5. No Data and Report Error

Read More

Command Button Animation-2

Introduction.

A Screen with Animated Text or Controls gives the application a lively look and feel than a rigid screen to the user and to the onlooker. Command Button Animation was the first Blogpost that I have published on this Website.

Another Screen design improvement was creating 3D headings on Form and Report with different Fonts, Sizes, and Styles. Initially, create them manually and this leads to the creation of a 3D-Text Wizard.

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

1. Create 3D Headings on Forms
2. Border 2D Heading Text
3. Border 3D Heading
4. Shadow 3D Heading Style

IBM AS400 (iSeries) Screens influenced me to go along with designing MS-Access Screens with a dark background, data labels with light shades, and Text in Green. Even though these are old text-based screens better visibility of information is the main attraction of these screens.

But, when I started designing them I faced a little problem with the Command Button Animation that was in use till that time because it was not designed for dark backgrounds. I had to invent a different animation method around the original command button. It is simple to design besides easy to implement without the use of too complex VBA Code.

So, here it is for you and I hope you like it too. First, we will implement it with a simple and easy-to-understand method. After that, we will write a common routine that can drive the animation on any form with one or two lines of code.

Command Button Design.

1. Open a new Form or an existing one.

2. Select the Footer of the Form. If it is not visible select Form Header/Footer from View Menu.

3. Display the Property Sheet (View - ->Properties).

4. Change the Back Color Property Value to 0.

5. Select the Command Button Tool from Toolbox and draw a Command Button in the Footer Section of the Form.

6. Display the Property Sheet of the Command Button.

7. Change the Name Property Value to cmdExit and Caption property value to Exit.

8. Select the Rectangle Tool from Toolbox and draw a rectangle around the Command Button as shown in the sample design below:

   

9. 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.

10. Click on the Rectangle and display its Property Sheet.

11. 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 is made visible and when the mouse moves out it is hidden. When this action is repeated it will look like the command button become raised and goes flat again. It has a better look and feels in a dark background rather than remains flat all the time.

    We need to place Code at two places to trap the mouse movements, on the On Mouse Move Event of the Command Button and on the On Mouse Move Event in the Form Footer.

12. Select the Command Button.

13. Display the Property Sheet of the Command Button (View - ->Properties).

14. Select [Event Procedure] in the On Mouse Move property and click on the build button (. . .).

15. 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
    

16. Click anywhere within the Form Footer, to select that area, display the Property Sheet, and repeat Step-14 above.

17. 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. . .Then 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

18. Save the Form and open it in Normal view.

19. Move the Mouse over and out of the Command Button repeatedly which will give the button a sense of going flat and raised state every time.

    The AnimateFrame() Function

    When we implement this animation at several places duplicating the above code everywhere is not good programming. A common routine is given below that can be called with a one-line code so that it is easy to implement anywhere and for any number of buttons.

20. 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 each 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 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.

1. Command Button Animation
2. Double Action Command Button
3. Colorful Command Buttons
4. Transparent Command Button
5. Command Button Animation-2
6. Creating Animated Command Button with VBA
7. Command Button Color Change on Mouse Move

Read More

Cardinal Text Format In Access

Introduction

The other day one of my colleagues asked me to open MS Word and type the expression =Rand() on a separate line and press the Enter key. It was magic that I didn't know till that time. The following sentence appears fifteen times repeatedly (in 3 rows and 5 columns) overwriting the expression itself:

The quick brown fox jumps over the lazy dog.

Open a Document in MS Word and try it out yourself. The above sentence has all the letters of the alphabet in it. You can control the printing, by inputting parameters to the Function like =Rand(5,1) will print the same sentence in 5 lines in one Column. It is a built-in Function with different constructs that can accept a different set of parameters and looks like created for fun, I think! It works only when you type it on a separate line. Even though it looks like a Random Function it has nothing to do with it.

There is another feature in MS-Word that, I like to see in MS-Access, formats numeric values in Cardinal Text Format.

For example, the result of the Mail Merge formula { = 9.20 + 5.35 \* CardText } outputs fourteen and 55/100, when the document is merged into another Document or to Printer. Format switches can be either \* DollarText or \* CardText to get the above output. When the \* Caps switch is added to it, like { = 9.20 + 5.35 \* DollarText \* Caps} , then it changes the first letter of each word in upper case.

This is very useful for printing Invoice values in MS-Access. I have written a Function to achieve this in MS-Access and here it is for you to try it out.

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 decimal places.

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 VBA Window (Alt+F11) and open the Immediate Window (Ctrl+G) and type the following statement or 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-cases 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)

Or

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

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

You may 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

• Seriality Control - Missing Numbers
• Wave Shaped Reminder Ticker
• No Data and Report Error
• Lost Links of External Tables
• Link External Tables with VBA

Read More