Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    287

    Unanswered: RecordSource Filter problem

    I am having problems changing the recordsource and filter on a subform.. I think I have the correct coding below it's just the LIKE A* where I believe the problem is.. can anyone help please?


    Code:
        Viewer_Sub.Form.RecordSource = "Qry_ViewrSubAZKeys"
        
        Dim strNewSet As String
        strNewRecord = "SELECT * FROM TBL_Wills " & " WHERE [Surname] = 'Like A*' "
        Me.RecordSource = strNewSet
        Viewer_Sub.Form.RecordSource = strNewSet

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    That's looking for a surname with the value "LIKE A*" in it.

    What you are looking for is

    strNewRecord = "SELECT * FROM TBL_Wills WHERE [Surname] Like 'A*' "
    Inspiration Through Fermentation

  3. #3
    Join Date
    Mar 2004
    Posts
    287
    thanks but that's still not working, i have it filtering all 'A' by using a query with the search criteria "Like A*" and this works fine.
    I have removed this query to leave an open query (ViewrSubAZKeys) then want to change the filter options for that subform using the code above.

    Any idea's?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    RedNeckGeeks answer is the correct one.
    You were originally looking for people with the surname "LIKE A*" - I've never met anyone with that name before!

    The operator you are wanting to use is in fact LIKE (not equals).
    Try swapping the asterix for a percent sign if the asterix doesn't work.
    Code:
    SELECT * FROM TBL_Wills WHERE [Surname] LIKE 'A%' "
    If memory serves correctly the wildcard characters are different in Access VBA and Access queries (built in the query designer)... We'll see, eh?
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2004
    Posts
    287
    nope still doesn't work - this is my code behind a command button that has to change the subform below to read all of the surname files with A*

    Code:
        Dim strNewSet As String
        strNewRecord = "SELECT * FROM TBL_Wills WHERE [Surname] LIKE 'A*' "
        Viewer_Sub.Form.RecordSource = strNewSet
    My original code that uses seperate queries (not the ideal solution but it works) is follows;

    Code:
        Viewer_Sub.Form.RecordSource = "Qry_ViewerSubA"
    Any ideas on why the top code isn't working or even better if there is a way to use Qry_ViewrerSubA and select a WHERE statement on the end like you would a normal query selection for a form.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Does it have anything to do with...
    Code:
        Dim strNewSet As String
        strNewRecord = "SELECT * FROM TBL_Wills WHERE [Surname] LIKE 'A*' "
        Viewer_Sub.Form.RecordSource = strNewSet
    George
    Home | Blog

  7. #7
    Join Date
    Mar 2004
    Posts
    287
    OMG!!! Can't believe i missed that!! LOL

    Thank you very much!

    All works fine now, even doen another search box to search for words within another name field "*Name*" etc and that all works fine

    Sweet!! Thanks guys!

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by NielMansell
    OMG!!! Can't believe i missed that!! LOL
    Hehe, silly Neil
    Remember quick watches and debugging are your friend!
    Well done on getting it sorted
    George
    Home | Blog

Posting Permissions

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