Click here to Skip to main content
15,868,016 members
Articles / Programming Languages / Visual Basic

How to Print Invoice using VB6?

Rate me:
Please Sign up or sign in to vote.
4.33/5 (5 votes)
13 May 2011CPOL2 min read 82.5K   7.2K   5   7
Using Data Report and VB6 code to design and print Invoice

Invoice_VB6/image0023.JPG

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):

VB.NET
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):

VB.NET
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):

VB
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#:

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Egypt Egypt
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionData report Pin
Odewusi Oluwasijibomi Joshua16-Jun-18 18:53
Odewusi Oluwasijibomi Joshua16-Jun-18 18:53 
Questionhow to run this project Pin
Member 1123233113-Nov-14 15:07
Member 1123233113-Nov-14 15:07 
AnswerRe: how to run this project Pin
Mostafa Kaisoun13-Nov-14 21:02
Mostafa Kaisoun13-Nov-14 21:02 
QuestionPrint multiple invoices Pin
waeiluen1-Nov-12 18:33
waeiluen1-Nov-12 18:33 
GeneralMy vote of 5 Pin
jayantbramhankar18-Jun-11 0:09
jayantbramhankar18-Jun-11 0:09 
GeneralMy vote of 3 Pin
JohnBrock16-May-11 21:14
JohnBrock16-May-11 21:14 
GeneralRe: My vote of 3 Pin
Mostafa Kaisoun18-May-11 2:11
Mostafa Kaisoun18-May-11 2:11 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.