Hi all members
I am a new user of access I am working in Government Office In my Office I want make one recovery register In Access I create a form like frmAdvance in frmAdvance there are five unbound columns’ like txtName,txtstno,txtAdvance,txtSanctionDt,txtRecAmt ,txtRecfrom are there and one command button Now I want when I click the button I want all above information the form it should make one table I write click event code like
Private Sub cmdOk_Click()
Dim sql As String
sql = ("INSERT INTO tblIbb1(stno,name,advance,sdt,recamt,recfrom) SELECT [Forms]![frmAdvance].[txtname],[Forms]![frmAdvance].[txtstno], [Forms]![frmAdvance].[txtAdvance], [Forms]![frmAdvance].[txtSanctionDt](date), [Forms]![frmAdvance].[txtRecAmt], [Forms]![frmAdvance].[txtRecfrom](date) FROM [Forms]![frmAdvance]")
End Sub
In above event no records append in my table So please how should I write event code in my form and one more question Advance amount should recover in 12 equal month in my form another command button cmdReport is there
When I click the cmdReport I want make report like below
In Page Header
Name:
StNo:
Advance:
Sanction date:
Received Amount
Recovery start from
In Detail
O.Balance(Advance) Rec.start from Rec.Amount(recamt) C.Balance
    1000 01/01/2007 83 917 917 01/02/2007 83 834
    834 01/03/2007 83 751
    751 01/04/2007 83 668
    668 01/05/2007 83 585
    585 01/06/2007 83 502
    502 01/07/2007 83 419
    419 01/08/2007 83 336
    336 01/09/2007 83 253
    253 01/10/2007 83 170
    170 01/11/2007 83 87
    87 01/12/2007 83 4
    4 01/01/2008 4 0
In above method how should the code

As to the first issue, I find a temporary table and a BOUND form is the way to go.

Start your code with:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDELETEAllFromTempTable" 'delete query to remove old data
DoCmd.SetWarnings True

Call data.
Write data to temp table.
DoCmd.OpenForm "frmYourForm" ' bound to the temp table.


As to the second issue, this is a design issue. You should have a table for these transactions, and then group these records in a query.

HTH


mmcdonal

Thank you so much for your reply.
I write a small program in foxpro like below
close all
clear
set stat off
set talk off
set safe off
set cent on
set date brit
sele a
use advance
store 0 to mob,madd,mrec,mcb
store date() to mdop
@ 10,10 say [Enter Amount of Advance : ] get madd pict '9,99,999'
@ 12,10 say [Enter Date of Payment : ] get mdop
read
mdor=mdop
@ 14,10 say [Enter Date of Recovery : ] get mdor
@ 16,10 say [Enter Rate of Recovery : ] get mrec pict '9,99,999'
read

do while mrec>0
   appe blank
   repl ob with mcb
   repl add with madd
   if mdor<=mdop
      if mcb+madd>mrec
         repl recovery with mrec
      else
         repl recovery with mcb
         mrec=0
      endif
   endif
   repl cb with ob+add-recovery
   repl date with mdop
   mcb=mcb+add-recovery
   mdop=gomo(mdop,1)
   madd=0
enddo
close all
clear
set stat on
set talk on
set safe on
retu
in above method I want to write code in click event
so please help me

Can you write this in psuedo code? I am not sure what each thing does. If you can explain what you want to do, we can give you the code.


mmcdonal

I am working women in Government Office Acutely I want if a official taken advance from our department the advance should recover in ten equal month and interest should recover after principal amount so how should I write the code for make detail report in click event. In above Foxpro program I replace the all data will update but in access how should do?
In my table loan as ob(opening balance), rec(recovery amount), dop(date of payment), dor(date of recover), cb(closing balance)

Can you write out the equation you want to use? You can store this in a payments table.

mmcdonal

Yes in my table ob(advance),dor,rec field are there spouse advance is 7525 it should recover in the ten equal month from date of recovery with the help of do while function
If I take report Report will be automatically to be generate like below
opening balance/10
Ob(op. bal) Dor(date of recovery) rec(recovery amount) cb(clo. bal)
7525 01/01/2007 752 6773
6773 01/02/2007 752 6021
6021 01/03/2007 752 5269
5269 01/04/2007 752 4517
4517 01/05/2007 752 3765
3765 01/06/2007 752 3013
3013 01/07/2007 752 2261
2261 01/08/2007 752 1509
1509 01/09/2007 752 757
757 01/10/2007 752 5
5 01/11/2007 5 0
In above method how should write the code