LEARN MS-ACCESS TIPS AND TRICKS

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.

Learn Binary Number System-2

a.p.r. pillaiSeptember 15, 2010How Tos, Numbering System 1 comment

Continued from Last Week's Post

This is the continuation of last week's article, Learn the Binary Number System

Last week, we went through the fundamentals of the Binary Number System, learned how to convert the decimal number 10 to binary, and used different ways to convert a decimal number to Binary.

I hope you have tried converting the sample number 255 yourself.

If you could not do it, then let us try it here.

Method-1:

Find the highest integer value in the binary table that can be subtracted from the Decimal Number. Here, 128 is the highest value that can be taken.

255

-128

=127

Write the binary digit 1 at the 128 (2⁷) number position underneath the Binary Table.

2¹⁵	2¹⁴	2¹³	2¹²	2¹¹	2¹⁰	2⁹	2⁸	2⁷	2⁶	2⁵	2⁴	2³	2²	2¹	2⁰
32,768	16,384	8,192	4,096	2,048	1,024	512	256	128	64	32	16	8	4	2	1
								1

The next highest integer in the binary Table that goes into 127 is 64.

127

-64

=63

2¹⁵	2¹⁴	2¹³	2¹²	2¹¹	2¹⁰	2⁹	2⁸	2⁷	2⁶	2⁵	2⁴	2³	2²	2¹	2⁰
32,768	16,384	8,192	4,096	2,048	1,024	512	256	128	64	32	16	8	4	2	1
								1	1

Repeat this method up to the unit Value position.

2¹⁵	2¹⁴	2¹³	2¹²	2¹¹	2¹⁰	2⁹	2⁸	2⁷	2⁶	2⁵	2⁴	2³	2²	2¹	2⁰
32,768	16,384	8,192	4,096	2,048	1,024	512	256	128	64	32	16	8	4	2	1
								1	1	1	1	1	1	1	1

You can cross-check the result by adding up all values taken from the 1s bit (the name of the binary digit) position to arrive at the total value you were trying to convert into Binary.

Method-2:

Divide the decimal number by 2, take the remainder, and write it at the unit position in the Binary Table.
255/2 = Quotient = 127, Remainder = 1
Next step, take the Quotient Value (127) of the previous calculation, divide it by 2, and find the remainder. Write the remainder value to the left of the earlier written binary digit (bit). Repeat this method and write the final remainder in the binary table.

127/2 = Quotient = 63, Remainder = 1

63/2 = Quotient = 31, Remainder = 1

31/2 = Quotient = 15, Remainder = 1

15/2 = Quotient = 7, Remainder = 1

7/2 = Quotient = 3, Remainder = 1

3/2 = Quotient = 1, Remainder = 1

1/2 = Quotient = 0, Remainder = 1

You will get the Binary Number 11111111 equal the Decimal Number 255.

You can experiment with larger decimal values or write some unknown Binary Values with random 1s and 0s and try converting them back into Decimal Numbers.

Next, let us try some additions and subtractions with Binary Numbers. If you know the rules of Decimal addition and subtraction, then you have no problems with Binary Numbers.

Example: Addition

11101110	238
+1110111	119
101100101	357

Start adding the rightmost digits:

0+1 = 1
Next 1+1 = 2, put 0 and carry 2 to the next position (like 5+5=10, we put 0 at the unit's position and carry 1 to the next position to add)
next 1+1+1 carry = 3 (binary 11), put 1 and carry 2 to the next position
next 1+1 carry = 2(binary 10), put 0 and carry 2 to the next position
Next 1+1 carry = 2(binary 10), put 0 and carry 2 to the next position
Next 1+1+1 carry = 3(binary 11), put 1 and carry 2 to the next position
Next 1+1+1 carry = 3(binary 11), put 1 and carry 2 to the next position
Next 1+1 carry = 2(binary 10), put 0 and carry 2 to the next position.

Example: Subtraction

11001110	206
-1111111	127
1001111	79

0-1 cannot be done, so take 2 from the next position; now 2-1 = 1, but the next position on the first line becomes 0.
0-1 cannot be done, so take 2 from the next position; now 2-1 = 1, but the next position on the first line becomes 0.
0-1 cannot be done, so take 2 from the next position; now 2-1 = 1, and the next 3 positions become 0.
Take the value from the 8th position and move forward to the 4 positions and to the 2 value position; 2-1 = 1
1-1 = 0
1-1 = 0
After moving the value forward from the 7th digit position on the top line, it is now 0. So move 2 from the next position. 2-1 = 1

You can try it out yourself, starting with smaller binary values and progressively with bigger ones.

For your information, there is no Multiplication or Division in computers. These calculations are achieved by successive addition or subtraction of values.

Continued../-

Earlier Post Link References:

Learn Binary Number System

a.p.r. pillaiSeptember 08, 2010How Tos, Numbering System 4 comments

Learn the Binary Number System.

Are you hesitant about learning the computer’s own language—the Binary Number System? I hope not! If you are a programmer or plan to become one, then I strongly recommend that you learn it. No, you won’t be writing full programs in binary, but sooner or later, you will encounter Binary, Octal, and Hexadecimal numbers. If you want to avoid surprises, it’s best to build a solid understanding of these number systems early on.

The good news is—it’s not as hard as it may sound. In fact, once you understand a few simple rules that apply to our familiar decimal system, you’ll discover that you can devise and work with any number system, as long as others can also interpret it and agree on its usage.

Simple Rules that Govern the Decimal Number System.

Let us explore a few simple rules of the Decimal Number System that we are already familiar with.

The Decimal Number System.

The decimal Number System's Base value is 10, which is known as the Base-10 Number System.
The Base-10 Number System has 10 digits to express quantities: 0 to 9, and the highest digit value is 9, i.e., one less than the Base value of 10.
Any value more than 9 is expressed in multiples of 10.
Note: Keep this simple rule in mind: when you create a number system with a particular Base, the total number of digits in that system will always be equal to the Base. The largest single digit in that system will be one less than the Base. You’ll see this rule in action when we explore other number systems commonly used in computers, such as Octal and Hexadecimal.
The decimal value 10 cannot be written with a single digit; instead, 0 in the unit's position and 1 in the 10th position. So, decimal Value 10:
10^¹ = 10x1= 10
10⁰ (1 x 0) = 0
10+0 = 10

Let us create a table to see how each digit value is calculated and added up to the decimal quantity.

Note: Please use your Laptop or Tablet to view the Table correctly.

10⁶	10⁵	10⁴	10³	10²	10¹	10⁰
1,000,000	100,000	10,000	1,000	100	10	1
				2	5	5

2 x 10² OR 2 x 10 x 10 OR 2 x 100 = 200

5 x 10¹ OR 5 x 10 = 50

5 x 10⁰ OR 5 x 1 = 5

=======

255

=======

Following the above rules, we can devise any number system. For example, if we create a number system with Base 8, it will use the digits 0 to 7. The highest single-digit value is always one less than the base, so in this case, 7. This system does not include the digits 8 or 9. In fact, this Base-8 system—known as the Octal Number System—has long been used in the computer world. We will study it in detail after exploring the Binary Number System.

Binary Number System.

By keeping the simple rules in mind, we can easily learn the Binary, or Base-2 Number System. This Number System has only two digits, 0 and 1 (the highest digit value is 1, i.e., one less than the base value 2) to write any Decimal value in Binary form.

First, let us create a Binary Table, similar to the decimal table, so that converting Decimal Numbers to Binary and vice versa is easy.

Note: Please use your Laptop or Tablet to view the Table correctly.

2¹⁵	2¹⁴	2¹³	2¹²	2¹¹	2¹⁰	2⁹	2⁸	2⁷	2⁶	2⁵	2⁴	2³	2²	2¹	2⁰
32,768	16,384	8,192	4,096	2,048	1,024	512	256	128	64	32	16	8	4	2	1

In the above table, each digit position value is given in the second row. For example, if you put 1 below the value 1024 and fill the other slots to the right with all zeroes, then the value of Binary Number 10000000000 is 1024 (or 1K or 2¹⁰). If you type 1 replacing the rightmost 0 (10000000001), then the Binary Value becomes 1024 + 1 = 1025.

Let us try converting the small decimal number 10 to binary.

Method-1:

To convert the decimal number 10, we look at the table and find the highest integer value that can be subtracted from it. In this case, the highest value is 8.
Subtract 8 from 10 and find the result.

10
-8
====
2
====
Put 1 under the value 8 slot in the binary table.

2¹⁵	2¹⁴	2¹³	2¹²	2¹¹	2¹⁰	2⁹	2⁸	2⁷	2⁶	2⁵	2⁴	2³	2²	2¹	2⁰
32,768	16,384	8,192	4,096	2,048	1,024	512	256	128	64	32	16	8	4	2	1
												1

Now, we have 2 as the balance value, and the next binary positional value is 4. 4 cannot be subtracted from 2, so put a 0 in the slot of value 4.

2¹⁵	2¹⁴	2¹³	2¹²	2¹¹	2¹⁰	2⁹	2⁸	2⁷	2⁶	2⁵	2⁴	2³	2²	2¹	2⁰
32,768	16,384	8,192	4,096	2,048	1,024	512	256	128	64	32	16	8	4	2	1
												1	0

Next, value 2 can be subtracted from the balance 2.
2
-2
=====
0
=====
Put 1 under the value 2 in the binary table. The remainder is zero (0), so put a 0 in the unit position of the binary table. So the result of Decimal Number 10 in binary form is 1010 as given below:

2¹⁵	2¹⁴	2¹³	2¹²	2¹¹	2¹⁰	2⁹	2⁸	2⁷	2⁶	2⁵	2⁴	2³	2²	2¹	2⁰
32,768	16,384	8,192	4,096	2,048	1,024	512	256	128	64	32	16	8	4	2	1
												1	0	1	0

Cross-Checking the Result.

You can quickly cross-check whether the binary number is correct for the decimal number by adding up the values in the second row when digit 1 is present in the third row: 8 + 2 = 10.

It is not always convenient to build the value table whenever we want to convert a decimal number to binary. Instead, we can do it with a simple calculation. Let us convert the decimal number 10 to binary with this new method.

Method-2:

Divide the decimal number by 2 and record the remainder ( 0 or 1, since this is integer division). Begin constructing the binary value from right to left, placing each remainder in order.

10/2 = Quotient = 5, Remainder=0

2¹⁵	2¹⁴	2¹³	2¹²	2¹¹	2¹⁰	2⁹	2⁸	2⁷	2⁶	2⁵	2⁴	2³	2²	2¹	2⁰
32,768	16,384	8,192	4,096	2,048	1,024	512	256	128	64	32	16	8	4	2	1
															0

Each time, take the Quotient Value from the previous division and divide it by 2 again.

5/2 = Quotient = 2, Remainder = 1

2¹⁵	2¹⁴	2¹³	2¹²	2¹¹	2¹⁰	2⁹	2⁸	2⁷	2⁶	2⁵	2⁴	2³	2²	2¹	2⁰
32,768	16,384	8,192	4,096	2,048	1,024	512	256	128	64	32	16	8	4	2	1
														1	0

2/2 = Quotient=1, Remainder = 0

2¹⁵	2¹⁴	2¹³	2¹²	2¹¹	2¹⁰	2⁹	2⁸	2⁷	2⁶	2⁵	2⁴	2³	2²	2¹	2⁰
32,768	16,384	8,192	4,096	2,048	1,024	512	256	128	64	32	16	8	4	2	1
													0	1	0

1/2 = Quotient = 0, Remainder=1

2¹⁵	2¹⁴	2¹³	2¹²	2¹¹	2¹⁰	2⁹	2⁸	2⁷	2⁶	2⁵	2⁴	2³	2²	2¹	2⁰
32,768	16,384	8,192	4,096	2,048	1,024	512	256	128	64	32	16	8	4	2	1
												1	0	1	0

If you have understood this simple number system so far, try converting some larger numbers than we have practiced. As you can see in the binary table above, the highest value listed is 32,768 (2¹⁵). However, you can attempt to convert any decimal number below 65,536 using the same table.

If you need a sample number, then try converting the decimal number 255 to binary.

Earlier Post Link References:

User and Group Check

a.p.r. pillaiAugust 27, 2010Access Security, MS-Access Security No comments

User and Group Check.

In a secured database, basic access rights to objects—such as Tables, Forms, Queries, and Reports—are defined for specific Workgroups or Users as a one-time exercise. These permissions take effect automatically when a User belongs to a particular Workgroup for the database objects.

For example, if the Employees Table is configured to allow only Read Data permission for the Group-A Workgroup, any User in Group A cannot update, insert, or delete records when opening the Employees Form (with the Employees Table as its Record Source) or when accessing the Table directly.

If you want to make this scenario more flexible—for instance, to allow Users to update data— then this can be enabled in the User and Group Permissions control under the Security option in the Tools menu.

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

In this case, all Group-A Workgroup Users can edit and update all data fields of the Employees Table. Normally, Users are not allowed to open Tables directly; instead, they interact with the data through Data Entry, Edit, or Display Forms, which gives the Developer greater control over how the data is accessed and modified.

When the Update Data permission is assigned, Users can modify all fields in the Table. However, if we want to prevent Users from changing certain specific fields, this cannot be enforced using the standard security methods described above.

Field-Level Security Implementation.

This level of security can be implemented only through Visual Basic Programs. This method can be implemented in the following way:

When the Employee Form is opened by the User for normal work, we can get the User Name through the CurrentUser() Function.
The next step is to check whether this User belongs to the Group-A Workgroup.
If so, lock the Birth Date and Hire Date fields on the Form to prevent the current user from making changes.

We need two programs to try out this method:

A Function to check and confirm whether the User Name passed to it belongs to a particular Workgroup; if so, send a positive signal back to the calling program.
If the user is identified as a member of the Group-A Workgroup, the Birth Date and Hire Date data fields are locked on the Form through the Form_Load() Event Procedure; the current user cannot edit these field contents.
If the user belongs to a different Workgroup, then the above fields are unlocked for editing/updating.

The Demo Run.

To try this out:

Import the Employees Table and Northwind.mdb
Open an existing Standard VBA Module or create a new one.

Copy and paste the following Visual Basic Code into the Module and save it:

Public Function UserGroupCheck(ByVal strGroupName As String, ByVal strUserName As String) As Boolean
Dim WrkSpc As Workspace, Usr As User

On Error GoTo UserGroupCheck_Err

Set WrkSpc = DBEngine.Workspaces(0)

For Each Usr In WrkSpc.Groups(strGroupName).Users
If Usr.Name = strUserName Then
    UserGroupCheck = True
    Exit For
Else
    UserGroupCheck = False
End If

Next

UserGroupCheck_Exit:
Exit Function

UserGroupCheck_Err:
MsgBox Err.Description, , "UserGroupCheck_Err"
Resume UserGroupCheck_Exit

End Function

Open the Employees Form in Design View.
Display the Form's VBA Module (View --> Code).

Copy and paste the following code into the VBA Module and save the Form:

Private Sub Form_Load()
Dim strUser As String, strGroup As String, boolFlag As Boolean

strUser = CurrentUser
strGroup = "GroupA" 'replace the GroupA value with your own test Group Name
boolFlag = UserGroupCheck(strGroup, strUser)

If boolFlag Then
   Me.BirthDate.Locked = True
   Me.HireDate.Locked = True
Else
   Me.BirthDate.Locked = False
   Me.HireDate.Locked = False
End If

End Sub

Open the Form in Normal View.
Try to change the existing values in the Birth Date and Hire Date Fields.

If the Current User belongs to the Workgroup name assigned to the strGroup Variable, then the Birthdate and HireDate fields will be locked.

Tip: Even if your database is not implemented with Microsoft Access Security, you can test these programs. Assign the value Admins to the strGroup variable in the Subroutine. By default, you will be logged in as Admin User, a member of the Admins Workgroup. This will lock both the test fields from editing when the Employees Form is open.

Technorati Tags: Microsoft

User Defined Data Type

a.p.r. pillaiAugust 20, 2010Data Type, Functions, How Tos No comments

User-Defined Data Type.

VBA (Visual Basic for Applications) provides several predefined data types, such as Integer, String, Date, and others, which are used to store specific types of data. For example, an Integer variable can hold numeric values ranging from -32,768 to +32,767, while a String variable stores alphanumeric values, and so on.

However, programmers can also define their own custom data types combining multiple predefined data types into a single structure, and use them in their programs. Let’s explore this concept with a simple example.

Creating a User-Defined Type.

Open one of your existing databases or create a new one.
Open the VBA Editing Window (Alt+F11 or Tools ->Macro -> VBA Editing)
Access2007:
- Select Modules from the Object drop-down list.
- Double-click on an existing Module or select Create Menu.
- Select the Macro -> Modules toolbar button to create a new Standard Module.

Copy and paste the following Code into the Module.

Public Type WagesRec
    strName As String
    dblGrossPay As Double
    dblTaxRate As Double
    dblNetPay As Double
    booTaxPaid As Boolean
End Type

Public Function WagesCalc()
Dim netWages As WagesRec, strMsg As String
Dim fmt As String

With netWages
.strName = InputBox("Employee Name: ", , "")
.dblGrossPay = InputBox("Enter Gross Pay:", , 0)
.dblTaxRate = InputBox("Enter Taxrate", , 0)

.dblNetPay = .dblGrossPay - (.dblGrossPay * .dblTaxRate)

If .dblTaxRate <> 0 Then
   .booTaxPaid = True
End If

'Display Record
fmt = "#,##0.00"
strMsg = "Name:     " & .strName & vbCr & "Grosspay:     " & Format(.dblGrossPay, fmt) & vbCr
strMsg = strMsg & "Tax Rate:     " & Format(.dblTaxRate * 100, fmt) & "%" & vbCr & "Tax Amt.:     " & Format(.dblGrossPay * .dblTaxRate, fmt) & vbCr
strMsg = strMsg & "Net Pay:      " & Format(.dblNetPay, fmt) & vbCr & "Tax Paid:     " & .booTaxPaid

MsgBox strMsg, , "WagesCalc()"

End With

End Function

Place the insertion point somewhere in the middle of the WagesCalc() Function and press the F5 Key to run the Code.
Key in the Employee name, Gross Pay, and Tax Rate when prompted.

The output display of the program is shown below:

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

The Type Declaration and Properties.

Let us examine the above Code. The User-defined data type declaration is made in the global area of a Standard Module within the Type WagesRec... End Type structure. WagesRec is an arbitrary name; it can be anything that you like, but it should follow the Variable naming conventions. By default, the scope of the data type is Public. When it is declared as Private, like Private Type WagesRec... End Type: The scope of the data Type is within that Module only.

The individual data-member names of the new Data Type should also follow the variable naming conventions.

We have declared a Variable NetWages (you may visualize NetWages as an Object with several properties that can be set with different values) using the new data type WagesRec in our WagesCalc() Function. Individual elements of the NetWages Variable can be addressed as a subset of that object; separating both with a dot (.) like 'Netwages.dblGrossPay' to set its value or retrieve its contents.

We have used three InputBox statements to ask the user to input values for the Name, Grosspay, Tax Rate, calculate the Tax Value, Net Payable amount, and set the Tax Paid flag if the Tax Rate is a non-zero value.

In the next part of the program, we have loaded a String Variable strMsg with the output labels and values to display them through a MsgBox.

Array Data Type Elements.

In the Type declaration example, we have used the predefined System data types as elements. Besides that, we can declare Subscripted Elements and other User-Defined Data Types also as elements, like the following example:

Public Type MyRecord
dblIncentives(1 to 100) as double
EmployeeRec as WagesRec
End Type

In our program, let us assume that we have declared a variable with the above data type, like the following:

Dim EmployeeWages as MyRecord

Addressing the individual elements and their sub-elements will be as follows to assign values to them:

EmployeeWages.dblIncentives(1) = 5000

EmployeeWages.EmployeeRec.strName = "John Smith"

Subscripted Variable.

But the whole Data Type can be declared as a Subscripted Variable like:

Dim EmployeeWages(1 to 100) as MyRecord

Then how do we address the individual elements of the Variable?

EmployeeWages(1).dblIncentives(1) = 500
EmployeeWages(1).dblIncentives(2) = 750
EmployeeWages(1).EmployeeRec.strName = "John Smith"
EmployeeWages(1).EmployeeRec.dblGrossPay = 15000
EmployeeWages(2).dblIncentives(1) = 400
EmployeeWages(2).dblIncentives(2) = 450
EmployeeWages(2).EmployeeRec.strName = "George"
EmployeeWages(2).EmployeeRec.dblGrossPay = 17000

Sorting the Array of User-Defined Types.

We will see another example that uses subscripted user-defined data types. In this example, we will declare a new Data Type for the Employees Table from the Northwind.mdb sample database. We will load a few field values of the Employees Table into our User-Defined Subscripted Variable, sort the Names in memory, and print the output in the Debug Window.

1. Import the Employees Table from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb the sample database,

2. Copy and paste the following VBA Code into a new Standard Module and save the Module:

Type PersonalRecord
    strFirstName As String
    strLastName As String
    dtDB As Date
    strAddress As String
    strCity As String
    strPostalCode As String
End Type


Public Function ReadSort()
Dim PRec() As PersonalRecord, PRecX As PersonalRecord
Dim db As Database, rst As Recordset, recCount As Long
Dim J As Long, k As Long, h As Long

Set db = CurrentDb
Set rst = db.OpenRecordset("Employees", dbOpenDynaset)
rst.MoveLast
recCount = rst.RecordCount

ReDim PRec(1 To recCount) As PersonalRecord
rst.MoveFirst
J = 0
'Load Employee Records into Userdefined Variable Array
Do While Not rst.EOF
J = J + 1
With rst
    PRec(J).strFirstName = ![FirstName]
    PRec(J).strLastName = ![LastName]
    PRec(J).dtDB = ![BirthDate]
    PRec(J).strAddress = ![Address]
    PRec(J).strCity = ![City]
    PRec(J).strPostalCode = ![PostalCode]
End With
rst.MoveNext
Loop

rst.Close
Debug.Print "Before Sorting"
Debug.Print "--------------"
DisplayRoutine PRec()

'Bubble Sort on FirstName
For k = 1 To J - 1
   For h = k + 1 To J
       If PRec(h).strFirstName < PRec(k).strFirstName Then
           'Swap the Records
           'move the first record to temporary storage area
           PRecX.strFirstName = PRec(k).strFirstName
           PRecX.strLastName = PRec(k).strLastName
           PRecX.dtDB = PRec(k).dtDB
           PRecX.strAddress = PRec(k).strAddress
           PRecX.strCity = PRec(k).strCity
           PRecX.strPostalCode = PRec(k).strPostalCode
        
           'move the second record to replace the first
           PRec(k).strFirstName = PRec(h).strFirstName
           PRec(k).strLastName = PRec(h).strLastName
           PRec(k).dtDB = PRec(h).dtDB
           PRec(k).strAddress = PRec(h).strAddress
           PRec(k).strCity = PRec(h).strCity
           PRec(k).strPostalCode = PRec(h).strPostalCode
           
           'move the from temporary storage to replace the second record
           PRec(h).strFirstName = PRecX.strFirstName
           PRec(h).strLastName = PRecX.strLastName
           PRec(h).dtDB = PRecX.dtDB
           PRec(h).strAddress = PRecX.strAddress
           PRec(h).strCity = PRecX.strCity
           PRec(h).strPostalCode = PRecX.strPostalCode
        End If
    Next h
Next k

Debug.Print "After Sorting"
Debug.Print "--------------"
DisplayRoutine PRec()

End Function


Public Function DisplayRoutine(ByRef getRecord() As PersonalRecord)
Dim RecordCount As Long, J As Long

RecordCount = UBound(getRecord)
For J = 1 To RecordCount
   Debug.Print getRecord(J).strFirstName, getRecord(J).strLastName, getRecord(J).dtDB
Next
Debug.Print
Debug.Print

End Function

3. Place the insertion point in the middle of the Module and press F5 to run the Code.

4. Press Ctrl+G to display the Debug Window, and you will find the following output printed there:

Before Sorting
--------------
Nancy         Davolio       08/09/1968 
Andrew        Fuller        19/02/1952 
Janet         Leverling     30/08/1963 
Margaret      Peacock       19/09/1958 
Steven        Buchanan      04/03/1955 
Michael       Suyama        02/07/1963 
Robert        King          29/05/1960 
Laura         Callahan      09/01/1958 
Anne          Dodsworth     02/07/1969 


After Sorting
--------------
Andrew        Fuller        19/02/1952 
Anne          Dodsworth     02/07/1969 
Janet         Leverling     30/08/1963 
Laura         Callahan      09/01/1958 
Margaret      Peacock       19/09/1958 
Michael       Suyama        02/07/1963 
Nancy         Davolio       08/09/1968 
Robert        King          29/05/1960 
Steven        Buchanan      04/03/1955

How it Works.

At the beginning of the program, we opened the Employees Table, read the count of records in the Table, and re-dimensioned the user-defined variable PersonalRecord to reserve enough space to hold all the Employees records.
Next, we opened the Employees Table and loaded all the employees' data into the array.
We have sent a list of the unsorted data in the Debug Window.
The data is sorted on FirstName in Ascending Order in memory using the bubble sort method.
The sorted employee records are listed in the Debug Window.

Tip: You can change the sorting order to descending order by changing the logical operator < to > in the following statement:

If PRec(h).strFirstName < PRec(k).strFirstName Then
If PRec(h).strFirstName > PRec(k).strFirstName Then
As you can see, the data printing Routine is a separate Function Display Routine() and we have passed the whole Array of records to this program twice to print its contents into the Debug Window.

Budgeting and Control

a.p.r. pillaiAugust 09, 2010Budgeting, Forms, Process Controls 5 comments

Budgeting and Control.

The local Charity Organization for Children allocates funds for disbursement under various categories to eligible individuals or entities. The Accounts Section oversees these disbursement activities and ensures that the total payments made under each category do not exceed the allocated budget.

We have been asked to develop a computerized system to monitor the payment activity and verify that the cumulative value of all payments for a given category remains within the approved budget limit.

Below is a sample screen used for recording payment details:

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

As shown in the screen above, a Budget Amount of $10,000 has been allocated to the Poor Children’s Education Fund. This amount is distributed to eligible individuals or deserving institutions after careful evaluation of their cases. The payment records are entered in the datasheet subform below. Both the Main Form and the Subform are linked through the Category Code, an AutoNumber field in the main table.

When a new record is entered in the subform with a payment amount, the program calculates the total of all payment records, including the current entry, and compares it against the budget amount on the main form. If the total payment amount exceeds the allocated budget, an error message is displayed. In such cases, the program automatically deducts the excess amount from the current payment value.

After this adjustment, the focus is set to the Amount field, allowing the User to review the correction and take appropriate action if necessary.

In this example, users are not restricted from modifying the Budget Amount. However, the field can be locked immediately after a new main record is created for the budget value. If authorized modifications are required at a later stage, special access rights can be granted to designated Users through Microsoft Access Security features. For the time being, let us keep aside the security aspect; let us take a closer look at the design and implementation of the datasheet subform and the associated procedures.

An image of the Payment Record Sub-Form Data Sheet Design View is given below:

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

A TextBox with an Active Record not yet saved.

We created a Text Box in the Subform Footer Section with an expression to calculate the total of all payment records for the current category, excluding the current new record. This happens because the Sum() function does not include the new record value until it is saved in the table.

For example, the Text Box expression:

=Sum([Amt])

will correctly total all saved records. Although this control is not visible in Datasheet View, it can still be referenced in VBA procedures. (For additional techniques with Datasheet Forms, see the article Event Trapping and Summary on Datasheet.)

To include the value of the current (unsaved) record in the total, we can read it directly from the field (Me![Amt]) and add it to the result of the Sum() function. This gives us the Total of all disbursement records, including the current entry.

We can then compare this calculated total against the Budget Amount on the main form before accepting the new record. If the total exceeds the budget, the program can alert the user. This ensures that no payment entry pushes the cumulative disbursement beyond the allocated amount.

The Sub-Form Module Code.

The VBA Program Code written in the Sub-Form Module is given below:

Option Compare Database
Option Explicit
'Gobal declarations
Dim Disbursedtotal As Currency, BudgetAmount As Currency, BalanceAmt As Currency
Dim errFlag As Boolean, oldvalue As Currency

Private Sub Amt_GotFocus()
'Me!TAmt is Form Footer Total except the new record value
Disbursedtotal = Nz(Me!TAMT, 0)
BudgetAmount = Me.Parent!TotalAmount
oldvalue = Me![Amt]
End Sub

Private Sub Amt_LostFocus()
Dim current_amt As Currency, msg As String, button As Long

On Error GoTo Amt_LostFocus_Err
Me.Refresh
'add current record value to total and cross-check
'with main form amount, if the transactions exceed
'then trigger error and set the focus back to the
'field so that corrections can be done
current_amt = Disbursedtotal + Nz(Me!Amt, 0)
BalanceAmt = BudgetAmount - current_amt
errFlag = False
If BalanceAmt < 0 And oldvalue = 0 Then
    errFlag = True
    button = 1
        GoSub DisplayMsg
ElseIf oldvalue > 0 Then
    current_amt = (Disbursedtotal - oldvalue) + Nz(Me!Amt, 0)
    BalanceAmt = BudgetAmount - current_amt
    If BalanceAmt < 0 Then
        errFlag = True
        button = 1
          GoSub DisplayMsg
    End If
Else
    Me.Parent![Status] = 1
End If

Amt_LostFocus_Exit:
Exit Sub

DisplayMsg:
    msg = "Total Approved Amt.: " & BudgetAmount & vbCr & vbCr & "Payments Total: " & current_amt & vbCr & vbCr & "Payment Exceeds by : " & Abs(BalanceAmt)
    MsgBox msg, vbOKOnly, "Amt_LostFocus()"
Return


Amt_LostFocus_Err:
MsgBox Err.Description, , "Amt_LostFocus()"
Resume Amt_LostFocus_Exit
End Sub

Private Sub Form_Current()
Dim budget As Currency, payments As Currency

On Error Resume Next

budget = Me.Parent.TotalAmount.Value

payments = Nz(Me![TAMT], 0)

If payments = budget Then
 Me.AllowAdditions = False
Else
  Me.AllowAdditions = True
End If

End Sub

Private Sub Remarks_GotFocus()
If errFlag Then
  errFlag = False
  Me![Amt] = Me![Amt] + BalanceAmt
  BalanceAmt = 0
  Me.Parent![Status] = 2
  Me.Amt.SetFocus
End If

End Sub

Performing Validation Checks.

During data entry in the Payment Subform, if the cumulative value of all payment records reaches the allocated Budget Amount, the form will prevent adding any more payment records. However, existing payment records may still be opened and edited.

Similarly, when any Budget Category record becomes current on the Main Form, the program checks whether the total of its related payment records already equals the budgeted amount. If this condition is met, the Payment Subform is locked against new entries, but existing payment records remain editable.

The following VBA procedure, written in the Main Form’s module, enforces this rule and ensures that users cannot enter payment records once the budget is fully utilized:

Main Form Module Code.

Option Compare Database

Private Sub cmdClose_Click()
DoCmd.Close
End Sub

Private Sub Form_Load()
DoCmd.Restore
End Sub

Private Sub Form_Current()
Dim budget As Currency, payments As Currency
Dim frm As Form
On Error Resume Next

Set frm = Me.Transactions.Form
budget = Me!TotalAmount
payments = Nz(frm![TAMT], 0)

If payments = budget Then
 frm.AllowAdditions = False
Else
  frm.AllowAdditions = True
End If

End Sub