VBA Search Box to Search Subform

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

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.  

Answer 5

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.