Navigate a Recordset With a Userform - Part II
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
1. The TextBoxes need to show the current record of the Recordset
2. 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
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.
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.
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:
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.
Rather than using the Tag property, why not name the textboxes with names such as txtField1, txtField2, etc.
Then, if you have a variable that has the number of fields (nine, in this case). Then you can just iterate through using Controls(”txtField” & i) ‘(where i is the counter variable). You wouldn’t have to parse to get the field number that way either.
Of course, the textbox names don’t have as much meaning that way.
“Of course, the textbox names don’t have as much meaning that way.”
That’s pretty much why I use the tags. Although this example doesn’t really use the textboxes, in a more complex form where you need to validate entries, I would rather see tbxLastName_AfterUpdate than txtField13_AfterUpdate. More overhead in exchange for better self-documentation, I guess.
What would probably be even better is to use the actual Recordset field name in the tag.
I tried using you recordset navigation for setting up a userform, but it did not work. The command ADO was not recognised. Can you please help or direct me to a resource that I can use to navigate through cells in a worksheet using the userform.
Thank You
Imraan: Make sure you set a reference to the ADO object libarary. In the VBE, go to Tools > References and check Miscrosoft ADO x.x Library.