Hi, I have some working code but would like to code it a little better than i'm currently doing. I'm using VB 6.0. I know that making the code Object Oriented would be better but i'm not there yet and hope t be in the near future. Please give me your suggestions. Thanks!

This is just one of many procedures. But the suggestions that you give me will likely help me in other areas. Thanks!

VB Code:
  1. Private Function SearchEmployeeDatabase(sFirstName As String, sLastName As String, sBadge As String, sDepartment As String, sCenter As String, sDate As String) As Integer

  2.  
  3. 'Declarations Section

  4. Dim adoConnection As ADODB.Connection    'Used to establish connection with the Employee Database

  5. Dim adoRecordset As ADODB.Recordset      'Used to query the Employee table

  6. Dim connString As String                 'Stores connection string

  7. Dim SQLstr As String                     'Stores query

  8. 'Dim iEmployeeID As Integer              'Stores the Employee's ID in Database

  9. Dim iResponse As Integer                 'Stores response from message box

  10. Dim boolSaveToDB As Boolean              'Stores value indicating whether to save to DB or not

  11. Dim x As Integer                         'Counter for For Loop

  12. Dim sPreviousLastName As String          'Used in case of change in last name

  13.  
  14. On Error GoTo AdoErrors

  15. 'Establish connection with FAE database

  16. Set adoConnection = New ADODB.Connection

  17.  
  18. connString = "Provider=microsoft.jet.oledb.4.0;Data source=" & App.Path & "\FAEDB2.2.mdb; Jet OLEDB:Database password=f.a.e.1.0"

  19.  
  20. adoConnection.Open connString

  21.  
  22. 'Verifies database connection

  23. If adoConnection.State = adStateOpen Then

  24.      'Debug.Print "Connection was established"

  25.  
  26.    SQLstr = "SELECT * From Employees WHERE BadgeNumber='" & sBadge & "'"

  27.           

  28.    'Create Recordset Object and Search Employee Database

  29.    Set adoRecordset = New ADODB.Recordset

  30.  
  31.    With adoRecordset

  32.         .Open SQLstr, adoConnection, adOpenKeyset, adLockOptimistic, adCmdText

  33.     

  34.    'Gather employee information

  35.    If .RecordCount = 1 Then

  36.       

  37.       If .Fields("FName").Value = sFirstName And .Fields("LName").Value = sLastName Then

  38.         'If this is the same name,Do all of this

  39.         gbDuplicatedBadge = False

  40.       

  41.         'Stores Employee ID in variable

  42.         gEmployee.EmployeeID = .Fields("EmpID").Value

  43.       

  44.         'Check to see if user has changed departments or centers

  45.         If .Fields("DeptID").Value = cboDepartment.ItemData(cboDepartment.ListIndex) Then

  46.             'Do nothing

  47.         Else

  48.             'Department changed. Save new department value to DB

  49.             .Fields("DeptID").Value = cboDepartment.ItemData(cboDepartment.ListIndex)

  50.         End If

  51.          

  52.         If .Fields("CenterID").Value = cboCenter.ItemData(cboCenter.ListIndex) Then

  53.             'Do nothing

  54.         Else

  55.             'Center changed. Save new department value to DB

  56.             .Fields("CenterID").Value = cboCenter.ItemData(cboCenter.ListIndex)

  57.         End If

  58.         .Update

  59.         .MoveNext

  60.  

  61.       ElseIf .Fields("FName").Value = sFirstName Then 'Assumes a possible change in last name

  62.         'Ask if user's last name changed.

  63.         iResponse = MsgBox("This badge number is already in use. Has your last name changed?", vbQuestion + vbYesNo, "Duplicated Badge Numbers")

  64.         If iResponse = vbNo Then

  65.             iResponse = MsgBox("Someone is already using this badge number. Would you like to make corrections?", vbExclamation + vbYesNo, "Duplicated Badge Numbers")

  66.             If iResponse = vbNo Then

  67.                 'Show message box if user does not change badge numbers.

  68.                 MsgBox "A note will be placed on your exam printout. Please send a copy to the PI Department so that the duplicated badge numbers can be resolved.", vbInformation + vbOKOnly, "Duplicated Badge Numbers"

  69.                 gbDuplicatedBadge = True

  70.                 boolSaveToDB = True

  71.             Else

  72.                 gbDuplicatedBadge = False

  73.                 boolSaveToDB = False

  74.                 txtBadge.SetFocus

  75.                 

  76.                 'Close recordset and connection

  77.                 adoRecordset.Close

  78.                 Set adoRecordset = Nothing

  79.                 adoConnection.Close

  80.                 Set adoConnection = Nothing

  81.                 

  82.                 SearchEmployeeDatabase = vbCancel

  83.                 Exit Function

  84.             End If

  85.         Else

  86.             Do

  87.             'Prompt user to enter previous last name

  88.             sPreviousLastName = InputBox("Please enter your previous last name and press enter.", "Changing Last Name")

  89.             

  90.             Loop While sPreviousLastName = ""

  91.             

  92.             sPreviousLastName = FormatText(sPreviousLastName)

  93.             

  94.             'Check user's previous last name with last name in DB

  95.             If UCase(sPreviousLastName) = UCase(.Fields("LName").Value) Then

  96.                 'Place code here that will update last name

  97.                 adoRecordset!lname = sLastName

  98.                 adoRecordset.Update

  99.                 adoRecordset.MoveNext

  100.                 

  101.                 MsgBox "Your last name has been updated!", vbInformation + vbOKOnly, "Duplicated Badge Numbers"

  102.                 boolSaveToDB = False

  103.             Else

  104.                 MsgBox "There is no record of " & sPreviousLastName & " in the database.", vbExclamation + vbOKOnly, "Changing Last Name"

  105.                 boolSaveToDB = False

  106.                 

  107.                 'Close recordset and connection

  108.                 adoRecordset.Close

  109.                 Set adoRecordset = Nothing

  110.                 adoConnection.Close

  111.                 Set adoConnection = Nothing

  112.             

  113.                 SearchEmployeeDatabase = vbCancel

  114.             End If

  115.         End If

  116.       Else

  117.         'Ask if user would like to change badge number.

  118.         iResponse = MsgBox("Someone is already using this badge number. Would you like to make corrections?", vbExclamation + vbYesNo, "Duplicated Badge Numbers")

  119.         If iResponse = vbNo Then

  120.             'Show message box if user does not change badge numbers.

  121.             MsgBox "A note will be placed on your exam printout. Please send a copy to the PI Department so that the duplicated badge numbers can be resolved.", vbInformation + vbOKOnly, "Duplicated Badge Numbers"

  122.             gbDuplicatedBadge = True

  123.             boolSaveToDB = True

  124.         Else

  125.             gbDuplicatedBadge = False

  126.             boolSaveToDB = False

  127.             txtBadge.SetFocus

  128.             

  129.             'Close recordset and connection

  130.             adoRecordset.Close

  131.             Set adoRecordset = Nothing

  132.             adoConnection.Close

  133.             Set adoConnection = Nothing

  134.                 

  135.             SearchEmployeeDatabase = vbCancel

  136.             Exit Function

  137.         End If

  138.       End If

  139.    ElseIf .RecordCount > 1 Then

  140.         'Ask if user would like to change badge number.

  141.         iResponse = MsgBox("Someone is already using this badge number. Would you like to make corrections?", vbExclamation + vbYesNo, "Duplicated Badge Numbers")

  142.         If iResponse = vbNo Then

  143.             'Show message box if user does not change badge numbers.

  144.             MsgBox "A note will be placed on your exam printout. Please send a copy to the PI Department so that the duplicated badge numbers can be resolved.", vbInformation + vbOKOnly, "Duplicated Badge Numbers"

  145.                         

  146.             'Check to see if one of the duplicated badge numbers belong to user.

  147.             For x = 0 To .RecordCount - 1

  148.                If .Fields("FName").Value = sFirstName And .Fields("LName").Value = sLastName Then

  149.                   'If one of the names match, do this

  150.                   'Stores Employee ID in variable

  151.                   gEmployee.EmployeeID = .Fields("EmpID").Value

  152.             

  153.                   gbDuplicatedBadge = True

  154.                   

  155.                   

  156.                   'Check to see if user has changed departments or centers

  157.                   If .Fields("DeptID").Value = cboDepartment.ItemData(cboDepartment.ListIndex) And _

  158.                      .Fields("CenterID").Value = cboCenter.ItemData(cboCenter.ListIndex) Then

  159.                      'Do nothing

  160.                   ElseIf .Fields("DeptID").Value = cboDepartment.ItemData(cboDepartment.ListIndex) Then

  161.                      'Do nothing

  162.                   Else

  163.                      'Change save new department value to DB

  164.                      adoRecordset!DeptID = cboDepartment.ItemData(cboDepartment.ListIndex)

  165.                   End If

  166.          

  167.                   If .Fields("CenterID").Value = cboCenter.ItemData(cboCenter.ListIndex) Then

  168.                      'Do nothing

  169.                   Else

  170.                      'Change save new department value to DB

  171.                      adoRecordset!CenterID = cboCenter.ItemData(cboCenter.ListIndex)

  172.                   End If

  173.                   

  174.                 .Update

  175.                 

  176.                 Exit For