I have a table tblIbbMstr there are ob(opening balance),recmth(recovery month), recamt(recovery amount), 3 fields.
Now I want make table query from above record. My sql is SELECT tblIbbMstr.ob, tblIbbMstr.recmth, tblIbbMstr.recamt, ob-recamt AS cb INTO lonanMstr
FROM tblIbbMstr;
Now my problems is when I click the button in my form I want replace the all data automatically until cb become 0 like below
  OB RECMTH RECAMT CB
1000 01/01/2007 200 800
800 01/02/2007 200 600
600 01/03/2007 200 400
400 01/04/2007 200 200
200 01/05/2007 200 0
Whether it is possible and if yes how?

iisha64,

Why would you want to stop if the closing balance is zero?
Would it not be best to just query data for say, a month and then calculate the closing balance based on the data entered.

The reason I say this is that in my experience, its not good to put "rules"/"quirks" on financial data like this.
Using your example, what if a value is entered for 01/05/2007 were accidentally entered 300 as the received amount, this would make your closing balance negative 100.

How would the system handle this small yet important difference?

I personally would insert the data into the table and calculate the closing balance on the fly. And then just request data for a date range in question.

Perhaps you could explain your problem in a bit more detail?

Best Regards,
Rob

So it sounds like you have this:

tblIbbMstr
ob
recmth
recamt

And you want to take an entry from this table, for example:

ob - 1000
ecmth - 01/01/2007
recamt - 200

And post it to a new table:

tbl???
ob
recmnth
recamt
cb

until the account i zeroed out. Is that correct?

If so:

Dim rs1, rs2 As ADODB.Recordset
Dim sSQL1, sSQL2 As String
Dim iPK, i As Integer 'Primary key from your form?
Dim l_Ob, l_cb As Long
Dim dtRecmth As Date

iPK = Me.PKID

'Open recordset on source table
sSQL1 = "SELECT * FROM tblIbbMstr WHERE [PKID] = " & iPK
Set rs1 = New ADODB.Recordset
rs1.Open sSQL1, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

'Open recordset on target table
sSQL2 = "SELECT * FROM tbl???"
Set rs2 = New ADODB.Recordset
rs2.Open sSQL2, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

If rs1.Recordcount <> 0 Then
   l_Ob = rs1("ob")
   dtRecmth = rs1("recmth")
   l_amt = rs1("recamt")
   l_cb = l_Ob - l_amt
Else
   MsgBox "No record", vbCritical
   Exit Sub
End If

i = 1
Do Until l_Ob = 0 Or i = 100
   rs2.AddNew
   rs2("ob") = l_Ob
   rs2("recmnth") = dtRecmth
   rs2("recamt") = rs1("recamt")
   rs2("cb") = l_Ob - rs1("recamt")
   rs2.Update
   dtRecmth = DateAdd("m", 1, dtRecmth)
   l_Ob = l_Ob - l_amt
   l_cb = l_cb - l_amt
   i = i + 1
Loop

That should get you there in ADO. I only do ADO since I usually only do Access/SQL, but the flow control should work for DAO if that is what you are using.

You also probably want to add the PK to the target table to know what record is subtends from.

Here is the flow control in a VBScript to test. I use the i counter to prevent the loop from running away, but you may need more than 100 iterations.

l_Ob = CInt(1000)
Recmth = Date()
l_amt = CInt(200)
l_cb = l_ob - l_amt

i = 1

Do Until l_Ob = 0 Or i = 10
   WScript.Echo l_Ob & ", " & Recmth & ", " & l_amt & ", " & l_cb
   Recmth = DateAdd("m", 1, dtRecmth)
   l_cb = l_cb - l_amt
   l_Ob = l_Ob - l_amt
   i = i + 1
Loop

Did this work?

mmcdonal