Indicate filtered record number on a form 2

[Islwyn]

This is probably simple but it's driving me up the wall. All I want is a textbox on a continuous form that indicates that this is record 1, record 2, record 1200 or whatever out of the filtered or queried table. So basically what I want is the number that's displayed next to the navigation buttons when they are enabled.
I thought it would be a simple matter of using runningsum and a textbox with "1" in it but I can't see any way of doing a running sum on a form.

Cheers

Islwyn

 

[gol4]

not sure in a relational database why the number matters but will something like this work for you?

make text box and place
=Countrecs() as its control source

this in the forms module

Private Function Countrecs() As Integer
Static lngX As Long
lngX = lngX + 1
If lngX > Me.RecordsetClone.RecordCount Then lngX = 0
Countrecs = lngX
End Function

 

[Islwyn]

Thanks Gol4. Unfortunately this is a little bit too alive for want of a better word. It works well at first but jumps around more and more eratically as you scroll through the list. By the way the reason I want this is because it has been requested for some silly reason. Something to do with a numbered filing system.

At first I thought that it would be simple to do the count in the underlining query but I've not found a simple way of doing this. I first tried using dsum and then a module with code similar to the code you posted above but neither give me what I want - they're either the total count or some seemingly random lower number.

Perhaps I'll just have to write a sub query that writes to a temp table.

If only there was a runningsum function !

Thanks for your help

Islwyn

 

[jimb0ne]

I'd lose the regular navigation buttons, create my own and just run code to set what the current record was using absoluteposition + 1, that would do the trick.

 

[RoyVidar]

Hi!

Simplest - textcontrol with control source: [tt]=CurrentRecord[/tt]

- if recalculation is a problem, use the forms on current event to: [tt]Me.Recalc[/tt] (recalculates all calculated controls)

Code - Forms on current event (unbound textcontrol txtMyRecNum)

[tt]Me!txtMyRecNum.Value = Me.CurrentRecord[/tt]

Recordcount too?

[tt]Me!txtMyRecCount.Value = Me.Recordset.RecordCount[/tt]

- or just [tt]=Count(*)[/tt] as controlsource in a textcontrol

Roy-Vidar

 

[Islwyn]

Thanks RoyVidar, that nice and simple and works perfectly. And thanks everyone else for your time
Cheers
Islwyn

 

[RoyVidar]

Thank you for the star!

Just a bit worried about the sentence "Something to do with a numbered filing system.". Didn't see that on the first reading.

These methods (and any methods) for programatic retrieval of a record number for the records currently filtered within a subform, will NOT give any absolute and everlasting number for a record. The numbers will vary with the number of records shown in the form, and the sort order applied.

If such number is required, with possibility to refer to a specific record by its number, then I think you'll have to add that as a field to the table and assign values as you add records.

Roy-Vidar

 

[Islwyn]

Hi,here's why your solution does work.
What I have is a Main Form with FieldOfficer and County combo boxes. These filter FarmRecords on a sub form. If a new farm is added for a specific county and field officer the records remain in chronological order because they are ordered by the FarmID (autonumbered) field (in other words you can't go back in time and add a record). This is the way the real files are stored in a filing cabinet; with files being split into groups of Field Officer then sub groups of County and new records are added at the front (rather than alphabetically) and numbered. So, with your solution, the number that is displayed exactly matches the filing system.

One thing that I thought was a bit odd but is not important was that the CurrentRecord textbox worked fine on the form header and footer but did not work on the main part of the form (which lists continuous forms) - it just shows 1 for each record.

Cheers

 

[RoyVidar]

Hi again. That's not very reassuring.

1 - using currentrecord within the continous part of the form, then it's not bound to any record, and all "boxes" (the repeating of the one box) will occur on any row. But, with recalc I think all of them should display the selected records recordnumber - but that's a bit academic and of no particular use;-)

2 - I'm not really worried about the adding, that could easily be achieved with entered date, incremental field (autonumber)... but what happens with your numbering system WHEN a farm is deleted, altered (two farms joined to be one)- if such is possible IT WILL BOMB. Please do give this a thought - if this sort order/numbering is to have ANY meaning/value in your organisation - STORE the value in stead. Never rely on such calculations. You or your organisation will have to be the judge of that, but I would not dare calculations.

Roy-Vidar