Filtering: Locate Current Record Number via a Query/Lookup

[marcus101]

Hello:

I have a bit of a tricky dilemma, and I'm not sure how easy this is to solve...

Here's what I am working with:

I have a form that executes a search of Client Names and File Numbers.

The form record source is based on two tables that are linked in a 1-1 relationship based on a simple ID/primary key value.

The form works fine. The end-user types in the name or the file number and presses a search button to locate those records.

I then use a FILTER to display those records only.

However, I'd like to change my SHOW ALL RECORDS filtering button to be able to retain the current filtered record I'm currently viewing/editing so that it "stays" at that position when I return to to the "all records" display.

As many of you know, when you reset filtering on a form, the record number value jumps back to 1. I don't want this to happen if I can avoid it.

I need to be able to figure out a way to LOOKUP the internal counted record number/position based on this searched value as if it is/was part of a full recordset of ALL items in the table.

And this value is NOT directly stored or available in the table per se, so doing a conventional DLookup is not really workable, however the number itself is consistent if one is looking at ALL records, so this shouldn't really be that hard once one knows how to do it.

Maybe there is a built in property or method function I can use for this instead?

If I can get this numeric record position value, then I can use DoCmd.GoToRecord to simply jump to this record when I use the ShowAllRecords method.

I'm not changing the sorting parameters or anything fancy, the records themselves will always follow the same display order.

Is this record position number lookup possible without getting into anything too involved?

All ideas/suggestions welcome, involved or not...

Thanks,

marcus101
Access/SQL/XML Developer
Ottawa, Canada

 

[ProgramError]

Presuming your table has a unique key. Save the value of the current key field then use the

docmd.gotorecord field = savedKeyValue

Take a look at the dlookup function for moe information

Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.

 

[TheAceMan1]

How are ya marcus101 . . .

Something like this ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue]   Dim hldID
   
   hldID = Me![purple][b]PrimaryKeyName[/b][/purple]
   [green]'Your Filtering Code Here[/green]
   Me.Recordset.FindFirst "[[purple][b]PrimaryKeyName[/b][/purple]] = [red][b]'[/b][/red]" & hldID & "[red][b]'[/b][/red]"[/blue]

If [purple]PrimaryKeyName[/purple] is numeric, remove the two single quotation marks in [red]red[/red] . . .

See Ya! . . . . . .

 

[marcus101]

Hi AceMan/ProgramError:

Good suggestions, but perhaps I didn't quite make this clear enough in my first post, so I'll reiterate:

I DON'T HAVE AN AUTONUMBER PRIMARY KEY IN MY FORM TABLES.

(breath)

Here's the thing: My primary Key is a client FILE number, which means it's not a nice neat record number I can grab using a DLookup from either table.

It's an arbitrary value that the end user types in, so there's no real consistency to it, even though I do have an Input Mask.

And while I DO have a pseudo-ID field that IS an AutoNumber in my form tables, it's not linked in any consistent way to the other tables that I have joined to the two I'm looking at at any one time in the form.

So DLookup options are OUT, I'm afraid.

I was wondering if there was some way I can grab the current record number and save it BEFORE I Filter - that IS a good idea ProgramError, and was actually the first thing that came to mind for me.

Here's what I've tried so far:

1. Grab the current record number using Me.CurrentRecord and then STORE it into a hidden form control.

2. Use this stored value after I run the filter to search/find to DoCmd.GoToRecord when I hit the "Show All Records" button.

I run the DoCmd after the Filter has been removed using DoCmd.ShowAllRecords. I see all the records but I don't jump to the record position I thought I'd stored.

Even when I make the control visible, it still doesn't seem to work.

I can't quite seem to get the damned value to stay put on the form for some reason, it shows up initially but then changes once I filter the records, thereby preventing me from using that number to execute DoCmd.GoToRecord.

Theoretically I should just be able to store it and keep it there, but...

-Maybe it has something to do with the way I'm calling and storing the value on the form. It's probably directly linked to that record value and is trying to update it.

-Maybe if I just make the control value a string that gets that value indirectly via Me.CurrentRecord I can do this.

-or maybe I can tell that control to NOT update somehow while I'm doing filtering using Filter On/Off settings..

I will experiment a bit and let you know how it goes.

Thanks for your help.

marcus101
Access/SQL/XML Developer
Ottawa, Canada

 

[TheAceMan1]

marcus101 . . .

Then you have a Table & Relationships problem! . . . [purple]No way for you to extract anything unique for your purposes![/purple].

Make it a habit when your setting up any table, first thing you do is enter a primarykey . . . autonumber or not! I've had many table that don't really need it (lookups mostly), but if future tense . . . thank God I includede them.

Until you get that unique key, there's nothing anyone can do to help you. Someone may provide a work aroundbut its not the kind of work around you need.

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

See Ya! . . . . . .

 

[marcus101]

AceMan:

I DO understand where you're coming from, but respectfully, here's my thought:

If what you say is TRUE, then WHY can I use a recordset object to PRINT my current numeric position on a form?

It stands to reason that if I can get and use that value from THERE, I should just be able to GRAB AND STORE that at any time before I switch over to a new set of records.

And since that numeric position from that recordset effectively acts as a record numeric counter, then..why not use/re-use?

That's pretty much all I'm trying to do here in a nutshell.

Hope that makes sense.

I'll let you know how things go. You may be right, I'm having a hell of a time trying to implement this in any practical way so far.

Thanks,


marcus101
Access/SQL/XML Developer
Ottawa, Canada

 

[TheAceMan1]

[blue]If what you say is TRUE, then WHY can I use a recordset object to PRINT my current numeric position on a form?[/blue]

Seting the position of a recordset to the position of the currently selected record in the form is easy . . .

[blue]   rst.AbsolutePosition = Me.CurrentRecord - 1[/blue]

. . . your poblem is [blue]setting the focus to a specific record in the form![/blue] . . . an entirely different process.

Be aware: AbsolutePosition, CurrentRecord, and the [blue]record number that resides in the navigation bar[/blue] are all relative. In your case this means a record in a filtered set will more likely have a different record number when all are shown. As another example . . . delete a record and the record number for all records that follow, decrease by 1.

Going back a little, you say:

[blue]My primary Key is a client FILE number, which means it's not a nice neat record number I can grab using a DLookup from either table.

It's an arbitrary value that the end user types in, so there's no real consistency to it . . .[/blue]

A primarykey doesn't have to be autonumber, doesn't have to be numeric, doesn't have to be some complex formulated 64bit number . . . [purple]a primarykey only has to be unique![/purple]

So are you saying your primarykey is not unique (duplicate primarykeys are allowed)?

If the above is true is possible to make some unique identifier (for the purposes of criteria) out of the primarykey and 1 or 2 of the other fields?

See Ya! . . . . . .

 

[Pampers]

I DON'T HAVE AN AUTONUMBER PRIMARY KEY IN MY FORM TABLES.

Maybe you want to start using them, it can save a lot problems.


Pampers
Keeping it simple can be very difficult

 

[TheAceMan1]

Howdy pampers . . .

Neither myself nor [blue]ProgramError[/blue] mentioned anything about autonumber. But apparently this is what [blue]marcus101[/blue] took it to mean!

I've prompted [blue]marcus101[/blue] as to wether his PK of [blue]client FILE number[/blue] is unique!

His next post will be interesting!

[blue]Lets see what happens! . . .[/blue]

See Ya! . . . . . .

 

[Pampers]

I agree Aceman. You need a unique PK. Like you said: "until you get that unique key, there's nothing anyone can do to help you. Someone may provide a work aroundbut its not the kind of work around you need."

Also marcus101, this is something you want to alter:
"The form record source is based on two tables that are linked in a 1-1 relationship based on a simple ID/primary key value."


Pampers
Keeping it simple can be very difficult

 

[ProgramError]

Agreement with Aceman and Pampers...

From marcus's first post

The form record source is based on two tables that are linked in a 1-1 relationship based on a simple ID/primary key value.

The only reasons I would do this is to separate sensitive information (security), if the main table is too large.

save the recordnumber or simple ID in a public variable on your form (assuming your form is NOT going to be closed) using (once again assuming your ID is in numeric format)

[red] Public VariableName as long [/red]

Save the current record by saving the records 'simple ID/primary key value' with

[red]variableName = simpleIDprimarykeyvalue[/red]

do the show all records bit
now jump to the simple ID/primary key value record you saved with..

[red]docmd.gotorecord simpleIDprimarykeyvalue[/red]

Aceman's method will work just as well too. It can't be any simpler.

Perhaps it's time to show your code.

Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.

 

[marcus101]

Hello:

My code is pretty extensive, and I figured what I had described was fairly straightforward.

I still think it's straightforward, but some people only seem to be able to work well by trying to make things more complicated than they need to be, or are....;-)

So to sum up:

AceMan: Yes, the primary key IS unique.

I don't know of too many situations where people go out of their way to make something like that NOT unique (at least not for a PK anyway), but let's get that cleared up, because you're right, that DOES obviously influence whether or not the record number can be established or not, so..my bad..

Sort of..

Pampers: I appreciate what you're trying to say, but in this case, an Autonumber value won't always work well at least on paper, for me because I am linking to multiple tables (albeit 2 at a time when I'm running the form, but they can come from as many as 10 different, yet similar-valued ones, that make up the pair) that need to be maintained separately as well.

While I know this probably sounds silly to many of you, it DOES work, because the unique PK I'm using is common to ALL of my tables, so I can link through those.

And frankly, because I AM using unique PKs all across the board that are all consistent values that line-up, that still shouldn't really stop me from getting that record position, because the two tables I use for the form when it opens will always be the same, as a DEFAULT, and therefore, SHOULD also influence those record positional values as the source changes.

This is what I want, because to change things around otherwise makes what I'm asking for impossible, though I see no logical reason why I can't enforce that right from the get-go using code if it doesn't.

(- maybe THIS is the root of my problem here. hmmm.. -)

And this ties in to ProgramError's statement regarding DoCmd.GoToRecord, which I already made clear in my first post.

Because my unique linking PK is a TEXT VALUE, that doesn't make it possible for me to jump to that position, because that value MUST be a numeric for that method parameter.

And well, this is what I've been inquiring about all along, unless I've missed something here.

All I was asking about was "is it possible to get the number of the current record position in a way that can allow me to reposition my location after I do a filter/unfilter".

What I was hoping for was that there was some sort of built in object or method that would allow me to get something that by nature, isn't in the data, beyond using something like Me.CurrentRecord, which I've been trying to use and store in the form itself BEFORE I do filtering so it can be retrieved AFTER filtering, but so far, with no luck.

Maybe that's not possible with the way I've set things up table/data-wise...If so, fair enough.

I may need to break down and add a whole bunch of AutoNumber fields in my tables, but that puts me in a difficult position, because due to my maintenance work, those values will eventually slide out of position relative to each other, which will prevent consistency in my forms display and record numbering..I may have to do a re-think.

(d'OH!)

Anyway, I'm still working on this, and I will post my code and my thoughts when I'm finished later this weekend.

marcus101
Access/SQL/XML Developer
Ottawa, Canada

 

[TheAceMan1]

marcus101 . . .

One more time. In your [blue]All[/blue] button:

[blue]   Dim hldID As String
   
   hldID = Me![[purple][b]PrimaryKeyName[/b][/purple]]
   Me.FilterOn = False
   Me.Recordset.FindFirst "[[purple][b]PrimaryKeyName[/b][/purple]] = '" & hldID & "'"[/blue]

See Ya! . . . . . .