Point of Sale Tutorial Part 2 – Setting Up Database Connection

In part one of these series tutorial we focus on the creating of database and tables. In this tutorial, I will teach you how to set up the database connection in the project using the module to establish a connection, we are going to use different programming structures like methods, declaring functions, classes and variables. So that, it will trim down the bunch of codes and it will lessen your work.

I’m going to use the VISUAL BASIC 6.0 in creating this project.

Let’s begin:
Open the Microsoft Visual Basic 6.0 and create a new Form. After that, add a module by navigating in PROJECT --> ADD MODULE --> Click MODULE then Click Open, leave it with a name Module1 in properties.

Then go to Menu Bar and Select PROJECT --> REFERENCES, then a Reference dialog will appear. Search for MICROSOFT ACTIVEX DATA OBJECTS 2.8 LIBRARY. Check it and Click OK.

 

Let’s go back to the Module1 and start coding.
Put this code in General Declaration below Option Explicit

Public cn As ADODB.Connection
Public rs As ADODB.Recordset
Public txt As Control
The cn, rs and txt are variables with a different types,

cn – variable used to define adodb connection
rs – variable used to define adodb recordset
txt – variable used to define a control objects for TextBox

This section, we create a sub function and declared as a public to call anywhere in the form.

Public Sub Connect()
Set cn = New ADODB.Connection
cn.CursorLocation = adUseClient
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Stark\Desktop\POSProject\POS.mdb;Persist Security Info=False"
cn.Open 
End Sub

Connect() – Name of Sub (You can change this if you want)
cn.CursorLocation = adUseClient – to point where the cursor location is and equal to aduseclient because we are not going to use a Server Client in a single station.
cn.ConnectionString – is the Data Source or the directory of your database access
cn.Open – to open a connection in database.

Creating the Clear Textbox Function.

Public Function ClearTextBox(frm As Form)
For Each txt In frm
If TypeOf txt Is TextBox Then
txt.Text = ""
End If
Next
End Function

ClearTextBox – Name of function.
(frm As Form) – a parameter, which applies in the form.
For Each txt In frm – to loop the txt object
If TypeOf txt Is TextBox Then – a condition to identify if what type of object to be cleared
txt.Text = “” – to clear the textbox using the declared variable object.

The next code will apply in User Account form
This sub function loads all the record from UserAccount Table to the Listview

Public Sub LoadListviewUser()
frmUAccount.lvAccount.ListItems.Clear
Set rs = New ADODB.Recordset
rs.Open "Select * From UserAccount", cn, adOpenForwardOnly, adLockPessimistic
With rs
Do While Not .EOF
frmUAccount.lvAccount.ListItems.Add , , !UserID, 1, 1
frmUAccount.lvAccount.ListItems(frmUAccount.lvAccount.ListItems.Count).SubItems(1) = "" & !UserName
frmUAccount.lvAccount.ListItems(frmUAccount.lvAccount.ListItems.Count).SubItems(2) = "" & !Password
frmUAccount.lvAccount.ListItems(frmUAccount.lvAccount.ListItems.Count).SubItems(3) = "" & !Confirm
frmUAccount.lvAccount.ListItems(frmUAccount.lvAccount.ListItems.Count).SubItems(4) = "" & !CName
frmUAccount.lvAccount.ListItems(frmUAccount.lvAccount.ListItems.Count).SubItems(5) = "" & !Designation
frmUAccount.lvAccount.ListItems(frmUAccount.lvAccount.ListItems.Count).SubItems(6) = "" & !DateCreate
.MoveNext
Loop
.Close
End With
Set rs = Nothing
End Sub
A sub function loads all the record from Designation Table to the Listview

Public Sub LoadListviewDesignation()
frmAddDesig.lvDesignation.ListItems.Clear
Set rs = New ADODB.Recordset
rs.Open "Select * From Designation", cn, adOpenForwardOnly, adLockPessimistic
With rs
Do While Not .EOF
frmAddDesig.lvDesignation.ListItems.Add , , !ID, 1, 1 frmAddDesig.lvDesignation.ListItems(frmAddDesig.lvDesignation.ListItems.Count).SubItems(1) = "" & !Designation
.MoveNext
Loop
.Close
End With
Set rs = Nothing
End Sub

Sub Functions that populate the records from Designation fieldname and load it to the combobox
rs.Open "Select * from Designation Order by Designation ASC" – A query that open the recordset of Designation fieldname and arrange it by Ascending Order.

Public Sub PopulateDesignation()
Set rs = New ADODB.Recordset
rs.Open "Select * from Designation Order by Designation ASC", cn, 3, 3
Do While Not rs.EOF
frmUAccount.cboDesignation.AddItem rs!Designation
rs.MoveNext
Loop
Set rs = Nothing
End Sub

Sub Functions that populate the records from Category fieldname and load it to the combobox

rs.Open "Select * from Category Order by Category ASC" – A query that open the recordset of Category fieldname and arrange it by Ascending Order.

Public Sub PopulateCategory()
Set rs = New ADODB.Recordset
rs.Open "Select * from Category Order by Category ASC", cn, 3, 3
Do While Not rs.EOF
frmProductAE.cboCategory.AddItem rs!Category
rs.MoveNext
Loop
Set rs = Nothing
End Sub

Sub Functions that populate the records from SupplierName fieldname and load it to the combobox
rs.Open "Select * from Supplier Order by SupplierName ASC" – A query that open the recordset of SupplierName fieldname and arrange it by Ascending Order.

Public Sub PopulateSupplier()
Set rs = New ADODB.Recordset
rs.Open "Select * from Supplier Order by SupplierName ASC", cn, 3, 3
Do While Not rs.EOF
frmProductAE.cboSupplier.AddItem rs!SupplierName
rs.MoveNext
Loop
Set rs = Nothing
End Sub

In the next part of tutorial we will focus on the making of Graphical Interface of our project.

Point of Sale Tutorial Part 3 - Creating Graphical Interfaces