Form1 !DataTable Example Program - DEMO4 Form1 Customer DATATABLE C:\CUSTOMER Label1 Name: CustomerName Label2 Address: Address Label3 City: Label4 State: State Label5 Label6 Phone: Phone TopButton NextButton InsertButton Insert DeleteButton Delete QuitButton BottomButton Bottom PreviousButton Previous UpdateButton Update ClearButton Clear QuitButton_Click Form_Click Form_Load> Customer OpenTableg Action CloseTable% FillForm GetRecord FieldName GetField CustomerName: Text$ FieldValue Address StateW Phone} InsertRecW PutField InsertRecord InsertButton_Click ClearButton_Click TopButton_Click FirstRecord BottomButton_Click LastRecord NextButton_ClickA NextRecord PreviousButton_ClickJ PreviousRecord DeleteButton_Click DeleteRecord4 UpdateRec UpdateRecord UpdateButton_Click Reaction DisplayError TheError CustomerName_Change CustomerName_LostFocus SearchMode SEARCHFIRST SearchField TheNameTyped' database tableN DataTable KeySearch SearchKey FillCustomerCombo EmptyCustomerCombo ListCount Form_Load Open up the database table Fill the combo box with customer names Now fill the form with data in the first record. QuitButton_Click Close the database table And quit the program FillForm We need to get the current record in the database: Ok, now lets get the values in the database fields for the record we just read in:: We need to make a one-to-one relationship between the fields in the database table and the fields on our Visual Basic Form. We will read in the field values from the database record and place those values into the .TEXT property of our TEXT controlss on our Visual Basic form. For each field of interest this is a three-step process: 1. Specify the name of the database field of interest ---> DataTable.FieldName = " 2. Get the field value ---> DataTable.Action = GetFieldd 3. Place the field value in the TEXT control ---> Text1.TEXT = DataTable.FieldValuee Thats all there is to it!! You should make a FillForm like subroutine position independent. Notice that the first line of code in this subroutine reads in the current record. Use other routines to move around in the table (like the TOP,BOTTOM, NEXT and PREVIOUS buttons in this demo). Making the routine position independent means that it can be used and re-used at any time to fill inn your form with information from any record in your database. We will fill in the form now:o Address" State" Phone" InsertRec Here we transfer the information from the Visual Basic Form and insert itt into the database table. To do this requires a three-step process for each field in the database table: 1. Specify the name of the field of interest ---> DataTable.FieldNamee 2. Specify the field value ---> DataTable.FieldValue = A String valuee 3. Put the field into the DataTable record ---> DataTable.Action = PutFieldd We do this for each field in the database table. When we are finished we INSERT the record into the database table ---> DataTable.Action = InsertRecordd Address" State" Phone" InsertButton_Click ClearButton_Click This code simply clears the text in all of the Visual Basic form's Text controls.. TopButton_Click The following code moves to the first record in the data basen and fills in our form with the information found in that first record Moveto the first record in the database Fill in the form with data BottomButton_Click The following code moves to the last record in the data base and fills in our form with the information found in that last record Move to the last recordd Fill the form NextButton_Click The following code moves to the next record in the database table. If an error is encountered during the move to the next record an error message is displayed. The DisplayError subroutine is an example of the structure of a generalized DataTable error handling routine. Once the repositioning to the next record is successfull, we fill in our form with information from that record. Moving to the next record in the databaseu If an errort Display the DataTable errore If no error Fill in our form with the information in the record. PreviousButton_Click The following code moves to the previous record in the database table. If an error is encountered during the move to the record an error message is displayed.a The DisplayError subroutine is an example of the structure of a generalizedl DataTable error handling routine. Once the repositioning to the previous record is successfull, we fill in our forma with information from that record. Moving to previous records If an errors Display the DataTable Errorr If no error Fill in our form with info from the record DeleteButton_Click This code deletes the current record from the database The current record after the delete is performed, is the record immediatelyr following the deleted record. We then fill in our form with info from that record.n We will delete the current record. Now fill in form with the current record. UpdateRec Here we transfer the information from the Visual Basic Form and update the current record in the database table. To do this requires a three-step process for each field in the database table: 1. Specify the name of the field of interest ---> DataTable.FieldNames 2. Specify the field value ---> DataTable.FieldValue = A String values 3. Put the field into the DataTable record ---> DataTable.Action = PutFieldl We do this for each field in the database table. When we are finished we UPDATE the record in the database table ---> DataTable.Action = UpdateRecordd Address" State" Phone" UpdateButton_Click DisplayError Here we show how to build a general purpose error routine for the DataTablee it is not complete but gives you the basic idea of how to build such a routine.. For a listting of all DataTable error codes see the Working Model documentation. Drive not ready! Directory not found! File is busy!" File is Locked!" File not Found!" Record not Found! End of Table!" Start of Table!" Unknown Error! CustomerName_LostFocus This code demonstrates how to search a database on a specific key. When you leave this field, this lost focus subroutine is executed. The value in the CustomerName.Text is then searched for in the database table. The database field searched is "Name" (The only key field in this example).l If the search is successfull the record is displayed on the form. If the search fails an error message is displayed, the form is cleared (with the typed in value) replaced in the CustomerName.Text expecting to get a new customer's information. Temporary variable to hold Customer's name.c The first and only field in PRIMARY index The first and only field of the keye The value to search forf Submit the search criteria Perform the search If an errorh Display the errort Assume new customer, temp store for new customer name' Clear the form Place the new customer name back in the form Now let the user type in the new infoa Search was successfull Fill in the form with customer infoa FillCustomerCombo Move to first record in the CUSTOMER database We want to put customer names in the combo box Loop to fill combo box with customer names Get the record Get the customer named Add name to the list Move to next recorde Reposition to the first record EmptyCustomerCombo Here we empty the combo box to eliminate duplicate names Loop to remove names from list