Next available in a series..

[HitechUser]

I currently have a procedure that will "increment" to the next available account number. I use a MAX query to get the last account number used and include a call to the procedure to display or print the next available account number.

Now I need to print a report show the next "N" number (variable) of account numbers. The user will be prompted to enter the number of "new" entries to print on the report. (That part is no problem).

The report will create the following result.

Report Example (Note: There is more detail to this report...)

Acct. Number

BG2181-12
BG2182-13
BG2183-14
BG2184-15
BG2185-16
BG2186-17
BG2187-18
BG2188-19

I believe it may be easier to do this using VBA. Unfortunatly I have not used alot of VBA in reports (mostly with forms). Does anyone have an example or an idea?

Thank in advance..

 

[GingerR]

Just want to be clear--how is your pattern done for the 'next available acct number'? Looks like both the 5 & 6 positions as well as the 8 & 9 positions increment at the same time?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[HitechUser]

GingerR,

Yes that is true. I currently call the procedure and it handles that part. Sorry to confuse the example. I was just trying to show that I need to call the function.

How about a simple example with just numbers.. Say..

Acct Number
100
101
102
103
104
105
106
..

'Get last Account Number used and increment it for next available.
SELECT IncrAcctNumber(Max([AcctNum])) AS AcctNext FROM tblAccount;

I can print the report from existing records (no problem) I am even able to print the next available (See code).

Now how do I print the next five or six or eight available Account numbers???

Thanks again..

 

[GingerR]

Make a table (tblIncrement) with one field, called Increment (integer). Fill it with integers from 1 to whatever.

Then your report's recordsource will be

SELECT tblIncrement.Increment FROM tblIncrement WHERE tblIncrement.Increment<=[Enter Count:] ORDER BY tblIncrement.Increment;

and a text box on your report will be:

=dmax("AcctNum","tblAccount") + Increment

--------------------------------------
Another option is to fill a temp table with integers after the user responds with a number. You'd do this with code, looping thru filling up the table. Then do the same thing with the report. Disadvantage is bloating in the db from delete/append to the temp table. But if you want to go this route let me know.

Hope this helps.




Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[HitechUser]

I can get the MAX then "loop" the number of times (See psuedo code). How to I "print" to a report in VBA?

' Psuedo code not complete...
nextAcct = DLookUp...  ' Use MAX to get last used account
For i = 1 to 8 step 1
  nextAcct = IncrAcctNumber(nextAcct) ' Get next available
[COLOR=red yellow]'VBA Code needed here to print to report form????[/color]
Next i
...

I thought about the integer file example but my function only increments to the next account number (not next 2 or 3 etc.). As you can see by my code example I must keep calling it. I know how to do that to a form, but how do I do it to a report form???

I am looking for a example of writing to a report form using VBA. This way I can modifiy anything I need before I output it to the detail section.
Make sense??

 

[GingerR]

i don't know, sorry. All I would know to do is output that to a temporary table which you then use in a report, just like I showed above. Maybe search the forum on "using a recordset as Record Source for a REport"

Why not just do what I suggested? You don't need a function. Just a table with integers in it...I understand having a function to use in other places throughout the db, but for the report, just use the table...

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244