SQL keywordsearch in subform/tabcontrol RRS feed

  • Question

  • Hi,

    I'am just started on acces and following some youtube clips to learn.

    But there are a few things i cant figure out..

    I Have

    Option Compare Database
    Private Sub btnsearch_Click()
    Dim SQL As String
    SQL = "SELECT client.[client ID], client.first, client.last FROM clients " _
        & "WHERE [last] LIKE '*" & Me.txtsearch & "*' " _
        & "ORDER BY client.last "
        Me.Child18.Form.RecordSource = SQL
     Me.client.Form.RecordSource = SQL
    End Sub

    The problem is, the child subform works perfectly when searching for a last name.(I've put it there for testing purposes)

    But the .client subform is placed inside of a tab control. And wont update when hitting the search button. however when i use the navigationpanel of the main form below i can let it show the right search, all of the other names are blankt out.

    I've tried a few things but can't figure it out. 

    Monday, March 12, 2018 8:54 PM

All replies

  • Hi,

    Are you able to post some screenshots? It might help us understand the problem better.

    Monday, March 12, 2018 9:00 PM
  • If "client" and "Chilld18" are both direct subforms of the main form where this code is running, and they both have the same controls with the same controlsources, then they should both work with the above code.  If they don't, then there's something different about them.  The fact that "client" is on a page of a tab control should make no difference, so that isn't it.

    I don't know what you mean when you say "when i use the navigationpanel of the main form below i can let it show the right search" -- what sort of navigation panel are you talking about?

    Incidentally, you don't have to requery a form or subform after setting its RecordSource property, the way you are doing in your code.  Setting the RecordSource property causes a Requery automatically.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, March 12, 2018 9:31 PM
  • Hello sjost,

    I create a form based on client table and then I create a form for searching.

    In the searching form a put the text box and search button and a sub form and a tab control.

    I set the client form as the source object of the sub form and drop a client form to the tab control.

    Everything works well when searching for a last name for both the sub form and the client form in tab control.

    I would suggest you share a simply database file so we could try to use it to reproduce your issue.

    For sharing file, you could share the file via Cloud Storage, such as One Drive, and then put link address here. Thanks for understanding.

    By the way, I note that in the sql string, you select data form "clients". I think it should be "client" and hope it does not have effect on your testing result.

    Best Regards,


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 13, 2018 3:16 AM