Learn Binary Number System-2

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:

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

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

215	214	213	212	211	210	29	28	27	26	25	24	23	22	21	20
32,768	16,384	8,192	4,096	2,048	1,024	512	256	128	64	32	16	8	4	2	1
								1

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

127

-64

=63

215	214	213	212	211	210	29	28	27	26	25	24	23	22	21	20
32,768	16,384	8,192	4,096	2,048	1,024	512	256	128	64	32	16	8	4	2	1
								1	1

4. Repeat this method up to the unit Value position.

215	214	213	212	211	210	29	28	27	26	25	24	23	22	21	20
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:

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

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

1.   0+1 = 1

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

3.   next 1+1+1 carry = 3 (binary 11), put 1 and carry 2 to the next position

4.   next 1+1 carry = 2(binary 10), put 0 and carry 2 to the next position

5.   Next 1+1 carry = 2(binary 10), put 0 and carry 2 to the next position

6.   Next 1+1+1 carry = 3(binary 11), put 1 and carry 2 to the next position

7.   Next 1+1+1 carry = 3(binary 11), put 1 and carry 2 to the next position

8.   Next 1+1 carry = 2(binary 10), put 0 and carry 2 to the next position.

Example: Subtraction

11001110	206
-1111111	127
1001111	79

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

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

3.   0-1 cannot be done, so take 2 from the next position; now 2-1 = 1, and the next 3 positions become 0.

4.   Take the value from the 8th position and move forward to the 4 positions and to the 2 value position; 2-1 = 1

5.   1-1 = 0

6.   1-1 = 0

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

1. Learn the Binary Numbering System
2. Learn Binary Numbering System-2
3. Octal Numbering System
4. Hexadecimal Numbering System
5. Colors 24-Bits And Binary Conversion.
6. Create Your Own Color Palette

Read More

Learn Binary Number System

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.

1. The decimal Number System's Base value is 10, which is known as the Base-10 Number System.

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

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

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

106	105	104	103	102	101	100
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.

215	214	213	212	211	210	29	28	27	26	25	24	23	22	21	20
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: 

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. 

2. Subtract 8 from 10 and find the result.
   

   10

   -8

   ====

     2

   ====

3. Put 1 under the value 8 slot in the binary table.

 

215	214	213	212	211	210	29	28	27	26	25	24	23	22	21	20
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.

 

215	214	213	212	211	210	29	28	27	26	25	24	23	22	21	20
32,768	16,384	8,192	4,096	2,048	1,024	512	256	128	64	32	16	8	4	2	1
												1	0

 

4. Next, value 2 can be subtracted from the balance 2.

    2

   -2

   =====

     0

   =====

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

    

215	214	213	212	211	210	29	28	27	26	25	24	23	22	21	20
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:

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

215	214	213	212	211	210	29	28	27	26	25	24	23	22	21	20
32,768	16,384	8,192	4,096	2,048	1,024	512	256	128	64	32	16	8	4	2	1
															0

 

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

 5/2 = Quotient = 2, Remainder = 1

215	214	213	212	211	210	29	28	27	26	25	24	23	22	21	20
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

215	214	213	212	211	210	29	28	27	26	25	24	23	22	21	20
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

215	214	213	212	211	210	29	28	27	26	25	24	23	22	21	20
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:

1. Learn the Binary Numbering System
2. Learn Binary Numbering System-2
3. Octal Numbering System
4. Hexadecimal Numbering System
5. Colors 24-Bits And Binary Conversion.
6. Create Your Own Color Palette

Read More

User and Group Check

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:

1. When the Employee Form is opened by the User for normal work, we can get the User Name through the CurrentUser() Function.

2. The next step is to check whether this User belongs to the Group-A Workgroup.

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

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

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

3. If the user belongs to a different Workgroup, then the above fields are unlocked for editing/updating. 

The Demo Run.

To try this out:

1. Import the Employees Table and Northwind.mdb

2. Open an existing Standard VBA Module or create a new one.

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

4. Open the Employees Form in Design View.

5. Display the Form's VBA Module (View --> Code).

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

7. Open the Form in Normal View.

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

• Group Account Permissions with VBA
• Users Group and Permissions
• Creating a User Account with VBA
• Microsoft Date/Time Picker Control
• Form and Report Open Arguments

Read More

User Defined Data Type

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.

1. Open one of your existing databases or create a new one.

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

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

4. Place the insertion point somewhere in the middle of the WagesCalc() Function and press the F5 Key to run the Code.

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

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

2. Next, we opened the Employees Table and loaded all the employees' data into the array.

3. We have sent a list of the unsorted data in the Debug Window.

4. The data is sorted on FirstName in Ascending Order in memory using the bubble sort method.

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

• Users Group and Permissions
• Creating a User Account with VBA
• Microsoft Date/Time Picker Control
• Form and Report Open Arguments
• Indexing and sorting with VBA

Read More

Budgeting and Control

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

Demo Database Download.

Click the following link to download a Demonstration Database with the above Code.

Download Demo BudgetDemo.zip

• Creating a User Account with VBA
• Microsoft Date/Time Picker Control
• Form and Report Open Arguments
• Indexing and sorting with VBA
• Data Upload Controls

Read More

Change Secure DB to Unsecured

Change Secure DB to Unsecured.

It is generally uncommon to convert a secured database (implemented using Microsoft Access Security) into an unsecured one. However, this step may become necessary when you want to deploy or share a database in an environment that does not use security.

The first step in removing security from a database is to change the ownership of the database objects. By default, the User who creates the database is its Owner. The owner of an object has full access rights to it and can also assign permissions to other Users or Groups. Additionally, members of the Admins group possess these privileges.

Points to remember:

1. The User who attempts to convert the Database must have at least Read Permission to all Objects of the Database.

2. The Hidden Objects, if any, cannot be transferred into the target Database.

The Conversion Process.

The conversion process is simple and needs only a few steps.

1. Create a new Database.

2. Select File --> Get External Data --> Import.

3. Browse to the location of the Database you are trying to convert, and open it.

4. Select the Tables tab, and click the Select All Command Button to select all Tables to import.

5. Repeat this method for all Queries, Forms, Reports, Macros, and Modules.

   

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

6. If your Database has Custom Menus and Toolbars, then click on Options.

7. Select the Menus and Toolbars option.

8. Click OK to import all Objects (except the hidden objects) into the new Database.

At this stage, the access privileges of all objects in the new database are reset to their defaults. By default, every database user is a member of the Users group, and therefore has full access rights to all objects. This includes permissions to Open/Run, Read Design, Modify Design, and Administer permissions.

However, if you intend to share this unsecured database over a network, a few additional changes are required to enable concurrent use. Without these adjustments, the database will be limited to single-user access, preventing concurrent use. 

Changes for Multi-User Environment.

1. Select Tools --> Options --> Advanced.

2. Select Shared under the Default Open Mode Options Group.

3. Select Edited Record under the Default Record Locking Options Group.  Open Database using Record Level Locking Option is already in the selected state.

   

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

4. Click OK to close the Dialog Box.

5. Select Tools --> Security --> User and Group Permissions.

6. Select the Admin User Name under the User/Group Name List. 

   Why select Admin User Account? Because in an unsecured environment, it is a member of the Admins Group and logged in silently when no password is set.  MS Access will not prompt for User ID and Password.

7. Select the Database Object in the Object Type Control.

8. Unselect the Open Exclusive Option.

   

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

9. Click OK to close the Dialog Box.

Now, you have a new Database with no Security settings.  The old database will remain unchanged.

Technorati Tags: Microsoft Security

1. Microsoft Access Security
2. Convert MS-Access Old Versions
3. Convert Old Version Workgroup File
4. Share Previous Version Database

Read More

Date and Time Values

Date and Time Values.

The Date/Time Field in MS Access can store a date alone or a date and Time together. When you enter a date value (for example, 14/07/2010), Access actually stores it as a whole number: 40373. This number is the day count since 30/12/1899, where day 1 corresponds to 31/12/1899.

You can verify this by typing the expression Format(1, "dd/mm/yyyy") in the Immediate (Debug) window. To open it, press Alt+F11 to display the VBA editor, then press Ctrl+G. Pressing the Enter Key will display the result 31/12/1899.

Similarly, time values are stored as decimal fractions of a day. For example, midnight corresponds to 0.0, while 12:00 noon corresponds to 0.5. When combined, a date and time are held in memory as a single numeric value. Thus, 14/07/2010 at 12:00 noon is stored as 40373.5.

When calculating time differences across midnight, Access treats midnight as 24.00 rather than 0.00 to ensure accurate results for times before midnight.

Type ? Format(40373.5,"dd/mm/yyyy hh:nn:ss") and press the Enter Key.

Result:  14/07/2010 12:00:00

It is interesting to explore how 0.5 becomes 12:00:00 noon or how the System maintains Date and Time internally.

We know we have 24 Hours in a Day, or 24 x 60 = 1440 minutes in a Day, or 24 x 60 x 60 = 86400 Seconds in a Day.

Time Calculations.

That is, 1 Second = 1 Day/86400 Seconds = 0.000011574074074074 Day (we can take it rounded as 0.0000115741).  The end value of 074 is infinite. Again, 1 second is = 1000 Milliseconds.

From midnight onward, the time value increases by 0.0000115741 day per second. At 23:59:59 (one second before midnight), the stored value is approximately 0.9999906659 (representing 86,399 seconds). After one more second, the day value increases by 1, so the timestamp becomes 40374.0, representing 15/07/2010 at 00:00:00.

Each second is further subdivided into milliseconds, which can be accessed using the built-in Timer function.

For example, type the following direct command in the Debug Window:

? Timer

You will get output similar to the example below, depending on when you try this.

Result in Seconds: 68473.81

The .81 part is the time in milliseconds, and 68473 is the current time in seconds.

If you want to see this value in the Current Time of the Day format, type the following expression in the Debug Window and press the Enter key:

? format(68473.81/86400,"hh:nn:ss")

OR

? format(68473.81*0.0000115741,"hh:nn:ss")

The Value 68473.81 Seconds multiplied by 0.0000115741 gives the daytime value.

Result: 19:01:14

Using the Timer Function.

You can use the Timer() Function to build a delay loop in a Program. The code below delays the action by 5 seconds in program execution.

Public Function myFunction()
.
.
.
t = Timer
Do While Timer < t + 5 

  DoEvents

Loop
.
.
.
End Function

In the sample program shown earlier, the action is delayed by 5 seconds before the next statement execution after the loop.

We can retrieve the current system date and time using the built-in Now() function, while the Date() Function returns only the current system date.

When designing a table, you can set the Default Value property of a Date/Time field to either Date() or Now(). This automatically inserts the current date or the current date and time stamp, respectively, whenever a new record is added.

Now that we understand the basics of how time values are maintained internally, let’s look at some examples of the normal time-value conversions involving hours, minutes, and seconds.

Always use date and time values together when calculating time differences. If you are designing a table and performing time-based calculations, store both the date and time in a single Date/Time field, rather than in separate fields. This is especially important when the time period spans more than one day—for example, if work begins at 20:00 and ends at 04:00 the following day.

Now, consider a case where the values are stored separately:

• Date: 25/10/2020

• Time: 5 hours, 7 minutes, 15 seconds

How can these be combined and converted into the correct internal storage format that represents both the date and time together?

Date and Time Converting to store in the Date/Time Field.

The Date Number 25/10/2020 is 44129 is the internal value.

To cross-check whether the number is correct or not, type the following expression in the VBA Debug window and print the result:

? format(#25/10/2020#,"0")

Result: 44129

Now, all the time values (5 Hours, 7 Minutes, and 15 Seconds) we need to convert into seconds first, then add them all together and divide the result by 86400 or (24*60*60) to get the internal time format suitable to add to the date number so that the date and time value stays together in the Date/Time Field.

Now, let us do that as follows:

d_date = #25/10/2020#

hrs = 5
min = 7
sec = 15

h_seconds = hrs * 60 * 60
m_seconds = min * 60
Total = h_seconds + m_seconds + sec
? Total
Result: 18435 'seconds

'Convert to Time Value
timVal = Total/86400
OR
timval = Total/(24*60*60)

? timval
Result: 0.213368055555556

'Add TimeValue to d_date
d_date = d_date + timval

'Print the value of d_date in Date/time format
? format(d_date,"dd/mm/yyyy hh:nn:ss")

Result: 25/10/2020 05:07:15

You may convert the Hours, Minutes, and Seconds into Time Value format in a single expression:

timval = (((hrs*3600)+(min*60)+sec)/86400)
d_date = d_date + timval
OR

d_date = d_date + (((hrs*3600)+(min*60)+sec)/86400)

Date/Time Values change to Date, Hours, Minutes, and Seconds.

How do we separate them again into Date, Hours, Minutes, and Seconds, if we want them in that way again, from the Date/Time Values?

'The Date/Time Value
'we have the date+time in:
d_date = d_date + timval

'get date value separate
dt = int(d_date)

timval = d_date - dt

'get hours
hrs = int(timval*24)

'subtract hrs value from time value
timval = timval - ((hrs*3600)/86400)

'get Minutes
min = int(timval * (24*60))

'subtract Minutes from time value
timval = timval-(min*60/86400)

'get seconds
s = int(timval*86400+0.1)

The +0.1 added for the correction of the rounding Error of the actual value of

The Simple Recommended Method.

If you want to do it differently, here it is:

d = 1/86400 :'1 second value = in day value internaly
H = 5
M = 7
S = 15

t = ((H*3600)+(M*60)+S)/86400
? t
0.213368055555556

TotalSeconds = t/d

? TotalSeconds
 18435 

hr = int(TotalSeconds/3600)
? hr
5

bal = TotalSeconds-(hr*3600)
? bal
 435 

mi = int(bal/60)
? mi
 7 

 se = bal-(mi*60)
? s
 15 
? 
? format(t,"hh:nn:ss")

05:07:15

You can follow any method you feel comfortable working with, and I recommend the last one.

Earlier Post Link References:

• Microsoft Date/Time Picker Control
• Form and Report Open Arguments
• Indexing and sorting with VBA
• Data Upload Controls
• Auto Numbering in Query Column

Read More