Filter a Form By Selecting Any Part of Field

[beesee]

My first post. Hopefully someone can help !

I have a continuous form that I want to filter with a command button which will prompt the user to select the complete field, or any portion of the field. For instance, if the field has the value W1725B, the user could select the whole thing to filter that value, or select W and all records with a W in the field will show.



When I use the Filter by Selection in the Menu, the above filtering selections work fine. However, when I try it using VBA, no matter how many characters I highlight, the form only filters the complete field.

What am I missing? I have tried both of the following in the VBA code, and the results are exactly the same:

Screen.PreviousControl.SetFocus
DoCmd.RunCommand acCmdFilterBySelection

Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acCmdFilterBySelection, , acMenuVer70

I am using Access 2000 (yes 2000 ) on Windows 7 64bit

Thanks in advance!

[Skarvion]

Hi Beese,

Someone in the forum helped me with another kind of problem but I think this can help you too.
http://allenbrowne.com/ser-62code.html
I'm sure you can understand the concept.
Cheers!

[beesee]

Thanks for that link Skarvion. That works very well (and even more) than what I was trying to do.

However, I am still curious as to why filtering by selection from the menu does not work the same way as filtering using
acCmdFilterBySelection in vba.

[Micron]

I believe it's because in form view, the selection is recognized as the selected text, which may or may not be on the clipboard as part of the process Access handles. When you're dealing with vba, it's the selected field of the selected record, AFAIK. You would have to run a function that gets the selected text and works with Windows to pass the value back to Access. Then you'd have to apply the selection as a filter to the form.

There is a function here that seems to do that - I've never used it
http://www.dbforums.com/showthread.p...unction-in-VBA

Update: playing around, I see that giving the control the focus causes the contents to become selected, which can be seen by commenting out all the other code. This is probably what is happening to you as well. Under Advanced Options, you can set the behaviour to Start or End of field, or Select Entire Field, which is what I had it set to. A Key event may work because you can check for say, a function key press and use it to copy the text using the control SelText property.

[beesee]

Thanks for your input Micron. That link you sent looks interesting and I will try it out. Meanwhile, I am quite happy with the search with a text box and command button I created based on input from this forum.

[Micron]

I did think of that method, but felt challenged to find something simpler than that post. I've found that F3 does not seem to be a mapped function key in Access, so I chose that for a keydown event and came up with something that works when I press F3 after selecting the text.

Private Sub System_KeyDown(KeyCode As Integer, Shift As Integer)
Dim strText As String

If KeyCode = 114 Then strText = Me.ActiveControl.SelText
MsgBox strText
End Sub

All that would be needed is to construct a filter and apply it on the form, which I suppose you needed to do with your textbox anyway.

[beesee]

Thanks again Micron. Is that code supposed to work in any control you move your mouse to and select some text? It does not seem to do anything at all when I use it on my form. However this modification works, but only in the designated control of course:

Private Sub MyControl_KeyDown(KeyCode As Integer, Shift As Integer)
Dim strText As String

If KeyCode = 114 Then strText = Me.MyControl.SelText
MsgBox strText

End Sub

Am I applying yours with ActiveControl incorrectly?

[Micron]

Yours looks good. It's written for a particular control whereas I believe mine was relative to the application window. Don't know why it didn't work for you; it worked for me so I posted it. Main thing is, it sounds like you have a working solution.

[beesee]

When I put the "System_KeyDown" in the code it does not work, nothing happens, no message. I suppose "system" is what is suppose to make it work anywhere in the form?

[Micron]

Could be. System / application, must be the same effect. Your specific reference to the control should do the trick.

[beesee]

I am happy with what I have now, thanks.