Results 1 to 11 of 11
  1. #1
    beesee is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Jan 2016
    Location
    Vancouver, BC
    Posts
    15

    Question Filter a Form By Selecting Any Part of Field

    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:



    Code:
    Screen.PreviousControl.SetFocus
    DoCmd.RunCommand acCmdFilterBySelection
    Code:
    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acCmdFilterBySelection, , acMenuVer70
    I am using Access 2000 (yes 2000 ) on Windows 7 64bit

    Thanks in advance!
    Last edited by beesee; 01-28-2016 at 01:36 PM.

  2. #2
    Skarvion is offline Novice
    Windows 10 Access 2013
    Join Date
    Jan 2016
    Posts
    13
    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!

  3. #3
    beesee is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Jan 2016
    Location
    Vancouver, BC
    Posts
    15
    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.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.
    Last edited by Micron; 02-03-2016 at 06:44 PM. Reason: posted link
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    beesee is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Jan 2016
    Location
    Vancouver, BC
    Posts
    15
    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.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.

    Code:
    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.
    Last edited by Micron; 02-03-2016 at 06:59 PM. Reason: applying filter
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    beesee is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Jan 2016
    Location
    Vancouver, BC
    Posts
    15
    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:

    Code:
    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?
    Last edited by beesee; 02-05-2016 at 03:04 PM. Reason: Corrected Me.MyControl.SelText typo

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.

  9. #9
    beesee is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Jan 2016
    Location
    Vancouver, BC
    Posts
    15
    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?

  10. #10
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Could be. System / application, must be the same effect. Your specific reference to the control should do the trick.

  11. #11
    beesee is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Jan 2016
    Location
    Vancouver, BC
    Posts
    15
    I am happy with what I have now, thanks.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Selecting Records by Part Size
    By ColbyDog in forum Queries
    Replies: 2
    Last Post: 08-20-2014, 04:47 PM
  2. Replies: 3
    Last Post: 04-20-2014, 08:13 PM
  3. Filter based on part of value
    By bikeordie1 in forum Reports
    Replies: 3
    Last Post: 03-01-2013, 08:52 PM
  4. Replies: 1
    Last Post: 10-05-2012, 08:48 AM
  5. Upercase in filed of form
    By miziri in forum Forms
    Replies: 2
    Last Post: 12-23-2009, 05:13 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums