Basic VBA Programs Using Forms


OK, for the past 7 chapters, you have gotten a lot of theory. Now it is time to put some of it to the test and actually do some coding. We are going to begin by setting up some simple form procedures. This will give you a chance to get your toes a bit wet before moving to deeper parts of the VBA pool.

The variations of the following code examples could be endless, but it is my hope that you will have enough examples to experiment on your own.

Setting a Focus

When you set the focus, it means that you are transferring control to one of the form’s controls (buttons, text fields, combo boxes, etc.) Let’s assume that you want to set the focus to the last name field when you open a form.

Our sample database (available on www.osborne.com) contains a form called frmCustomer. Let’s go ahead and open that in Design View, as shown in Figure 8-3. Notice that the mouse pointer is pointing to the small black square located in the upper-left corner of the form.

click to expand
Figure 8-3: frmCustomer in Design View

Right-click on the square, select Properties, and select the Event tab. You want the focus to be set on the last name when the form opens. For that reason, select the OnOpen event and click on the Builder button shown in Figure 8-4.


Figure 8-4: Event tab and Code Builder button

Select the Code Builder option. When you select OK, you will be brought to the VBA Editor with a new form module open and ready to go, as Figure 8-5 shows. Notice the lines:

Private Sub Form_Open(Cancel As Integer)
End Sub

click to expand
Figure 8-5: The VBA Editor with the form module open

This means that the event procedure Form_Open is already set up. In addition, if you look in the Project Explorer, you will see that the module for frmCustomer is already named and listed:

Now click between the opening and closing lines of the procedure and press the SPACEBAR three times (indenting makes for clearer code). Type docmd followed by a dot (the period key). Auto List Members should pop up, as shown here:

We are going to use the GoToControl method. (You can tell it is a method by the icon to its left in the Auto List Members list.) You can select it by either double-clicking on it or pressing the SPACEBAR.

You now need to add the name of the control that you want to set the focus to. This is done as a string, which means that you have to enclose the argument in quotation marks. If you are new to VBA programming, you might make a beginner's mistake and complete the line as follows:

DoCmd.GoToControl "txtCustLastName"

When you go to run the form (by opening it), you will end up with the following message:

click to expand

Wait! What’s wrong? If you looked at the table structure, you would see that obviously it had a field called txtCustLastname. Now it says it does not exist?

The explanation is easy. A control is an object on the form. In this case, it is a text field that holds the information from the field txtCustLastName. It is not the table field itself, however. As a matter of fact, if you open the form in Design View (if it isn’t already), click on the control, and go to its properties, you will see the name of the control at the top, as shown in Figure 8-6.


Figure 8-6: Properties for ctlCustLastName

It is traditional to give the control the same name as the field it is associated with. You just change the three-letter prefix to ctl. It is strongly suggested that you go through the form and change the prefixes of the controls to ctl.

Based on that information, the focus will need to be on ctlCustLastName, and the code should be as follows:

Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToControl "ctlCustLastName"
End Sub

Save the code by clicking on the save icon in the VBA Editor, and then open the form. If all worked well, it should look like Figure 8-7.

click to expand
Figure 8-7: custForm with the focus set on the last name

There is another way you could have found the control’s name. Click on the left-hand drop-down list above the Code window:

click to expand

Finding a Record

You have changed the focus to the control for the last name. Let’s take this one step further by adding another line to this simple example:

Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToControl "ctlCustLastName"
DoCmd.FindRecord "Miller"
End Sub

If you now go ahead and save and then reopen the form, not only will the focus be placed on ctlLastName, but you are taken to the first record that has a last name of Miller. This is rather simplistic, admittedly, but it serves to illustrate some important concepts.

If you had reversed these two lines and opened the form, you would have received the following error message:

click to expand

The reason for this is that you have not built the sophistication into the code yet to know where the last name of Miller is unless you first physically put the focus on that field.

Now, this is all good, except, what if you need to find someone with a different last name from Miller? Here is where the concept of variables comes in. Remember, I stated earlier that a variable is something that can change. Keep that in mind as we progress.

You know that the parameter necessary to find the last name is a string. By setting a variable as type String, and then assigning a name to it, you could flow the code as follows.

Private Sub Form_Open(Cancel As Integer)
Dim strLastName As String
strLastName = "Miller"

DoCmd.GoToControl "ctlCustLastName"
DoCmd.FindRecord strLastName
End Sub

Notice in this case the string “Miller” is assigned to the string variable strLastName. That variable is then used as the parameter to find the record. Notice that strLastName did not need to be surrounded by quotation marks as the FindRecord argument. You need quotes only when using the string directly.

Now when you save the code and open the form, it has exactly the same effect as before. It takes you to Miller. That does not solve our problem of wanting to find last names other than Miller, though.

In earlier chapters, you used the InputBox function. This would be a good place to employ that again as follows:

Private Sub Form_Open(Cancel As Integer)
Dim strLastName As String
strLastName = InputBox("Enter a last name")

DoCmd.GoToControl "ctlCustLastName"
DoCmd.FindRecord strLastName
End Sub

The input from the InputBox is used to assign a value to strLastName. As a result, when you save the code and open the form, you are first presented with the input box:

click to expand

To summarize the important concepts presented here: First of all, you got to see an event, OnOpen, work. Second, you got to see how variables interact with the code. Third, you saw VBA interact with Access objects. Finally, you interacted with the variables through the use of an input box.

The Me Object

There is one interesting little variation that many programmers like to use to help clarify things a bit: the object Me. Me can be used to refer to whatever object you have open at the moment. For instance, if the frmCustomer object is open, Me will refer to that.

You can change the way you set the focus by adjusting the line:

DoCmd.GoToControl "ctlCustLastName"
If you delete that line and type Me instead, you get the following members:

click to expand

Notice that the controls of the present form are now listed as members. You can select ctlCustLastName and then type a dot again. Now you can select the SetFocus method. The end result should look like the following line of code:

Me.ctlCustLastName.SetFocus

The end result is exactly the same, and you now know that you are talking about this form.

You could also use the Me object to prevent any records from being changed. Add the following line above the SetFocus statement:

Me.AllowEdits = False

If you now open the form and select a last name, the form will not permit you to make any changes. This could be handy in situations in which you want to give someone access to the records, but not the ability to change them.

Validating a Record

What happens if you want to require a last name? Well, you can certainly set that as a required field in the table, but you can also do it in VBA. Before we look at the code, you must understand a bit about the pseudocode, or sequence of events, which must occur in VBA. First, there will be a test to see if the last name exists. If it does not, a message will appear telling you that it is missing. If it does exist, no message will appear. Either way, Access will save the record and move on to the next record (even if it is a blank one).

If the last name is missing, VBA must instruct Access to return to that record and set the focus on the control for the missing last name.

Understanding that sequence will make the code easy to follow. When you save a record, it is called updating. You want the message to appear before the update sequence happens. As a result, the event you will handle will be BeforeUpdate.

As with OnOpen, go through the form’s properties, and code the event through the BeforeUpdate property. You should have the following procedure open as a result. (Note that if you are actually trying out this code, you may want to delete the previous OnOpen procedure; it could conflict with the following example.)

Private Sub Form_BeforeUpdate(Cancel As Integer)
End Sub

You will now use an If…Then statement to test whether txtLastName is null. This will be a good chance to see the If…Then structure at work.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(txtCustLastName) Then
MsgBox "Please enter a last name"
DoCmd.GoToRecord , , acLast
Me.ctlCustLastName.SetFocus
End If
End Sub

There are several important points here. First of all, the function IsNull is used to test txtCustLastName to see if it is empty. If it is, the message appears.

Notice the line:

DoCmd.GoToRecord , , acLast

GoToRecord is a method of the DoCmd object. There is a second optional argument, but you do not need it here. Many times, even though an argument is not used in VBA, you still need to leave a place setting in between the commas. Finally, acLast is an intrinsic constant, which was discussed earlier. The ac prefix means it is an intrinsic constant of Access.

Try saving the code and opening the form. Go to a new record, and enter it without a last name. When you go to move off the record, the preceding code should be triggered and the record should return with the focus set on the last name control.




Access VBA Programming
Microsoft Access VBA Programming for the Absolute Beginner
ISBN: 1598633937
EAN: 2147483647
Year: 2006
Pages: 214
Authors: Michael Vine

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net