VBA Services Form 01

Option Compare Database

Dim rsServicesID As Recordset
Dim SoftAddMode As Boolean
Dim HardAddMode As Boolean


' On Form Load ==============================================================================================================
Private Sub Form_Load()
    Dim db As DAO.Database
    Dim strSQL As String

    Me.btnAddSoft.Enabled = False
    Me.btnAddHard.Enabled = False
    Me.btnDelSoft.Enabled = True
    Me.btnDelHard.Enabled = True

    Set db = CurrentDb

    ' Query for all Services IDs
    strSQL = "SELECT Services.[Service_ID] " & _
             "FROM Services"

    Set rsServicesID = db.OpenRecordset(strSQL)
    rsServicesID.MoveFirst

    ShowServiceDetails (rsServicesID.Fields("Service_ID").Value)
End Sub


' Buttons for Software List  ================================================================================================
' Show full Software list
Private Sub btnShowSoft_Click()
    ShowFullList ("Software")
    Me.btnAddSoft.Enabled = True
    Me.btnDelSoft.Enabled = False
    SoftAddMode = True
End Sub
' Add selected Softwares to Service
Private Sub btnAddSoft_Click()
    AddSoftwareToService (rsServicesID.Fields("Service_ID").Value)
    ShowServiceDetails (rsServicesID.Fields("Service_ID").Value)
    Me.btnAddSoft.Enabled = False
End Sub
' Delete selected Softwares to Service
Private Sub btnDelSoft_Click()
    DeleteSoftwareFromService (rsServicesID.Fields("Service_ID").Value)
    ShowServiceDetails (rsServicesID.Fields("Service_ID").Value)
    Me.btnAddSoft.Enabled = False
End Sub


' Buttons for Hardware List  ================================================================================================
' Show full Hardware list
Private Sub btnShowHard_Click()
    ShowFullList ("Hardware")
    Me.btnAddHard.Enabled = True
    Me.btnDelHard.Enabled = False
    HardAddMode = True
End Sub
' Add selected Hardware to Service
Private Sub btnAddHard_Click()
    AddHardwareToService (rsServicesID.Fields("Service_ID").Value)
    ShowServiceDetails (rsServicesID.Fields("Service_ID").Value)
    Me.btnAddHard.Enabled = False
End Sub
' Delete selected Hardware to Service
Private Sub btnDelHard_Click()
    DeleteHardwareFromService (rsServicesID.Fields("Service_ID").Value)
    ShowServiceDetails (rsServicesID.Fields("Service_ID").Value)
    Me.btnAddHard.Enabled = False
End Sub


' Buttons for moving through Services =======================================================================================
' Next Service
Private Sub btnNext_Click()
    Me.btnAddSoft.Enabled = False
    Me.btnAddHard.Enabled = False
    Me.btnDelSoft.Enabled = True
    Me.btnDelHard.Enabled = True

    If rsServicesID.RecordCount <> 0 Then
        On Error Resume Next
            rsServicesID.MoveNext
            If Err.Number = 3021 Then
                MsgBox "Äîñòèãíóòà ïîñëåäíÿÿ çàïèñü"
                Err.Clear
            Else
                ShowServiceDetails (rsServicesID.Fields("Service_ID").Value)
            End If
        On Error GoTo 0
    End If
End Sub
' Previous Service
Private Sub btnPrevious_Click()
    Me.btnAddSoft.Enabled = False
    Me.btnAddHard.Enabled = False
    Me.btnDelSoft.Enabled = True
    Me.btnDelHard.Enabled = True

    If rsServicesID.RecordCount <> 0 Then
        On Error Resume Next
            rsServicesID.MovePrevious
            If Err.Number = 3021 Then
                MsgBox "Äîñòèãíóòà ïåðâàÿ çàïèñü"
                Err.Clear
            Else
                ShowServiceDetails (rsServicesID.Fields("Service_ID").Value)
            End If
        On Error GoTo 0
    End If
End Sub


' Buttons to add, delete and edit Service ===================================================================================
' Delete Service
Private Sub btnDelete_Click()
    DeleteService (rsServicesID.Fields("Service_ID").Value)
End Sub
' Edit Service
Private Sub btnChange_Click()
    EditService (rsServicesID.Fields("Service_ID").Value)
    ShowServiceDetails (rsServicesID.Fields("Service_ID").Value)
End Sub
' Add Service
Private Sub btnAdd_Click()
    AddService
    ShowServiceDetails (rsServicesID.Fields("Service_ID").Value)
End Sub

' User-defined Subs =========================================================================================================
' Show Service details
Private Sub ShowServiceDetails(ID As Integer)
    Dim db As DAO.Database
    Dim rsSevice As DAO.Recordset
    Dim rsServiceSoftware As DAO.Recordset
    Dim rsServiceHardware As DAO.Recordset

    Set db = CurrentDb

    ' Query for Services
    strSQL = "SELECT Services.[Service_ID], Services.[Service_name], Services.[Price] " & _
             "FROM Services " & _
             "WHERE Services.[Service_ID] = " & ID
    Debug.Print strSQL

    Set rsSevice = db.OpenRecordset(strSQL)

    Me.textID = rsSevice.Fields("Service_ID").Value    ' Display ID
    Me.sIDlist = rsSevice.Fields("Service_name").Value    ' Display Name
    Me.textPrice = rsSevice.Fields("Price").Value    ' Display Price

    ' Query for Services/Software
    strSQL = "SELECT [Services/Software].Service_ID, [Services/Software].Software_ID, Software.Software_name " & _
             "FROM Software INNER JOIN [Services/Software] ON Software.Software_ID = [Services/Software].Software_ID " & _
             "WHERE [Services/Software].Service_ID = " & ID
    Debug.Print strSQL

    Set rsServiceSoftware = db.OpenRecordset(strSQL)
    Set Me.listSoft.Recordset = rsServiceSoftware

    ' Query for Services/Hardware
    strSQL = "SELECT [Services/Hardware].Service_ID, [Services/Hardware].Hardware_ID, Hardware.Hardware_name " & _
             "FROM Hardware INNER JOIN [Services/Hardware] ON Hardware.Hardware_ID = [Services/Hardware].Hardware_ID " & _
             "WHERE [Services/Hardware].Service_ID = " & ID
    Debug.Print strSQL

    Set rsServiceHardware = db.OpenRecordset(strSQL)
    Set Me.listHard.Recordset = rsServiceHardware
End Sub
' Delete Service
Private Sub DeleteService(ID As Integer)
    Dim db As DAO.Database
    Dim strSQL As String

    Set db = CurrentDb

    ' Delete from Services
    strSQL = "DELETE FROM Services  " & _
             "WHERE Services.Service_ID = " & ID
    Debug.Print strSQL
    DoCmd.RunSQL (strSQL)

    ' Delete from Services/Software
    strSQL = "DELETE FROM [Services/Software] " & _
             "WHERE [Services/Software].Service_ID = " & ID
    Debug.Print strSQL
    DoCmd.RunSQL (strSQL)

    ' Delete from Services/Hardware
    strSQL = "DELETE FROM [Services/Hardware] " & _
             "WHERE [Services/Hardware].Service_ID = " & ID
    Debug.Print strSQL
    DoCmd.RunSQL (strSQL)
End Sub
' Edit Service
Private Sub EditService(ID As Integer)
    Dim db As DAO.Database
    Dim strSQL As String

    Set db = CurrentDb

    ' Update Service_name & Price
    strSQL = "UPDATE Services " & _
             "SET Service_name = '" & Me.sIDlist.Value & "',  " & _
             "Price = '" & Me.textPrice.Value & "' " & _
             "WHERE Services.Service_ID = " & rsServicesID.Fields("Service_ID").Value
    Debug.Print strSQL
    DoCmd.RunSQL (strSQL)
End Sub
' Add Service
Private Sub AddService()
    Dim db As DAO.Database
    Dim strSQL As String

    Set db = CurrentDb

    strSQL = "INSERT INTO Services (Service_name, Price) " & _
             "VALUES ('" & Me.sIDlist.Value & "', " & Me.textPrice.Value & ")"
    Debug.Print strSQL
    DoCmd.RunSQL strSQL
End Sub
' Show full Software or Hardware list
Private Sub ShowFullList(NAME As String)
    Dim db As DAO.Database
    Dim rsList As DAO.Recordset
    Dim strSQL As String

    Set db = CurrentDb

    strSQL = "SELECT " & NAME & "." & NAME & "_ID, " & NAME & "." & NAME & "_ID, " & NAME & "." & NAME & "_name " & _
             "FROM " & NAME
    Debug.Print strSQL

    Set rsList = db.OpenRecordset(strSQL)
    Select Case NAME
        Case "Software"
            Set Me.listSoft.Recordset = rsList
        Case "Hardware"
            Set Me.listHard.Recordset = rsList
    End Select
End Sub
' Add new Softwares to Service
Private Sub AddSoftwareToService(ID As Integer)
    Dim db As DAO.Database
    Dim strSQL As String
    Dim colSelects As Collection
    Set colSelects = New Collection

    Set db = CurrentDb

    For Each i In Me.listSoft.ItemsSelected
        colSelects.Add Me.listSoft.Column(1, i)
    Next i
    For i = 1 To colSelects.Count
        Debug.Print "Collection Software ADD : " & colSelects.Item(i)
        strSQL = "INSERT INTO [Services/Software] (Service_ID, Software_ID) " & _
                 "VALUES (" & ID & ", " & colSelects.Item(i) & ")"
        Debug.Print strSQL
        DoCmd.RunSQL (strSQL)
    Next
End Sub
' Add new Hardwares to Service
Private Sub AddHardwareToService(ID As Integer)
    Dim db As DAO.Database
    Dim strSQL As String
    Dim colSelects As Collection
    Set colSelects = New Collection

    Set db = CurrentDb

    For Each i In Me.listHard.ItemsSelected
        colSelects.Add Me.listHard.Column(1, i)
    Next i
    For i = 1 To colSelects.Count
        Debug.Print "Collection Hardware ADD : " & colSelects.Item(i)
        strSQL = "INSERT INTO [Services/Hardware] (Service_ID, Hardware_ID) " & _
                 "VALUES (" & ID & ", " & colSelects.Item(i) & ")"
        Debug.Print strSQL
        DoCmd.RunSQL (strSQL)
    Next
End Sub
' Delete selected Softwares from Service
Private Sub DeleteSoftwareFromService(ID As Integer)
    Dim db As DAO.Database
    Dim strSQL As String
    Dim colSelects As Collection
    Set colSelects = New Collection

    Set db = CurrentDb

    For Each i In Me.listSoft.ItemsSelected
        colSelects.Add Me.listSoft.Column(1, i)
    Next i
    For i = 1 To colSelects.Count
        Debug.Print "Collection Software DEL : " & colSelects.Item(i)
        strSQL = "DELETE FROM [Services/Software] " & _
                 "WHERE Service_ID = " & ID & " AND Software_ID = " & colSelects.Item(i)
        Debug.Print strSQL
        DoCmd.RunSQL (strSQL)
    Next
End Sub
' Delete selected Hardwares from Service
Private Sub DeleteHardwareFromService(ID As Integer)
    Dim db As DAO.Database
    Dim strSQL As String
    Dim colSelects As Collection
    Set colSelects = New Collection

    Set db = CurrentDb

    For Each i In Me.listHard.ItemsSelected
        colSelects.Add Me.listHard.Column(1, i)
    Next i
    For i = 1 To colSelects.Count
        Debug.Print "Collection Hardware DEL : " & colSelects.Item(i)
        strSQL = "DELETE FROM [Services/Hardware] " & _
                 "WHERE Service_ID = " & ID & " AND Hardware_ID = " & colSelects.Item(i)
        Debug.Print strSQL
        DoCmd.RunSQL (strSQL)
    Next
End Sub