Generating cheque no

civa · March 19th, 2005, 08:30 AM

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.

rjweers · March 26th, 2005, 10:50 AM

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

civa · March 27th, 2005, 04:42 AM

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,

civa · April 17th, 2005, 01:48 AM

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

rjweers · April 17th, 2005, 08:55 AM

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