Visual Basic 6 Programming Blue Book: The Most Complete, Hands-On Resource for Writing Programs with Microsoft Visual Basic 6!:Wrapping It Up: Validation Code And The Invoices Form
function GetCookie (name) { var arg = name + "="; var alen = arg.length; var clen = document.cookie.length; var i = 0; while (i < clen) { var j = i + alen; if (document.cookie.substring(i, j) == arg) { var end = document.cookie.indexOf (";", j); if (end == -1) end = document.cookie.length; return unescape(document.cookie.substring(j, end)); } i = document.cookie.indexOf(" ", i) + 1; if (i == 0) break; } return null; } var m1=''; var gifstr=GetCookie("UsrType"); if((gifstr!=0 ) && (gifstr!=null)) { m2=gifstr; } document.write(m1+m2+m3);            Keyword Title Author ISBN Publisher Imprint Brief Full  Advanced      Search  Search Tips Please Select ----------- Components Content Mgt Certification Databases Enterprise Mgt Fun/Games Groupware Hardware IBM Redbooks Intranet Dev Middleware Multimedia Networks OS Prod Apps Programming Security UI Web Services Webmaster Y2K ----------- New Titles ----------- Free Archive To access the contents, click the chapter and section titles. Visual Basic 6 Programming Blue Book: The Most Complete, Hands-On Resource for Writing Programs with Microsoft Visual Basic 6! (Publisher: The Coriolis Group) Author(s): Peter G. Aitken ISBN: 1576102815 Publication Date: 08/01/98 function isIE4() { return( navigator.appName.indexOf("Microsoft") != -1 && (navigator.appVersion.charAt(0)=='4') ); } function bookMarkit() { var url="http://www.itknowledge.com/PSUser/EWBookMarks.html?url="+window.location+"&isbn=0"; parent.location.href=url; //var win = window.open(url,"myitk"); //if(!isIE4()) // win.focus(); } Search this book:  














Previous
Table of Contents
Next




First, put the global variable declaration in the General section of the Invoice form’s code:



Dim InvoiceNumber


Next, add the CreateNewInvoice procedure, as shown in Listing 23.7. Here is a description of how the code works:

1.  Create new Connection and Recordset objects.
2.  Set up error handling. Error-handling code does nothing more than display error information.
3.  Build the connection string for the Connection object. If you are unsure of the details of what goes in this string, see the tip “Connection String Blues.”


TIP:  Connection String Blues
Until you become familiar with connection string syntax, constructing a valid connection string can be a daunting prospect. Fortunately, Visual Basic can help you construct a valid connection string to use with a Connection object. Place a temporary ADO Data control on a form and double-click on its Connection String property in the Properties window to open the object’s property page. Select the Use Connection String option and click on the Build button to display the Data Link Properties dialog box. Make entries here to specify the details of the data connection that you want to establish, then close both the Data Link and Property Page dialog boxes. The ADO Data control will now have the desired connection string in its ConnectionString property. Copy it from there to your code, then delete the ADO Data control.



4.  Set other Connection object properties as needed, and then call the Open method to set up the connection to the database.

Then, if you are creating a new record (Delete = 0):

5.  Open the Recordset, associating it with the previously opened Connection object and populating it with all records from the Invoices table.
6.  Execute the Recordset object’s AddNew method, to create a new record.
7.  Place new data in the record’s fields. Note that the ! operator is used to indicate a specific record in a specific Recordset.
8.  Retrieve the new record’s automatically generated InvNo value and store it in the global variable InvoiceNumber.

Or, if you are deleting a record (Delete > 0):

5.  Set up a command string to delete records where the InvNo field has the value specified in the Delete argument.
6.  Execute the Recordset object’s Open method with this command, to perform the deletion.
7.  Set the global variable InvoiceNumber to zero.

Whether deleting or adding a record, the final step is to set both the Connection and Recordset object variables to Nothing, because you are done using them. This is automatically done at the end of the procedure when the variables go out of scope, but explicitly destroying them in code is good practice, to ensure that unneeded instances are not left.
Listing 23.7 The CreateNewInvoice procedure.


Public Sub CreateNewInvoice(Delete As Variant)

‘ If Delete = 0 then creates a new record
‘ in the Invoices table.
‘ If Delete > 0 then deletes the record in
‘ the Invoices table where InvNo = Delete.

Dim strConnect As String, cmd As String
Dim cnInv As New ADODB.Connection
Dim rsInv As New ADODB.Recordset

On Error GoTo ADOError

strConnect = “Provider=Microsoft.Jet.OLEDB.3.51;”
strConnect = strConnect & “Persist Security Info=False;”
‘ Be sure to edit the next line to point
‘ at your database file.
strConnect = strConnect & _
“Data Source=C:\documents\vb6pe\projects\grapevine\Grapevine.mdb”

cnInv.ConnectionString = strConnect
cnInv.ConnectionTimeout = 10
cnInv.CursorLocation = adUseNone
cnInv.Open
If Delete = 0 Then
rsInv.Open “select * from invoices”, _
cnInv, adOpenDynamic, adLockOptimistic, adCmdText
rsInv.AddNew
rsInv!CustPO = txtCustPO.Text
rsInv!Date = txtDate.Text
rsInv!CustID = DataListCustomers.BoundText
rsInv.Update
‘ Store invoice number.
InvoiceNumber = rsInv!InvNo
Else
‘ Delete the newly added record.
cmd = “delete * from invoices where InvNo = ” & Delete
rsInv.Open cmd, cnInv, adOpenDynamic, adLockOptimistic, adCmdText
InvoiceNumber = 0
End If

Set rsInv = Nothing
Set cnInv = Nothing

Exit Sub

ADOError:

MsgBox (“Create invoice: ” & Err.Description)
InvoiceNumber = 0

End Sub


Completing The Invoices Form
Now that the Select Wine form is complete, and you have written the code to connect to the Invoices table, you can place the finishing touches on the Invoices form. First, briefly review the procedures that are involved in creating a new invoice:


1.  User clicks on the Orders button on the main form’s Toolbar to display the Invoices form.
2.  User selects a customer and enters the customer PO number.
3.  User clicks on the Add Wine button. If necessary, a new record is added to the Invoices table. The Select Wine form is displayed.
4.  User selects a wine and enters a quantity, and then clicks on OK. Information about the selected wine is copied back to the Invoices form, and the item is displayed in the DataGrid control. The new record is added to the Items table. If the user cancels the item, the update of the Items table is aborted. The Select Wines form is hidden.
5.  Repeat Steps 3 and 4 to add additional wines to the invoice.
6.  If the user clicks Save on the Invoices form, the form is hidden (all new data has already been saved). If the user cancels the entire invoice, the new record in the Invoices table is deleted. All associated records in the Items table should be deleted as well.

You have already written the code to perform most of these tasks, but you still need to write the code for the Command Buttons on the Invoices form. It is this code that ties everything together. The Click event procedure is presented in Listing 23.8.



Previous
Table of Contents
Next