LEARN MS-ACCESS TIPS AND TRICKS

Create Menus with Macros

a.p.r. pillaiNovember 29, 2010Forms, Macro, Menus, Menus and Toolbars 1 comment

Create Menus with Macros - MS Access 2003.

We learned earlier how to create Menus and toolbars through the Toolbars -> Customize options. If you would like to have a second look at them, then the links are given below:

Calendar and Toolbars - Access 2003
Custom Menus and Toolbars - Access 2003
Custom Menus and Toolbars-2 - Access 2003
Inputbox and Simple Menus

There are other methods for creating simple custom menus for Forms/Reports in your Applications. This time, we create a Shortcut Menu with Macros and attach it to a Form.

Sample Demo Run.

Select the Macro Tab on the Objects pane.
Click New to open a new Macro in Design View.
If the Macro Name Column is not visible, click the Macro-Name Toolbar Button (the xyz label) that displays the name column.
Type 'Clo&se' under the Macro Name column in the first row.
Select Close from the drop-down list under Actions.
Type &Preview in the second row under Macro Name.
Under Action, select the OpenReport option from the drop-down list.
In the Property Sheet under Action Arguments, select a Report from your list of Reports (I selected the Employees Report in the sample image given above) in the Report Name Property.
Select Print Preview in the View Property.
Type &Form in the third row under the Macro Name column.
Select Open Form in the Action Column.
Select an existing Form's name from the Form Name Property drop-down list.
Select the Form in the View property.
Type a dash (-) character in the next row under the Macro Name column.
Type &GotoNext in the fifth row under the Macro Name column.
Select GotoRecord in the Action Column.
Type &Minimize in the sixth row under Macro Name.
Select Minimize from the drop-down list in the Action Column.
Save the Macro named mcrShortCut and close it.

You can add as many commands or actions, like opening Queries or running Programs, to your menu for the convenience of your Application Users.

Defining Macro as Menu.

Our work on the Macro Menu is complete, but a few more actions are left to define the Macro as a Menu.

Open a new macro in the design view.
Select AddMenu from the drop-down list in the Action Column.
Leave the Menu Name Property Blank. This is used for Group Menu Names when you have group Menus with different sets of actions. In this case, the Macro will have more than one AddMenu Action line with a different set of Menu Macros.
Select mcrShortCut in the Menu Macro Name Property.
Save the macro named mcrShortCutMenu.
Open one of your Forms in Design View (don't open the form that you have used in the OpenForm action in the first macro).
Display the Form's Property Sheet (View -> Properties).
Set the following Property Values of the Form as given below:
- Shortcut Menu = Yes
- Shortcut Menu Bar = mcrShortCutMenu
Save the Form.
Open the Form in a normal view.
Right-click on the Form to display the Shortcut Menu. See the sample image given below:

Try out the options displayed in the Shortcut Menu.

We can create Shortcut Menus, Toolbars, and Menu Bars too. This we will learn Next.

Join Split Array Functions-2

a.p.r. pillaiNovember 22, 2010Array, Functions, How Tos, Join, Split No comments

Join Split Array Functions-2.

This is the continuation of last week's Article: Join Split Array Functions

If you haven’t reviewed the fundamentals of the functions mentioned above, I recommend doing so by following the link provided earlier before continuing.

Since the Join() and Split() functions are closely related, this is a good time to explore a real-world example that demonstrates their use. While the approach we are about to take may not be the simplest solution to the problem at hand, it will clearly illustrate how these functions work and help deepen your understanding of their practical applications.
Import Employees Table from the Northwind sample database: C:\Program Files\Microsoft Office\Office11\samples\Northwind.mdb
Open a new Query in SQL View (don't select any Table from the displayed list).
Copy and paste the following SQL String into the SQL editing window and save the Query with the name EmployeeSelectQ
.
```
SELECT Employees.*
FROM Employees;
```
Design a new Form similar to the sample image shown below. You can use the Form Wizard to create it quickly, and set EmployeeSelectQ as the record source for the Form.

Right-click to open the large image in a new tab or window.
Add a Text Box and a CommandButton at the bottom of the design.
Select the Text Box and display the Property Sheet (View -> Properties or Design -> Property Sheet in 2007).
Change the Name Property Value to txtCodes.
Select the CommandButton, change the Name Property value to cmdFilter, and the Caption Property value to Apply Filter.
Select the Child label of the Text Box and change its Caption to Employee Codes:.
Display the Form's VBA Code Module (View -> Code or click the View Code toolbar button from the 2007 Design Menu).

Copy and paste the following VBA Code into the Form Module and save the Form with the name EmployeeSelect:

Private Sub cmdFilter_Click()
Dim txt_Codes As String, varList As Variant
Dim varCodes As Variant, j As Integer, x
Dim maxCodes As Variant, invalid_Codes As String

txt_Codes = Nz(Me.txtCodes, "")
maxCodes = DMax("EmployeeID", "Employees")

If Len(txt_Codes) = 0 Then
  varList = Null
Else
   'Split the items into the array.
  
  '(Here Array() Function will not work
  'becuase the values in txtCodes variable is a String
  'and will be treated as a single item).

  varCodes = Split(txt_Codes, ",")
  
  'Validation check
  invalid_Codes = ""
  For j = 0 To UBound(varCodes)
     x = Val(varCodes(j))
     If x < 1 Or x > maxCodes Then
        invalid_Codes = invalid_Codes & Format(x, "0 ")
     End If
  Next
  If Len(invalid_Codes) > 0 Then
     MsgBox "Invalid Employee Codes: " & invalid_Codes & vbCr & vbCr & "Correct and retry."
     Exit Sub
  End If
  'here "varList = txt_Codes" is also works
  'because txt_Codes values are separated with commas already
  varList = Join(varCodes, ",")
End If

  'Call the EmployeeFilter() function
  EmployeeFilter varList
  Me.RecordSource = "EmployeeSelectQ"
  Me.Requery

End Sub

Press Alt+F11 to open the VBA Editing window. If you have already closed it, select Module from the Insert Menu to add a new Standard Module.

Copy and paste the following EmployeeFilter() Function VBA Code into the Standard Module and save it:

Public Function EmployeeFilter(ByVal Criteria As Variant)
Dim strsql0 As String, sql As String
Dim db As Database, qryDef As QueryDef

strsql0 = "SELECT Employees.* FROM Employees WHERE (((Employees.EmployeeID) In ("

Set db = CurrentDb
Set qryDef = db.QueryDefs("EmployeeSelectQ")

If IsNull(Criteria) Or Len(Criteria) = 0 Then
    sql = "SELECT Employees.* FROM Employees;"
Else
    Criteria = Criteria & ")));"
    sql = strsql0 & Criteria

End If
    qryDef.SQL = sql
    db.QueryDefs.Refresh

End Function

Open the EmployeeSelect Form in a normal view. The sample image of the Form is given below:

Right-click to open Large Image in New Tab or Window.
Enter Employee IDs 2, 3, 7, and click the Apply Filter Command Button.

Now, the Employee Select Form has only three records, with employee codes 2, 3 & 7.
Delete all the Text Box contents and click the Apply Filter Command Button.

The filter action is reversed, and the Employee Table records are now available on the Form. In other words, it works like a Reset command when the TextBox is empty.

You may try it out with a different Employee ID.

The values entered into the TextBox must be within the range of the available Employee Code. The numbers entered outside this range will display an Error message and abort the program.

Join Split Array Functions

a.p.r. pillaiNovember 12, 2010Array, Functions, Join, Split 1 comment

Join Split Array Functions.

The Join() and Split() functions in MS Access are not widely used, but they can be both interesting and surprisingly powerful. To understand their potential, let’s examine them. Later, we will write a program to demonstrate how these functions can be applied in real-world scenarios.

We will start with the Array() function. But before exploring it in detail, let’s first review how to define an array variable and assign a value to each array element.

Demo of Array() Function

'Dimension the array for six elements
Dim varNumber(0 To 5) As Variant, j As Integer
For j = 0 To 5
    varNumber(j) = j + 1
Next

The first statement in the program defines a variable named varNumber as a Variant Type Array for 6 elements.
Another variable j is defined as an integer type that will be used as an index variable in the For. . .Next loop.
The next three statements in the above program assign values 1 to 6 to the Array elements as:

varNumber(0) = 1
varNumber(1) = 2
varNumber(2) = 3
varNumber(3) = 4
varNumber(4) = 5
varNumber(5) = 6

We can do this task with only one statement if we use the Array() Function as below:

varNumber = Array(1, 2, 3, 4, 5, 6)

Unlike the first example, we don’t need to define the variable with a fixed number of elements—the array is automatically sized based on the number of items in the argument list. In this case, we assigned constant values from 1 to 6 to the array elements indexed 0 to 5.

Another important point to note is that the target variable (varNumber) must always be declared as a Variant type. This provides greater flexibility, allowing you to assign mixed data types to different elements of the array, as shown in the example below:

varNumber = Array("Nancy", 25, "Andrew", 30, 172.5)

We have assigned a mix of String, Integer, and Double Data Types to different elements of the same array. Again, we have used constant values to assign to the array.

The Array Function can accept several parameters as a single block, without defining several parameter declarations in the main program.

You can use Constants, Variables, or data Field Values to assign values to the array.

Example-1:

a = "Nancy"
 b = 25
 c = 172.5
 varNumber = Arrary(a,b,c)

Example-2:

varNumber = Array(Me![FirstName],Me![BirthDate],Me![Address])

Next, we will explore the Join() function. To better understand how it works, let’s create another array of values and use it as input for this function.

varNumber = Array("Sun","Mon","Tue","Wed","Thu","Fri","Sat")

We have the names of weekdays in the array variable varNumber.

varNumber(0) = "Sun"
 .
 . 
 . 
 varNumber(6) = "Sat"

If you want to combine all seven elements of this array variable into a single string, with each item separated by commas (for example: Sun, Mon, Tue, Wed, Thu, Fri, Sat), you can do so with the following statements:

 Dim strWeeks as string, j as integer
 strWeeks=""
For j = 0 to 6
   if j=6 then
     strWeeks = strWeeks & varNumber(j)
   Else
     strWeeks = strWeeks & varNumber(j) & ","
   end if
Next

Join() Function:

The above task takes only one statement with the Join() Function:

strWeeks = Join(varNumber,",")

The first parameter of the Join() function is the array containing the values to be combined into a single string. The second parameter specifies the separator character inserted between the array elements. If this parameter is omitted, a space is used as the default separator. Otherwise, the character or string you specify will be used as the separator.

Result: strWeeks = "Sun,Mon,Tue,Wed,Thu,Fri,Sat"

Split() Function:

Split() is the complementary Function of Join(). It splits the individual items, separated by the delimiter character, and stores the values into an array variable of Variant Type.

We need the following lines of code to do the same task as the Split() Function:

Dim strWeeks(0 To 6) As Variant, strtxt As String
Dim j As Integer, k As Integer

strtxt = "Sun,Mon,Tue,Wed,Thu,Fri,Sat"
k = 0
For j = 1 To Len(strtxt) Step 4
   strWeeks(k) = Mid(strtxt, j, 3)
   k = k + 1
Next

With the use of the Split() Function, it takes only one statement to do the job that we did with the above program:

strWeeks = Split(strTxt,",")

Next week, we will use these functions in a Program to redefine a Query linked to a Form to filter and view data.

Earlier Post Link References:

Memo field and data filtering

a.p.r. pillaiOctober 31, 2010Data Filtering, Queries No comments

Memo field and data filtering.

When designing tables, we carefully organize information to make it easy to retrieve through searches, filters, and queries. For example, in the Employees table of the Northwind.mdb sample database, an employee’s name is split into three separate fields—Title, FirstName, and LastName—so that each piece of information can be managed individually. These fields are also defined with specific lengths, based on the size of the source data.

However, when recording details such as an employee’s qualifications or work experience, we cannot predict the length of the text. In such cases, the Memo field type is used. A memo field allows free-form text of varying lengths, making it ideal for storing descriptive information.

That said, memo fields are not used directly in reports or queries because their contents are unstructured and more difficult to work with. Still, they do provide some flexibility in filtering records—for example, by searching for specific text that may appear anywhere within the field.

Let’s look at a few examples of working with memo field data from the Employees table in the Northwind.mdb sample database.

Prepare for a Trial Run.

Import the Employees Table from the sample database C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb.
Open the Employees Table in the datasheet view.
Move the horizontal scrollbar at the bottom to the right, so that the Notes Memo Field contents are visible to you.
Point the mouse at the left border of the table at the intersection of two rows, and the mouse pointer turns into a cross.
Click and drag down to increase the row size so that the Notes field contents can be viewed properly.

If you review the qualification details stored in each employee record, you’ll notice that many employees hold a BA degree. However, the text “BA” does not appear in a fixed position within the memo field—it may occur anywhere in the description. So how can we filter all employee records that include a BA degree?

To begin, let’s try this directly in Datasheet View before moving on to writing a query that filters data based on text in a memo field.
Highlight the letters BA in any record, and Right-click on the highlighted text.
A shortcut menu is displayed, and the suggested options for filtering data from the Memo Field are Contains "BA" or Does Not Contain "BA".
Click the Contains 'BA' option to filter records with the text "BA" matching anywhere in the memo field.

If you want to filter records this way for printing a Report, then we must create Queries to filter data based on the text in the Memo Field. You can use the Like Operator with AND, OR logical operators.

Copy and paste the following SQL Strings into the SQL Editing Window of new Queries and save them with the suggested names:

Query Name: Employee_BAQ

SELECT Employees.LastName, Employees.FirstName, Employees.Notes
FROM Employees
WHERE (((Employees.Notes) Like "*BA*"));

The output of this query will also include the record of an employee with an MBA degree because the text "BA" appears within "MBA". To exclude this record, modify the criteria by inserting a space immediately after the first asterisk, as in "* BA*".

Query Name: Employee_BA_BSCQ

SELECT Employees.LastName, Employees.FirstName, Employees.Notes
FROM Employees
WHERE (((Employees.Notes) Like "* BA*")) OR (((Employees.Notes) Like "*BSC*"));

The above query is an example use of the logical operator OR to filter data having BA or BSC.

Query Name: Employee_BA_BSCQ

SELECT Employees.LastName, Employees.FirstName, Employees.Notes
FROM Employees
WHERE (((Employees.Notes) Like "* BA*" And (Employees.Notes) Like "*psychology*"));

The above example demonstrates the logical operator AND and filters records of employees with BA in Psychology.

Earlier Post Link References:

Lively Controls on Form

a.p.r. pillaiOctober 23, 2010Animations, Controls, Forms No comments

Lively Controls on Form.

In most projects, our primary focus is always on completing the work on time and delivering it to the user as quickly as possible. I make every effort to stay on schedule and ensure that the user receives the application for testing without delay. However, I also like to take a second look at the overall design and appearance of the Forms and Reports in a more relaxed frame of mind. This allows me to introduce improvements, explore new ideas, and incorporate enhancements—alongside the requirements suggestions received from the user.

From my past experiences, these little refinements have always been met with some appreciation. Users often respond positively to subtle design improvements, which motivates me to keep experimenting with fresh ideas in future projects. After all, Forms are the first component that catches a user’s eye, followed by well-formatted Reports and visually engaging charts.

Forms hold a special place in users' minds. They should not only be functional but also visually pleasing and user-friendly. Once the core functionality of an application is complete, and if there is some free time, revisiting the main forms’ design can open the door to creativity. Even small visual tricks can make a significant difference. For instance, most controls we place on Microsoft Access forms are static, but by introducing a touch of movement or animation—applied tastefully and without excess—we can leave a lasting positive impression on users.

We have already explored several animation techniques for form controls in earlier discussions. If you’d like to revisit those, the links are provided below:

Option Group Control Style Change on Mouse Move.

In this example, we will apply a simple trick to an Option Group control to make it respond visually when the mouse moves over it. The idea is straightforward: when the Option Group is created, we set its Style property to Raised. As the mouse moves over the control, the style changes to Sunken, and when the mouse moves away, it reverts to the original Raised state. This repeated action gives the Option Group a more dynamic and lively appearance, making the form more engaging for users.

Create a Form for Demo.

Open a new Form in Design View.
Click the Wizard Button (with the magic wand icon) in the Toolbox to select it.
Select the Option Group control and draw it on the detail section of the form.
Enter the options as shown in the design below by pressing the Tab key after each option to advance to the next row. You may enter any Text as options as you like.

Right-click to open Large Image in New Tab/Window.
Complete the design by selecting the Finish Command Button.
Move the attached label above and make it as wide as the Option Group Control, and change its Caption to Main Menu.
Move the attached child label above and change its width to match the Option Group control, and align it to the left edge.
Change the Label Caption to Main Menu.
Click on the Option Group to select it and display its Property Sheet (View -> Properties or press ALT+Enter).
Change the following Property Values as given below:
- Name = Frame0
- Back Style = Normal
- Special Effect = Raised
The Form's Class Module Code.
Display the Form's VBA Code Module (View ->Code, or click the Module Toolbar Button or press ALT+F11).

Copy and Paste the following VBA Code into the VBA Module of the Form:

Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, x As Single, Y As Single)
   If Me.Frame0.SpecialEffect <> 1 Then
       Me.Frame0.SpecialEffect = 1
   End If
End Sub

Private Sub Frame0_MouseMove(Button As Integer, Shift As Integer, x As Single, Y As Single)
   If Me.Frame0.SpecialEffect <> 2 Then
       Me.Frame0.SpecialEffect = 2
   End If
End Sub

Save and close the Form.
The Demo Run.
Open the Form in the normal view. You will find the Option Group control is in Raised style.
Move the mouse over the control, and it will enter the Sunken state.
Move the mouse pointer away from the control, and it will restore to the Raised state.
Repeat this action quickly; you can see how the Option Group control responds to this simple action; otherwise, it remains rigid forever.

Technorati Tags: Animation, Forms

Create your own color palette

a.p.r. pillaiOctober 15, 2010Color Palette, How Tos No comments

Create your own color palette.

We have already explored the Binary, Octal, and Hexadecimal number systems and learned some simple rules that allow us to devise new number systems with any base value, as long as the user understands the basic rules and can interpret the numbers correctly.

In our previous session, we worked with a form that allowed us to enter decimal numbers, convert them into binary, and also display their corresponding RGB color representation.

If you would like to learn and understand Binary, Octal, and Hexadecimal Number Systems, then check the following Posts:

This week, we will build a Form-based utility that will be especially useful during the design phase of a new project. With this tool, you can visually create your own 24-bit custom colors and save up to 15 colors in a color palette within the form. Any of these saved colors can then be applied to the form background, or to the foreground, background, or border color properties of other controls at design time.

For reference, an image of the Employees Form in Design View is shown below, displaying the form’s Header Section Property Sheet alongside the Color Palette Form.

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

The Color Palette Usage.

A new custom color is created on the Color Palette Form (see the large rectangle) and then applied to the Employees Form Header Section backcolor by setting its value in the Back Color property.

Click on the image to enlarge it. The Employees Form is shown in Design View, with the Header Section selected. Its Property Sheet is also visible, displaying the Back Color property.

When you click in any of the 15 Color Boxes (which you can create and overwrite with your own saved colors), the corresponding color value is displayed in the text box above the palette, labeled as an RGB Color. You can then copy this value (Ctrl+C) and paste it (Ctrl+V) into the Property Sheet’s Back Color, ForeColor, or Border Color property—depending on where you want to apply it—thus setting the form or control color.

In the Property Sheet, color values appear in hexadecimal format prefixed with a hash (#4E61BC). However, you can paste the decimal color value, and Access will convert it to hexadecimal form.

Since we have already learned about the hexadecimal number system, I’d like to draw your attention to a small issue related to this conversion.

A Test Run.

Let us take a sample color number in decimal: 12345678. Enter (or paste) this value into the Form Header or Detail Section Back Color property and press Enter. Access will automatically convert it into a hexadecimal value, such as #4E61BC and the form background will be filled with a dark blue color.

Notice the difference between the first two digits and the last two digits of the hexadecimal number—this is the key to understanding how Access interprets color values.

Checking the value in VBA

Open the VBA Editor (Alt+F11).
Display the Immediate Window (Ctrl+G).
Type the following and press Enter:


? HEX(12345678)

The result will be:

BC614E

This is the raw hexadecimal equivalent of 12345678.

If you paste this number into a property in the form of #BC614E Access displays a dark red color.

Why do the results differ?

When you type the decimal value 12345678 directly into a color property, Access reinterprets the bytes internally and reformats them as #4E61BC in 24 Bit RGB order:
- R = 78 (&H4E) - least significant 8-bit Value.
- G = 97 (&H61) - middle 8-bit Value
- B = 188 (&HBC) - most significant 8-bit value.
On the other hand, when you convert 12345678 using VBA’s Hex() function, you get BC614E. If this is placed directly into the property (#BC614E) Access does not remap it to RGB order—it takes it literally to a completely different color.

Conclusion

In both cases, decimal-to-hexadecimal conversion works correctly, just as in the decimal-to-binary method we discussed earlier. However, Access interprets decimal values and hex color values given directly in the color property differently internally.

The safest method: Always paste the decimal value into the color property and let Access handle the conversion automatically.

Creating New Colors and Saving Them in the Color Palette.

Now, we will look at the simple trick of designing new Colors (logically, there are over 16 million 24 Bit color values available) and saving them on the Color palette for later use.

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

You can download a sample demo database from the link at the bottom of this article; you can import this Form into your new Projects, design your own colors, and use them on your Forms or Controls.

You can click the above image to enlarge it for a clearer view of the controls on the Form. Let us examine them one by one.

Scrollbars (Red, Green, Blue):
There are three scrollbar controls to input integer values ranging from 0 to 255 for the primary colors Red, Green, and Blue.
- Moving a scrollbar to the right increases its value, while moving it to the left decreases it.
- Clicking the arrow buttons at either end adjusts the value by 1 with each click.
- Holding down an arrow button rapidly increases or decreases the value, and the color graph updates accordingly.
Color Bar Graphs:
On the right side of each scrollbar, a vertical bar graph gives a visual clue of the current intensity of each primary color. The corresponding numeric values also appear in the text boxes labeled Red, Green, and Blue.
Preview Rectangle:
The large rectangle at the bottom-right displays the resulting color created by mixing the Red, Green, and Blue values from the scrollbars.
RGB Color Text Box:
The text box labeled RGB Color shows the decimal color number representing the combined RGB value.
- You can copy this number (Ctrl+C) and paste it (Ctrl+V) into a Form’s or Control’s BackColor, ForeColor, or Border Color property to apply the new color.
Saving Custom Colors:
To preserve a newly created color:
1. Click on the preview rectangle to select the color.
2. Then click on one of the 15 color boxes on the left to store it there for later use (overwriting any existing color in that box).
Reusing Saved Colors:
To apply a previously saved color, simply click on the desired color box. The selected color’s decimal number will appear in the RGB Color text box, ready to copy and paste into the appropriate property.

Tips

Modify an Existing Color:
If you already have an RGB color number but want to adjust it, copy and paste the decimal value into the RGB Color text box (or type it directly) and press Enter. The selected color will appear in the preview rectangle, and the Red, Green, and Blue text boxes will update with their respective values. The bar chart will also provide a quick visual indication of each color component. From there, you can use the scrollbar sliders to fine-tune the color further.
Working with Hexadecimal Values (MS Access 2007):
If you are using MS Access 2007, color values are displayed in hexadecimal format. To convert them into decimal numbers for use in the RGB Color text box:
1. Open the VBA Debug window (Ctrl+G).
2. Type: ? &hXXxxXX (replace XXxxXX with your six-digit hex value).
3. Swap the rightmost two digits with the leftmost two digits of the hex value before conversion.
4. The result will be the decimal equivalent, which you can copy into the RGB Color text box for modification.
Direct Value Entry:
Instead of using scrollbars, you can type integer values (from 0 to 255) into the Red, Green, and Blue text boxes to generate a new color instantly.

Technorati Tags: Microsoft, Binary, Colors

Download the Demo Database.

Download Demo RGBColor.zip

Colors 24 Bits and Binary Conversion

a.p.r. pillaiOctober 07, 2010Art Work, Binary Numbers, Forms, How Tos No comments

Colors: 24 Bits and Binary Conversion.

If you have been following the last four articles on Number Systems (Decimal, Binary, Octal, and Hexadecimal), here’s something interesting for you to explore. Before we dive in, I recommend reviewing those earlier articles using the links provided below. By doing so, you know the background information on this topic, understand the concept more clearly, appreciate its practical usefulness, and enjoy experimenting with the methods we are about to discuss.

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

Color Number Range 0 to 16 Million.

The image above shows an MS Access Form where you can enter a Decimal Number ranging from 0 to 16,777,215 (the maximum value represented by a 24-bit binary number). The form converts the entered value to its Binary equivalent and simultaneously uses the number to generate an RGB color, displayed in the top color band. With this method, you can visualize over 16 million possible colors on the form.

In the example shown, the value 65535 is entered in the text box. The corresponding Binary representation (1111111111111111) is displayed in the gray band just above the text box. Each binary digit is aligned under a label in red font, which represents the decimal value of that bit position. By adding together all these red-labeled values where the binary digit is 1 You arrive at the decimal number entered in the text box.

The Red, Green, and Blue (RGB) Colors.

Bits 0 to 7 (the first 8 bits) represent the Red component, while bits 8 to 15 (the next 8 bits) represent the Green component. When both Red and Green are at full strength, they combine to produce Yellow, which is displayed on the top color band above the Red, Green, and Blue bars.

Now, let’s slightly modify this sample color. Starting with the value 65535, click in the labels with the values 4096 and 64. Each selected value is transferred into the text box and subtracted from 65535. The clicked labels return to their default black font color, indicating that those bit values are no longer active.

Finally, click the Convert command button. The updated result (the new RGB number) is displayed in blue beneath the text box, and the top color band updates to show the new color generated after subtracting the selected bit values. See the image below for reference.

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

If you would like to add more Red, Green, or Blue to the existing color number, simply click in the labels that display numbers in black font. Each selected value will be added up to form the current color, and when you click the Convert command button, the new result will be displayed in the top color band. The intensity of the added color depends on the magnitude of the number chosen within the respective color band. Once a value is included, its label font changes to red, indicating that it has been added to the current color number.

If you click a label that is already red, its font will switch back to black, meaning that value will now be subtracted from the total. Again, click the Convert command button to evaluate the final color value. The updated binary digits of the selected values will appear in the gray band above the text box each time, allowing you to trace which bits are active.

To restore the form to its default state, click the Reset command button.

How to make a Color Choice?

You can use one of the three methods given below or a mix of all three to enter the number into the TextBox:

Enter a Decimal Number directly into the TextBox and click the Convert command button.
Enter a valid expression into the TextBox (for example: 2^10 + 2^5 + 5*25 + 1638) and click the Convert command button. The expression will be evaluated, and the result will be converted.
Click on the labels beneath the Red, Green, and Blue color bands to select values and combine them into a new color. Each clicked value is added to the TextBox as part of an expression, and its label font changes to red; this color number has been included. Clicking the same label again will subtract that value, and the font will revert to black. When ready, click the Convert command button to apply the result.
You can also combine all three methods—direct number entry, expressions, and label selections—to arrive at a valid color number between 1 and 16,777,215 (16777215 = 256 Blue × 256 Green × 256 Red – 1).

Get the result in three ways:

The RGB Color number selected is displayed in the top color band.
The Binary Number.
The RGB Color Number of the top color band.

How to Use the Newly Created Color.

You can use this color number when designing Forms or Controls in Access by setting it as the Background, Foreground, or Border Color in the Form or Control Property Sheet. If you are a web designer, you can convert the decimal color number into a hexadecimal value and use it in your style sheets. To do this, call the function Hex(DecimalNumber) in the Debug Window. The result will give you the 24-bit hexadecimal format, which can be applied directly in CSS, for example: #0000FF for the decimal color value 255.

Enter the number 16777215 in the Text Box and click the Convert Command Button to change the top color band to white.

You can download a database with the above Form and Programs by clicking on the Download link below:

Technorati Tags: Forms, Artwork

Download Demo Database

Download Demo Binary.zip

Hexadecimal Number System

a.p.r. pillaiSeptember 29, 2010Hexadecimal Numbers, How Tos No comments

Continuation of earlier Articles: Hexadecimal Number System

Hexadecimal numbers provide yet another way of writing binary values, offering a more compact form than octal numbers. This number system is based on 16 as its radix (or base). Following the general rules of number systems, the Base-16 system has numeral values ranging from 0 to 15 (i.e., one less than the base). Like the decimal system, it uses the digits 0 to 9 for the first ten values. For the values 10 to 15, the letters A to F are used as single-digit representations.

Hexadecimal Decimal
 0            0
 1            1
 2            2
 3            3
 4            4
 5            5
 6            6
 7            7
 8            8
 9            9
 A           10
 B           11
 C           12
 D           13
 E           14
 F           15

Now, let us see how to convert binary numbers to hexadecimal form. We will use the same method we used to convert binary to Octal form. We have formed groups of 3 binary digits, each with a binary value of 011,111,111 (equivalent to 255 decimal), to find the Octal Number &O377. For a Hexadecimal Number, we should take groups of 4 binary digits (Binary 1111 = (1+2+4+8) = 15, the maximum value of a hexadecimal digit) and add the values of binary bits to find the hexadecimal digit.

For hexadecimal conversion, we instead group the binary digits in sets of four. This works because the largest value represented by four binary digits (1111) is 15, which corresponds to the highest single-digit value in hexadecimal (F). Once the digits are grouped, simply add the positional values of the binary bits in each group to determine the hexadecimal digit.

Let us find the Hexadecimal value of the Decimal Number 255.

Decimal 255 = Binary 1111,1111 = Hexadecimal digits FF

It takes 3 digits to write the quantity 255 in decimal; in Octal, 377 (this may not be true when larger decimal numbers are converted to Octal); in Hexadecimal, it takes only two Digits: FF or ff (not case-sensitive).

Let us try another example:

Decimal Number 500 = Binary Number 111110100

111,110,100 = Octal Number 764

0001,1111,0100 = Hexadecimal Number 1F4

To identify Octal Numbers, we have used prefix characters &O or &0 with the Number. Similarly, Hexadecimal numbers &H (not case-sensitive), when entered into computers, like &H1F4, &hFF, etc.

You may type Print &H1F4 in the Debug Window and press the Enter Key to convert and print its Decimal Value.

MS-Access Functions

There are two conversion Functions in Microsoft Access: Hex() and Oct(), both use Decimal Numbers as parameters.

Try the following Examples, by typing them in the Debug Window to convert a few Decimal Numbers to Octal and Hexadecimal:

? OCT(255)
Result: 377

? &O377
Result: 255

? HEX(255)
Result: FF

? &hFF
Result: 255

? HEX(512)
Result: 200

? &H200
Result: 512

MS-Excel Functions

In Microsoft Excel, there are Functions for converting values to any of these forms. The list of functions is given below:

Simple Usage: X = Application.WorksheetFunction.DEC2BIN(255)

Decimal Value Range -512 to +511 for Binary.

DEC2BIN()
DEC2OCT()
DEC2HEX()
BIN2DEC()
BIN2OCT()
BIN2HEX()
OCT2DEC()
OCT2BIN()
OCT2HEX()
HEX2DEC()
HEX2OCT()
HEX2BIN()

You can convert the decimal value of a maximum value of 99,999,999 to Octal Number with the Function DEC2OCT(). You can type: ? &o575360377 in the Debug window to convert Octal to Decimal Number.

499,999,999,999 is the maximum decimal value for DEC2HEX() and its equal value in Hexadecimal form for HEX2DEC() Function.

With the Binary functions, you can work with the Decimal value range from -512 to 511 up to a maximum of 10 binary digits (1111111111). The 10th bit (left) is the sign bit: 1 negative, and 0 denotes a positive value.

We have created two Excel Functions in Access to convert Decimal Numbers to Binary and Binary Values to Decimal.

But first, you have to attach the Excel Application Object Library file to the Access Object References List.

Open the VBA Window, select References from the Tools Menu, look for the Microsoft Excel Object Library File, and checkmark to select.
Copy the following VBA Code and paste it into a Standard Module:

Public Function DEC2_BIN(ByVal DEC As Variant) As Variant
Dim app As Excel.Application
Dim obj As Object

Set app = CreateObject("Excel.Application")
Set obj = app.WorksheetFunction
DEC2_BIN = obj.DEC2BIN(DEC)

End Function

Public Function BIN2_DEC(ByVal BIN As Variant) As Variant
Dim app As Excel.Application
Dim obj As Object

Set app = CreateObject("Excel.Application")
Set obj = app.WorksheetFunction
BIN2_DEC = obj.Bin2Dec(BIN)

End Function

Demo Run from the Debug Window, and the output values are shown below:

? DEC2_BIN(-512)
1000000000

? DEC2_BIN(-500)
1000001100


? DEC2_BIN(511)
111111111

? BIN2_DEC(1000000000)
-512 

? BIN2_DEC(111111111)
 511

In the first example, if the leftmost Bit (the 10th bit value 512) is 1, then the value is negative. For positive values, 9 bits are used up to a maximum decimal value of 511. This limitation is only for the Functions, and as you are aware, computers can handle very large positive/negative numbers.

In the first example, you can see that the leftmost sign Bit is 1, in the binary value 512 position, indicating that it is a negative value, and the other positive value Bits are all zeroes.

In the second example, the number -500 output in binary shows that the positive value Binary Bit value at 4 + 8 is on. That means the positive value 12 is added to -512, resulting in the value -500.

You can convert any value between -512 and 511 for Binary conversions, as we stated earlier. functions.

You may implement other functions in the Excel Function list in a similar way in Access, if needed.

Technorati Tags: Microsoft

Earlier Post Link References:

Octal Number System

a.p.r. pillaiSeptember 24, 2010How Tos, Octal Numbers No comments

Continued from Last Week's Post. Octal Number System.

This is the continuation of earlier Articles:

1. Learn the Binary Number System.

2. Learn Binary Number System-2.

Please refer to the earlier Articles before continuing.

We will take the result of the Decimal (Base 10) Number 255 converted to Binary for a closer look at these two numbers: 11111111.

The decimal value 255 is represented using only three decimal digits. However, when the same value is expressed in binary, it requires eight binary digits (bits) to represent the identical quantity. Earlier, Computer Programs were written using Binary Instructions. Look at the example code given below:

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

Source: www.wikipedia.org

Later, programming languages like Assembly Language were developed using Mnemonics (8-bit-based Binary instructions) ADD, MOV, POP, etc. Present-day Compilers for high-level languages are developed using Assembly Language. A new number system was devised to write binary numbers in short form.

Octal Number System.

The Octal number system has the decimal number 8 as its base and is known as the Octal Numbers. Based on the general rule we have learned, Octal Numbers have digits 0 to 7 (one less than the base value 8) to write numerical quantities. Octal Numbers don't have digits 8 or 9. This Number System has been devised to write Binary Instructions for Computers in a shorter form and to write program codes easily.

For example, an 8-bit Binary instruction looks like the following:

00010111 (instruction in Octal form 027), ADD B, A (Assembly Language).

The first two bits (00) represent the operation code ADD, the next three bits (010) represent CPU Register B, and the next three bits (111) represent CPU Register A. The 8-bit binary instruction adds register A to B. If the instruction must be changed to (ADD A, B), add the contents of register B to A, then the last six bits must be altered to 00,111,010. This can be easily understood if it is written in Octal 027 to 072 rather than Binary 00111010.

The Octal (Base-8) Number System was developed as a compact way to represent binary-based instructions. Returning to the Octal Number System, let us examine how these numbers are used. To begin, we will create a table similar to those used for the Decimal and Binary Number Systems.

8⁵	8⁴	8³	8²	8¹	8⁰
32768	4096	512	64	8	1

We will use the same methods used for Binary to convert Decimal to Octal Numbers.

Example: Converting 255 into an Octal Number.

We cannot use the binary-to-decimal style conversion method for octal numbers. Looking at the table, we can see that 512 is greater than 255, so it cannot be used. The next lower value is 64, and our task is to determine how many times 64 can fit into 255.

Method-1:

255/64 = Quotient 3, Remainder 63 (Here, we have to take the Quotient as the Octal Digit).

In this method, we must take the quotient 3 (64 x 3 = 192) for our result value, and the balance is 63 (i.e., 255 - 192)

8⁵	8⁴	8³	8²	8¹	8⁰
32768	4096	512	64	8	1
			3

63/8 = Quotient 7, Remainder 7

8⁵	8⁴	8³	8²	8¹	8⁰
32768	4096	512	64	8	1
			3	7

7 is not divisible by 8; hence, 7 goes into the Units position

8⁵	8⁴	8³	8²	8¹	8⁰
32768	4096	512	64	8	1
			3	7	7

Method-2:

255/8 = Quotient = 31, Remainder=7

8⁵	8⁴	8³	8²	8¹	8⁰
32768	4096	512	64	8	1
					7

31/8 = Quotient = 3, Remainder=7

8⁵	8⁴	8³	8²	8¹	8⁰
32768	4096	512	64	8	1
				7	7

3 is not divisible by 8; hence, it is taken to the third digit position.

8⁵	8⁴	8³	8²	8¹	8⁰
32768	4096	512	64	8	1
			3	7	7

Writing Binary to Octal Short Form.

As I mentioned earlier, the Octal Number System was devised to express Binary in a shorter form. Let us see how we can do this and convert binary numbers easily into Octal numbers.

When the decimal number 255 is converted into Binary, we get 11111111. To convert it into Octal Numbers, organize the binary digits into groups of three bits (011,111,111) from right to left, add up the binary values of each group, and write the Octal value.

011 = 1+2 = 3

111 = 1+2+4 = 7

Result: = 377 Octal.

To get a better grasp of this Number System, try converting a few more numbers on your own. Start by converting some decimal numbers into binary, then group the binary digits into sets of three bits. Next, calculate the value of each group as though they represent the first three bits of a binary number.

Since octal numbers use digits 0 through 7, they can easily be mistaken for decimal numbers by both humans and machines. To avoid confusion, octal numbers are always written with a prefix. In MS Access VBA, the prefix is &O (the letter O, not case-sensitive) or &0 (digit zero). For example, the octal number 377 can be written as &O0377, &O377, or &0377.

You can try this by typing the number in the Debug Window of Microsoft Access or Excel.

Examples:

? &O0377

Result: 255

? &0377

Result: 255

? &0377 * 2

Result: 510

Next, we will learn the Base-16 (Hexadecimal) Number System.

Technorati Tags: How-Tos.

Create Menus with Macros - MS Access 2003.

Sample Demo Run.

Defining Macro as Menu.

Join Split Array Functions.

Example-1:

Example-2:

Join() Function:

Split() Function:

Earlier Post Link References:

Memo field and data filtering.

Prepare for a Trial Run.

Earlier Post Link References:

Lively Controls on Form.

Option Group Control Style Change on Mouse Move.

Create a Form for Demo.

The Form's Class Module Code.

The Demo Run.

Create your own color palette.

The Color Palette Usage.

A Test Run.

Checking the value in VBA

Why do the results differ?

Conclusion

Creating New Colors and Saving Them in the Color Palette.

Tips

Download the Demo Database.

Colors: 24 Bits and Binary Conversion.

Color Number Range 0 to 16 Million.

The Red, Green, and Blue (RGB) Colors.

How to make a Color Choice?

How to Use the Newly Created Color.

Download Demo Database

Continuation of earlier Articles: Hexadecimal Number System

MS-Access Functions

MS-Excel Functions

Earlier Post Link References:

Continued from Last Week's Post. Octal Number System.

Octal Number System.

Method-2:

Writing Binary to Octal Short Form.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

Post Feed

Get new posts by email:

Search

USEFUL LINKS

Popular Posts

About Me

Blog Archive

Labels

Statcounter