More Visual Basic Examples:

I am going to create a form that will have some code attached written in VBA (Visual Basic for Applications). The first thing I did was create a form. I wanted to put together the whole name on the form and I did it by concatenating the three parts. Note that if you went to builder it would have thrown in the table name [Payroll]![FirstName] - when tested, the name would not appear.

On this form that I am building I will not allow additions, so putting the name together will cause no problems. Notice that I am allowing editing and deletes.

The first check that I want to create is to make sure that the PayCode entry is either an H or a S. I decided to use the events that involve getting and losing focus. The code is:

Private Sub txtPayCode_LostFocus() 
     If txtPayCode = "H" Then 
          Forms!PayForm!txtPayHr.SetFocus 
     End If 
 End Sub 

 Private Sub txtSalary_GotFocus() 
     If txtPayCode <> "S" Then 
         MsgBox ("Pay Code must be S or H") 
         Forms!PayForm!txtPayCode.SetFocus 
     End If 
 End Sub

Now let me try to explain. When the Pay Code field which I have named txtPayCode loses focus, the entry will be checked. If the code is an H, then the focus will move to txtPayHr because there is no salary. If the code is not an H, then the focus will move to the next sequential text box which is txtSalary. Now I need to check and make sure that the code is indeed an S if the control got here. I do that test and display a message box and reset the focus back to txtPayCode if it is not a match. Remember, if it was H it already skipped this box so the only valid code here is S. This allows me to illustrate both the LostFocus and the GotFocus events.

Now I will look at the rest of the code that I eventually attached to this form - it is all based on the LostFocus and GotFocus events. The logic is moving the processing through the text boxes, checking for valid codes and appropriate pay information for the codes. It is also moving the focus as appropriate while the processing is happens. On the SetFocus command, I actually used the entire command which is Forms!PayForm!txtPayHr.SetFocus which means set the focus to the txtPayHr box on the PayForm which is on Forms.

 

 

Private Sub txtIdno_GotFocus() 
    txtWeeksPay = 0 
 End Sub 

 Private Sub txtPayCode_LostFocus() 
     If txtPayCode = "H" Then 
          Forms!PayForm!txtPayHr.SetFocus 
     End If 
 End Sub 

 Private Sub txtSalary_GotFocus() 
     If txtPayCode <> "S" Then 
         MsgBox ("Pay Code must be S or H") 
         Forms!PayForm!txtPayCode.SetFocus 
     Else 
         Forms!PayForm!txtWeeksPay.SetFocus 
     End If 
 End Sub 

   
 Private Sub txtWeeksPay_GotFocus() 
     If txtPayCode = "S" Then 
        If txtSalary > 0 Then 
            txtWeeksPay = txtSalary / 52 
        Else 
            MsgBox ("S Code requires a salary") 
            Forms!PayForm!txtSalary.SetFocus 
        End If 
     Else 
        If txtPayCode = "H" Then 
           If txtPayHr > 0 Then 
               If txtHoursWk > 40 Then 
                  txtWeeksPay = txtPayHr * 40 + (txtHoursWk - 40) * 1.4 * txtPayHr 
               Else 
                   txtWeeksPay = txtPayHr * txtHoursWk 
               End If 
           Else 
               MsgBox ("H Code requires pay per hour") 
               Forms!PayForm!txtPayHr.SetFocus 
           End If 
        Else 
           MsgBox ("Pay Code must be S or H") 
           Forms!PayForm!txtPayCode.SetFocus 
        End If 
     End If 
 End Sub