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

[MacroScope]

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.

 

[dhookom]

I would not rely on the printing process to also assign the numbers. Consider using code to:
[ol 1]
[li] Prompt the user for the starting number[/li]
[li] open a recordset[/li]
[li] loop through the recordset updating the check number field and incrementing the number[/li]
[li] Then print the checks[/li]
[/ol]

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

 

[MacroScope]

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

 

[dhookom]

This is "air code" so use with caution:

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

 

[MacroScope]

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.