Exercise 1
We are going to write some Visual Basic for Applications (VBA). VBA is a programming language that takes several courses to learn; so, we will not get carried away learning VBA but will build a little to demonstrate how VBA code is behind the operation of all the controls you have placed on your forms.
You can read Tutorial 11, pp. 554-581 to get an idea of VBA terminology but, again, don't get carried away as this subject is much different than the rest of Access and should be studied on its own.
We will add some more controls to our Company Employee form so that a user can enter an employee's last name and have Access search for that person's record without having to click through all the records.
Open the Company Employee form in design view. Open up some space below the tab control pages so you can work on the form's main page, but stay in the Detail section. Add three controls to your form:
A Label box: type some instructions to the user like, "Type a last name and click the button."
A Text box: go to the properties and in the Name property type findEmployee.
A Command Button: as you go through the wizard, choose the action Find Next, have the button say something like "Find Employee," and write findButton for the control name in the last wizard screen.
Make sure this button works with a hot key like all the others, preferable use the "F".
To open the VBA editor either: (1)go to the properties for the Find Employee button, click in the On Click property, click the ellipsis button (3 dots) to the right; or (2)click the Code toolbar button while in the form design screen.
Make sure you work only in the "Private Sub findButton_Click()" subroutine.
We hope your routine has the commands "Screen.PreviousControl.SetFocus" and "DoCmd.FindNext." Edit these two lines to read instead: DoCmd.GoToControl "LastName" (or whatever you have named your last name field), and DoCmd.FindRecord findEmployee (this is the name of the text box you specified above).
Let's add another line of code below the two above: findEmployee = Null. This will clear the text box after each search.
You should now be able to view your form, enter a last name and have Access find the record of the person you chose. Note, however, it will find only the first instance of a last name. We need more complex code to also add a first name.
From:
http://www.rrcc-online.com/~lenm/14501db11.htm