Today you'll learn about the use of data-bound lists, combo boxes, and grids in your Visual Basic 4 database applications. Before Visual Basic 4.0, this was an arduous task that required a great deal of coding and program maintenance to perform. Now, Visual Basic 4.0 ships with the tools you need to add lists, combo boxes, and data grids to your project with very little coding needed.
Today, you'll learn how to add features to your data entry forms that provide pick lists that support and enforce the database relationships already defined in your data tables. You'll also learn the difference between data lists and combo boxes, and you'll learn where it's appropriate to use them.
We will also show you how to easily add a data grid to your form to show more than one record at a time in a table form. This grid can be used for display only, or for data entry, too. We'll show you how to decide which is the best method for your project.
After you learn how to use the data-bound list, combo box, and grid, you'll use them to create a data entry Subform that combines all three controls on a single form.
The data-bound list and combo controls are used in conjunction with the data control to allow you to display multiple rows of data in the same control. This provides you with a pick list of values displayed in a list or combo box. You can use these types of controls on your data entry forms to speed data entry, provide tighter data entry validation and control, and give users suggested correct values for the data entry field.
Setting up data-bound lists and combo boxes are a bit trickier than standard data-bound controls. But once you get the hang of setting up data-bound list and combo boxes, you'll want to use them in every data entry screen you can.
Although the data-bound list control looks like the standard list control, there are several differences between the two. The data-bound list control has six new properties that provide the data-binding aspects and are not found in the standard list control. The first two of these properties are the RowSource and ListField properties of the data-bound list control.
These two properties are used to bind the list control to a data control. Once these two properties are set, Visual Basic 4 automatically populates the list control for you when you open the data entry form.
Let's start a new project and illustrate the data-bound list control. Once you start the new project, you must make sure you have added the data-bound list controls to your project. Select Tools | Custom Controls item from the Visual Basic 4 main menu. Locate and select the Microsoft Data-Bound List Controls item. Your screen should look like the one in Figure 13.1.
Adding the data-bound list controls to your project.
Now you need to add the data-bound list control, a standard data control, and two labels and text boxes. Use Table 13.1 and Figure 13.2 as guides as you build your first data-bound list project. Make sure to save your work periodically. Save the form as CH1301.FRM, and the project as CH1301.VBP.
TIP: If you lay out the controls in the order they are listed in the table, you'll be able to use the down arrows of most of the property fields to get a selection list for the field names, and so on. This will save you some typing.
Table 13.1. The controls for the CH1301.VBP project.
| Controls | Properties | Settings |
|---|---|---|
| Form | Name Caption Height Left Top Width | frmCh1301 Data-Bound List Controls 2670 1215 1170 4995 |
| DataControl | Name Caption DatabaseName Height Left RecordsetType RecordSource Top Data1 Width | Data1 C:\TYSDBVB\CHAP13\CH1301.MDB 300 120 2—Snapshot ValidNames 1860 1875 |
| DBList | Name Height Left RowSource ListField Top Width | DBList1 1620 120 Data1 NameText 120 1875 |
| Label | Name Alignment 1 BorderStyle Caption Height Left Top Width | Label1 -Right justify 1—Fixed Single List Field: 300 2160 120 1200 |
| Label | Name Alignment BorderStyle Caption Height Left Top Width | Label2 1—right justify 1—fixed single Text: 300 2160 540 1200 |
| Text Box | Name Height Left Top Width | Text1 300 3540 120 1200 |
| Text Box | Name Height Left Top Width | Text2 300 3540 540 1200 |
| Command Button | Name Caption Height Left Top Width | cmdGetList &Get List 300 2160 1860 1200 |
Laying out the CH1301 form.
Notice that in the preceding table, we have added a single data control to open the database and create a Snapshot object of the ValidNames table. It's always a good idea to use Snapshot objects as the RowSource for data-bound lists and combo boxes. Snapshot objects are static views of the data set and, even though they take up more workstation memory than Dynaset objects, they run faster. Notice also that we set the ListField property of the data-bound list to NameText. This fills the control with the values stored in the NameText column of the data set.
Now you need to add two lines of code to the project. Open the cmdGetList_Click event and enter the following lines of code:
Private Sub cmdGetList_Click()
Text1 = DBList1.ListField
Text2 = DBList1.TEXT
End Sub
These two lines of code update the text box controls each time you press the GetList button on the form. That way you are able to see the current values of the ListField and Text properties of the data-bound list control.
Save the form as CH1301.FRM and the project as CH1301.VBP. Now run the project. When the form first comes up, you see the list box already filled with all the values in the NameText column of the data set (that is, the ListField we used for the DBList). Select one of the items in the list box by clicking on it. Now press the GetList button. You'll see the two text controls updated with the ListField and Text values of the list control. Your screen should look like the one in Figure 13.3.
Running the CH1301.VBP project.
The data-bound list control has two more properties that you need to know. These are the properties that you can use to create an output value based on the item selected from the list. The two properties are
Usually, data-bound lists present the user with a familiar set of names. The user can pick from these names, and then the program uses the selection to locate a more computer-like ID or code represented by the familiar name selected by the user. The table we have created for this example contains just such information.
Set the BoundColumn property of the data-bound list control to point to the NameID column of the ValidNames data set. Select the data-bound list control, then press F4 to bring up the property window. Now locate the BoundColumn property and set it to NameID.
Now add two more labels and text boxes to display the new properties. Do this by selecting the existing two labels and the two text controls all as a set. Then select Edit | Copy. This places the four selected controls on the Clipboard. Now select Edit | Paste from the Visual Basic 4 main menu. This places copies of the controls on your new form. Answer Yes to the prompts that ask if you want to create a control array. Set the caption properties of the two new labels to Bound Column: and Bound Text:. Use Figure 13.4 as a guide in laying out the new controls.
Adding new controls to the CH1301.VBP project.
Finally, modify the code in the cmdGetList_Click event to match the following code. This shows you the results of the new BoundColumn and BoundText properties.
Private Sub cmdGetList_Click()
Text1(0) = DBList1.ListField
Text2(0) = DBList1.TEXT
Text1(1) = DBList1.BoundColumn
Text2(1) = DBList1.BoundText
End Sub
Notice that we added the array references to the code to account for the new control arrays. Now save and run the project. When you select an item from the list and click the GetList button, you'll see the BoundColumn and BoundText properties displayed in the appropriate text boxes, as shown in Figure 13.5.
Displaying the new BoundColumn and BoundText properties.
NOTE: You can also activate the Get List event by entering cmdGetList_Click in the Dbl_Click event of DBList. The user can get the same results by selecting the command button, or by double-clicking on the item in the list. This type of call provides a quick way of adding functionality to your code. You don't need to enter or maintain the code in both events.
The data that is produced by the BoundText property can be used to update another column in a separate table. The easiest way to do this is to add a second data control and link the data-bound list control to that second data control. You can do this by setting the following two properties of the data-bound list control.
Now let's add a second data control to the form and a bound input control that will be updated by the data-bound list. First, add a data control. Set its DatabaseName property to C:\TYSDBVB\CHAP13\CH1301.MDB and its RecordSource property to Destination. Also, set the EOFAction property of the Data2 data control to AddNew. Now add a text control to the project. Set its DataSource property to Data2 and its DataField property to NameID. Refer to Figure 13.6 as a layout guide.
Adding a second data control and text control.
Before you save and run the project, set the DataSource and DataField properties of the data-bound list control. Set these to Data2 and NameID, respectively. This tells the list control to automatically update the Destination.NameID field. Now, each time a user selects an item in the list, and then saves the data set of the second control, the designated field of the second data set is automatically updated with the value in the BoundColumn property of the data-bound list.
Save and run the project. This time, select the first item in the list by clicking on it. Now click on the GetList button to bring up the list properties in the text boxes. Force the second data control to save its contents by repositioning the record pointer by clicking the left-most arrow to force the second data set to the first record in the set. You should now see that the second data set, Destination, has been updated by the value in the BoundColumn property of the data-bound list. Your screen should look like the one in Figure 13.6.
Do this a few times to add records to the Destination table. Also notice that each time you move the record pointer of the Destination table, the data-bound control reads the value in the bound column and moves the list pointer to highlight the related NameText field. You now have a fully functional data-bound list box!
The data-bound combo box works very much the same as the data-bound list control. The only difference is the way the data is displayed. The data-bound combo control can be used as a basic data entry text box with added validation. By allowing experienced users to type values they know are correct, they can speed up the data entry process. Also, new users are able to scan the list of valid entries until they learn them. The data-bound combo is an excellent data entry control.
Let's build a new project that shows how you can use the data-bound combo box to create friendly data entry forms. Start a new Visual Basic 4 project. Use Table 13.2 and Figure 13.7 as guides as you build your new form. Save your form as CH1302.FRM, and the project as CH1302.VBP.
Table 13.2. The controls for the CH1302.VBP project.
| Controls | Properties | Settings |
|---|---|---|
| Form | Name Caption Height Left Top Width | frmCh1302 Data Bound ComboBox 2500 2750 2500 3000 |
| DataControl | Caption DatabaseName EOFAction Height Left RecordsetType RecordSource Top Width | Name
dtaDestination Destination C:\TYSDBVB\CHAP13\CH1301.MDB 2—AddNew 300 120 1—Dynaset Destination 960 2535 |
| DataControl | Name Caption DatabaseName Height Left RecordsetType RecordSource Visible Width | dtaValidStates Valid States C:\TYSDBVB\CHAP13\CH1301.MDB 300 120 2—Snapshot "ValidStates" 0—False 2535 |
| DataControl | Name Caption DatabaseName Height Left RecordsetType RecordSource Top Visible Width | dtaValidNames Valid Names C:\TYSDBVB\CHAP13\CH1301.MDB 300 120 2—Snapshot ValidNames 1680 0—False 2535 |
| DBCombo | Name DataSource DataField Height Left RowSource ListField BoundColumn Top Width | DBCombo1 dtaDestination StateCode 300 120 dtaValidStates StateName StateCode 120 1200 |
| DBCombo | Name DataSource DataField Height Left Top Width RowSource ListField BoundColumn | DBCombo2 dtaDestination NameID 300 120 540 1200 dtaValidNames NameText NameID |
| Label | Name BorderStyle DataSource DataField Height Left Top Width | Label1 1—Fixed Single dtaDestination StateCode 300 1440 120 1200 |
| Label | Name BorderStyle DataSource DataField Height Left Top Width | Label2 1—Fixed Single dtaDestination NameID 300 1440 540 1200 |
Laying out CH1302.VBP project.
You need to add two lines of code to the project before it's complete. The following lines force Visual Basic 4 to update the form controls as soon as the user makes a selection in the combo box.
Private Sub DBCombo1_Click(Area As Integer)
Label1 = DBCombo1.BoundText
End Sub
Private Sub DBCombo2_Click(Area As Integer)
Label2 = DBCombo2.BoundText
End Sub
Save the form as CH1302.FRM and the project as CH1302.VBP. Now run the project and check your screen against the one in Figure 13.8.
Running the CH1302.VBP project.
You can make selections in either of the two combo boxes and see that the label controls get updated automatically. Also, you can move through the data set using the data control arrow buttons and watch the two combo boxes automatically update as each record changes.
The choice between list and combo controls depends on the type of data-entry screen you have and the amount of real estate available to your data entry form. Typically, use lists where you want to show users more than one possible entry. This encourages them to scroll through the list and locate the desired record. The data-bound list control doesn't allow users to enter their own values to the list. Therefore, you should not use the data-bound list control if you want to allow users to enter new values to the list.
The data-bound combo box is very good when you are short on form space. You can provide the functionality of a list box without using as much space. Also, combo boxes have the added benefit of allowing users to type in their selected values. This is very useful for users who are performing heads-down data entry. They type the exact values right at the keyboard without using the mouse or checking a list. Also, novices can use the same form to learn about valid list values without slowing down the more experienced users.
The data-bound grid control in Visual Basic 4.0 adds new power and flexibility to your database programs. You can now very easily provide grid access to any available database. You can provide simple display-only access for use with summary data and on-screen reports. You can also provide editing capabilities to your data grid including modify only, add rights, or delete rights.
It's really quite easy to create a data-bound grid form. First, start a new Visual Basic 4 project. Next, make sure you add the data-bound grid tool to your list of custom controls. To do this, select Tools | Custom Controls from the Visual Basic 4 main menu. Locate and select the Apex Data-Bound Grid Control. Your screen should resemble Figure 13.9.
Adding the data-bound grid control to your project.
Now drop a standard data control on the form. Place it at the bottom of the form. Set the DatabaseName property to C:\TYSDBVB\CHAP13\CH1303.MDB and the RecordSource property to HeaderTable. Now place the data-bound grid tool on the form and set its DataSource property to Data1. That's all there is to it. Now save the form as CH1303.FRM and the project as CH1303.VBP and run the project. Your screen should look like the one in Figure 13.10
Running the first data-bound grid project.
You can move through the grid by clicking on the left margin of the grid control. You can also move through the grid by clicking on the navigation arrows of the data control. If you select a cell in the grid, you can edit that cell. As soon as you leave the row, that cell is updated by Visual Basic 4. Right now, you cannot add or delete records from the grid. You'll add those features in the next example.
It's very easy to include add and delete capabilities to the data grid. Bring up the same project you just completed. Select the data grid control and press F4 to bring up the Properties Window. Locate the AllowAddNew property and the AllowDelete property and set them to True. You now have add and delete power within the grid.
Before you run this project, make two other changes. Set the Visible property of the data control to False. Because you can navigate through the grid using scroll bars and the mouse, you don't need the data control arrow buttons. Second, set the Align property of the grid control to Top. This forces the grid to hug the top and sides of the form whenever it is resized.
Now save and run the project. Notice that you can resize the columns. Figure 13.11 shows the form resized with several columns adjusted.
Resizing form and columns of a data grid control.
To add a record to the data grid, all you need to do is place the cursor at the first field in the empty row at the bottom of the grid and start typing. Visual Basic 4 creates a new line for you and allows you to enter data. Take note how the record pointer turns into a pencil as you type. Use Figure 13.12 as a guide. When you leave the line, Visual Basic 4 saves the record to the data set.
Adding a record to the data grid.
The problem with resizing the form at runtime is that the moment you close the form, all the column settings are lost. You can prevent this by resizing the form at design time. Select the data grid control and press the right (alternate) mouse button. This brings up the context menu. Select Retrieve Fields. This loads the column names of the data set into the grid control. Select Edit from this menu. Now you can resize the columns of the control. The dimensions of these columns are stored in the control and used each time the form is loaded.
You can modify the names of the column headers at design time by using the built-in tabbed property sheet. To do this, click the alternate mouse button while the grid control is selected. When the context menu appears, select Properties from this menu. You should now see a series of tabs that allow you to set several grid-level and column-level properties. (See Figure 13.13.)
Using the data grid tabbed properties page.
The data grid control has several unique events that you can use to monitor user actions in your graph. The following events can be used to check the contents of your data table before you allow the user to continue:
You can use the events listed here to perform field and record-level validation and force user confirmation on critical events, such as adding a new record or deleting an existing record. Let's add some code to the CH1303.VBP project to illustrate the use of these events.
First, add code that monitors the adding of new records to the grid. Select the grid control and open the DBGrid1_BeforeInsert event. Add the code in Listing 13.1.
Listing 13.1. Code to monitor addition of new records to a data bound grid.
Private Sub DBGrid1_BeforeInsert(Cancel As Integer)
'
' make user confirm add operation
'
Dim nResult As Integer
'
nResult = MsgBox("Do you want to add a new record?",
[ic:ccc]vbInformation + vbYesNo, "DBGrid.BeforeInsert")
If nResult = vbNo Then
Cancel = True ' cancel add
End If
End Sub
In Listing 13.1, you present a message to the user to confirm the intention to add a new record to the set. If the answer is No, the add operation is canceled.
Now let's add code that tells the user the add operation has been completed. Add the following code in the DBGrid1_AfterInsert event window.
Private Sub DBGrid1_AfterInsert()
'
' tell user what you just did!
'
MsgBox "New record written to data set!", vbInformation,
[ic:ccc] "DBGrid.AfterInsert"
End Sub
Now save and run the project. Go to the last row in the grid. Begin entering a new record. As soon as you press the first key, the confirmation message appears. (See Figure 13.14.)
Attempting to add a record to the grid.
Once you fill in all the columns and attempt to move to another record in the grid, you'll see the message telling you that the new record was added to the data set.
Now add some code that monitors attempts to update existing records. Add Listing 13.2 to the DBGrid1.BeforeUpdate event.
Listing 13.2. Code to monitor for attempted data updates.
Private Sub DBGrid1_BeforeUpdate(Cancel As Integer)
'
' make user confirm update operation
'
Dim nResult As Integer
'
nResult = MsgBox("Write any changes to data set?",
[ic:ccc] vbInformation + vbYesNo, "DBGrid.BeforeUpdate")
If nResult = vbNo Then
Cancel = True ' ignore changes
DBGrid1.ReBind ' reset all values
End If
End Sub
This code looks similar to the code used to monitor the add record events. The only thing different here is you that you force the ReBind method to refresh the data grid after the canceled attempt to update the record.
Now add the code to confirm the update of the record. Add the following code to the DBGrid1.AfterUpdate event.
Private Sub DBGrid1_AfterUpdate()
'
' tell 'em!
'
MsgBox "The record has been updated.", vbInformation, "DBGrid.AfterUpdate"
End Sub
Now save and run the project. When you press a key in any column of an existing record, you'll see the message asking you to confirm the update. When you move off the record, you'll see a message telling you the record has been updated.
Now add some events to track any attempts to delete existing records. Place Listing 13.3 in the DBGrid1.BeforeDelete event.
Listing 13.3. Code to track for record deletes.
Private Sub DBGrid1_BeforeDelete(Cancel As Integer)
'
' force user to confirm delete operation
'
Dim nResult As Integer
'
nResult = MsgBox("Delete the current record?",
[ic:ccc]vbInformation + vbYesNo, "DBGrid.BeforeDelete")
If nResult = vbNo Then
Cancel = True ' cancel delete op
End If
End Sub
Again, no real news here. Simply ask the user to confirm the delete operation. If the answer is No, the operation is canceled. Now add the code to report the results of the delete. Put this code in the DBGrid1.AfterDelete event.
Private Sub DBGrid1_AfterDelete()
'
' tell user the news!
'
MsgBox "Record has been deleted", vbInformation, "DBGrid.AfterDelete"
End Sub
Now save and run the project. Select an entire record by clicking on the left margin of the grid. This highlights all the columns in the row. (See Figure 13.15 Figure 13.15
Attempting to delete a record from the grid.
Several column-level events are available for the data grid. The following are only two of them.
NOTE: Refer to the Visual Basic 4.0 documentation for a list of all the events associated with the DBGrid control.
These events work just like the BeforeUpdate and AfterUpdate events seen earlier. However, instead of occurring whenever the record value is updated, the BeforeColUpdate and AfterColUpdate events occur whenever a column value is changed. This gives you the ability to perform field-level validation within the data grid.
Add some code in the BeforeColUpdate event to force the user to confirm the update of a column. Open the DBGrid.BeforeColUpdate event and enter the code in Listing 13.4.
Listing 13.4. Code to request confirmation on column updates.
Private Sub DBGrid1_BeforeColUpdate(ByVal ColIndex As Integer,
[ic:ccc] OldValue As Variant, Cancel As Integer)
'
' ask user for confirmation
'
Dim nResult As Integer
'
nResult = MsgBox("Write changes to Column", vbInformation + vbYesNo,
[ic:ccc] "DBGrid.BeforeColUpdate")
If nResult = vbNo Then
Cancel = False ' cancel change & get old value
End If
End Sub
Now add the code that tells the user the column has been updated as requested. Place the following code in the DBGrid1.AfterColUpdate event.
Private Sub DBGrid1_AfterColUpdate(ByVal ColIndex As Integer)
'
' tell user
'
MsgBox "Column has been updated", vbInformation, "DBGrid.AfterColUpdate"
End Sub
Save and run the project. Now, each time you attempt to alter a column, you are asked to confirm the column update. (See Figure 13.16.)
Updating a grid column.
You can also see a message when you leave the column telling you that the data has been changed.
Use the data grid to create one of the most common forms of data entry screens, the Subform. Subforms are data entry forms that actually contain two forms within the same screen. Usually, Subforms are used to combine standard form layout data entry screens with view-only or view and edit lists. For example, if you want to create a form that shows the customer information (name, address, and so on) at the top of the form and the list of invoices outstanding for that customer at the bottom of the form, you'd have a Subform type entry screen.
Typically, Subforms are used to display data tables linked via relationship definitions. In the case just mentioned, the customer information is probably in a single master table, and the invoice data is probably in a related list table that is linked via the customer ID or some other unique field. When you have these types of relationships, Subforms make an excellent way to present data.
If you spend much time programming databases, you'll meet up with the need for a good Subform strategy. Let's go through the process of designing and coding a Subform using Visual Basic 4 data-bound controls, especially the data grid.
For example, you have a database that already exists, CH1303.MDB, that contains two tables. The first table is called Header. It contains all the information needed to fill out a header on an invoice or monthly statement, such as CustID, CustName, Address, City, State, and Zip. There is also a table called SalesData. This table contains a list of each invoice currently on file for the customer, and it includes the CustID, Invoice Number, Invoice Description, and the Invoice Amount. The two tables are linked via the CustID field that exists in both tables. There is a one-to-many (Header-to-SalesData) relationship defined for the two tables.
You need to design a form that allows users to browse through the master table (Header), displaying all the address information for review and update. At the same time, you need to provide the user with a view of the invoice data on the same screen. As the customer records are changed, the list of invoices must also be changed. You need a Subform.
Start a new project in Visual Basic 4. Lay out the Header table information at the top of the form and the SalesTable information in a grid at the bottom of the form. You need two data controls, one for the Header table and one for the SalesTable, one grid for the sales data, and several label and input controls for the Header data. Use Table 13.3 and Figure 13.17 as guides as you lay out the Subform.
The controls table and Figure 13.17 contain almost all you need to design and code the Visual Basic 4 Subform. Notice that all the text box and label controls have the same name. These are part of a control array. Lay out the first label/text box pair. Then use the alternate mouse button to copy and repeatedly paste these two buttons until you have all the fields you need for your form.
TIP: Not only is it easier to build forms using data controls because you save a lot of typing, but it also saves workstation resources. To Visual Basic 4, each control is a resource that must be allotted memory for tracking. Control arrays are counted as a single resource, no matter how many members you have in the array.
Table 13.3. The controls for the Subform project.
| Controls | Properties | Settings |
|---|---|---|
| Form | Name Caption Height Left Top Width | frmSubForm Header/Sales SubForm 4545 1395 1335 6180 |
| Data Control | Name Caption DatabaseName EOfAction Height Left RecordsetType RecordSource Top Width | Data1 Header Data Set C:\TYSDBVB\CHAP13\CH1303.MDB 2—AddNew 300 120 1—Dynaset HeaderTable 1800 5835 |
| Data Control | Name Caption DatabaseName EOFAction Height Left RecordsetType RecordSource Top Visible Width | Data2 Sales Data Set C:\TYSDBVB\CHAP13\CH1303.MDB 2—AddNew 300 120 1—Dynaset SalesTable 3780 0—False 5835 |
| Text Box | Name DataSource DataField Height Left Top Width | Text1 Data1 CustID 300 1440 120 1200 |
| Text Box | Name DataSource DataField Height Left Top Width | Text1 Data1 CustName 300 1440 540 2400 |
| Text Box | Name DataSource DataField Height Left Top Width | Text1 Data1 AddrLine 300 1440 960 2400 |
| Text Box | Name DataSource DataField Height Left Top Width | Text1 Data1 City 300 1440 1380 2400 |
| Text Box | Name DataSource DataField Height Left Top Width | Text1 Data1 StateCode 300 4020 1380 600 |
| Text Box | Name DataSource DataField Height Left Top Width | Text1 Data1 ZipCode 300 4740 1380 1200 |
| Label | Name BorderStyle Caption Height Left Top Width | Label1 1—Fixed Single CustID: 300 120 120 1200 |
| Label | Name BorderStyle Caption Height Left Top Width | Label1 1—Fixed Single Cust Name: 300 120 540 1200 |
| Label | Name BorderStyle Caption Height Left Top Width | Label1 1—Fixed Single Address Line 300 120 960 1200 |
| Label | Name Borderstyle Caption Height Left Top Width | Label1 1—Fixed Single City/State/Zip 300 120 1380 1200 |
| MSDBGrid | Name Height Left Top Width | DBGrid1 1455 120 2222 5835 |
Layout design of Subform project.
It would be nice to say that you could build a Subform without using any Visual Basic 4 code, but that's not quite true. You need just under ten lines of code to get your data grid at the bottom of the form linked to the master table at the top of the form. Place Listing 13.5 in the Data1_Reposition event of the HeaderTable data control.
Listing 13.5. Code to update Subform with Reposition event.
Private Sub Data1_Reposition()
Dim cSQL As String
'
' create select to load grid
cSQL = "SELECT * FROM SalesTable WHERE CustID='" + Trim(Text1(0)) + "'"
Data2.RecordSource = cSQL ' load grid-bound data control
Data2.Refresh ' refresh data control
DBGrid1.ReBind ' refresh grid
End Sub
The preceding code is used to create a new SQL SELECT statement using the CustID value of the HeaderTable. This SQL statement is used to generate a new data set for the Data2 data control. This is the control that supplies the data grid. Once the new recordsouce has been created, invoke the Refresh method to update the data control and the ReBind method to update the data grid. That's it, only seven lines of Visual Basic
Now save the form as CH1304.FRM and the project as CH1304.VBP and run the program. When the form loads, you see the first record in the Header table displayed at the top of the form, and a list of all the outstanding invoices for that customer in the grid at the bottom of the form. (See Figure 13.18.)
Running the Subform project.
As you browse through the Header table, you'll see the data grid is updated, too. You can add records to the data grid or to the HeaderTable master. If this were a production project, you would add event trapping features like the ones mentioned in the previous section in order to maintain data integrity.
Today, you learned how to load and use three of the new data-bound controls that are shipped with Visual Basic 4.0.
You learned how to link these new controls to Recordsets using the Visual Basic 4 data controls, and how to use these links to update related tables.
You also learned several of the important Visual Basic 4 events associated with the data grid. These events let you create user friendly data entry routines using just a data control and the data grid.
Finally, you drew upon your knowledge of data grids, SQL, and form layout to design and implement a data entry Subform. This form showed a master table at the top, and a related list table at the bottom of the form in a data-bound grid.
Assume that you have been assigned the responsibility of maintaining the BIBLIO.MDB database application that ships with Visual Basic 4.0. Your organization has determined that the information contained in this database will be of value to Help Desk personnel. The Help Desk Manager has come to you and requested a Visual Basic 4 application for their use.
Build a data form that contains a data-bound list box that displays the Name field from the Publishers table. Once selection is made in this list box, text boxes should display PubID, CompanyName, Address, City, State, Zip, Telephone, and Fax of the publisher selected.
In addition, a listing of all publications of the selected publisher should appear in a data-bound grid Subform. For each entry, display the Title, Year Published, and ISBN from the Titles table.
Hint: You will need to use three data controls for this form.
