To be modified to best & easy code:

[sweetie_2005]

Hi friends,

Could somebody make this code best & easy to understand. Do we really need the module for this project?

Public db As New ADODB.Connection
Public rs As New ADODB.Recordset
Public key As String
Public deptID As Integer
 
Public Sub data_connect()
Dim path As String
path = App.path & "\db40.mdb"
db.CursorLocation = adUseClient
db.Open "Provider=microsoft.jet.oledb.4.0;persist security info=false;data source=" & path & ";"
End Sub
 
Private Sub cmdadd_Click()
If cmdadd.Caption = "&Add" Then
    data_disable
    txt_unlock
    clear_txt
    cmdsave.Enabled = True
    cmdadd.Caption = "&Cancel"
    cmdedit.Enabled = False
    rs.MoveLast
    deptID = rs(0)
    deptID = deptID + 1
    cmddel.Enabled = False
Else
    data_enable
    txt_unlock
    clear_txt
    cmdsave.Enabled = False
    cmdadd.Caption = "&Add"
    cmdedit.Enabled = True
    cmddel.Enabled = True
    rs.Requery
    show_rec
End If
End Sub
 
Private Sub data_disable()
cmdprev.Enabled = False
cmdfirst.Enabled = False
cmdnext.Enabled = False
cmdlast.Enabled = False
End Sub
Private Sub data_disable()
cmdprev.Enabled = False
cmdfirst.Enabled = False
cmdnext.Enabled = False
cmdlast.Enabled = False
End Sub
 
Private Sub data_enable()
cmdprev.Enabled = True
cmdfirst.Enabled = True
cmdnext.Enabled = True
cmdlast.Enabled = True
End Sub
 
Private Sub cmddel_Click()
Dim ano As String
ano = MsgBox("Do you really want to delete this record?", vbYesNo, "Comfirm Delete?")
If ano = vbYes Then
    db.Execute "Delete from dept where cusnum like " & rs(0)
    rs.Requery
    show_rec
End If
End Sub
 
Private Sub cmdedit_Click()
data_disable
txt_unlock
key = Text1
cmdsave.Enabled = True
cmdedit.Enabled = False
cmdadd.Enabled = False
cmddel.Enabled = False
End Sub
 
Private Sub cmdexit_Click()
Unload Me
End Sub
 
Private Sub cmdfirst_Click()
On Error GoTo k
rs.MoveFirst
show_rec
Exit Sub 'you need this or the error trapping code
         'will ALWAYS execute
k:
If Err.Number = 3021 Then
End If
End Sub
 
Private Sub cmdlast_Click()
On Error GoTo last
rs.MoveLast
show_rec
Exit Sub 'you need this or the error trapping code
         'will ALWAYS execute
last:
If Err.Number = 3021 Then
End If
End Sub
 
Private Sub cmdnext_Click()
On Error GoTo cANTmOVEnEXT
rs.MoveNext
If rs.EOF Then
    rs.MoveLast
End If
show_rec
Exit Sub 'you need this or the error trapping code
         'will ALWAYS execute
cANTmOVEnEXT:
If Err.Number = 3021 Then
End If
End Sub
 
Private Sub cmdprev_Click()
On Error GoTo p
rs.MovePrevious
If rs.BOF Then
    rs.MoveFirst
End If
show_rec
Exit Sub 'you need this or the error trapping code
         'will ALWAYS execute
p:
If Err.Number = 3021 Then
End If
End Sub
 
Private Sub cmdsave_Click()
If cmdadd.Caption <> "&Cancel" Then
    txt_lock
    db.Execute "Update dept set Fname = '" & Text2 & "', Lname = '" & Text3 & "', Address = '" & Text4 & "', phonenum = '" & Text6 _
    & "', credlimit = '" & Text8 & "', emaddress = '" & text5 & "', deptname = '" & Text7 & "' where cusnum = " & key
    rs.Requery (1)
    cmdsave.Enabled = False
    cmdedit.Enabled = True
    cmdadd.Enabled = True
Else
    txt_lock
    db.Execute "Insert Into dept values (" & deptID & ",'" & Text2 & "','" & Text3 & "','" & Text4 & "','" & text5 & "','" & Text6 & "','" & Text7 & "','" & Text8 & "',0 ,0 )"
    rs.Requery (1)
    cmdsave.Enabled = False
    cmdedit.Enabled = True
    cmdadd.Enabled = True
    cmdadd.Caption = "Add"
End If
data_enable
 
DataGrid1.Refresh
cmddel.Enabled = True
End Sub
 
Private Sub DataGrid1_RowColChange(LastRow As Variant, ByVal LastCol As Integer)
On Error GoTo k
show_rec
Exit Sub 'you need this or the error trapping code
         'will ALWAYS execute
k:
If Err.Number = 6160 Then Exit Sub
End Sub
 
Private Sub fname_Click()
Call Text9_Change
Text9.SetFocus
End Sub
 
Private Sub Form_Load()
Call data_connect
rs.Open "Select * from dept", db
Set DataGrid1.DataSource = rs
show_rec
End Sub
Public Sub txt_lock()
Text1.Locked = True
Text2.Locked = True
Text3.Locked = True
Text4.Locked = True
text5.Locked = True
Text6.Locked = True
Text7.Locked = True
Text8.Locked = True
End Sub
 
Public Sub txt_unlock()
Text1.Locked = False
Text2.Locked = False
Text3.Locked = False
Text4.Locked = False
text5.Locked = False
Text6.Locked = False
Text7.Locked = False
Text8.Locked = False
End Sub
 
Public Sub show_rec()
Text1 = rs(0)
Text2 = rs(1)
Text3 = rs(2)
Text4 = rs(3)
text5 = rs(4)
Text6 = rs(5)
Text7 = rs(7)
Text8 = rs(6)
End Sub
 
Public Sub clear_txt()
Text1 = ""
Text2 = ""
Text3 = ""
Text4 = ""
text5 = ""
Text6 = ""
Text7 = ""
Text8 = ""
End Sub
 
Private Sub Form_Unload(Cancel As Integer)
Cancel = 1
End Sub
 
Private Sub lname_Click()
Call Text9_Change
Text9.SetFocus
End Sub
 
Private Sub text5_Change()
If rs.RecordCount <> 0 Then
    ctrl_unlock
Else
    ctrl_lock
End If
End Sub
Public Sub ctrl_lock()
data_disable
cmdadd.Enabled = False
cmdedit.Enabled = False
cmddel.Enabled = False
cmdexit.Enabled = False
End Sub
 
Public Sub ctrl_unlock()
data_enable
cmdadd.Enabled = True
cmdedit.Enabled = True
cmddel.Enabled = True
cmdexit.Enabled = True
End Sub
 
Private Sub Text9_Change()
rs.Close
If fname.Value = True Then
    rs.Open "Select * from dept where fname like '" & Text9 & "%'", db
    rs.Requery
Else
    rs.Open "Select * from dept where lname like '" & Text9 & "%'", db
    rs.Requery
End If
 
 
If rs.RecordCount <> 0 Then
    show_rec
Else
    clear_txt
End If
End Sub

Please clarify some doubts regarding the above project code:

1. do we really need to put the connection in a module?
2. why we need this txt5_change event?
3. why do we need the ID field in this project? If we want to remove it what changes we have to do in the code?
4.what is rs.query & rs.query(1)
5. how it can be easily understable for the beginners?

Could somebody modify this project code plz.





Edit: Added [vbcode][/vbcode] tags and indenting for clarity. - Hack