424,294 Members | 1,898 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,294 IT Pros & Developers. It's quick & easy.

VBA code for extracting filtered record ID

P: 3
I've been away from coding for a while. I have a movie database of personal movies to which I've added IMDB cinematographers and composers. So I have a join on main table MoviesXBMC with qryCinematographers and qryComposers.

I highlight / filter to see other movies by same composer.

When I unfilter, the view is reset to record one.

I wanted to create a piece of code to 1) select the primary key of the active record (while filtered) 2) set the filter property to false 3) go to the record active when I pressed the button.

I would assign a button to a macro which would runcode the module.

I can't remember the way to reference the immediate record's primary key. I tried:

Expand|Select|Wrap|Line Numbers
  1. Function CurrentRec()
  3. Dim KEY As Variant
  4. With qryMovieCinemaComposer
  6.    KEY = qryMovieCinemaComposer.KEY
  7.   .FilterOn = False
  8.   .Recordset.FindFirst "[KEY]=" & KEY
  10. End With
  11. End Function


Attached Images
File Type: jpg Unfiltered.jpg (54.1 KB, 121 views)
File Type: jpg unfiltered2.jpg (115.6 KB, 121 views)
File Type: jpg unfiltered3.jpg (124.1 KB, 121 views)
3 Weeks Ago #1
Share this Question
Share on Google+
6 Replies

Expert Mod 2.5K+
P: 2,894
Is this directly from the query? From a form in datasheet view?

If you are in a Form, the basic syntax for the index (based upon your example would be Me.KEY. Thus your code might look like this:

Expand|Select|Wrap|Line Numbers
  1. Private Function cmdUnfilterForm_Click()
  2.     Dim KEY As Variant
  4.     With Me
  5.         KEY = .KEY
  6.        .FilterOn = False
  7.        .Recordset.FindFirst "[KEY]=" & KEY
  8.     End With
  9. End Function
This assumes you have a command button aptly named.
3 Weeks Ago #2

P: 3
Yes, Twinnyfo it is basically simple query joins displayed as datasheets. It serves my purposes - I am simply trying to automate some simple repetitive actions.

For example, when I right-click a field and filter, I then want to remove the filter and have another active-filtered record be the active record in the full display. I could copy the key, then when I return search for that key.

I've been searching all day. Is there a way to programmaticly issue a 'go to' record command feeding it the key of the record active while under the filter. Something equivalent to 'ME' for forms.

Also, how do you reference the query's underlying table's value through code for a query that's been opened from the navigation pane.
3 Weeks Ago #3

Expert Mod 2.5K+
P: 2,894
What you are trying to do is not possible from a query. The data needs to be a record source on a form.
3 Weeks Ago #4

Expert Mod 10K+
P: 12,303
To expand on twinnyfo's latest reply, you can replicate the query view as a form if you're looking to maintain that look and gain the functionality of a form.
3 Weeks Ago #5

P: 3
I was hoping there was a hack to the query's hidden recordset!

I've been playing with a string of SendKeys (^c), ShowAllRecords, Sendkeys (^f, %n, ^v, %f) - to simulate manual keystrokes haha.

But the when the showAllRecords (or Filter=false) executes the value captured by ctrl-c on the filtered list gets lost!

I hope there is a system hack to get the address of the internal recordset to emulate 'with me.' functionality.

Thanks for your help.
3 Weeks Ago #6

Expert Mod 2.5K+
P: 2,894
I would recommend just creating a form based upon your query. This would allow you to do what you need to do without using SendKeys() or trying to foll the system into doing something it was never designed to do. Using a form gives you a tremendous amount of flexibility.
3 Weeks Ago #7

Post your reply

Sign in to post your reply or Sign up for a free account.