Point of Sale Tutorial Part 5 – Code for Login and Look Up section

Source code in validating user credentials during login and loading the items, filtering barcode numbers

Option Explicit

Private Sub cmdCancel_Click()
End
End Sub

Private Sub cmdLogin_Click()
Dim names As String

Set rs = New ADODB.Recordset
rs.Open "Select * From UserAccount Where Username='" & LCase(txtUsername.Text) & "'", cn, adOpenStatic, adLockReadOnly
If rs.RecordCount <> 0 Then
If LCase(txtPassword.Text) = rs!Password Then
MsgBox "Username and Password Successfully Log In", vbInformation, "Log In"
names = rs!CName
Unload Me
MDIMain.Show
MDIMain.MDIStatus.Panels(3).Text = UCase(names)
Else
txtUsername.Text = ""
txtPassword.Text = ""
txtUsername.SetFocus
End If
Else
MsgBox "Invalid Login, Please Try again!", vbCritical, "Warning"
txtUsername.Text = ""
txtPassword.Text = ""
txtUsername.SetFocus
rs.Close
End If
Set rs = Nothing
End Sub

Private Sub Form_Load()
Call Module2.Connect
End Sub

Private Sub Label3_Click()
frmUAccount.Show 1
End Sub

Private Sub txtPassword_KeyPress(KeyAscii As Integer)
KeyAscii = Asc(LCase$(Chr$(KeyAscii)))
If KeyAscii = 13 Then
Call cmdLogin_Click
End If
End Sub

Private Sub txtUsername_KeyPress(KeyAscii As Integer)
KeyAscii = Asc(LCase$(Chr$(KeyAscii)))
End Sub

Option Explicit

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub cmdReload_Click()
Set rs = New ADODB.Recordset
rs.Open "Select * from StockIn Order By BarCode ASC", cn, adOpenKeyset, adLockPessimistic
If rs.RecordCount > 0 Then
lvList.ListItems.Clear
While Not rs.EOF
lvList.ListItems.Add , , rs!BarCode
lvList.ListItems(lvList.ListItems.Count).SubItems(1) = "" & rs!Product
lvList.ListItems(lvList.ListItems.Count).SubItems(2) = "" & Format(rs!UnitPrice, "#,###,###.00")
lvList.ListItems(lvList.ListItems.Count).SubItems(3) = "" & rs!QTY
lvList.ListItems(lvList.ListItems.Count).Bold = True
lvList.ListItems(lvList.ListItems.Count).ListSubItems(1).ForeColor = &HC0&
lvList.ListItems(lvList.ListItems.Count).ListSubItems(1).Bold = True
lvList.ListItems(lvList.ListItems.Count).ListSubItems(2).Bold = True
lvList.ListItems(lvList.ListItems.Count).ListSubItems(3).Bold = True
lvList.Refresh
lblRecords.Caption = "Total Records: " & lvList.ListItems.Count
rs.MoveNext
Wend

MsgBox "Items Successfully Loaded", vbExclamation, "Loaded"
Exit Sub
rs.Close
Set rs = Nothing
Else
lvList.ListItems.Clear
MsgBox "No Records Found!", vbExclamation, "No Records"
Exit Sub
End If
End Sub

Private Sub cmdSearch_Click()
If cboBarcode.Text = vbNullString Then
MsgBox "The query is empty!", vbExclamation, "Warning"
Call cmdReload_Click
Exit Sub
Else
Set rs = New ADODB.Recordset
With rs
rs.Open "Select * from StockIn where BarCode like '" & cboBarcode.Text & "'", cn, 3, 3
If .RecordCount >= 1 Then
lvList.ListItems.Clear
Do While Not .EOF
lvList.ListItems.Add , , !BarCode
lvList.ListItems(lvList.ListItems.Count).SubItems(1) = "" & !Product
lvList.ListItems(lvList.ListItems.Count).SubItems(2) = "" & Format(rs!UnitPrice, "#,###,###.00")
lvList.ListItems(lvList.ListItems.Count).SubItems(3) = "" & rs!QTY
lvList.ListItems(lvList.ListItems.Count).Bold = True
lvList.ListItems(lvList.ListItems.Count).ListSubItems(1).ForeColor = &HC0&
lvList.ListItems(lvList.ListItems.Count).ListSubItems(1).Bold = True
lvList.ListItems(lvList.ListItems.Count).ListSubItems(2).Bold = True
lvList.ListItems(lvList.ListItems.Count).ListSubItems(3).Bold = True
lvList.Refresh
.MoveNext
Loop
Exit Sub
Else
MsgBox "No Record Found!", vbExclamation, "Warning"
Call cmdReload_Click
Exit Sub
End If
.Close
End With
End If
End Sub

Private Sub Form_Load()
Call Module2.Connect
Call Load_ComboBarCode
On Error Resume Next
With lvList
.View = lvwReport
.ColumnHeaders.Clear
.ColumnHeaders.Add , , "Barcode", 4000 ' txtProductCD.Width
.ColumnHeaders.Add , , "Description", 5800 'txtDescription.Width
.ColumnHeaders.Add , , "UnitPrice", 1700 ', txtUnitPrice.Width
.ColumnHeaders.Add , , "Quantity" ', txtQuantity.Width
' .ColumnHeaders.Add , , "Sub-Total", 1900 ', txtSubTotal.Width
End With
End Sub

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

Stay tuned for the next coding chapter, happy coding!