Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access 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 March 19th, 2005, 08:30 AM
Authorized User
 
Join Date: Jan 2005
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default Generating cheque no

Hii,
     I have created a small cheque writing program which consists of a table name "DATA" with fields CHEQUENO, CHEQUEDT, BANKNAME & AMOUNT & also a query with date criteria with the above fields. I have 2 problems while generating reports
    1. I have created a report based on the above query the problem is that while entering data into table i will enter all the fields except "CHEQUENO". This cheque no should be asked when the cheques to be printed & same time cheque no to be updated simultaneously in "DATA" table.
    2. "BANKNAME" consists of atleast 3 banks for which a different series of cheque no is used, for which each time as an when bank name changes report should ask for fresh starting cheque no & it should be incremented sequentially.
        Any help on VBA coding for this.
        Thank you.
 
Old March 26th, 2005, 10:50 AM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

Note: this same question is asked here: http://p2p.wrox.com/topic.asp?TOPIC_ID=28443

This is one of those problems that isn't all that simple to solve. Which is probably why you weren't answered before. Questions come up. Like what happens if the cheque (check) doesn't print correctly or a check number is somehow skipped? What if you don't want to print all unprinted checks?

Another reason you may not have been answered is you said it is a "small check writing program". If you're printing a few checks at a time, it's better just to update the checks manually. If you're printing hundreds of checks, you still need to audit the data to be sure the right check number is recorded in your SMALL program.

Ignoring all of the issues, to answer you basic question about updating the check numbers, I'd recommend that you add the check numbers just before printing...

You'll have to process one bank at a time. So prompt for the bank and the starting check number.

Then update your CHEQUENO field for that bank with the code shown below. BTW, this is a common way to update records that you should learn. You probably will have to write a similar routine that will back out checks if the printing fails.

After the records are updated, generate your report. You should change the query for your report to include looking for the bank you updated and check number great than or equal to the first check number specified.

Code:
Public Sub UpdateCheckNo(pstrBank As String, plngStartCheckNo As Long)

Dim lngCheckNo As Long
Dim db As Database
Dim rs As Recordset
Dim sql As String

Set db = CurrentDb()
sql = "Select CheckNo From Table1 " _
    & "where (Field2 = """ & pstrBank & """) AND (CheckNo is Null)"
Set rs = db.OpenRecordset(sql)

lngCheckNo = plngStartCheckNo
While Not rs.EOF
    rs.Edit
    rs!CheckNo = lngCheckNo
    rs.Update
    rs.MoveNext
    lngCheckNo = lngCheckNo + 1
Wend
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub
Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org
 
Old March 27th, 2005, 04:42 AM
Authorized User
 
Join Date: Jan 2005
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hiii,
 Randall J Weers

   First I tnank you for your valuable help

   I want to clarifiy that if check doesn't print correctly particuler check no will be deleted & new check no will be alloted, if check no is some how skipped the check wil be cancelled & atlast all the cheques will be printed in one go.

   If it becomes sucesfull I want to implement the same in my business work where daily 60 checks on average are issued to various clients.

thank you,




 
Old April 17th, 2005, 01:48 AM
Authorized User
 
Join Date: Jan 2005
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

how to use above code any help has iam new to ms access

 
Old April 17th, 2005, 08:55 AM
Friend of Wrox
 
Join Date: Nov 2004
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

If you're having trouble using this, you should probably start with a more basic application. This is pretty straight-forward stuff. It troubles me that you're thinking about this kind of manipulation of your data but need explanation about how to use the code. Nonetheless, without really understanding what kind of explanation you need, here is one...

With the above code written in VBA, you need to set up a way to call the code. This will likely be a form with a button (I'll call the button btnPrint). On the form you should probably provide the ability to select which bank the checks will be printed for (I'll call that cboBank, implying a combo box with a list of valid banks) and the starting check number (I'll call that txtStartCheckNo). Set up the Click event for the button as follows:

Private Sub btnPrint_Click()
Call UpdateCheckNo(me.cboBank, me.txtStartCheckNo)
End Sub

I've left out things assuming you will take responsibility for how this should really work. For example, before btnPrint_Click calls UpdateCheckNo, you should check that the user has selected a bank and that they have entered a check number. Calling UpdateCheckNo with inappropriate values will lead to unpredictable results -- which why I'm troubled that you need explanation about how to use the code.

You really should take a look at the Northwind sample database or at other sample code and/or get a book on Access and VBA.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org





Similar Threads
Thread Thread Starter Forum Replies Last Post
Generating an invoice. dgprotective VB How-To 10 October 30th, 2007 09:07 AM
Generating reports osemollie VB How-To 3 July 19th, 2006 05:14 PM
Generating XML m_rajib Crystal Reports 0 May 4th, 2006 07:51 AM
generating two different GUID shaileshmark General .NET 5 August 1st, 2004 01:25 PM
generating words kozam C++ Programming 1 September 30th, 2003 12:55 PM





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