INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

I need to generate consecutive numbers in a report with user defined start point

I need to generate consecutive numbers in a report with user defined start point

I need to generate consecutive numbers in a report with user defined start point

(OP)
I have an Access2007/SQL Server 2012 system with 20 users for an insurance company. Checks to our dealer network are currently being written manually in Quick Books, even though the amount to be paid is determined within Access. I've been asked to use an Access report to print the checks, table the data, and then export it back to QB so that our accounting matches checks written.

I've got a table that reflects just the records to be printed, but I need a way to sequentially number each record in the new table beginning with a user generated starting point equivalent to the first check number.

The check blanks are on standard letter sized paper, three to a page, with tear-off perforations to separate them, in case that information has any relevance.

I'm not sure if the best way to accomplish this is from the report itself. I've created a blank field on each table record for the check number, and what seems most logical is that the sequential number is generated on print and written back to the table, rather than just generating all the numbers at once within the table. That way, should print ever be interrupted, it will be easy to take up where we left off. However, whatever way makes most sense is what I'm interested in. If generating them all within the table is more logical it's fine with me.

All help will be greatly appreciated.

RE: I need to generate consecutive numbers in a report with user defined start point

I would not rely on the printing process to also assign the numbers. Consider using code to:
  1. Prompt the user for the starting number
  2. open a recordset
  3. loop through the recordset updating the check number field and incrementing the number
  4. Then print the checks
Do you need assistance with the code? If so, please reply with your significant table and field names as well as the order the checknumbers need to be applied.

Duane
Hook'D on Access
MS Access MVP

RE: I need to generate consecutive numbers in a report with user defined start point

(OP)
Thanks very much.

Yes, I would definitely appreciate help with the necessary coding.

To describe the structure I currently have, the dealers who receive checks are grouped into three payment structures. I have a field called Structure with numeric 1, 2, and 3 data, and that is the first sort. Then it's sorted ascending on DealerName, and again ascending on a field called IndName.

I'm using a Union Query to combine the three independent payment structures, and then a MakeTable query to output it all to a brand new table every time it's run. The MakeTable does the exact same sorting as the report sorting does, so the order the data is written to the table should already be ready for numerical sequencing without extra effort. I don't think you'll have to make any other changes.

Table name is PrintChecks, and the fields and sample data are:

DealerID: 1234
DealerName: Joe's RV
IndName: Joe Smith
AmtPaid: $225.63
Memo: Referral fees from 6/1/2014 to 6/30/2014
Address: 1234 Biscayne Blvd.
DealerCSZip: Miami, FL 33125
AltAdd: 6014 Home St.
Ft. Lauderdale, FL 33333
Payee: Tom Jones
CheckNumber: TBD
Structure: 1, 2, or 3




RE: I need to generate consecutive numbers in a report with user defined start point

This is "air code" so use with caution:

CODE --> vba

Dim strSQL as String
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim intCheckNum as Integer

set db = CurrentDb
strSQL = "SELECT Stucture, DealerName, IndName, CheckNumber " & _
   "FROM PrintChecks " & _
   "ORDER Stucture, DealerName, IndName"

intCheckNum = Int(InputBox("Enter Beginning Check Number"))

Set rs = db.OpenRecordSet(strSQL)
With rs
    Do Until .eof
	.edit
        .Fields("CheckNumber") = intChecNum
        .Update
        .MoveNext
        intCheckNum = intChecNum + 1
    Loop
    .Close
End With
set rs = nothing
set db = nothing
MsgBox "Complete" 

Duane
Hook'D on Access
MS Access MVP

RE: I need to generate consecutive numbers in a report with user defined start point

(OP)
Wow! I can't thank you enough!

I had to do a little tweaking, but your "air code" worked like a charm.

Thank you again.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close