How can i Calculate Transactions from DB

[Nova6112]

I've developed a simple banking system containing Deposite and Withdraw.

I am using deposite,withdraw and balance are my database fields

when i try to deposite the last balance fetched and then added with new deposite value i'm using (select last(deposite) from bank) this query to fetch last balance value .

sometimes the arrangement of database value changes so last(balance) value becomes wrong

how can i overcome this problem

if is there any technique to save transaction pls help me
thanking you in advance..

[Santanu.Das]

Post your codes first, then describe your problem and what do you desire. Too many exparts are here, obviously they help you to ovecome your problem.

[Nova6112]

Table Name : ac
Fields : desc,dep,exp,tot,dat

dep:- it describes the deposited amounts are stored in this field
exp:- it describes the withdraw amounts are stored in this field
tot:- it describes the balance amounts after the deposite and withdraw operations are done are stored in this field
dat:- date of the transaction.

1.For deposite or withdraw i should add or subtract from the tot value so i need to know the last tot amount and add with the current deposite or withdraw amount
for that i'm using the following code:

Set rs = con.Execute("select last(tot) from onhand")
'For Deposite
totalamt = rs(0)
total = Val(totalamt) + Val(txtAmt)
con.Execute ("insert into ac values('" + txtDesc.Text + "','" + txtAmt.Text + "','" +  + "','" + total + "','" + dat + "' )")

'For withdraw

totalamt = rs(0)
total = Val(totalamt) - Val(txtAmt)
con.Execute ("insert into ac values('" + txtDesc.Text + "','" +  + "','" + txtAmt.Text + "','" + total + "','" + dat + "' )")

2.while executing sometimes the wrong value of tot are used when the arrangement of DB table is changed. i'm using MSAccess...
If there is any other ways for this transactions please help me

[Santanu.Das]

From my point of view your problem arises from the first line.

Set rs = con.Execute.......
This not a process to assign or open of recordset.

"select last(tot) from onhand"
This is also a wrong SQL Statement.

Make a SQl Statement on basis of "dat" or date of transaction and by the statement the last transaction date would be the last row of the query. The statement should be

"Select tot From ac Order By dat"

Here Order By clause will create the result serially date by date.

Now we can write codes to open the recordset.

rs.CursorType = adOpenDynamic
rs.LockType = adLockPessimistic
rs.CursorLocation = adUseClient
rs.Source = "Select tot From ac Order By dat"
Set rs.ActiveConnection = con
rs.Open

Now the codes for deposite

rs.MoveLast
totalamt = rs.Fields("tot")
total = Val(totalamt) + Val(txtAmt.Text)
con.Execute ("insert into ac values('" + txtDesc.Text + "','" + txtAmt.Text + "','" +  + "','" + total + "','" + Curdat + "' )")

Now the codes for withdrawal

rs.MoveLast
totalamt = rs.Fields("tot")
total = Val(totalamt) - Val(txtAmt.Text)
con.Execute ("insert into ac values('" + txtDesc.Text + "','" +  + "','" + txtAmt.Text + "','" + total + "','" + Curdat + "' )")

Hope it can help you

[Nova6112]

thanks a lot Shark1.. its working without any problem but when i insert a first value its getting error to overcome that error i have to type 0 in tot field of the DB table how can i do this witout typing

[Santanu.Das]

What type of error are you getting?

[Nova6112]

Runtime error3021:
Either BOF or EOF is true, or the current record has been deleted

[Nova6112]

I'm sorry i can understand this problem and i found a solution for this... Thanks a lot

[Nova6112]

Dear Shark1 still i'm getting a problem in this. i'm using Now to specify Date and time of the current time to save the Transaction time but sill its not sorting. ive added i small image that shows my datagrid which sorts by date only not by time