Printing line number through vba keeps incrementing on each print

[saudkazia]

i want to print numbers for each row item. I can't use =1 over group or over all because I have artificially produced blank rows which are just the last row a bunch of times.



So I am using this code which I found online

gbl_line_counter = gbl_line_counter + 1
Me.line_number = gbl_line_counter


The problem with this is that the number just keeps increasing without any limit. If I print preview it comes perfectly but then the print starts from the next number - 15. So each time I print the number increases. I moved the code from detail_print to detail_format, but it doesn't seem to reset correctly.

Any ideas on how to tackle this.

[June7]

The global variable is reset to 0 next time db is opened?

Somewhere, need to reset the variable to 0 when switching between Preview and Print. Maybe report Load or Open event.

[saudkazia]

how can i reset when the group changes. This would fix my issue

[June7]

Maybe:

Declare a global variable to hold the group identifier. Then code to set the variables when report opens and code to reset variables when the variable does not match the group identifier value in textbox of report group section.

[saudkazia]

Pardon my ignorance how do I do that...any sample code

[June7]

Never done anything like this. Maybe:

If gblGroup <> Me.tbxGroup Then
gblGroup = Me.tbxGroup
gbl_line_counter = 0
End If

[saudkazia]

what would the controlsource be of txbGroup

[June7]

Just an example of a name for a textbox in report group section. I assumed you have report design using Sorting & Grouping features. What 'group' are you talking about?

[saudkazia]

Grouping is on [order id]

the group I am talking about is the grouping group. So if the order id is 1001 on order id 1002 the counter would reset.

[saudkazia]

or is there any other variable (field or textbox) i can use on which to reset the counter or any other method that will list the item numbers.

[June7]

Sorry, new territory for me. Don't have the answers without experimenting. I don't even know if this possible to do with the blank rows you are generating.

The only time I tried to the 'blank rows' by VBA method failed when I tried to incorporate something else in the report design. Finally gave up on it.

You might have to take the approach of actually creating blank records in a 'temp' table and UNION query to merge that table into the report's recordset. And then just use the RunningSum property of textbox.

[saudkazia]

i tried doing that as well. but i didn't know how to do it. filter is on order id so how would the union query understand the filter.

[June7]

I am confused.

If you are filtering report to a specific OrderID, why would the counter need to reset, there would be only the one OrderID?

Do you mean the GROUPING is on OrderID? The temp records would need a dummy OrderID that would sort to the end.

[saudkazia]

orders table has a unique value order id that has relationship with order item details that has also an order id.

there can only be one order id in orders but many order id in order item details

so when i want to create an invoice i will run this report and filter the order id to the record i want

this will display all the items with a sequential serial number including blank records (used to keep form uniform).

[June7]

My comments still apply. The blank records would need dummy IDs for whatever fields the grouping is based on.

Code to determine how many dummy records would be needed is the tricky part.

Bing: Access database report add blank rows

http://www.tek-tips.com/viewthread.cfm?qid=1696006