none
VBA Search Box to Search Subform RRS feed

  • Question

  • I have an unbound Main Form that I'm only using as a placeholder for a Datasheet Subform.  The only reason for doing this is to place a search box in the Main Form Header (since the datasheet subform cannot have a header).

    I have created a text box and search button and copied code that has worked perfectly on other forms (forms that did not have a subform)  Can someone please help with what is needing in the VBA code to search the subform?  Here is a sample of my code.  The debugger keeps hitting the last line of Me.RecourdSource = strsearch:

    Private Sub Command245_Click()
    Dim strsearch As String
    Dim strText As String
    strText = Me.txtSearch.Value
    strsearch = "SELECT * FROM Products_qry where ((Job like ""*" & strText & "*"") or(Serial like ""*" & strText & "*"") or(Product like ""*" & strText & "*"") or(Type like ""*" & strText & "*"") or(Rope like ""*" & strText & "*"") or(Capacity like ""*" & strText & "*"") or(Condition like ""*" & strText & "*"") or(Installed like ""*" & strText & "*""))"
    Me.RecordSource = strsearch
    End Sub

    Tuesday, August 15, 2017 9:03 PM

Answers

  • The debugger just keeps taking me back to the Me.Recourdsource line.

    Hi FK_1234,

    That line should be something like:    Me!<subform control name>.Form.RecordSource

    But I still have my concerns about the validity of strsearch!

    If it starts asking for parametes for each of the Fields, means that the fields used in the strsearch are not recognized.

    Imb.

    • Proposed as answer by Terry Xu - MSFT Wednesday, August 16, 2017 8:44 AM
    • Marked as answer by FK_1234 Wednesday, August 16, 2017 2:25 PM
    Tuesday, August 15, 2017 9:58 PM

All replies

  • Me.RecordSource = strsearch

    Hi FK_1234,

    If you want to address the RecordSource of the subform you have to use, starting from Me:

    Me!<subform control name>.Form.RecordSource

    Imb.

    Tuesday, August 15, 2017 9:15 PM
  • Me.RecordSource = strsearch

    Hi FK_1234,

    If you want to address the RecordSource of the subform you have to use, starting from Me:

    Me!<subform control name>.Form.RecordSource

    Imb.

    I tried some variations of the code you provided, and I still cannot get it to work.  That same line is still my problem area in the debugger.
    Tuesday, August 15, 2017 9:24 PM
  • strsearch = "SELECT * FROM Products_qry where ((Job like ""*" & strText & "*"") or(Serial like ""*" & strText & "*"") or(Product like ""*" & strText & "*"") or(Type like ""*" & strText & "*"") or(Rope like ""*" & strText & "*"") or(Capacity like ""*" & strText & "*"") or(Condition like ""*" & strText & "*"") or(Installed like ""*" & strText & "*""))"
    Me.RecordSource = strsearch

    Hi FK_1234,

    Is the strsearch readable? Is  "or(" acceptable, or should it be "OR (", with a space? I always use spaces around AND and OR, also for human readability. Are all used field valid fields?

    Imb.

    Edit: an other problem are double quotes that you use around the asterisk:  "*". They are in conflict with the double quotes of the strsearch itself.

    Better use '*' (with single quotes) instead of "*".

    Imb.

    • Edited by Imb-hb Tuesday, August 15, 2017 9:45 PM Edit added
    Tuesday, August 15, 2017 9:41 PM
  • The above code is copied directly from another search box I created, so I know it should work.

    The FROM Products_qry is the source of records for the subform.

    All Fields are named according to the control sources.

    The debugger just keeps taking me back to the Me.Recourdsource line.

    When I try to perform a search with the code as-is, it starts asking for parameters for each of the Fields.  Maybe it has something to do with the subform just being placed into an unbound form?  There isn't a Master/Child relationship between Main and Subform.  

    Tuesday, August 15, 2017 9:48 PM
  • The debugger just keeps taking me back to the Me.Recourdsource line.

    Hi FK_1234,

    That line should be something like:    Me!<subform control name>.Form.RecordSource

    But I still have my concerns about the validity of strsearch!

    If it starts asking for parametes for each of the Fields, means that the fields used in the strsearch are not recognized.

    Imb.

    • Proposed as answer by Terry Xu - MSFT Wednesday, August 16, 2017 8:44 AM
    • Marked as answer by FK_1234 Wednesday, August 16, 2017 2:25 PM
    Tuesday, August 15, 2017 9:58 PM