SQL keywordsearch in subform/tabcontrol

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.Child18.Requery
 
 Me.client.Form.RecordSource = SQL
 Me.client.Requery

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. 

Answer 1

Hi,

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

Answer 2

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

Answer 3

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,

Terry

---

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 [e-mail].