Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Scaling the Chart Object

Introduction

Normally, a Graph Chart is designed to the required size and formatting on a Form/Report. The design stays with its dimension for ever, unless the developer has second thoughts and modifies it.  But, there is a way to resize the Chart Object automatically when the user resizes the Chart Form Window.

This is true when the Microsoft Access Application window is maximized; the chart object dimension and font size changes according to the window size and screen resolution.

Click here to learn more about this method and to get the VBA Code. 

  1. MS-Access and Graph Charts
  2. MS-Access and Graph Charts-2
  3. Working With Chart Object in VBA
  4. Column Chart and VBA
  5. Pie Chart Object and VBA
  6. Missing Lines in Line-Chart
  7. Pie Chart and Live Data on Form
  8. Scaling Chart Object
  9. Cross-Tab Union Queries for Chart
Share:

User and Group Check

Introduction.

In a secured Database; basic access rights to Objects like Tables, Forms, Queries and Reports are defined for specific Workgroups/Users as a one time exercise. This takes effect automatically when a User belongs to a particular Workgroup accesses the Database Objects.

For example: Assume that the Employees Table is set with only Read Data permission for GroupA Workgroup.  When a User belongs to GroupA opens the Employees Form with the Employees Table as Record Source or opens the Table directly, he cannot execute Update/Insert/Delete Data operations on the Table. 

But, if we want to make this scenario little more flexible like allowing to Update data then this can be enabled on the User and Group Permissions Control under Security of Tools Menu.

In this case all Users belongs to GroupA Workgroup can Edit and Update all Data Fields of the Employees Table.  Normally, Users are never allowed to open Tables directly but through Data Entry/Edit/Display Forms so that the Developer have more control on the Users.

When we assign Update Data permission all data fields can be modified/updated by Users.  But, if we want to prevent the Users from making changes to certain fields; it cannot be done through the normal security implementation method explained above.


Field-Level Security Implementation.

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

  1. When the Employees Form is open by the User for normal operations we can get the User Name with the use of CurrentUser() Function.
  2. Next step is to check whether this User belongs to the GroupA Workgroup or not.
  3. If so then lock the BirthDate and HireDate fields so that the current user is prevented from making changes to these two fields.

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 GroupA Workgroup then the BirthDate and HireDate data fields are locked on the Form through the Form_Load() Event Procedure so that the current user cannot edit the contents of these fields.
  3. If the User belongs to some other Workgroup then the above fields are unlocked and allowed to edit/update.

The Demo Run.

To try this out:

  1. Import the Employees Table and Employees Form from the sample database C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb.
  2. Open and 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 BirthDate and HireDate Fields.

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

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 above Subroutine. By default you will be logged in as Admin User, as a member of the Admins Workgroup. This will lock both the above test fields from editing when the Employees Form is open.

Technorati Tags:
Share:

User Defined Data Type

Introduction.

VBA have several predefined data types like Integer, String, Date and so on to use in Programs.  These can be used to hold only one type of data in them.  Integer Variable can hold Numeric Values ranging from -32768 to +32767 and String Type stores Alpha-Numeric Values and so on.

But, Programmers can define their own data Type with a mix of all these predefined data types and use it in their programs.  We are going to try this out 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 Objects dropdown list
    • Double-click on an existing Module or select Create Menu.
    • Select 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 F5 Key to run the Code.
  5. Key in name of the Employee, Gross Pay and Tax Rate Values when prompted for them.

The output display of the program  is shown below:


The Type Declaration and Properties.

Let us examine the above Code.  The User defined data type declaration is made at 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 i.e. we can declare a variable using the new data type in Standard Modules and Class Modules as well.  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 element's name of the new data type should also follow the normal variable naming conventions.

We have declared a Variable NetWages (you can take NetWages as an Object having several properties that can be set with 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; both separating 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 Name, Grosspay, Tax Rate and calculate the Tax Value, Net Payable amount and set the Tax Paid flag, if Tax Rate is a non-zero value.

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 above 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 Type 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 into 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 to 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 Type.

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

1. Import the Employees Table from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb 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 part of the program we have opened the Employees Table, read the count of records in the Table and accordingly we have re-dimensioned the user-defined variable PersonalRecord to reserve enough space to hold all the Employees record.
  2. Next we have opened the Employees Table and loaded all the employees data into the array.
  3. We have sent a listing of the unsorted data into the Debug Window.
  4. The data is sorted in Ascending Order on FirstName in memory using the Bubble-Sort method.
  5. The sorted employee records are listed in the Debug Window again.

Tip:  You can change the sorting order in 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.

Share:

Budgeting and Control

Introduction

The local Charity Organization for Children allocate funds for disbursement to eligible individuals or entities under various categories.  Accounts Section supervises the disbursement activities.  It is part of the responsibility of Accounts Section to ensure that the total value of all payments of a particular category is not exceeding the allocated Amount.

We have been asked to write a program to monitor the payment activity and see that Total Value of all payments stays within or up to the limit of the budgeted amount.  A sample screen, where payment details are recorded, is given below:

As you can see in the above screen; the Budget Amount $10,000/- is allocated to the Poor Children's Education Fund.  This Amount will be given away to eligible individuals or deserving institutions.  The amount given away after due consideration of the merit of their issues.  The payment records are entered into the datasheet sub-form below.  Both Forms are linked on the Category Code, which is an AutoNumber Field on the main Table. 

When a new record is entered into the sub-form with the payment value; the total value of all payment records, including the new record, are calculated and cross checked with the budget amount on the main form.  If the total of all payment values is not less than or equal to the budget amount then an error message is displayed. The excess value entered is deducted automatically from the current payment value to adjust it and make the total of all payment value equal to the budget amount.

The focus is set back to the amount field so that the User can take appropriate action.

In the above example the User is not prevented from making modifications to the Budget Amount.  But, this field can be locked immediately after creating a new main record with a value. If authorized changes to these records are required at a later period then special access rights can be given to some category of authorized Users. But, this part involves Microsoft Access Security implementation. Leaving that part aside we will take a closer look at the Datasheet Sub-form design and programs, where we have implemented the above procedure.

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


TextBox with Active Record not yet Saved Value.

We have created a Text Box at the Footer Section of the Form and written an expression to Sum() the Amount of all Payment records of the current Payment Category, except the current new record.  The new record value will not appear in the result of the Sum() Function till the record is saved in the Table. Even though the Text Box that we have created with the expression =Sum(me![amt]) in the  Footer Section of the Sub-Form,  It is not visible in Datasheet view, but we can refer it in Programs. For more tricks with Datasheet Form read the Article: Event Trapping and Summary on Datasheet

The current record value can be read directly from the form field (Me![Amt]) and add it to the result of the Sum() Function to get the Total Value of all records, including the record not yet saved to the Table.  This result can be checked with the Budget control value before the new record value is accepted in the current record.  If it is not acceptable then the User is alerted so that corrective action can be initiated.


The Sub-Form Module Code.

The VBA Program Code written on 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 on the Payment Sub-Form, if the total of all payment value equals to the Budget Amount then the Form will not allow to add more payment records. Exiting Payment records can be edited.

When any of the Budget Record on the Main Form become Current it checks whether the total Value of its payments records equal to the Budget Value, if so the Payment Form is locked and will not allow to add more records. But, the existing payment records can be edited.

The following VBA Code on the Main Form Module keep track of the above activity on the Main Form:


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



Share:

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms Functions How Tos MS-Access Security Reports Class Module msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array External Links Queries msaccess reports msaccess tips Accesstips Downloads Objects Property Collection Object Event Menus and Toolbars Controls MsaccessLinks Process Controls WithEvents Art Work msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Dictionary Object Form Report Calculation Command Button Data Emails and Alerts Query RaiseEvent Custom Functions Custom Wizards DOS Commands Data Type Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Item msaccessprocess security advanced Access Security Add Auto-Number Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Copy Excel Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database function ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

Opening Access Objects from Desktop

Frequently Used Methods. Set the Form Name in Display Form Option of Current Database in Access Options . BIG DEAL!, this is the first t...

Labels

Blog Archive

Recent Posts