Oracle Table Structure : cust : Name Null? Type ----------------------------------------- -------- ---------------------------ACCNO VARCHAR2(10) USERNAME VARCHAR2(20) PIN NUMBER(5) ACCTYPE VARCHAR2(15) INTRATE VARCHAR2(10) BALANCE NUMBER(10) ADDRESS VARCHAR2(30) STATE VARCHAR2(20) PHONENO NUMBER(15) Form Design : Welcome :
Main :
New User :
Choice :
Deposit :
Withdraw :
Balance Enquiry :
Account Status :
Change Password :
Money Transfer :
Thank You :
Coding : Module : Public db As New ADODB.Connection Public rs As New ADODB.Recordset Public rsTemp As New ADODB.Recordset Public Sub connectDatabase() With db .Provider = "MSDAORA.1;Password=3it9;User ID=3it9;Data Source=oracledb.lab3.sairam.edu;Persist Security Info=True" .Open End With rs.Open "Cust", db, adOpenDynamic, adLockOptimistic, adCmdTable End Sub Public Sub clear_Form_Controls(frm As Form) Dim ctrl As Control For Each ctrl In frm.Controls If TypeOf ctrl Is TextBox Then ctrl.Text = "" ElseIf TypeOf ctrl Is ComboBox Then ctrl.Text = "" End If Next ctrl End Sub Main : Private Sub cmdCancel_Click() End End Sub Private Sub cmdLogin_Click() rs.MoveFirst While (rs.EOF = False) If ((txtAN = rs!AccNo) And (txtPN = rs!pin)) Then txtPN = "" Me.Hide Choice.Show GoTo g End If rs.MoveNext Wend MsgBox "Invalid Account No or Pin No", vbExclamation, "ATM" txtAN = "" txtPN = "" txtAN.SetFocus g: End Sub
Private Sub cmdNU_Click() Me.Hide Admin.Show End Sub New User : Private Sub cboAcc_Click() If (cboAcc.Text = "Current") Then txtID = "20000" Else If (cboAcc.Text = "Savings") Then txtID = "10000" End If End Sub Private Sub cmdCls_Click() Call clear_Form_Controls(Me) End Sub Private Sub cmdHome_Click() Me.Hide Main.Show End Sub Private Sub cmdReg_Click() Dim str As String str = "1.5%" If (txtAccNo = "") Then MsgBox "Enter the Account No", vbExclamation, "ATM" GoTo g ElseIf (txtUN = "") Then MsgBox "Enter the User Name", vbExclamation, "ATM" GoTo g ElseIf (txtPN = "") Then MsgBox "Enter the Pin No", vbExclamation, "ATM" GoTo g ElseIf (txtAdd = "") Then MsgBox "Enter the Address", vbExclamation, "ATM" GoTo g ElseIf (txtSt = "") Then MsgBox "Enter the State", vbExclamation, "ATM" GoTo g ElseIf (txtPhN = "") Then MsgBox "Enter the Phone No", vbExclamation, "ATM" GoTo g End If If (rs.BOF = False) Then rs.MoveLast
End If rsTemp.Open "Insert into cust values('" & txtAccNo & "','" & txtUN & "','" & txtPN & "','" & cboAcc & "','" & str & "','" & txtID & "','" & txtAdd & "','" & txtSt & "','" & txtPhN & "')", db, adOpenKeyset, adLockOptimistic MsgBox "You are registered as a new User" Me.Hide Main.Show g: End Sub Private Sub cmdX_Click() End End Sub Admin : Private Sub cmdCan_Click() Me.Hide Main.Show End Sub Private Sub cmdMO_Click() If (txtPass = "delllaptop") Then txtPass = "" Me.Hide NewUser.Show Else MsgBox "Invalid Password...Check it again", vbExclamation, "ATM" txtPass = "" txtPass.SetFocus End If End Sub Choice : Private Sub cmdAS_Click() rs.MoveFirst While (rs.EOF = False) If (rs!AccNo = Main.txtAN) Then Account.txtAcc = rs!AccNo Account.txtUN = rs!UserName Account.txtAT = rs!AccType Account.txtIR = rs!IntRate GoTo g End If rs.MoveNext Wend g: Me.Hide Account.Show End Sub
Private Sub cmdBal_Click() rs.MoveFirst While (rs.EOF = False) If (rs!AccNo = Main.txtAN) Then Balance.txtAcc = rs!AccNo Balance.txtUN = rs!UserName Balance.txtAT = rs!AccType Balance.txtBal = rs!Balance GoTo g End If rs.MoveNext Wend g: Me.Hide Balance.Show End Sub Private Sub cmdCPN_Click() rs.MoveFirst While (rs.EOF = False) If (rs!AccNo = Main.txtAN) Then pin.txtUN = rs!UserName GoTo g End If rs.MoveNext Wend g: Me.Hide pin.Show End Sub Private Sub cmdDep_Click() Me.Hide Deposit.Show End Sub Private Sub cmdLO_Click() Me.Hide Welcome.Show End Sub Private Sub cmdMT_Click() Me.Hide Money.Show End Sub Private Sub cmdQuit_Click() End End Sub
Private Sub cmdWith_Click() rs.MoveFirst While (rs.EOF = False) If (rs!AccNo = Main.txtAN) Then Withdraw.txtBal = rs!Balance GoTo e Else rs.MoveNext End If Wend e: Me.Hide Withdraw.Show End Sub Deposit : Private Sub cmdClear_Click() txtAmt = "" txtAmt.SetFocus End Sub Private Sub cmdDeposit_Click() Dim bal As Double If (txtAmt = "") Then MsgBox "Enter the amount to be deposited", vbExclamation, "ATM" GoTo g End If rs.MoveFirst While (rs.EOF = False) If (rs!AccNo = Main.txtAN) Then bal = Val(rs!Balance) + Val(txtAmt) rsTemp.Open "Update cust set balance='" & bal & "' where AccNo='" & rs!AccNo & "'", db, adOpenKeyset, adLockOptimistic MsgBox "Your Amount has been deposited", vbInformation Me.Hide Thanks.Show GoTo g Else rs.MoveNext End If Wend g: End Sub Private Sub cmdQuit_Click() End End Sub Withdraw :
Private Sub cmdGC_Click() Dim bal As Double If (txtWA = "") Then MsgBox "Enter the amount to be Withdrawn", vbExclamation, "ATM" GoTo g End If rs.MoveFirst While (rs.EOF = False) If (rs!AccNo = Main.txtAN) Then If ((Val(rs!Balance) - Val(txtWA)) > 0) Then bal = Val(rs!Balance) - Val(txtWA) rsTemp.Open "Update cust set balance='" & bal & "' where AccNo='" & rs!AccNo & "'", db, adOpenKeyset, adLockOptimistic MsgBox "The Amount have been Withdrawn", vbInformation, "ATM" Me.Hide Thanks.Show GoTo g ElseIf ((Val(rs!Balance) - Val(txtWA)) < 0) Then MsgBox "The Amount you are trying to withdraw is greater then your balance", vbInformation, "ATM" txtWA = "" txtWA.SetFocus GoTo g ElseIf ((Val(rs!Balance) - Val(txtWA)) = 0) Then bal = Val(rs!Balance) - Val(txtWA) rsTemp.Open "Update cust set balance='" & bal & "' where AccNo='" & rs!AccNo & "'", db, adOpenKeyset, adLockOptimistic MsgBox "You have zero Balance", vbInformation, "ATM" Me.Hide Thanks.Show GoTo g End If Else rs.MoveNext End If Wend g: End Sub Private Sub cmdQuit_Click() End End Sub Balance Enquiry : Private Sub cmdExit_Click() End End Sub Private Sub cmdOk_Click()
Me.Hide Thanks.Show End Sub Account Status : Private Sub cmdOk_Click() Me.Hide Thanks.Show End Sub Change Password : Private Sub cmdOk_Click() Dim newpin As Integer rs.MoveFirst While (rs.EOF = False) If (rs!AccNo = Main.txtAN) Then If ((rs!pin = Val(txtCPN)) And (txtNP = txtRPN)) Then newpin = Val(txtNP) rsTemp.Open "Update cust set Pin='" & newpin & "' where AccNo='" & rs!AccNo & "'", db, adOpenKeyset, adLockOptimistic MsgBox "Your Pin No has been changed", vbInformation, "ATM" Me.Hide Thanks.Show GoTo e Else MsgBox "The pin no does not match....Try again", vbInformation, "ATM" txtCPN = "" txtNP = "" txtRPN = "" txtCPN.SetFocus GoTo e End If End If rs.MoveNext Wend e: End Sub Private Sub txtQuit_Click() End End Sub Money Transfer : Private Sub cmdClear_Click() txtAmt = "" txtUAcc = "" txtUAcc.SetFocus
End Sub Private Sub cmdQuit_Click() End End Sub Private Sub cmdTrans_Click() Dim a, b As Double rs.MoveFirst While (rs.EOF = False) If (rs!AccNo = Main.txtAN) Then a = Val(txtAmt) If (Val(rs!Balance) < a) Then MsgBox "The amount to be transfered is greater than your balance", vbInformation, "ATM" txtAmt = "" txtAmt.SetFocus GoTo g End If rs.MoveFirst While (rs.EOF = False) If (rs!AccNo = txtUAcc) Then b = Val(rs!Balance) + a rsTemp.Open "Update cust set balance='" & b & "' where AccNo='" & rs!AccNo & "'", db, adOpenKeyset, adLockOptimistic MsgBox "Money has been transfered to the destination account", vbInformation, "ATM" rs.MoveFirst While (rs.EOF = False) If (rs!AccNo = Main.txtAN) Then b = Val(rs!Balance) - a rsTemp.Open "Update cust set balance='" & b & "' where AccNo='" & rs!AccNo & "'", db, adOpenKeyset, adLockOptimistic End If rs.MoveNext Wend Me.Hide Thanks.Show GoTo g End If rs.MoveNext Wend MsgBox "The destination Account No does not exist", vbExclamation, "ATM" txtUAcc = "" txtUAcc.SetFocus GoTo g End If rs.MoveNext Wend g:End Sub
Thank You : Private Sub cmdOk_Click() Call clear_Form_Controls(Main) Call clear_Form_Controls(Account) Call clear_Form_Controls(Balance) Call clear_Form_Controls(Deposit) Call clear_Form_Controls(Money) Call clear_Form_Controls(pin) Call clear_Form_Controls(Withdraw) Call clear_Form_Controls(NewUser) Me.Hide Welcome.Show End Sub Output : Welcome :
Login :
Choice :
Deposit :
Withdraw :
Balance :
Administrator :
User Details :
Change Password :
Money Transfer :
Account Type :
Thank You :