Creating the User Interface

Implementing our custom CEmployee object requires that you design a form to enter and manipulate employee data.

┬ęCustom Project 8-1 (Step 7): Designing a User Form

1. In the Database window, click the Forms object button and double-click Create form in Design View.

2. Save the form as frmEmployeeSalaries.

3. Use the toolbox to place controls on the form as shown in Figure 8-1.

Introduction to Access 2003 VBA Programming

Create Custom Input Forms Powershell
Figure 8-1: This form demonstrates the use of the CEmployee custom object. 4. Set the following properties for the form's controls:

Object

Property

Setting

Labell

Caption

Last Name

Text box next to the Last Name label

Name

txtLastName

Label2

Caption

First Name

Text box next to the First Name label

Name

txtFirstName

Label3

Caption

Salary

Text box next to the Salary label

Name

txtSalary

Framel (the first frame)

Name Caption

frChangeSalary Salary Modification

Text box in the frame titled "Salary Modification"

Name

txtRaise

Option button 1

Name Caption

optPercent Percent

Option button 2

Name Caption

optAmount Amount

Frame2 (the second frame)

Name Caption

frSalaryMod Change the Salary for

Option button 3

Name Caption

optSelected Selected Employee

Option button 4

All Employees

Row Source Type Column Count Column Widths

lboxPeople Value List 4

0.5";0.9";0.7";0.5"

Command button l

Name Caption

cmdAdd Add

Part I

Object

Property

Setting

Command button 2

Name

cmdClose

Caption

Close

Command button 3

Name

cmdUpdate

Caption

Update Salary

Command button 4

Name

cmdDelete

Caption

Delete Employee

┬ęCustom Project 8-1 (Step 8): Writing Event Procedures

Now that the form is ready, you need to write few event procedures to handle various events, such as clicking a command button or loading the form.

1. Activate the Code window behind the form by choosing View | Code.

2. Enter the following variable declarations at the top of the form's Code window:

Option Explicit ' variable declarations Dim choice As Integer Dim amount As Long

3. Type the following UserForm_Initialize procedure to enable or disable controls on the form:

Private Sub UserForm_Initialize() txtLastName.SetFocus cmdUpdate.Enabled = False cmdDelete.Enabled = False lboxPeople.Enabled = False frChangeSalary.Enabled = False frChangeSalary.Value = 0 frSalaryMod.Enabled = False frSalaryMod.Value = 0 txtRaise.Enabled = False txtRaise.Value = "" End Sub

4. Call the UserForm_Initialize procedure from the Form_Load event procedure:

Private Sub Form_Load()

Call UserForm_Initialize End Sub

5. Enter the following cmdAdd_Click procedure to add the employee to the collection:

Private Sub cmdAdd_Click() Dim strLast As String Dim strFirst As String Dim curSalary As Currency

' Validate data entry

If txtLastName.Value = "" Or txtFirstName.Value = "" Or _ txtSalary.Value = "" Then

Introduction to Access 2003 VBA Programming

MsgBox "Enter Last Name, First Name and Salary." txtLastName.SetFocus Exit Sub End If

If Not IsNumeric(txtSalary) Then

MsgBox "You must enter a value for the Salary." txtSalary.SetFocus Exit Sub End If

If txtSalary < 0 Then

MsgBox "Salary cannot be a negative number." Exit Sub End If

' assign text box values to variables strLast = txtLastName strFirst = txtFirstName curSalary = txtSalary

' enable buttons and other controls cmdUpdate.Enabled = True cmdDelete.Enabled = True lboxPeople.Enabled = True lboxPeople.Visible = True frChangeSalary.Enabled = True frSalaryMod.Enabled = True txtRaise.Enabled = True txtRaise.Value = ""

' enter data into the CEmployee collection EmpOperations.AddEmployee strLast, strFirst, curSalary

' update list box lboxPeople.RowSource = GetValues

' delete data from text boxes txtLastName = "" txtFirstName = "" txtSalary = "" txtLastName.SetFocus End Sub

The cmdAdd_Click procedure starts off with validating the user's input in the Last Name, First Name, and Salary text boxes. If the user entered correct data, the text box values are assigned to variables strLast, strFirst, and strSalary. Next, a number of statements enable buttons and other controls on the form so that the user can work with the employee data. The following statement calls the AddEmployee procedure in the EmpOperations standard module and passes the required parameters to it:

EmpOperations.AddEmployee strLast, strFirst, curSalary

Once the employee is entered into the collection, the employee data is added to the list box with the following statement:

Part I

lboxPeople.RowSource = GetValues

GetValues is the name of a function procedure in the EmpOperations module. This function cycles through the CEmployee collection to create a string of values for the list box row source.

The cmdAdd_Click procedure ends by clearing the text boxes, then setting the focus to the Last Name text box so the user can enter new employee data.

Figure 8-2: The list box control displays employee data as entered in the custom collection CEmployee.

Employee Data Entry Vba Excel

Figure 8-2: The list box control displays employee data as entered in the custom collection CEmployee.

6. Enter the following cmdClose_Click procedure to close the form:

Private Sub cmdClose_Click() DoCmd.Close

End Sub

7. Write the Click procedure for the cmdUpdate button as shown below:

Private Sub cmdUpdate_Click() Dim numOfPeople As Integer Dim colItem As Integer

' validate user selections

If frChangeSalary.Value = 0 Or frSalaryMod.Value = 0 Then

MsgBox "You must choose the appropriate option button in " & vbCr _

& " the 'Salary Modification' and 'Change the Salary for' areas.", _ vbOKOnly, "Insufficient selection" Exit Sub

ElseIf Not IsNumeric(txtRaise) Or txtRaise = "" Then MsgBox "You must enter a number." txtRaise.SetFocus Exit Sub

ElseIf frSalaryMod.Value = 1 And lboxPeople.ListIndex = -1 Then

MsgBox "Click the employee name.", , "Missing selection in the List box" Exit Sub End If

If frSalaryMod.Value = 1 And lboxPeople.ListIndex = -1 Then MsgBox "Enter data or select an option." Exit Sub End If

Introduction to Access 2003 VBA Programming

' get down to calculations amount = txtRaise colItem = lboxPeople.Listlndex + 1

If frChangeSalary.Value = 1 And frSalaryMod.Value = 1 Then ' by percent, one employee choice = 1 numOfPeople = 1

ElseIf frChangeSalary.Value = 2 And frSalaryMod.Value = 1 Then ' by amount, one employee choice = 2 numOfPeople = 1

ElseIf frChangeSalary.Value = 1 And frSalaryMod.Value = 2 Then ' by percent, all employees choice = 1 numOfPeople = 2

ElseIf frChangeSalary.Value = 2 And frSalaryMod.Value = 2 Then ' by amount, all employees choice = 2 numOfPeople = 2 End If

UpdateSalary choice, amount, numOfPeople, colItem lboxPeople.RowSource = GetValues End Sub

When the user clicks the Update Salary button, the procedure checks whether the user selected appropriate option buttons and entered the adjusted figure in the text box. The update can be done for the selected employee or for all the employees listed in the list box control and collection. You can increase the salary by the specified percentage or amount. Depending on which options are specified, values are assigned to the variables choice, amount, numOfpeople, and colItem. These variables serve as parameters for the UpdateSalary procedure located in the EmpOperations module. The last statement in the cmdUpdate_Click procedure sets the row source property of the list box control to the result obtained from the GetValues function located in the EmpOperations standard module.

8. The Delete Employee button has the following cmdDelete_Click procedure:

Private Sub cmdDelete_Click()

' make sure an employee is highlighted in the list box control If lboxPeople.ListIndex > -1 Then

DeleteEmployee lboxPeople.ListIndex + 1 If lboxPeople.ListCount = 1 Then lboxPeople.RowSource = GetValues UserForm_Initialize

Else lboxPeople.RowSource = GetValues End If

Else

MsgBox "Click the item you want to remove." End If End Sub

Part I

The cmdDelete_Click procedure lets you remove an employee from the custom collection CEmployee. If you click an item in the list box and then click the Delete Employee button, the DeleteEmployee procedure is called. This procedure requires one argument that specifies the index number of the item selected in the list box. After the employee is removed from the collection, the row source of the list box control is reset to display the remaining employees. After removing the last employee from the collection, the UserForm_Initialize procedure is called to tackle the task of disabling controls that cannot be used until at least one employee is entered into the CEmployee collection.

Figure 8-3: The employee salary can be increased or decreased by the specified percentage or amount.

Important Dates Template

Figure 8-3: The employee salary can be increased or decreased by the specified percentage or amount.

9. Activate the EmpOperations module that you created earlier. The top of the module should contain the following lines:

Option Compare Database Option Explicit Dim emp As New CEmployee Dim CEmployee As New Collection

10. In the EmpOperations standard module, enter the following AddEmployee procedure.

Sub AddEmployee(empLast As String, empFirst As String, _ empSalary As Currency) With emp

.Id = SetEmpId .LastName = empLast .FirstName = empFirst .Salary = CCur(empSalary) If .Salary = 0 Then Exit Sub CEmployee.Add emp End With End Sub

The AddEmployee procedure is called from the cmdAdd_Click procedure attached to the form's Add button. This procedure takes three arguments.

Introduction to Access 2003 VBA Programming

When Visual Basic reaches the With emp construct, a new instance of the CEmployee class is created. The LastName, FirstName, and Salary properties are set with the values passed from the cmdAdd_Click procedure. The Id property is set with the number generated by the result of the SetEmpId function (see the following step). Each time VBA sees the reference to the instanced emp object, it will call upon the appropriate Property Let procedure located in the class module. (The next chapter demonstrates how to walk through this procedure step by step to see exactly when the Property procedures are executed.) The last statement inside the With emp construct adds the user-defined object emp to the custom collection called CEmployee.

11. In the EmpOperations standard module, enter the following SetEmpId function procedure:

Function SetEmpId() As String Dim ref As String

Randomize ref = Int((99999 - 10000) * Rnd + 10000) SetEmpId = ref End Function

This function's purpose is to assign a unique five-digit number to each new employee. To generate a random integer between two given integers where ending_number = 99999 and beginning_number = 10000, the following formula is used:

= Int((ending_number - beginning_number) * Rnd + beginning_number)

The SetEmpId function procedure also uses the Randomize statement to reinitialize the random number generator. For more information on using the Rnd and Integer functions, as well as the Randomize statement, search the online help.

12. Enter the GetValues function procedure as shown below. This function, which is called from the cmdAdd_Click, cmdUpdate_Click, and cmdDelete_Click procedures, provides the values for the list box control to synchronize it with the current values in the CEmployee collection.

Function GetValues() Dim myList As String myList = ""

For Each emp In CEmployee myList = myList & emp.Id & ";" & _ emp.LastName & ";" & _ emp.FirstName &";$"&_ Format(emp.Salary, "0.00") & ";"

Next emp

GetValues = myList End Function

Part I

13. Enter the following UpdateSalary procedure:

Sub UpdateSalary(choice As Integer, myValue As Long, _ peopleCount As Integer, colItem As Integer) Set emp = New CEmployee

If choice = 1 And peopleCount = 1 Then CEmployee.Item(colItem).Salary = _

emp.CalcNewSalary(1, CEmployee.Item(colItem).Salary, myValue) ElseIf choice = 1 And peopleCount = 2 Then For Each emp In CEmployee emp.Salary = emp.Salary + ((emp.Salary * myValue) / 100) Next emp

ElseIf choice = 2 And peopleCount = 1 Then CEmployee.Item(colItem).Salary = _

CEmployee.Item(colItem).Salary + myValue ElseIf choice = 2 And peopleCount = 2 Then For Each emp In CEmployee emp.Salary = emp.Salary + myValue Next emp

Else

MsgBox "Enter data or select an option." End If End Sub

The UpdateSalary procedure is called from the cmdUpdate_Click procedure, which is assigned to the Update Salary button on the form. The click procedure passes four parameters that the UpdateSalary procedure uses for the salary calculations. When a salary for the selected employee needs to be updated by a percentage or amount, the CalcNewSalary method residing in the class module is called. For modification of salary figures for all the employees, we iterate over the CEmployee collection to obtain the value of the Salary property of each emp object, then perform the required calculation by using a formula. By entering a negative number in the form's txtRaise text box, you can decrease the salary by the specified percentage or amount.

14. Enter the DeleteEmployee procedure as shown below:

Sub DeleteEmployee(colItem As Integer) Dim getcount As Integer

CEmployee.Remove colItem End Sub

The DeleteEmployee procedure uses the Remove method to delete the selected employee from the CEmployee custom collection. Recall that the Remove method requires one argument, which is the position of the item in the collection. The value of this argument is obtained from the cmd-Delete_Click procedure. The class module procedures were called from the standard module named EmpOperations. This was done to avoid creating a new instance of a user-defined class every time we needed to call it.

Introduction to Access 2003 VBA Programming

+2 -2

Responses

Post a comment