By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,247 Members | 1,287 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,247 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, 143 views)
File Type: jpg unfiltered2.jpg (115.6 KB, 144 views)
File Type: jpg unfiltered3.jpg (124.1 KB, 149 views)
Jan 30 '19 #1
Share this Question
Share on Google+
6 Replies

Expert Mod 2.5K+
P: 3,284
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.
Jan 30 '19 #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.
Jan 31 '19 #3

Expert Mod 2.5K+
P: 3,284
What you are trying to do is not possible from a query. The data needs to be a record source on a form.
Jan 31 '19 #4

Expert Mod 10K+
P: 12,366
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.
Jan 31 '19 #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.
Jan 31 '19 #6

Expert Mod 2.5K+
P: 3,284
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.
Jan 31 '19 #7

Post your reply

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