So how does all this help us? If you remember, our original mission in this chapter was to produce intelligent navigation buttons. That is to say, we need to create navigation buttons that disable themselves when they are unavailable. How can we do this? Well, first of all we'll show you the answer, and then we'll explain how it works.
Open the frmCompany form in Design view.
Make sure that the form's properties are visible by double-clicking on the form selector at the top left, where the rulers meet:
Open the form's Current event procedure by clicking on the builder button to the right of the On Current property and selecting Code Builder :
Add the following code to the form's Current event procedure:
Private Sub Form_Current() 'If the form is on a new record, we should disable the Next button. 'To do this we have to shift the focus first. If there is 'a value in CompanyID, we must enable the button. If Me.NewRecord = True Then cmdPrevious.SetFocus cmdNext.Enabled = False Else cmdNext.Enabled = True End If End Sub
Switch back to Access by hitting Alt+F11 . Close the frmCompany form and save it when prompted. When you open the form again, what do you know! You've got an 'intelligent' Next button! When you get to the end of the records (by hitting the New button, for example), the Next button becomes disabled. However, clicking the Previous button enables the Next button again:
How It Works
Well, it looks simple enough, but bear in mind that we are only dealing with one button here and in fact it's the easiest one to handle! The first thing to note is that we have used the On Current event property. We need to enable or disable the button as soon as the form is opened, and then whenever the user moves from one record to another. Looking through the list of events, there is only one that fits the bill, and that is the On Current event. This event occurs whenever a form is opened and then when moving between records on that form the very times that we need to check whether the button should be enabled or disabled.
After deciding which event handler to use, we must determine what rules we will use for enabling and disabling the Next button. Well, we want the Next button disabled only when the user cannot move any further forward through the records. That means that the Next button should be disabled when the user is in a new (blank) record.
Bear in mind that Access provides a new blank record at the end of every updateable table or form. For now, we will allow the user to move to this record so that they can enter details of a new company. We will modify this behavior a little in Chapter 7 to cater for situations where the user cannot add new records.
If we are on a new record, the Next button should be disabled. If not a new record, the button should be enabled. We can determine whether or not the form is currently on a new record by inspecting the value of the form's NewRecord property. This property returns True if the form is on a new record and False otherwise :
If Me.NewRecord = True Then
The Me keyword simply indicates to Access that we want to use the current object (which is our frmCompany form).
However, it's not quite that easy we can't just disable the Next button directly because it has the focus it has just been clicked. Access does not allow us to disable an item that has the focus, and if we try to do so we'll get a run-time error. So first we must move the focus somewhere else, and where better than to the Previous button. This is likely to be the one you want to use next anyway. To set the focus to a control, we just call its SetFocus method:
If Me.NewRecord = True Then cmdPrevious.SetFocus cmdNext.Enabled = False
You'll notice that to enable or disable a control, we just set its Enabled property to True or False .
As it stands, the button will be disabled when we find ourselves in a new record (or if there are no records in the form when it opens) but it will also remain grayed out when we scroll back to an existing record. We must re-enable it each time the current record is not a new one
Else cmdNext.Enabled = True End If
This code uses the If...Then...Else construct which may be new to you. This is pretty intuitive, but in case you aren't familiar with branching statements, they are explained in more detail in Chapter 4.