Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 20th, 2007, 07:32 AM
Registered User
 
Join Date: Jan 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default unbound queries

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


 
Old January 22nd, 2007, 08:42 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old January 22nd, 2007, 12:34 PM
Registered User
 
Join Date: Jan 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



 
Old January 22nd, 2007, 02:14 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old January 23rd, 2007, 04:04 AM
Registered User
 
Join Date: Jan 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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)


 
Old January 23rd, 2007, 08:46 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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

mmcdonal
 
Old January 23rd, 2007, 11:37 AM
Registered User
 
Join Date: Jan 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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







Similar Threads
Thread Thread Starter Forum Replies Last Post
Unbound Forms jeremy1048 Access 1 August 4th, 2008 01:26 PM
Combining Queries or results from 2 queries Ford SQL Server 2000 24 November 7th, 2005 08:54 PM
Unbound field lee27 VB Databases Basics 1 January 5th, 2005 07:53 AM
How to bind unbound field ? cjordan BOOK: Professional Crystal Reports for VS.NET 1 January 23rd, 2004 01:34 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.