|< Day Day Up >|
Now that you have a blank UserForm open, let's look at some of these examples. Assume that you have two Excel workbooks that your company uses to produce expense reports: one used by the end user with drop-down boxes, list boxes, etc. on the worksheet to give a nice look and feel, and one is used by people who maintain the database for things such as the list of cost centers, approvers, etc. It is relatively easy to give this type of user the ability to add or edit records in the database.
To support these needs, let's make this UserForm a form that looks up a cost center record in the database. If it is an existing record, it gives you the ability to edit the cost center name, and if the center number does not exist, it gives you the ability to add the record to the database. To accomplish this, you need a text box for the lookup field, a text box for the center number from the database, a text box for the center name from the database, a button to perform the lookup, a button to complete the Add/Edit, and a button to cancel. Add the controls by clicking on the appropriate control on the control toolbox and then clicking and dragging the object to the appropriate size. Figure 10-2 shows what the completed form could look like. (I have the same label, Center Number, for the lookup value and the result of the lookup, but you could certainly give these different names.)
Figure 10-2. An example of a form that performs a basic lookup of a record and allows the user to edit a record or add a new record
Now that you have a form to use as the GUI, let's look at the code. The first button to look at is the Lookup button . If you double-click on the button, it brings up the code for the button. We need code (shown in Example 10-1) that checks whether the center number in the text box exists in the database. If the center number exists, bring the center number and center name across and fill in the boxes. If the number does not exist, prompt the user for a Yes/No, asking them whether they want to add the record. We need an ADO Connection, an ADO Recordset, an integer, and string variables to accomplish this. Go to Tools References to create a reference to the ADO Library. In this example, I use ADO 2.1, since it is likely that all users have that on their PC. Keep in mind that this is being built for the majority of users who may or may not have Access on their PC and may or may not be current with the latest versions of Microsoft updates. Also, note that the path and filenames used in this example will be different on your system.
Example 10-1. Lookup and add center code
Private Sub CommandButton1_Click( ) Dim adoconn As ADODB.Connection Dim adors As ADODB.Recordset Dim criteria As String Dim center As Long Dim centername As String Dim createchoice As Long Set adoconn = New ADODB.Connection adoconn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ "C:\BookInformation\Chapter10\Chapter10DB.MDB;" adoconn.Open Set adors = New ADODB.Recordset ' The text boxes are named LocalCenter for the number being entered ' by the user, DatabaseCenter for the center number being pulled from ' the database, and DatabaseCenterName for the center name being pulled ' from the database. You could have just as easily left them as ' TextBox1, TextBox2, and TextBox3, but this makes it easier to understand ' what each box represents. If IsNumeric(Me.LocalCenter.Value) Then center = CLng(Me.LocalCenter.Value) End If If Not IsNumeric(Me.LocalCenter.Value) Then MsgBox "You must enter a number for the Center Number to Lookup.", vbInformation GoTo closeout End If tryagain: adors.Open "Select CostCenter, CenterName from " & _ "tbl_CostCenters Where CostCenter = " & center, adoconn, adOpenStatic If adors.EOF And adors.BOF Then createchoice = MsgBox("Center does not exist, do you want to create " & _ "this center?", vbYesNo, "CreateCenter?") If createchoice = vbYes Then centername = InputBox("What Name would you like to give this center?", _ "Center Name", "") adors.Close adors.Open "tbl_CostCenters", adoconn, adOpenDynamic, adLockOptimistic adors.AddNew adors.Fields("CostCenter").Value = center adors.Fields("CenterName").Value = centername adors.Update adors.Close GoTo tryagain End If End If If Not adors.EOF Then adors.MoveFirst Me.DatabaseCenter.Value = adors.Fields("CostCenter").Value Me.DatabaseCenterName.Value = adors.Fields("CenterName").Value adors.Close End If closeout: adoconn.Close Set adors = Nothing Set adoconn = Nothing End Sub
There are a couple of items that should be very familiar to you by now, such as creating the connection to the database and opening the recordset. Notice the two lines that say TRyagain: and closeout:, which allow you to direct the flow of the code execution to those lines. For example, once the new record is added to the database, rather than having code that pulls the data again in that section, add the line that says GoTo tryagain to re-run the code it tried to pull from the database earlier. Provided that the add method worked, this should fill in the form with the data from the database. The other line that might be new to you is the IsNumeric function. Keeping in mind that the CenterNumber in the database is a long integer, we want to avoid errors of a user entering in anything other than a number. By checking that the field is a number, we also eliminate the possibility that the user leaves that field blank. To jazz up this code a little more, you could have it clear out that text box and move the focus to the center number entry by adding the following two lines prior to the GoTo closeout line:
Me.LocalCenter.Value = "" Me.LocalCenter.SetFocus
This shows the user exactly where she needs to enter the center number. This type of request normally doesn't come up during the design phase, but is often requested later. Keep your eye out for opportunities like this during programming.
Now that you see how to lookup and add a record, let's look at the other button that will update a record. In this button's code, first, make sure that the center exists. The user could have just typed in a center number and the center name and clicked Complete Edit. If the user knows which center he wants to update, you might want to allow this, so to avoid errors, make sure that it does exist first. You could also change the Enabled property of the DatabaseCenter textbox to False so that the user is forced to do a lookup. Either way works, so for Example 10-2, we test to make sure that the center number exists.
Example 10-2. Code for the complete edit button
Private Sub CommandButton2_Click( ) Dim adoconn As ADODB.Connection Dim adors As ADODB.Recordset Dim criteria As String Dim center As Long Dim centername As String Dim createchoice As Long Set adoconn = New ADODB.Connection adoconn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ "C:\BookInformation\Chapter10\Chapter10DB.MDB;" adoconn.Open Set adors = New ADODB.Recordset If IsNumeric(Me.DatabaseCenter.Value) Then center = CLng(Me.DatabaseCenter.Value) End If If Not IsNumeric(Me.DatabaseCenter.Value) Then MsgBox "Center must be a number, please use Lookup", vbInformation Me.LocalCenter.Value = "" Me.LocalCenter.SetFocus GoTo closeout End If adors.Open "tbl_CostCenters", adoconn, adOpenDynamic, adLockOptimistic adors.MoveFirst adors.Find "CostCenter = " & center, 0, adSearchForward If Not adors.EOF Then adors.Fields("CenterName").Value = Me.DatabaseCenterName.Value adors.Update End If If adors.EOF Then MsgBox "Center does not exist, please use Lookup", vbInformation Me.LocalCenter.Value = "" Me.LocalCenter.SetFocus GoTo closeout End If adors.Close closeout: adoconn.Close Set adors = Nothing Set adoconn = Nothing End Sub
There are really two things going on in this code: first, the code checks to ensure that the center number is, in fact, a number, and second, the code determines whether the center number exists. Since we have the add method as part of the lookup, the user is directed to look up the center if it does not exist. It is worth noting that you could force the user to look up the record first by locking out cells, but by doing it this way, you allow the user to update the centers where they know what they want to edit and avoid the lookup step. In real production code, you might want to trap other errors, such as the size of the center name field, etc.
The other piece of code that is necessary is the code for the Cancel button. In this example, the name of that button is CommandButton3 and its code is one line, shown in Example 10-3.
Example 10-3. Cancel button example
Private Sub CommandButton3_Click( ) Unload Me End Sub
If you have done some programming in Visual Basic, the Unload method should be familiar to you. You can also Unload other forms and objects. So, if you have multiple user forms open, you might have some code that runs at the very end to unload any forms left open. The Me keyword simply refers to the current open form (actually, to the current Class that is executing code, but in our examples it would only be referring to user forms). We have been using the Me keyword to refer to the names of the text boxes on the current form in the first two examples in this chapter.
|< Day Day Up >|