In Part I, we created a Userform and added the necessary controls to it. In this post, we’ll build some special purpose procedures to handle the data and the controls on the form.
This form has six events that we need to worry about; Initialize (when the form opens), QueryClose (when it closes), and a click event for each of the four CommandButtons we added. QueryClose will be a clean-up event and not really related to these special purpose procedures. When one of the other five events fires, there are certain aspects of the form that need to be changed. Basically, they are
- The TextBoxes need to show the current record of the Recordset
- The form needs to prevent the user from making errors (like clicking cmdPrev (<) when they're on the first record.
All this stuff can be done from the event, but you’ll find that you are using very similar, if not the same, code over and over. For that reason, it’s best to separate out these aspect changes into thier own procedure.
There are two module level variables behind this Userform
|
1 2 |
Dim mADOCon As ADODB.Connection Dim mADORs As ADODB.Recordset |
I use module level variables because I want the Connection and Recordset objects to be ‘in scope’ while the Userform is open. ‘In scope’ means that they will be available to any procedure in the module.
The first procedure will populate the TextBoxes with the data from the current record of the Recordset.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private Sub FillTextBoxes() Dim cTxtBx As Control Dim lFldNo As Long For Each cTxtBx In Me.Controls If cTxtBx.Tag Like “Field*” Then lFldNo = Mid(cTxtBx.Tag, 6) cTxtBx.Text = mADORs.Fields(lFldNo) End If Next cTxtBx End Sub |
Remember when we used the Tag property in setting up the TextBoxes? Now we’ll see why. This procedure loops through all the controls on the form, and if the control has a Tag that looks like Field0, Field1, Field2, etc., it uses that field from the Recordset to populate the TextBox. When form starts up or when any of the buttons are clicked, the textboxes will need to change to reflect the event. So this sub will be called from the Initialise event and the click event of each of the four buttons. Those events will first, before calling this sub, set the current record.
One nice thing about this sub is that you can add and delete TextBoxes without changing the code. As long as you fill in the Tag properly, the TextBox will populate properly. It doesn’t check to make sure that the field actually exists, however. For instance, if you put Field10 in a Tag, but your Recordset only had 9 fields, you would have a mess. That’s definitely error checking that I would wan’t to build in to this code, but for simplicity sake, it’s omitted here.
The next sub controls the CommandButtons. If we’re on the first record, we don’t want to give the user the option of going to the first record or the previous record. They’re already on the first and there is no previous record. Similarly, when the current record is the last record, the Last and Next buttons should be disabled. Every time an event fires, we want to make sure that the buttons reflect the current state.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Private Sub DisableButtons(ParamArray aBtnTags() As Variant) Dim i As Long Dim ctl As Control For Each ctl In Me.Controls ctl.Enabled = True For i = LBound(aBtnTags) To UBound(aBtnTags) If ctl.Tag = aBtnTags(i) Then ctl.Enabled = False Exit For End If Next i Next ctl End Sub |
For this procedure, I used a ParamArray argument. You can send in the name of the button (or buttons or no buttons at all) that you want to disable. For instance, if I want to disable the First and Previous buttons, I would call this sub like this:
|
1 |
DisableButtons “ButtonFirst”, “ButtonPrev” |
Every button will be enabled (clickable by the user) except the buttons that I pass as arguments. If I call this procedure with no arguments, all the buttons will be enabled. It loops through all the controls on the form and compares their Tag property to the argument list. If it finds a match, the control is disabled. It doesn’t discriminate between TextBoxes, CommandButtons or any other type of control. You just have to be sure that you use the Tag properties judiciously, lest you disable a control you didn’t mean to.
In Part III, we’ll look at the Initialize and QueryClose events. These events will create and destroy the Connection and Recordset objects, respectively. We will also see our special purpose subs in action.
