The VB application I've created consists of a Main Form and five individual forms. In a .BAS Module, I have declared my connection variables and created a connection object in a Sub Procedure within the Module. These items are Public to enable the same connection to be used on all forms in the application. The recordsets used are opened and closed as their respective forms are opened and closed. However, the connection is opened when the Main Form is loaded and remains opened to be used as the Active Connection for all subsequent Recordsets on their respective forms. The Connection is not closed until the user exits the application via the Main Form. This eliminates having multiple connections and thus reduces network traffic:
THE MODULE AND SUB PROCEDURE FOR THE OPEN CONNECTION:
Option ExplicitPublic rs As New ADODB.Recordset
Public mdkcmd As New ADODB.Command
Public conn As New ADODB.Connection
Public myobj As Object
Public Sub OpenConn()
With conn
.CursorLocation = adUseClient
.ConnectionString = "dsn=MizMoORCL;uid=MizMo;pwd=Pooh"
.Open
End With
End Sub
In the Load Event of my Main Form, I open the Connection:
Private Sub Form_Load()OpenConn
End Sub
THE MAIN FORM:
My main form consists of five Command Buttons and an Exit button. Each Command Button will open the subsequent forms within the application. In the Click Event of these buttons, I simply show the respective form.
Private Sub cmdKirbyCustomer_Click()frmKirbyCustomer.Show
End Sub
THE APPLICATION FORMS:
CUSTOMER, STOCK, ORDERS, ITEM AND HISTORY
In the Form Load Event of each subsequent form I choose my recordset options, open my Recordset off of the established Connection and fill the Text Boxes on the form with the appropriate data from the Fields in my Recordset using a sub procedure called "FillFields":
Private Sub Form_Load()With rs
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.CacheSize = 50
.Source = "Select * from Customer"
.ActiveConnection = "dsn=MizMoORCL;uid=MizMo;pwd=Pooh"
.Open
End With
FillFields
End Sub
THE FILLFIELDS PROCEDURE:
NOTE: In order for the Text Boxes to accept Null Values, I have set their Text Properties equal to the Recordset's Field Value and an Empty String:
Public Sub FillFields()txtCustomerID.Text = rs.Fields("CustomerID") & ""
txtCompanyName = rs.Fields("CompanyName") & ""
txtLastName = rs.Fields("LastName") & ""
txtFirstName = rs.Fields("FirstName") & ""
txtAddress = rs.Fields("Address") & ""
txtCity = rs.Fields("City") & ""
txtState = rs.Fields("State") & ""
txtZip = rs.Fields("ZipCode") & ""
txtPhone = rs.Fields("Phone") & ""
txtFax = rs.Fields("Fax") & ""
End Sub