Report that auto assigns check numbers

[jonathangpark]

I've built a report that tracks lease payment amounts and acreages for each monthly check printing for an oil & gas company. My problem is that I need a column in the report to automatically assign check numbers for each payment (record) in order, starting with the first check in the batch.



I currently have a text box with the control source set to '=1' and the running sum option set to "over all". This get's my desired auto assigned numbers but I don't have the option to set the starting check number for each check run.

Any suggestions would be helpful!

[June7]

The report textbox can reference a form textbox for the starting value and add that to the running sum value:

= Forms!formname!textboxname + [running sum textbox name]

I would think check number should be a permanent datum, not just generated dynamically on the report. The check printing process could include a step that assigns check number to each payment. This means code to generate a custom unique identifier.

[jonathangpark]

Thanks for the advice! This is in the ballpark with what I'm wanting to accomplish, but I do need to generate check numbers dynamically on the report. I will be running the same reports over and over for each year, with the additions of new lessors to the root table. Each time I process the monthly check run, I'll need to create a new report assigning new check numbers, in numerical order, based on the checks I've printed. If I hard code a beginning check number into the system and write a check for something that isn't related to the lease check run, my check numbers will be off from there on out.

[June7]

I am not saying a starting check number should be hard coded anywhere. The code would locate the last used check number in table and begin batch numbering with the next value and save the value to payment records.

Otherwise, use an unbound textbox on form to manually input starting number (actually, starting number -1 because of the running sum expression).

How are obligations selected/input for payment process?

[rpeare]

Are you able to identify items in your data that were part of a check run vs those that occurred outside of a regular check run? In other words, you need a way to identify your last piece of data that was part of a check run so you can return the highest check number used as part of a check run. For instance let's say you had this:

PK BillAmt CheckNum CheckDate

PK	BillAmt	CheckNum	CheckDate
1	500	1000000	1/1/2014
2	400	1000001	1/1/2014
3	250	54321	1/3/2014
4	600
5	400

Where items 1 and 2 were part of a check run
Item 3 was not part of a check run and was recorded individually

Now you are trying to process items 4 and 5 but you want to recover the check number from item 2 NOT the check number from item 3.

you have to be able to identify through your data which item is the correct 'last' item.

[jonathangpark]

We have certain leases we pay once a year on the lease initiation date. I've created a table that lists all the leases and has a two digit field for lease initiation month (example for March it's 04). I've created a query and input the code =[Enter two digit month] into the criteria box. Using the query, I've created a report that prints all the lease detail for the month with the check numbers assigned to it but I'm struggling to get the check numbers to auto assign. I use the checking account for many other functions in the business, so I'll need to change the starting check number each month.

Not all checks are run from this checking account from within access. I have a stand alone royalty accounting software package that will be issuing checks monthly as well. This is why I need to be able to assign a new starting check number each month.

I think this might work. I'll create a form and see what it does.

Thanks for the help!

[rpeare]

If you don't care about storing the check number for posterity

1. Create a text box in your detail section with a CONTROL SOURCE of =1
2. Set the RUNNING SUM property of that text box to OVER ALL
3. Name this control RS
4. Create a second text box
5. Set the CONTROL SOURCE to =[Enter the Starting Check Number]-1 + [RS]
6. Run the report

the texbox RS should have a numeric count starting with 1
the second textbox (doesn't matter what you name it) should have a check number starting where you want it.

[June7]

None of those reasons preclude saving the generated check number to table - don't have to do searches for check number, reconcile bank statement? How do you track that payment was made?

Of course, not saving does greatly simplify the process.

[jonathangpark]

I'm sure I'm probably doing it wrong but I've made a text box and named it RS. Entered the =1 in the source control and made a second text box and entered =[Enter the Starting Check Number]-1 + [RS]. In both boxes I've set Running Sum to "over all". When I run the report, it tries to count by what whatever number I've entered as my starting check number (example, if i enter 50 the checks go 50,100,150,etc...) and I have to assign a value of 1 for [RS] each time I run the report.

What am I missing here?

[rpeare]

Do not use running sum in the field that has the formula [Enter the Starting Check Number]-1 + RS

Don't think I said that in my original instructions, but like June7 said, i would strongly recommend you find a way to record the check number in your database either at the run time of the check run itself or as part of your data entry after the fact. If you have to do any auditing at all not having that check number is going to make your job a lot harder than it is.

[jonathangpark]

I don't want to save the check number because I reuse the same table every year to process the same payments. I use access as a stand alone software to create a user interface for those in the company outside of the accounting department to produce reports that I can use inside the accounting department. I reconcile checks and make entries in our accounting package using the reports that they print off from Access. I don't actually use access to manage check records.

[jonathangpark]

Thanks rpeare! That did exactly what I wanted it to.