Incrementing numbers in report field

  • Thread starter Thread starter Mike Revis
  • Start date Start date
M

Mike Revis

Hi Group
Access 2000 Win XPPro

Don't know if this is even possible but here goes.

On the input form I have a field for nQuantityOfPackages and a field for
nStartingNumber.

I would like to be able to have the report field nPackageNumbers start with
nStartingNumber and print numbers incrementally to nQuantityOfPackages.

Desired output example if nStartingNumber is 123 and nQuantityOfPackages is
3;

Package Numbers 123, 124, 125

As always any advice or suggestion is welcome.

Best Regards
Mike
 
Mike:

There are a couple of ways to accomplish this.

1.) If you have a form where you report is called, then simply put a text
field on that form where the user can enter the starting number. Then in
your report, dimension a variable say, lngCounter as Long in the general
declarations section. In the On Open event, add code like this:

lngCounter = Me!YourFormName!txtStartNumber -1

Then in the On Print event of the detail section, add code like:

lngCounter = lngCounter + 1
Me!YourCounterField = lngCounter

This will allow increment the counter for each record. The only problem
with this approach is that if the report is navigated from page to page, it
must be done sequentially for the counter to work properly; if you navigate
backward, the counter still increments.

2.) The better and safer route is to create the counter in your report's
underlying query and then use the start number as the base from which to add
the the counter value to eventually end up with the package numbers. To do
this, you need to use a sub query in the report's underlying query to number
the rows returned sequentially, (e.g. 1, 2, 3, 4 etc.) For an example of
how to do that stop by our web and look in the code and design tips area
under queries and recordsets for the tip titled "How to create an Auto
Number For Each Row of Your Query".

Then, in your report, again pull in the base starting number as in Solution
1 (remembering to subtract 1).

Add the counter field to the detail section as a non-visible control. Add
an unbound control to the detail section as well.

Then, in the On Print event of the detail section, rather than simply adding
1 to the base counter, your code would look like this:

Me!YourCounterField = lngCounter + Me!HiddenUnderlyingQueryCounter

That will allow you to navigate pages and the counter will always correspond
accurately to the row involved.

HTH
 
Back
Top