Option Compare Database PDF

Preview

Summary

Download Option Compare Database PDF

Description

Option Compare Database Option Explicit Dim rs As New ADODB.Recordset Dim con As New ADODB.Connection Dim dsales, interSales As Currency

Private Sub btnCreateTable_Click() On Error GoTo Problem con.Execute "CREATE TABLE tblVaalEnterpriseSales1" & "(" & " StaffNumber String(5)," & "MeyertonSales currency," & "VanderbijparkSales Currency, " & "TotalSales Currency," & " Commission Currency" & ");" MsgBox " Table created", vbOKOnly Or vbInformation Me.chkCreate.Visible = False Me.btnCreateTable.Enabled = False Exit Sub Problem: MsgBox ("Error " & Err.Description) End Sub

Private Sub btnview_Click() On Error GoTo Problem Dim sql As String

sql = " SELECT * FROM tblVaalEnterpriseSales" rs.Open sql, con, adOpenKeyset, adLockReadOnly, adCmdText rs.MoveFirst DisplayEmployee btnview.Enabled = False Exit Sub Problem: MsgBox (Err.Description) End Sub

Private Sub chkCreate_Click() If chkCreate.Value = True Then Me.btnCreateTable.Visible = True ElseIf Me.chkCreate.Value = False Then Me.btnCreateTable.Visible = False End If End Sub

'Save button Private Sub Command36_Click() Dim sales As Currency rs.Open "tblVaalEnterpriseSales", con, adOpenDynamic, adLockOptimistic 'adding data to the table With rs .AddNew .Fields.Item(0) = Me.cboCode .Fields.Item(1) = dsales .Fields.Item(2) = interSales sales = dsales + interSales .Fields.Item(3) = sales 'rs.Fields.Item(4) = CalcCommission(sales) .Update .Close End With Set rs = Nothing 'Set con = Nothing

End Sub 'commision 'Private Sub Command38_Click() '' Sales ($) Commission ''1 – 100,000.99 2% of sales ''100,001 – 400,000.99 R2000 plus 5% of the sales over R100,000 ''400,001 and over R17000 plus 10% of the sales over R400,000 ' 'End Sub Private Function CalcCommission(ByVal sales As Currency) Const PERC_COMM1 As Double = 0.02 Const PERC_COMM2 As Double = 0.05 Const PERC_COMM3 As Double = 0.1 Select Case sales Case 1 To 100000 CalcCommission = (sales * PERC_COMM1) Case 100001 To 400000 CalcCommission = (2000 + (sales - 100000) * PERC_COMM2) Case Is > 400000 CalcCommission = (17000 + (sales - 400000) * PERC_COMM3) End Select

End Function 'update button Private Sub Command39_Click() On Error GoTo Problem Dim rs1 As New ADODB.Recordset Dim sql As String Dim sales As Double 'rs.Close 'Build dynamic SQL statement based on record

'selected by the user. '=" & "'" & Me.txtProduct & "'" sql = "select * from tblVaalEnterpriseSales where StaffNumber = " & "'" & Me.txtsalesPcode & "'"

'Assign updatable cursor and lock type properties AND rs1.CursorType = adOpenDynamic rs1.LockType = adLockOptimistic 'Open the Recordset object. rs1.Open sql, con, , , adCmdText 'Don't try to update the record, if the recordset 'did not find a row. If rs1.EOF = False Then 'Update the record based on input from the user. With rs1 .Fields.Item(1) = Me.txtDomestic .Fields.Item(2) = Me.txtInternational sales = rs1.Fields.Item(1) + rs1.Fields.Item(2) .Fields.Item(3) = sales 'rs1.Fields.Item(4) = CalcCommission(sales) .Update .Close End With End If Exit Sub Problem: MsgBox (Err.Description) End Sub

'next record Private Sub Command58_Click()

If Not (rs.EOF And rs.BOF) Then rs.MoveNext DisplayEmployee End If If rs.EOF = True Then MsgBox ("you reached the end of the file. Click ok to display the first record") rs.MoveFirst DisplayEmployee End If

End Sub 'button calculate commisson Private Sub Command59_Click() rs1.Fields.Item(4) = CalcCommission(sales) End Sub

Private Sub Detail_Click()

End Sub

Private Sub Form_Load()

Me.cboSales.RowSource = "" Dim k As Double For k = 50000 To 600000 Step 55000 Me.cboSales.AddItem (k) Next

Me.cboCode.RowSource = "" Me.cboCode.AddItem ("BX200") Me.cboCode.AddItem ("SM216")

Me.cboCode.AddItem ("BR555") Me.cboCode.AddItem ("HH098")

Me.chkCreate.Value = False Me.btnCreateTable.Visible = False 'Me.btnCreateTable.Enabled = False Set con = CurrentProject.Connection End Sub

Private Sub OptDomestic_Click() Me.OptInternational.Value = False dsales = Me.cboSales Me.cboSales.Value = "" End Sub

Private Sub optInternational_Click() OptDomestic.Value = False interSales = Me.cboSales Me.cboSales.Value = "" End Sub Private Sub DisplayEmployee() If Not rs.EOF And Not rs.BOF Then Me.txtsalesPcode = rs.Fields.Item(0) Me.txtDomestic = rs.Fields.Item(1) Me.txtInternational = rs.Fields.Item(2) Me.txtTotal = rs.Fields.Item(3) Me.txtComm = rs.Fields.Item(4) End If End Sub...