How to Print Invoice using VB6?

• Download source - 523.89 KB

Introduction

Perhaps some programmers will be angry because I write an article about VB6, but I know that many programmers are still using VB6 - this article is for them.

This article is about how to use VB6 to print Invoice, this is a trial to print Invoice with VB6. My Project has three Forms:

• frmInvoice to bind DataGrid with all Orders from Northwind database file
• frmInput to choose one Order which you want to print its Invoice
• frmOrder to display Invoice on DataGrid, then you can Print Preview or Print the Invoice as Report

Be sure that you add Microsoft ActiveX Data Objects 2.x Library to References. We add Data Report to our Project for printing the Invoice. You can use any database file instead of Northwind.mdb and change my code to connect to your database file. Also, you can change my SQL string to bind DataGrid with data.

Using the Code

Connect with the database file and Load all Orders (you can read this code in frmInvoice form):

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim MyPass As String
Dim MyDataFile As String
Dim InvSql As String
Dim strCon As String

    MyPass = ""
    MyDataFile = App.Path & "\DataFile\Northwind.mdb"
    strCon = "provider=microsoft.jet.oledb.4.0;data source=" _
    & MyDataFile & ";" & "Jet OLEDB:Database Password=" & MyPass & ";"

    InvSql = "SELECT Customers.CompanyName, Customers.City, " _
    & "Employees.FirstName & Space(1) & Employees.LastName AS Salesperson, " _
    & "Orders.OrderID, Orders.OrderDate, " _
    & "[Order Details].ProductID, Products.ProductName, [Order Details].UnitPrice, " _
    & "[Order Details].Quantity, [Order Details].Discount, " _
    & "CCur([Order Details].UnitPrice*_
    [Quantity]*(1-[Discount])/100)*100 AS ExtendedPrice, " _
    & "Orders.Freight " _
    & "FROM Products INNER JOIN ((Employees INNER JOIN " _
    & "(Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) " _
    & "ON Employees.EmployeeID = Orders.EmployeeID) " _
    & "INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) " _
    & "ON Products.ProductID = [Order Details].ProductID;"

    Set cn = New ADODB.Connection
    cn.CursorLocation = adUseClient
    cn.Open strCon

    Set rs = New ADODB.Recordset
    rs.Open InvSql, cn, adOpenStatic, adLockOptimistic

    Set datGrid.DataSource = rs 

The following code is used to bind DataGrid with one Order (you can read this code in the frmOrder form):

Dim rs As ADODB.Recordset
Dim intOrder As Integer
Dim InvSql As String
Dim strCon As String

    'InvoiceOrder is the number of Order which you select:
    intOrder = Val(InvoiceOrder) 

    ' Get Invoice Data:
    InvSql = "SELECT [Order Details].ProductID, " _
    & "Products.ProductName, [Order Details].UnitPrice, " _
    & "[Order Details].Quantity, [Order Details].Discount, " _
    & "CCur([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100 " _
    & "AS ExtendedPrice " _
    & "FROM Products INNER JOIN [Order Details] " _
    & "ON Products.ProductID=[Order Details].ProductID " _
    & "WHERE [Order Details].OrderID = " & intOrder

    Set rs = New ADODB.Recordset
    rs.Open InvSql, cn, adOpenStatic, adLockOptimistic
    Set ordGrid.DataSource = rs

The following code is used to bind Data Report with one Order (you can read this code in the frmOrder form):

Dim repSql As String

    repSql = "SHAPE {SELECT Orders.OrderID,Orders.OrderDate,Orders.Freight," _
    & "Customers.CustomerID,Customers.CompanyName," _
    & "Customers.City,Customers.Phone," _
    & "(Employees.FirstName + Space(1) + Employees.LastName) As SalesName " _
    & "FROM ((Orders " _
    & "INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID) " _
    & "INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID) " _
    & "WHERE Orders.OrderID = " & intOrder & " } AS ParentCMD " _
    & "APPEND ({SELECT DISTINCTROW [Order Details].OrderID,_
    Products.ProductID,Products.ProductName," _
    & "[Order Details].UnitPrice,[Order Details].Quantity,[Order Details].Discount," _
    & "CCur([Order Details].UnitPrice*[Order Details].Quantity*_
    ((1-[Discount])/100)*100) As ExtendedPrice " _
    & "FROM [Order Details] " _
    & "INNER JOIN Products ON [Order Details].ProductID = Products.ProductID} " _
    & "AS ChildCMD RELATE OrderID TO OrderID)"

    Set invReport = New ADODB.Recordset

    invReport.ActiveConnection = cn

    If invReport.State = adStateOpen Then
        invReport.Close
    End If

    invReport.Open repSql, cn
    invReport.Requery

    Set repInvoice.DataSource = invReport

Design Data Report

• Report Header (Section 4): has 'PictureBox' to set any image (if any) and four controls of 'Label' for Invoice Title.
• Group Header (Section 6): has Customer name, City, Salesperson, Order ID, Order date and head of data table.
• Detail (Section 1): has six controls of 'TextBox' to display fields of data table.
• Group Footer (Section 7): to display Invoice total.

Please read the code in all the forms and Data Report, then run the code to see the result. I hope this article is useful. If you have any ideas or if you find any problems, please tell me. You can read my other articles to see how to print invoice using VB.NET or C#:

• How to Print Invoice using VB.NET?
• How to Print Invoice using C#?