is there a way to get position in recordset? 1

[chaft]

I have an text box that records the position of the record in the database. Whenever I do a find it does not update this box. The reason for this is that my 'find previous' and 'find next' buttons increment and take away 1 from the number in the box. So when the find result jumps around the database at will it makes the text box result completely wrong.

Is there a code or function that can give me the record number for a record amongst the records. So if I go to record 3 it will say it is record 3?

hope there is

 

[jrbarnett]

Look at the AbsolutePosition property in Access help.
It only works with Snapshot or Dynaset recordsets.

John

 

[TheAceMan1]

How are ya chaft . . .

You should also have a look at the [blue]CurrentRecord[/blue] property:

[blue]   Me![purple][b][i]TextboxName[/i][/b][/purple] = Me!CurrentRecord[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[JoeAtWork]

Be aware that databases don't store records in any particular order. The last record you entered will not necessarily be at the bottom, it could be at the top, middle, or anywhere. The only way to retrieve the records sorted in any particular order is by using an ORDER BY clause in the query.

 

[TheAceMan1]

Howdy JoeAtWork . . .

I know [blue]your intent is always well given[/blue], but correct me if I'm wrong, [blue]chaft's[/blue] premise involves the [blue]Find[/blue] method, which has [purple]no prejudice of record position![/purple]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[JoeAtWork]

I have an text box that records the position of the record in the database.

I would say his base premise is that there is such a thing as a persistent record position when not using a specified sort order.
If he has 1000 records and he adds one more, I think he is assuming the next time he opens the table he will find the new record at position 1001, which is not necessarily the case.
I had a client who was writing down the record position as an "ID Number" on their paper forms. This sounded like a similar case, hence my warning.

 

[chaft]

I appreciate the care to which you debated this. Just to clarify (and i hope it is clarification i'm adding)

I was not speaking of an ID or an autonumber that perhaps the computer would deal with. I wanted my customer to know the position of the record regardless of the content. So if the first record was deleted I expected the next record to become the first record.

Me!TextboxName = Me!CurrentRecord

worked fine for my purposes. Thanks everyone for their suggestions and warnings.

 

[boblarson]

Chaft:

Records are not stored in any particular order within Access tables. So, you must GIVE them order, such as a date/time stamp or perhaps an incrementing number. An Autonumber MAY work okay but it can, in certain situations (such as replication) move to including negative numbers and in a random pattern. So, if you want to use a numbered order, it is best to create that sequencing yourself.

I, personally, like to use a date/time stamp for my records and then order by that. In that case, you should always be able to tell what is the first record, etc.

But, if you think that opening a recordset from a table is going to always have the second record as the second record, without you imposing order, then you are mistaken. It is just not guaranteed to work that way, unless you tell it which order is what.

 

[TheAceMan1]

Howd boblarson . . .

[blue]My read[/blue] is that [blue]chaft[/blue] is using his own navigation buttons & [blue]position textbox[/blue]. However, when he does a [purple]find[/purple], his problem was updating the [blue]position textbox[/blue]!

[blue]I wanted my customer to know the position of the record regardless of the content.[/blue]

The customers would know the position if the [blue]position textbox[/blue] were updated properly. He also said:

[blue]So if the first record was deleted I expected the next record to become the first record.[/blue]

Is this not what would happen with ordered records, or not? (his could be the misleading factor)? If only he could get the position textbox right!

Also, if one expects [blue]order[/blue] from something [blue]unordered[/blue], then the [blue]CurrentRecord[/blue] property would be useless and return positions not fitting expected ordered logic!

I'm not saying you or [blue]JoeAtWork[/blue] are wrong. I just don't get the impression you've presented.

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[JoeAtWork]

Aceman - Bob and I are perceiving a problem beyond chaft's original stated problem. Your solution fixed his "position" textbox, however my view is that this position does not have a meaning that the user thinks it does. He probably thinks that if he's on record 100, then it is newer than the 99 preceeding records, which is a false premise.

I wanted my customer to know the position of the record regardless of the content.

This implies it is important to the customer that a record is always located in a predictable position. Therefore I see a problem with the textbox, even though you got it working exactly the way he wants.

 

[TheAceMan1]

JoeAtWork . . .

I agree! Record Position/Number is ambiguous in a Db.

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886