Add or Edit Company Name

Private Sub cbtn_Add_compname_Click()
  Dim conAddQueue As New ADODB.Connection
    Dim strSQLAddQueue, strSQLUpdateQueue As String
    Dim cmdAddQueue  As New ADODB.Command
    Dim cmdUpdateQueue  As New ADODB.Command
    Dim ConnString As String
    Dim RsQueue As ADODB.Recordset
    Set RsQueue = New ADODB.Recordset
  
    ‘Daily Database path
strDB_Path = “Z:\GSO\WorkTrackerNCD\FinalToSend\NCDWorkTracker.accdb”
‘Monthly Database path
strDB_PathMonthly = “Z:\GSO\WorkTrackerNCD\FinalToSend\MonthlyTracker.accdb”
     ConnString = “Provider= Microsoft.ACE.OLEDB.12.0; Data Source=” & ThisWorkbook.MyPath & “;”
 conAddQueue.Open ConnString
  
  
    Dim company_Id As Integer
    Dim status As String
  
     For iListCount1 = 0 To UserForm1.Company_name_ListBox1.ListCount – 1
If UserForm1.Company_name_ListBox1.Selected(iListCount1) = True Then
status = “Selected”
‘CntEmp = CntEmp + 1
 End If
Next iListCount1
If status = “Selected” Then
      
      Dim company_name
  company_name = UserForm1.txt_company_name.Text
        If company_name = “” Then
            MsgBox “Please enter company name..”
        Else
  
    company_Id = UserForm1.Company_name_ListBox1.Column(1)
 
 
    QStr = “SELECT Company_Name , SrNo FROM CompanyTbl where SrNo = ” & company_Id
    RsQueue.Open QStr, conAddQueue
  
    If RsQueue.EOF Then
  
    MsgBox “not allowed to add new company name”
      
    Else
        Set cmdUpdateQueue.ActiveConnection = conAddQueue
        strSQLUpdateQueue = “Update CompanyTbl set Company_Name = ‘” & company_name & “‘  where SrNo = ” & company_Id
      
        cmdUpdateQueue.CommandType = adCmdText
        cmdUpdateQueue.CommandText = strSQLUpdateQueue
        cmdUpdateQueue.Execute
        MsgBox (“Record for ” & company_name & ”  is Updated..”)
        UserForm1.Company_name_ListBox1.ListIndex = -1
        UserForm1.txt_company_name.Text = “”
      
    End If
  
 
    ‘Call cbtn_ViewEmp_Click
    ‘Call resetQueue_btn_Click
    Call companyList
  
    conAddQueue.Close
    Set RsQueue = Nothing
  
    End If
  
    Else
    
   
       company_name = UserForm1.txt_company_name.Text
   
            Set cmdAddQueue.ActiveConnection = conAddQueue
            strSQLAddQueue = “Insert into CompanyTbl(Company_Name)”
            strSQLAddQueue = strSQLAddQueue & ” Values( ‘” & company_name & “‘)”
            cmdAddQueue.CommandType = adCmdText
            cmdAddQueue.CommandText = strSQLAddQueue
            cmdAddQueue.Execute
            MsgBox (“Record for ” & company_name & ”  is added to the database..”)
            UserForm1.Company_name_ListBox1.ListIndex = -1
            UserForm1.txt_company_name.Text = “”
           ‘ Call resetQueue_btn_Click
            Call companyList
            conAddQueue.Close
            Set RsQueue = Nothing
        End If
  
  
End Sub
Private Sub cbtn_Delete_compname_Click()
  Dim conDelQueue As New ADODB.Connection
    Dim strSQLDelQueue As String
    Dim cmdDelQueue  As New ADODB.Command
    Dim ConnString As String
  
    Dim Rs_DelChk As ADODB.Recordset
    Set Rs_DelChk = New ADODB.Recordset
  
    Dim Rs_SearchQueue As ADODB.Recordset
    Set Rs_SearchQueue = New ADODB.Recordset
  
     ‘Daily Database path
strDB_Path = “Z:\GSO\WorkTrackerNCD\FinalToSend\NCDWorkTracker.accdb”
‘Monthly Database path
strDB_PathMonthly = “Z:\GSO\WorkTrackerNCD\FinalToSend\MonthlyTracker.accdb”
  
     ConnString = “Provider= Microsoft.ACE.OLEDB.12.0; Data Source=” & ThisWorkbook.MyPath & “;”
 conDelQueue.Open ConnString


  Dim company_name
  company_name = UserForm1.txt_company_name.Text
        If company_name = “” Then
            MsgBox “Please enter company name..”
        Else
  
    MSG = MsgBox(“Are you sure! you want to delete this record..”, vbYesNo, “Delete Queue”)
If MSG = vbYes Then
 
 
    Dim company_Id As String
    ‘EId = UserForm1.txt_EID.Text
    company_Id = UserForm1.Company_name_ListBox1.Column(1)
    company_name = UserForm1.txt_company_name.Text
  
      
    Rs_SearchQueue.Open “DELETE FROM CompanyTbl WHERE ((SrNo = ” & company_Id & “))”, conDelQueue, adOpenDynamic, adLockBatchOptimistic
    MsgBox “company name ” & company_name & ” has been deleted..”
    UserForm1.Company_name_ListBox1.ListIndex = -1
    UserForm1.txt_company_name.Text = “”
   ‘  Call resetQueue_btn_Click
   ‘ Call cbtn_ViewEmp_Click
  
  
  
    conDelQueue.Close
    ‘conDelEmp.Close
  
    Set Rs_DelChk = Nothing
    Set Rs_SearchQueue = Nothing
    ‘Rs_SearchEmp.Close
    Call companyList
  
    Else
    MsgBox “company name Cannot be deleted”
     UserForm1.Company_name_ListBox1.ListIndex = -1
    End If
  
End If
End Sub
Private Sub Company_name_ListBox1_Click()
 Dim conSearchQueue As New ADODB.Connection
    Dim strSQLSearch As String
    Dim cmdSearchQueue  As New ADODB.Command
    Dim ConnString As String
    Dim Rs_SearchQueue As ADODB.Recordset
    Set Rs_SearchQueue = New ADODB.Recordset
  
    ‘Daily Database path
strDB_Path = “Z:\GSO\WorkTrackerNCD\FinalToSend\NCDWorkTracker.accdb”
‘Monthly Database path
strDB_PathMonthly = “Z:\GSO\WorkTrackerNCD\FinalToSend\MonthlyTracker.accdb”
    ‘Set cmdSearchEmp.ActiveConnection = conn
    ConnString = “Provider= Microsoft.ACE.OLEDB.12.0; Data Source=” & ThisWorkbook.MyPath & “;”
 conSearchQueue.Open ConnString
    
    
    Dim company_Id As Integer
    company_Id = UserForm1.Company_name_ListBox1.Column(1)
      
    Rs_SearchQueue.Open “SELECT Company_Name , SrNo FROM CompanyTbl where SrNo = ” & company_Id, conSearchQueue, adOpenForwardOnly, adLockReadOnly
          
        If Rs_SearchQueue.EOF Then
            MsgBox “Company does not exist”
        Else
            UserForm1.txt_company_name.Text = Rs_SearchQueue.Fields(0)
          
        End If
    
    conSearchQueue.Close
    Set Rs_SearchQueue = Nothing
End Sub
Private Sub reset_companyname_btn_Click()
UserForm1.txt_company_name.Text = “”
UserForm1.Company_name_ListBox1.ListIndex = -1
End Sub
Private Sub UserForm_Click()
End Sub
Public Sub companyList()
     
      Dim connViewQueue As New ADODB.Connection
    Dim ConnString, lastrow, lastrow1
        Dim Rs_ViewQueue As ADODB.Recordset
        Set Rs_ViewQueue = New ADODB.Recordset
      
        ‘Daily Database path
strDB_Path = “Z:\GSO\WorkTrackerNCD\FinalToSend\NCDWorkTracker.accdb”
‘Monthly Database path
strDB_PathMonthly = “Z:\GSO\WorkTrackerNCD\FinalToSend\MonthlyTracker.accdb”
      
       ConnString = “Provider= Microsoft.ACE.OLEDB.12.0; Data Source=” & ThisWorkbook.MyPath & “;”
  connViewQueue.Open ConnString
      
        ‘UserForm1.list_ViewEmp.Visible = True
    
       ‘ Sheets(“QueueData”).Activate
        lastrow = Sheets(“Data”).Range(“A8″).End(xlDown).Row
      
  Rs_ViewQueue.Open “SELECT Company_Name , SrNo FROM CompanyTbl  “, connViewQueue, adOpenStatic, adLockReadOnly
     
        Sheets(“Data”).Range(“A8:G” & lastrow).ClearContents
        Sheets(“Data”).Range(“A8″).CopyFromRecordset Rs_ViewQueue
        lastrow1 = Sheets(“Data”).Range(“A8″).End(xlDown).Row
        ‘UserForm1.Company_name_ListBox1.RowSource = “”
        UserForm1.Company_name_ListBox1.RowSource = “‘Data’!A8:G” & lastrow1
      
        connViewQueue.Close
        Set Rs_ViewQueue = Nothing
         
      
End Sub
Private Sub UserForm_Initialize()
‘Daily Database path
strDB_Path = “Z:\GSO\WorkTrackerNCD\FinalToSend\NCDWorkTracker.accdb”
‘Monthly Database path
strDB_PathMonthly = “Z:\GSO\WorkTrackerNCD\FinalToSend\MonthlyTracker.accdb”
”Daily Database path
‘strDB_Path = “\\via\dfs\NUI\SM\M\R\CustCentreComms\CONFIDENTIAL\CONTROLLED ACCESS 2\NCDWorkTracker.accdb”
‘
”Monthly Database path
‘strDB_PathMonthly = “\\via\dfs\NUI\SM\M\R\CustCentreComms\CONFIDENTIAL\CONTROLLED ACCESS 2\MonthlyTracker.accdb”
Call companyList
End Sub