startapplication.vb

这是一个VBA开发的项目实例

        Me.Panel3.ResumeLayout(False)
        Me.TabPage2.ResumeLayout(False)
        Me.Panel1.ResumeLayout(False)
        Me.TabPage3.ResumeLayout(False)
        Me.Panel2.ResumeLayout(False)
        CType(Me.AxWebBrowser1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub StartApplication_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            'load the list of customers
            clientConnection.Open()
            strSqlStatement = "SELECT Client_Name FROM tblClients"
            clientCommand = New OleDbCommand(strSqlStatement, clientConnection)
            clientDataReader = clientCommand.ExecuteReader()
            While clientDataReader.Read
                lstExistingCustomers.Items.Add(clientDataReader.Item(0))
            End While
            clientDataReader.Close()

            'load data in the second tab
            strSqlStatement = "SELECT * FROM tblSalesRep"

            clientCommand = New OleDbCommand(strSqlStatement, clientConnection)

            clientDataReader = clientCommand.ExecuteReader()

            While clientDataReader.Read
                editName.Text = IIf(IsDBNull(clientDataReader.Item(1)), "", clientDataReader.Item(1))
                editTitle.Text = IIf(IsDBNull(clientDataReader.Item(2)), "", clientDataReader.Item(2))
                editAddress_Line1.Text = IIf(IsDBNull(clientDataReader.Item(3)), "", clientDataReader.Item(3))
                editAddress_Line2.Text = IIf(IsDBNull(clientDataReader.Item(4)), "", clientDataReader.Item(4))
                editCity.Text = IIf(IsDBNull(clientDataReader.Item(5)), "", clientDataReader.Item(5))
                editState.Text = IIf(IsDBNull(clientDataReader.Item(6)), "", clientDataReader.Item(6))
                editZipCode.Text = IIf(IsDBNull(clientDataReader.Item(7)), "", clientDataReader.Item(7))
                editPhoneNumber.Text = IIf(IsDBNull(clientDataReader.Item(8)), "", clientDataReader.Item(8))
                editCellPhoneNumber.Text = IIf(IsDBNull(clientDataReader.Item(9)), "", clientDataReader.Item(9))
                editFax.Text = IIf(IsDBNull(clientDataReader.Item(10)), "", clientDataReader.Item(10))
                editEmailAddress.Text = IIf(IsDBNull(clientDataReader.Item(11)), "", clientDataReader.Item(11))
                editCompany.Text = IIf(IsDBNull(clientDataReader.Item(12)), "", clientDataReader.Item(12))
            End While
            clientDataReader.Close()
            clientConnection.Close()
            'load the help html file
            AxWebBrowser1.Navigate2("c:\easyselling\help.html")
            AxWebBrowser1.BackColor = System.Drawing.Color.PowderBlue
        Catch
            MsgBox(Err.Description)
        End Try
    End Sub

    Private Sub btnDeleteCust_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeleteCust.Click
        Try
            Dim varReply As Integer
            If ShareClass.SharedValue = "" Then
                MsgBox("Select a Customer Name from the List.", MsgBoxStyle.Exclamation, "No Customer Selected")
            Else
                varReply = MsgBox("Do you Really want to Delete this Record?", MsgBoxStyle.YesNo, "Confirmation")
                If varReply = 6 Then
                    'delete record from tblClients
                    strSqlStatement = "DELETE FROM tblClients WHERE Client_Name = '" & lstExistingCustomers.SelectedItem & "'"
                    clientConnection.Open()
                    clientCommand = New OleDbCommand(strSqlStatement, clientConnection)
                    clientCommand.ExecuteNonQuery()

                    'delete record from tblClientExpections
                    strSqlStatement = "DELETE FROM tblClientExpectations WHERE Client_ID = " & ShareClass.SharedClientID
                    clientCommand = New OleDbCommand(strSqlStatement, clientConnection)
                    clientCommand.ExecuteNonQuery()

                    'delete record from tblClientSolutions
                    strSqlStatement = "DELETE FROM tblClientSolutions WHERE Client_ID = " & ShareClass.SharedClientID
                    clientCommand = New OleDbCommand(strSqlStatement, clientConnection)
                    clientCommand.ExecuteNonQuery()

                    'delete record from tblClientSolutions
                    strSqlStatement = "DELETE FROM tblProductUsageAndCompAnalysis WHERE Client_ID = " & ShareClass.SharedClientID
                    clientCommand = New OleDbCommand(strSqlStatement, clientConnection)
                    clientCommand.ExecuteNonQuery()
                    clientConnection.Close()
                    clientConnection = Nothing
                    clientConnection = New OleDbConnection(ourConn)
                    clientConnection.Open()
                    lstExistingCustomers.Items.Clear()
                    strSqlStatement = "SELECT Client_Name FROM tblClients"
                    clientCommand = New OleDbCommand(strSqlStatement, clientConnection)
                    clientDataReader = clientCommand.ExecuteReader()
                    While clientDataReader.Read
                        lstExistingCustomers.Items.Add(clientDataReader.Item(0))
                    End While
                    ShareClass.SharedValue = ""
                    clientConnection.Close()
                End If
            End If
        Catch
            MsgBox(Err.Description)
        End Try
    End Sub


    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        Dim adoConn As New ADODB.Connection()
        Dim rsSalesRep As New ADODB.Recordset()
        Try
            adoConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\EasySelling\EasySelling.mdb;Persist Security Info=False")
        Catch
            MsgBox("Could not open database", MsgBoxStyle.Critical, "Problem with Database Open")
            End
        End Try
        rsSalesRep.Open("select * from tblSalesRep", adoConn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)
        'check if the application is being run the first time, if 'N' then edit or addnew
        If checkFirstTime = "N" Then
            If ValidateFields() = True Then

                rsSalesRep.Fields("SalesRep_Name").Value = Trim(editName.Text)
                rsSalesRep.Fields("SalesRep_Title").Value = editTitle.Text
                rsSalesRep.Fields("Address_Line1").Value = editAddress_Line1.Text
                rsSalesRep.Fields("Address_Line2").Value = editAddress_Line2.Text
                rsSalesRep.Fields("city").Value = editCity.Text
                rsSalesRep.Fields("state").Value = editState.Text
                rsSalesRep.Fields("zipcode").Value = editZipCode.Text
                rsSalesRep.Fields("PhoneNumber").Value = Trim(editPhoneNumber.Text)
                rsSalesRep.Fields("CellPhoneNumber").Value = editCellPhoneNumber.Text
                rsSalesRep.Fields("Fax").Value = editFax.Text
                rsSalesRep.Fields("EmailAddress").Value = Trim(editEmailAddress.Text)
                rsSalesRep.Fields("CompanyName").Value = Trim(editCompany.Text)
                rsSalesRep.Update()
                MsgBox("Record Saved Successfully.", MsgBoxStyle.Information, "Record Saved")

            End If
        Else
            'if the data for sales rep is not entered as yet then add a new record
            If ValidateFields() = True Then
                rsSalesRep.AddNew()
                rsSalesRep.Fields("SalesRep_Name").Value = Trim(editName.Text)
                rsSalesRep.Fields("SalesRep_Title").Value = editTitle.Text
                rsSalesRep.Fields("Address_Line1").Value = editAddress_Line1.Text
                rsSalesRep.Fields("Address_Line2").Value = editAddress_Line2.Text
                rsSalesRep.Fields("city").Value = editCity.Text
                rsSalesRep.Fields("state").Value = editState.Text
                rsSalesRep.Fields("zipcode").Value = editZipCode.Text
                rsSalesRep.Fields("PhoneNumber").Value = Trim(editPhoneNumber.Text)
                rsSalesRep.Fields("CellPhoneNumber").Value = editCellPhoneNumber.Text
                rsSalesRep.Fields("Fax").Value = editFax.Text
                rsSalesRep.Fields("EmailAddress").Value = Trim(editEmailAddress.Text)
                rsSalesRep.Fields("CompanyName").Value = Trim(editCompany.Text)
                rsSalesRep.Update()
                MsgBox("Record Saved Successfully.", MsgBoxStyle.Information, "Record Saved")

                'set the flag for tblInstalldecide as 'N' to indicate that sales rep record
                'has been added
                strSqlStatement = "UPDATE tblInstallDecide SET First_time = 'N'"
                clientConnection.Open()
                clientCommand = New OleDbCommand(strSqlStatement, clientConnection)
                clientCommand.ExecuteNonQuery()
                clientConnection.Close()
            End If
        End If
        rsSalesRep.Close()
        adoConn.Close()
    End Sub

    Private Sub TabStart_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles TabStart.Click

        If TabStart.SelectedIndex = 1 Then
            'check if running for the first time and give a message
            clientConnection.Open()
            strSqlStatement = "SELECT First_Time FROM tblInstalldecide"
            clientCommand = New OleDbCommand(strSqlStatement, clientConnection)
            clientDataReader = clientCommand.ExecuteReader()
            While clientDataReader.Read
                checkFirstTime = clientDataReader.Item(0)
            End While
            If checkFirstTime = "Y" Then
                MsgBox("The Sales Representative's details are required to generate documents.Please fill in your details.", MsgBoxStyle.Information, "Welcome to EasySelling")
            End If
            clientDataReader.Close()
            clientConnection.Close()

        End If
    End Sub
    Private Function ValidateFields() As Boolean
        'the name, phone number, emailaddress and company name are required fields
        If Trim(editName.Text) = "" Then
            MsgBox("Sales Representative's Name cannot be empty.", MsgBoxStyle.Critical, "Information Missing")
            editName.Focus()
            Return False
            Exit Function
        End If
        If Trim(editPhoneNumber.Text) = "" Then
            MsgBox("Phone Number for Sales Representative cannot be empty.", MsgBoxStyle.Critical, "Information Missing")
            editPhoneNumber.Focus()
            Return False
            Exit Function
        End If
        If Trim(editEmailAddress.Text) = "" Then
            MsgBox("Email Address for Sales Representative cannot be empty.", MsgBoxStyle.Critical, "Information Missing")
            editEmailAddress.Focus()
            Return False
            Exit Function
        End If
        If Trim(editCompany.Text) = "" Then
            MsgBox("Parent Company for Sales Representative cannot be empty.", MsgBoxStyle.Critical, "Information Missing")
            editCompany.Focus()
            Return False
            Exit Function
        End If
        Return True
    End Function


    Private Sub btnDeselectAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDeselectAll.Click
        'clear the list box
        lstExistingCustomers.ClearSelected()
    End Sub

    Private Sub lstExistingCustomers_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstExistingCustomers.SelectedValueChanged
        'set the selected customer as the current customer
        ShareClass.SharedValue = lstExistingCustomers.SelectedItem
        clientConnection.Open()
        strSqlStatement = "SELECT Client_ID FROM tblClients WHERE Client_Name = '" & lstExistingCustomers.SelectedItem & "'"
        clientCommand = New OleDbCommand(strSqlStatement, clientConnection)
        clientDataReader = clientCommand.ExecuteReader()
        While clientDataReader.Read
            ShareClass.SharedClientID = clientDataReader.Item(0)
        End While
        clientDataReader.Close()
        clientConnection.Close()
    End Sub

    Private Sub btnRefreshList_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRefreshList.Click
        'refresh the list of customer from the database to reflect the latest entries
        lstExistingCustomers.Items.Clear()
        clientConnection.Open()
        strSqlStatement = "SELECT Client_Name FROM tblClients"
        clientCommand = New OleDbCommand(strSqlStatement, clientConnection)
        clientDataReader = clientCommand.ExecuteReader()
        While clientDataReader.Read
            lstExistingCustomers.Items.Add(clientDataReader.Item(0))
        End While
        clientDataReader.Close()
        clientConnection.Close()
    End Sub
End Class