Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160

    Unanswered: ApplyFilter on each key press

    hi again,

    This is kinda lined to another post i did but i closed it prematurely sorry.

    just after some help on this filter. It works fine as long as the value in txt_ContractSelection exactly matches the contents of field Contract.

    I have numerous records all starting with 2014********
    I use the afterupdate event in txt_ContractSelection to run the code below. The plan is to filter all records in Contract as i type. So on each key press it runs the filter. So 2* 20* 201* 2014* 2014C* etc so eventually it filters down to one record which exactly matches the contract number held in the filed called Contracts.

    DoCmd.ApplyFilter , "Contract Like '*' & (Forms.Main.txt_ContractSelection)"

    thanks
    marcus

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    text/alphanumeric values should be delimted with either a pair of ' or "
    wild card matching depends on where and what symbl you use for wild card matching
    when creating a string literal by pullign to gether text and values from controls or variables you need to be careful how you drop in and out of VBA

    assuming Forms.Main.txt_ContractSelection contains '2014'
    DoCmd.ApplyFilter , "Contract Like '*' & (Forms.Main.txt_ContractSelection)".
    will result in:-
    Contract Like '*' & (Forms.Main.txt_ContractSelection)
    so first fix get your VBA delimiting right
    nextDoCmd.ApplyFilter , "Contract Like '*' " & (Forms.Main.txt_ContractSelection)
    will result in:-
    Contract Like '*' 2014
    then fix the SQL delimiting issue
    nextDoCmd.ApplyFilter , "Contract Like '*" & (Forms.Main.txt_ContractSelection) & "'"
    will result in:-
    Contract Like '*2014'
    ..but thats not what you want, thats asking for all rows whose contract number ENDS in 2014
    so fix the wild card poistioning
    nextDoCmd.ApplyFilter , "Contract Like '" & (Forms.Main.txt_ContractSelection) & "*'"
    will result in:-
    Contract Like '2014*'

    when developing in Access use the debugger to examine what you are actually sending to Access not what you think you are sending
    put a break point on the code and step through it. if you dont' know how to debug and you are going to be using Access then put aside some time to learn how to debug. yes it will cost time int he short term but it will dramatically improve your development skills and effectiveness
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    thanks healdem, though embarrassingly i had to wiki the meaning of delimited, but it did help understanding your code which worked perfectly. big thanks and yes i will defo invest time in debugging skills. Any recommended links?
    thanks
    again
    marcus

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •