'=================================================================
Create a Bank Table with following fields,
a.
Sr No
b.
Name
c.
Account No
d.
Address
e.
Debit
f.
Credit
g.
Total
Write a script to perform below task.
a.
Write a function to insert a new entry for Debit
and it should update total field also.
b.
Write a function to insert a new entry for
credit and it should update total field also.
Write a function to update address of user
choice Account'=================================================================
Dim MyConn,cmd,rs
set MyConn= CreateObject("ADODB.Connection")
connectionString = "provider=Microsoft.ACE.OLEDB.12.0;" _
& "data source=D:\bankDB.accdb;Persist Security Info=False;"
MyConn.Open connectionString
Set adx = CreateObject("ADOX.Catalog")
set adx.ActiveConnection = MyConn
initialdebitVal = 0
initialTotalVal = 0
initialCreditVal = 0
userAcctNo = 0
user_exists=false
id=Inputbox ("Enter your Choice 1:debit amount 2:credit amount 3: Change Address 4: New Entry")
Select Case id
case 1: call debitAmt
case 2: call creditAmt
case 3: call changeAddr
case 4: call newEntry
End Select
MyConn.close
function getAmtVals
sInsData = "select * from Bank where Account = " & userAcctNo
MyConn.Execute(sInsData)
Set RS = MyConn.Execute(sInsData)
WHILE NOT RS.EOF
initialdebitVal = RS("Debit")
initialCreditVal = RS("Credit")
initialTotalVal = RS("Total")
user_exists=true
RS.MoveNext
WEND
RS.Close
set RS = nothing
end function
'function to debit amount from the account entered
function debitAmt
userAcctNo = Inputbox ("Enter the Account number")
call getAmtVals
'validating account exists or not
if user_exists=false then
msgbox "Account not found"
exit function
end if
debitVal=Inputbox ("Enter the debit amount")
totalVal = initialTotalVal - debitVal
sInsData = "update Bank set Debit = " & debitVal & " where account=" & userAcctNo
MyConn.Execute(sInsData)
sInsData = "update Bank set Total = " & totalVal & " where account=" & userAcctNo
MyConn.Execute(sInsData)
msgbox "Debited Successfully"
end function
'function to credit amount to the account entered
function creditAmt
userAcctNo = Inputbox ("Enter the Account number")
call getAmtVals
'validating account exists or not
if user_exists=false then
msgbox "Account not found"
exit function
end if
creditVal=Inputbox ("Enter the credit amount")
totalVal = initialTotalVal + creditVal
sInsData = "update Bank set Credit = " & creditVal & " where account=" & userAcctNo
MyConn.Execute(sInsData)
sInsData = "update Bank set Total = " & totalVal & " where account=" & userAcctNo
MyConn.Execute(sInsData)
msgbox "Credited Successfully"
end function
'function to update date to the account entered
function changeAddr
userAcctNo = Inputbox ("Enter the Account number")
call getAmtVals
'validating account exists or not
if user_exists=false then
msgbox "Account not found"
exit function
end if
address=Inputbox ("Enter the new address value")
sInsData = "update Bank set address = '" & address & "'" & " where account=" & userAcctNo
MyConn.Execute(sInsData)
msgbox "Address Updated Successfully"
end function
'function to add new entry
function newEntry
hName=Inputbox ("Enter the Account Holder Name")
hAccount=Inputbox ("Enter the Account number")
hAddress=Inputbox ("Enter the Address")
sql = "insert into Bank(HName,Account,address,debit,credit,total) values('" & hName & "',"& hAccount & ",'" & hAddress & "',0,0,0);"
MyConn.Execute(sql)
msgbox "User Added Successfully!!!!"
end function
