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.
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!CheckNo = lngCheckNo
lngCheckNo = lngCheckNo + 1
Set rs = Nothing
Set db = Nothing
Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group