Inserting a Autonumber into a report?

[daneo2k3]

ive created an order form in a report but need an item no adding but if i just add it in the table it just keeps adding up but i need it to start at one for each order. any help much appreciated dane

 

[MikeAngelastro]

Try using a page count in the report.

You may also use a text box on the report that is fed by a function that increments. This function will need a static variable and a parameter telling it if it needs to initialize the static variable (counter) - that is set to zero. Before you run the report you run the function telling it to initialize the counter. Then run the report.

 

[daneo2k3]

sorry im a total novice that made no sense can you help explain how to do that again please?

 

[MikeAngelastro]

A static variable in a sub or function remains as calculated when the function closes. The first time the function is run the static variable = 0; when the function opens it increments the static variable by 1 and then closes. The next time the function opens the static variable = 1 and the function increments it to 2 and closes. And so on.

Here is the function:

Function Increment(IncrementAmount as long, Optional Initialize = No) as long

Static Temp as long

If Initialize then Temp = 0

Temp = Temp + IncrementAmount

Increment = Temp

End Function

You can put this function into one of the query's fields as follows;

Increment(1)

Then run the query for an order using VBA as follows:

Increment( 1, Yes)
DoCmd.OpenQuery "QueryName"

The result will be that in the first line of query's recordset the counter will equal 1; in the second line the counter will equal 2; and so on.

If the query will have more than one sales order, you will have to figure out how to initialize the counter at the start of each sales order.

 

[Dugantrain]

Mike, I'd like to get this working as, to this point, I've been using a fairly complex and costly subquery as a Counter in my recordsets. I've been toying with your code but instead of the function being called for every record in the query as expected, the function seems to be called once when the query is opened and populates every record with the same number. When I close and open it again, it populates with that number +1. I put a break on the function and, sure enough, the function only gets called once when the query is opened. Every time I've called a function in a query, it gets called for every record; could it be the Static Declaration that is doing this?

 

[MikeAngelastro]

The function has two parameters: the incremental amount ( 1 for your need ) and the optional initialize parameter. The second parameter should be left blank in the query.