Seems Like It Should Be Simple But..... 1

[xweyer]

I'm trying to get a row number appear next to each row of output from a query. I want the row number to be dynamic so that if the criteria of the query changes the rows are renumbered correctly 1 thru whatever. (Essentially the same information as appears at the bottom of the datasheet view of a query "record x of y" only displayed within the query itself).

I feel like I must be missing something obvious but I haven't been able to find the answer.



[sig][/sig]

 

[MichaelRed]

XwEYEr,

Do you want this in the 'query view' or as a value displayed on a report?

I don't think Ms Access supports the "AutoNumber" for queries, which I think is what you are asking for. It should be fairly easy to implement as a function which simply returns an incrementing integer. Call this function from your query (as the field).

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[xweyer]

Yes, I wanted the number to appear in the query view. So basically the results I want would be the same as if the individual records displayed in the query were autonumbered.

I've tried a few things with functions and have been able to get the total number of rows in the query or the number the row occupies in the table itself but nothing that produces the equivalant of &quot;autonumbering&quot;. Any idea on what the function should look like? [sig][/sig]

 

[LarryDeLaruelle]

Michael:

I think you're right that you can't do what XwEYEr wants in a query but I thought it might be an interesting exercise to try creating a function to assign row numbers.

I created the following function:

Public Function Counter(strCount As String) As Integer

Static intCounter As Integer

If IsNull(strCount) Then
Exit Function
Else
intCounter = intCounter + 1
End If

Counter = intCounter

End Function

Then I set up a query with a field called Number that called the function and passed the value of a name field that should always contain data.

The function works, but I got spurious results on the row numbers.

The first time I ran the query it returned 2 for the first record and then 8, 9, 10, 11, 12 for the remainder of rows. A curious twist -- when I printed the results, it came out numbered 13 through 18. I assume that in formatting for the print output it ran through the function again for each row to be printed.

Of course, since the counter variable is defined as static it doesn't reset if you run the query again before closing the db. Thus, the next run was numbered 14 to 19; the third 21 to 26 and so on skipping two numbers between runs.

This is a good challenge for the technically advanced out there in Tek Tip land. First, is it possible to do what XwEYEr wants in a query and/or function. Second, if possible how is it done.

[sig]<p>Larry De Laruelle<br><a href=mailto:larry1de@yahoo.com>larry1de@yahoo.com</a><br><a href= > </a><br> [/sig]

 

[MichaelRed]

XwEYEr

hmmmmmmmmmmmmmmmmm,

Either it is harder than it looks or (more probably) I'm less alive than I thought (please no response to THIS part!!!!!).

Briefly, I can get sequential values - simplistically. BUT,
it appears that the 'smarts' in the Jet engine optimiser are fooling around w/ the function call. If I create the sequential value function w/o parameters, the query engine optimizes the function call 'out' after the first value. If I add adummy / constant parameter, again it is optimized 'out'. Using a value from the data sources appears to at least call the function for each record - however THEN it appears to generate the values twice!

Sometimes It is TO D. Smart for my own good!!!

I'll think about this over the weekend and try again later.

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[LarryDeLaruelle]

Good luck with that one Michael. I'll look forward to a post on Monday.

Don't spend to much time on it -- too much football this weekend.

Go Irish whip those Huskers. [sig]<p>Larry De Laruelle<br><a href=mailto:larry1de@yahoo.com>larry1de@yahoo.com</a><br><a href= > </a><br> [/sig]

 

[cmmrfrds]

Put this code in your standard module

Public glbRowCount As Long '- Running counter of rows

Function ReturnRowCount()
glbRowCount = glbRowCount + 1
ReturnRowCount = glbRowCount
End Function

On the Form the column for your count will have the data source =ReturnRowCount()

Make sure you initialize glbRowCount to zero the second time you use it. The first time it will start as zero. Initialize before a requery or second open of form. You probably could put this as a public function in your form, but the first works. In the on open put glbRowCount = 0 [sig][/sig]

 

[MichaelRed]

XwEYEr,

After some thought,

It can't be done - at least not exactly the way you are asking.

The reason my soloution and cmmrfrds soloution won't work is that the query will automatically requery the recordset whenever the 'cursor' is moved. This appears to be occuring when the recordset is displayed - and again when the displayed portion of the recordset changes. Thus the action of &quot;renumbering&quot; the lines. I have not tried it, but - changing the process to include a &quot;MakeTable&quot; query, and numbering the lines in the new table should work. cmmfrds code should be o.k. for this application - just change it to provide values to the table - after it is &quot;made&quot;.

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]