Steps:
1.
Create a CustomerRecordUI.frm as shown below:
a.
Select CustomerRecordUI form.
b.
On the Menu, click Tools->Menu Editor, and
type in as shown below:
2.
On dbs.bas, type the following:
Public db As New Connection
Public Sub
OpenConnection()
'validation
If db.State = 1 Then
db.Close()
db.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
_
& "Data Source=" & App.Path & "\Database\SALES.mdb"
db.Open(db.ConnectionString)
End Sub
Public Sub Execute(ByVal sql As String)
'validation
db.Execute(sql)
End Sub
Public Sub
CloseConnection()
'validation
db.Close()
End Sub
3.
On AppToolBox.bas, type the following:
Public Sub Reset(ByVal obj As Object)
Dim ndx As Control
With obj
For Each ndx In obj
If TypeOf ndx Is TextBox Then
ndx
= Empty
End If
Next
End With
End Sub
Public Function
IsEmpty(ByVal obj As
Object) As Boolean
IsEmpty = False
Dim ndx As Control
With obj
For Each ndx In obj
If TypeOf ndx Is TextBox Then
If ndx = Empty Then
IsEmpty = True
Exit Function
End If
End If
Next
End With
End Function
Public Sub Message(ByVal msg As String)
MsgBox(msg,
vbInformation, "Program Assistant")
End Sub
4.
On the CustomerUI (refer to tutorial no.1 to
view the UI) code window, type the
following:
Private Sub
btnClose_Click()
Unload Me
End Sub
Private Sub
btnReset_Click()
AppToolBox.Reset(Me)
txtID.SetFocus()
End Sub
Private Sub
btnSave_Click()
If AppToolBox.IsEmpty(Me)
Then
MsgBox("Cannot Continue, Check for empty textboxes.")
Exit Sub
End If
Dim Customer As New Customers
With Customer
.ID = CInt(Me.txtID.Text)
.Name = Me.txtName.Text
.Address = Me.txtAddress.Text
.ContactNo
= Me.txtContactNo.Text
.CreditLimit = CCur(Me.txtCreditLimit.Text)
If btnSave.Caption = "&Save"
Then
.Insert()
Else
.Update()
End If
End With
MsgBox("Successfully Save..")
Call CustomerRecordUI.DisplayCustomers()
Unload Me
End Sub
Private Sub
txtID_KeyPress(ByVal KeyAscii As Integer)
If KeyAscii = 13 And
txtID.Text <> "" Then
Dim Customer As New Customers
Dim rs As New Recordset
Customer.ID
= CInt(txtID.Text)
rs.Open(Customer.Search, dbs.db)
If rs.BOF = False And rs.EOF = False Then
AppToolBox.Message("Customer ID Already
Exists.")
Else
Me.txtName.SetFocus()
End If
End If
End Sub
5.
On the CustomerRecordUI.frm code window, type
the following:
Private Sub
Form_Load()
Call DisplayCustomers()
End Sub
Public Sub
DisplayCustomers()
dbs.OpenConnection()
Dim rs As New Recordset
rs.Open("Customers", dbs.db)
If rs.BOF = True And rs.EOF = True Then Exit Sub
Me.lvwCustomer.ListItems.Clear()
Dim counter As Integer
counter = 0
Do Until rs.EOF
Customer =
lvwCustomer.ListItems.add(, , rs.Fields("id"))
With Customer
.SubItems(1) = rs.Fields("name")
.SubItems(2) = rs.Fields("address")
.SubItems(3) = rs.Fields("contactno")
.SubItems(4) = Format(rs.Fields("creditlimit"),
"#,##0.00")
End With
counter =
counter + 1
rs.MoveNext()
Loop
rs.Close()
dbs.CloseConnection()
Me.stbCustomer.Panels(1).Text = "Total Customers: " & counter
End Sub
Private Sub Form_Resize()
On Error Resume Next
Me.lvwCustomer.Width = Me.Width
Me.lvwCustomer.Height = Me.Height
- 500
End Sub
Private Sub
lvwCustomer_DblClick()
Call DisplaySelectedCustomer()
End Sub
Private Sub
mnuEdit_Click()
Call DisplaySelectedCustomer()
End Sub
Private Sub
DisplaySelectedCustomer()
With CustomerUI
.txtID.Text
= Me.lvwCustomer.SelectedItem.Text
.txtName.Text = Me.lvwCustomer.SelectedItem.SubItems(1)
.txtAddress.Text = Me.lvwCustomer.SelectedItem.SubItems(2)
.txtContactNo.Text = Me.lvwCustomer.SelectedItem.SubItems(3)
.txtCreditLimit.Text = Me.lvwCustomer.SelectedItem.SubItems(4)
.btnSave.Caption = "&Update"
.Show(vbModal)
End With
End Sub
Private Sub
lvwCustomer_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)
On Error Resume Next
If Button = 2 Then
lvwCustomer.SelectedItem = lvwCustomer.HitTest(x, y)
PopupMenu(mnuPopUp)
End If
End Sub
Private Sub
mnuDelete_Click()
Dim result As
VbMsgBoxResult
result =
MsgBox(lvwCustomer.SelectedItem.SubItems(1) & "
will permanently be deleted. Continue?", vbYesNo, "Confirmation")
If result = vbYes Then
Dim Customer As New Customers
Customer.Delete(CInt(lvwCustomer.SelectedItem.Text))
Call DisplayCustomers()
End If
End Sub
Private Sub
mnuNew_Click()
CustomerUI.Show
vbModal
End Sub
6.
On the Customers.vb, type the following:
Option Explicit
'Data members
Private mID As Integer
Private mName As String
Private mAddress As String
Private mContactNo As
String
Private mCreditLimit As
String
'Setters and Getters
Public Property
Get ID() As Integer
ID = mID
End Property
Public Property
Let ID(ByVal
vNewValue As Integer)
mID = vNewValue
End Property
Public Property
Get Name() As String
Name = mName
End Property
Public Property
Let Name(ByVal
vNewValue As String)
mName = vNewValue
End Property
Public Property
Get Address() As
String
Address = mAddress
End Property
Public Property
Let Address(ByVal
vNewValue As String)
mAddress =
vNewValue
End Property
Public Property
Get ContactNo() As
String
ContactNo =
mContactNo
End Property
Public Property
Let ContactNo(ByVal
vNewValue As String)
mContactNo =
vNewValue
End Property
Public Property
Get CreditLimit() As
Currency
CreditLimit =
mCreditLimit
End Property
Public Property
Let CreditLimit(ByVal
vNewValue As Currency)
mCreditLimit =
vNewValue
End Property
Public Sub Insert()
Dim sql As String
sql = "INSERT INTO
Customers(id,name,address,contactno,creditlimit)" _
& "VALUES('" & ID & "', " _
& " '" & Name & "', " _
& " '" & Address & "'," _
& " '" & ContactNo & "'," _
& " '" & CreditLimit & "')"
dbs.Execute(sql)
End Sub
Public Sub Update()
Dim sql As String
sql = "UPDATE Customers SET " _
& "name='" & Name & "', " _
& "address='" & Address & "', " _
& "contactno='" & ContactNo & "'," _
& "creditlimit='" & CreditLimit & "' " _
& " WHERE id=" & ID & ""
dbs.Execute(sql)
End Sub
Public Sub Delete(ByVal ID As Integer)
Dim sql As String
sql = "DELETE FROM Customers WHERE id=" &
ID & ""
dbs.Execute(sql)
End Sub
Public Function
Search() As String
Search = "SELECT * FROM Customers WHERE id="
& ID & ""
End Function
Private Sub
Class_Initialize()
dbs.OpenConnection()
End Sub
Debugging your code
-To familiarize the behaviour of the language
or to know how your codes are executed in sequence, debugging should be done.
In visual basic, just put a break point by clicking at the left margin of the
procedure or function and then press F8. To break the debugging mode, just
click the end button at the right of the start button in the toolbar and to
remove the break point, just click the break point to toggle.
Congrats,
you made it-mariuz the farmer:)
No comments:
Post a Comment