hi all
My table tblDet there are Cid, OB, Dep, withdraw, CB fields like below

Cid OB Dep withdraw CB
01 1000 0 100 900
01 900 500 0 1400
01 1400 500 0 1900
01 1900 0 500 1400

My code is below

Private Sub cmdUpd_Click()
Dim sSQLa, sSQLb As String
Dim mob, mdeo, mwith, mcb, mcid As Double
Dim iPK, i As Double
iPK = Me.txtCid
sSQLa = "SELECT * FROM tblDet WHERE [Cid] = " & iPK
Set rs1 = New ADODB.Recordset
rs1.Open sSQLa, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
mob = rs1.Fields("ob")
mcb = mob + rs1.Fields("Dep") - rs1.Fields("withdraw")
Do Until rs1.EOF
rs1.Fields("ob") = mob
rs1.Fields("cb") = mob + rs1.Fields("Dep") - rs1.Fields("withdraw")
rs1.Update
rs1.Fields("ob") = rs1.Fields("ob") + rs1.Fields("Dep") - rs1.Fields("withdraw")
rs1.Fields("cb") = rs1.Fields("ob") + rs1.Fields("Dep") - rs1.Fields("withdraw")
rs1.MoveNext
Loop
End Sub
Now my problems is when I click update only one record update
remaining records not update I want replace all record like above
what is wrong my code should rectified

Mohamed Mohiddin
Officer

__________________
Mohamed Mohiddin
Officer

Dim sSQLa, sSQLb As String
Dim mob, mdeo, mwith, mcb, mcid As Double
Dim iPK, i As Double

iPK = Me.txtCid
sSQLa = "SELECT * FROM tblDet WHERE [Cid] = " & iPK

Set rs1 = New ADODB.Recordset
rs1.Open sSQLa, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Do Until rs1.EOF
   mob = rs1.Fields("ob")
   mcb = mob + rs1.Fields("Dep") - rs1.Fields("withdraw")

   rs1.Fields("ob") = mob
   rs1.Fields("cb") = mob + rs1.Fields("Dep") - rs1.Fields("withdraw")
   rs1.Update

   'you want variables here to do calculations, you can't put them back into the recordset since it will not be updated.

rs1.MoveNext
Loop

Whatever you are doing to all the fields, you must do that inside the recordset loop. If this is not working, create a second loop.

Also, any code after the rs1.Update will not be executed on the other fields.

Did any of this help?

mmcdonal

Thanks for your reply's , but I still have the problem...
when i add deposit field ob fields should come cb field
like blow
OB Dep with CB
1000 100 100 1000
1000 0 100 900
 900 100 100 900
in above method updating how should write the code then second loop how should create please explain me


Mohamed Mohiddin
Officer

What is the event that it triggering these updates? Do you click a button?

Is there a source table and a target table, or are you doing this all in one table? It seems to me you would have a transaction table, and then a daily balance table.

Please post the table structures, what the event it, what the data looks like before processing and where it is stored, and what the data should look like after processing and where it is stored.

Normally I think you would do this in a report based on a single transaction table, and then store the data in a temporary table to run the report from.

This is not a difficult problem.


mmcdonal

I started on this and here is the table structure I am using:

tblAccount (your account information table)
AcctID - PK
AcctNo - text
Name - text

tblTransaction (account transaction information)
TransID - PK
AcctID - FK to tblAccount
TransDate - Date
Trans - currency

This should be enough to generate the information you want when you need it. There shouldn't be a table with the info you have here, except as a temp table.

Based on this structure, I would clean out the temp table, then build the data you want by date, then run a report based on the the temp table. You could also create a form based on the temp table using tblAccount as the main form data source, and the temp table as a subform datasource (not updateable).

Is any of this helpful?



mmcdonal

Dear mmcdonal,
My source table tblDet structures is like below
cid,Ob,dep_date,dep,with_date, with,cb
I put ob, dep_date,dep(deposit),with_date, with,cb,(withdraw) fields manually
Then I click the cmd(R)refresh button the fields should update like below

Cid Ob dep_date dep with_date with cb
A001 1000 01/04/2007 500 0 1500
A001 1500 0 02/042007 500 1000
A001 1000 03/04/2007 1000 0 2000
A001 2000 0 0 2000
The above method I want updating the fields


Mohamed Mohiddin
Officer

Where are you getting the Deposit and Withdrawl information from? Is that calculated from another table, or does someone figure out the total amounts of deposits and withdrawals for a day and then enter them in? Or is the customer only allowed one deposit and one withdrawal per day?

The answer will determine how this is done. With my structure, you can enter as many trasnactions as the customer cares to make, and them run a routine on the main table (your table) after each transaction. So it updates after each transaction. That would be cool. Otherwise, it seems you just want to figure out the closing balance after deposits and withdrawals are somehow entered into this table for each date. Would you then run a routine at a certain time of day? That we could do.

Either way, we can do this, you just have to be clear on your processes. I will be able to post code tomorrow. Can you clarify in the meantime. It looks like the values are entered manually, and you want to total them up at the end of the day and populate the next day's opening balance at the same time.



mmcdonal

This is what is confusing to me:

"I put ob, dep_date,dep(deposit),with_date, with,cb,(withdraw) fields manually"

This looks like you are entering ALL the information manually, so I am not sure what is being automated. I would say that normalizing the data and using a transaction table, and then building this table data automatically from the transaction table is the way to go. Can you redesign the database like this, or are you stuck with this current design?

mmcdonal

Based on this structure:

tblAccount (your account information table)
AcctID - PK
AcctNo - text
Name - text

tblTransaction (account transaction information)
TransID - PK
AcctID - FK to tblAccount
TransDate - Date
Trans - currency

tblRunningBalance
RBID - PK
OBDate - Date
OpenBalance - currency
Withdrawals - currency
Deposits - currency
ClosingBalance - currency

You would get all the functionality and lots more automation. Then your tellers would only have to pull up a customer record and enter a transaction at a time, which could be automatically dated for the date of entry - with the option to change. Then on the Before Insert and Before Update events of that form, you would force a new set of data into the record with the date of the transaction entered each time.

mmcdonal

Thanks for your reply's
Acutely in my table there are only ob,dep,with,cb fields now my problems is if I put ob,dep,with manually like below
OB DEP WITH CB
5000 0 500 4500
4500 500 0 5000
5000 0 1000 4000
4000 0 2000 2000
2000 1000 0 3000
Now I want cb should come like ob+dep-with and ob should come cb like above method when I go to update my table.It is possible the same table or I want create another table? after updating my table I want take daily report



Mohamed Mohiddin
Officer