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