I need to consecutively number table rows with user determined start number

[vbaInet]

It's really up to you which way you want to go.

If it's just for printing purposes and you can control the start number then I would do it directly on the report. In addition users can start printing from any page to any page, so even if there was an interruption in the printing process the numbering shouldn't matter.

It would just be an unnecessary write process to the db if it's just for printing. You could save the last row number if you wanted to remember where to pick up from.

 

[bcmarshall]

One of the reasons I had originally opted for the Make Table+Appends option was that if the check number can be added to the appropriate table record, then we can export all the information from that table back to Quick Books.

The information ultimately has to be entered there to keep track of monies spent, so my logic is to have all the info, including the check numbers, in that table, and then from there I can create the necessary export routine to send it to its final destination, Quick Books. All accounting is still done there. I just want to transfer the check printing routine from QB to Access, and then send the data back once it's done.

I'm still uncertain of how to generate the user designated start point and the sequential numbers for each check as it is printed, as well as how to write that back to the original table (assuming I return to the table configuration).

 

[vbaInet]

The information ultimately has to be entered there to keep track of monies spent, so my logic is to have all the info, including the check numbers, in that table, and then from there I can create the necessary export routine to send it to its final destination, Quick Books.

If this is the case then I suppose we can stick to doing it in the table. However, I would advise you don't use a Make Table query. It's best to create the table and update it or delete from it when required. Making/deleting tables on the fly can cause bloating.

Do you know how to work with recordsets?

I'll write some aircode on here:

dim db as DAO.Database
dim rs as DAO.Recordset
dim lngCounter as Long

set db = Currentdb
set rs = db.openrecordset("[COLOR="blue"]NameOfQuery[/COLOR]", dbopendynaset)

lngcounter = nz(me.[COLOR="blue"]txtStartCounter[/COLOR], 1)

with rs
    if .recordcount <> 0 then
        do while not .eof
            .edit
            ![COLOR="blue"]RankField[/COLOR] = lngcounter
            .update

            .movenext
            lngcounter = lngcounter + 1
        loop
    end if    
end with

set rs = nothing
set db = nothing

Ensure the sort has already been applied in the query beforehand, otherwise, you can apply the sort in the OpenRecordset part, or use the Sort method of the recordset object.

 

[bcmarshall]

I really want to thank you for all the time and effort and thought you put into this. I had to do some tweaking with the code, but it wound up working great for me.

Thank you again for your efforts.

 

[vbaInet]

You're welcome!

 

[bcmarshall]

However, I would advise you don't use a Make Table query. It's best to create the table and update it or delete from it when required. Making/deleting tables on the fly can cause bloating.

Just one quick comment on this. Checks are printed once a month, and the total size of the recordset is only about 6-700, so I really don't think it's going to be too much of an issue.

Also, I use Compact and Repair regularly. I'm thinking that bloating will be minimal.

By the way, thank you again.

 

[vbaInet]

It's not the volume of records that causes bloating alone, it's the creating and dropping of tables. Most developers avoid this operation. Compact & Repair won't always help.