Check Numbers

[Guest]

I am developing an application which will print checks. Does a anyone have a
formula or function which will request a starting ckeck number and increment
it to display on the check as they are printed?
Thanks.

 

[Arvin Meyer [MVP]]

There are multiple ways of doing that. Can you be more specific with what
you are looking for?

 

[Guest]

I have set up a query to extract records that are summarized and then a check
is prepared as a report using the detail and totals from the query. They will
probably be sheet fed laser checks. I would request the number of the first
preprinted check to be printed, and use it to print it on the check and to
eventually load back into the paid record for reconcilation. The starting
number would be incremented by 1 for each subsequent check printed until
complete.

 

[Arvin Meyer [MVP]]

I would build a table with 1 record in it to hold the check number. I'm not
so sure that I'd allow the system to ask for the first number, because it's
too easy for a human to make a mistake.

Then I'd write a function that would go through all the query records and
put a check number in each one, increasing that number by 1. Something like
this:

Public Function GetNextNum() As Long
On Error GoTo Error_Handler

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("tblCheckNumber", dbOpenDynaset)

With rst
rst.MoveFirst
rst.Edit
rst!CheckNumber = rst!CheckNumber + 1
rst.Update
End With

GetNextNum = rst!CheckNumber

Exit_Here:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function

Error_Handler:
Resume Exit_Here

End Function

Use that function in your query to add numbers to a query column. Since you
said you want to update a check register when you are done, you may want to
make your query into an Append query and append the records to a temporary
table. Run your check printer report on the data in that table and use those
records to update the check register with the check number, date, and
amount. When you are all done printing the checks and updating the register,
delete the records in the temporary table.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

Thanks. As a semi beginner, learning by trial and error, where would I put
the function reference in the query table? Also, when I copied the data
base for backup purposes, all of my function references, including the system
ones are now not found. I get a request to input data for the function names,
even =date()!

As a MVP, do you have any suggestions re the best way to better understand
Access? I have the Bible, Complete Referenece and Microsoft Step by Step.
Also, I am using Access 2000 with 2003 available. should I switch to 2003?
Thanks for all your assistance.
Lou

 

[Arvin Meyer [MVP]]

Add a column to the query that calls the function. It should look like this:

PONumber: GetNextNum()

Remember if you look at the query and scroll up and down, it will keep
adding new numbers. Make the query into a Make-Table query so it runs only
once.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

Thanks. Created it, but get "User defined type not defined" error for Dim
statements on compile. What did I do wrong?

 

[John Vinson]

Thanks. Created it, but get "User defined type not defined" error for Dim
statements on compile. What did I do wrong?

Probably you don't have the DAO Object Library selected. With the VBA
editor open select Tools... References. If Microsoft DAO x.xx (the
highest version if there are more than one) isn't checked, check it.

John W. Vinson[MVP]

 

[Arvin Meyer [MVP]]

Did you set a reference to DAO in the Tools menu of the code window?
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

No. Do now and it complied fine.
Thanks.
--
Lou

Did you set a reference to DAO in the Tools menu of the code window?
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

Me again! The function works fine when I put it into the check print report
as far as incrementing the table field by 1 and displaying it for each check
printed. However, when I put it in the query, while it pulls the number from
the table, it does not update it in the resulting records created by the
query. It is the same for each record - the starting table number + 1.

 

[margaret]

I'm attempting the same if someone could help...

I've created a table ... tblControl with a field CheckNumber
I've created a make table query with a field "CKNO = modGetNextNum()
and the module is as follows:

Option Compare Database
Public Function modGetNextNum() As Long
On Error GoTo Error_Handler

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = GamesDb
Set rst = db.OpenRecordset("[tblControl]![checknumber]", dbOpenDynaset)

With rst
rst.MoveFirst
rst.Edit
rst!CheckNumber = rst![tblcontrol!CheckNumber] + 1
rst.Update
End With

modGetNextNum = rst!CkNo

Exit_Here:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function

Error_Handler:
Resume Exit_Here

End Function

However, the checknumber is not reading from the start number in the
tblControl!CheckNumber field. It's all zero's for every record. So I'm
doing something wrong.