• Download Invoice_VB6 - 523.89 KB

Introduction

    Perhaps some programmers are angry because I write an article about VB6, but I know that many programmers are still using VB6, this article for them. This article 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 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 database file and Load all Orders (you can read this code in frmInvoice form):

<span style="FONT-STYLE: normal">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</span><span style="FONT-STYLE: normal"> datGrid.DataSource = rs</span> 

Following code to bind DataGrid with one Order (you can read this code in 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
   

Following code to bind Data Report with one Order (you can read this code in frmOrder form):

<span style="FONT-STYLE: normal">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</span> 

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 the all forms and Data Report then run the code to see the result.I hope this article is useful, If you have any idea or if you find any problems please tell me. You can read my next article to see how to print invoice using VB.Net or C#.

Mostafa Kaisoun
m_kaisoun@hotmail.com