Update query in subform

Question

I have a form where there is string input that is an input to a parameter in a query (insert sql injection joke here), and a subform that displays the query's results.

Currently the parameter is essentially someVar Like "*" & forms!myForm!input & "*", but since the user can enter more than one string (i.e. a sentence) what I really want is someVar Like "*firstWord*" or someVar Like "*secondWord*"... etc

Since the number of parameters varies, I need to programatically generate the query. Looping through the words in the input and dynamically generating the where statement is not too hard in VBA:

dim sc as variant
sc=split(myInput)
dim where as string
where=""
for c=0 to UBound(sc)
where = where & "like '*" & sc(c) & "*' or "
next

where = left(where, len(where) - 3)

dim qd as querydef

set qd=currentDb.querydefs("myQuery")

qd.sql="select var where " & where

[my subform].requery

However, when I update the query, the subform that displays its data does not update even though I tell it to requery. If I leave the form and reenter it has the correct data. My approach seems to be wrong. What would be a better way to approach this?

The usual approach is to directly assign the generated SELECT sql string as recordsource of the subform (instead of a query). — Andre

Skippy Skippy · Accepted Answer · 2016-11-13T17:18:10

I would have "myQuery" saved as the base query without any where criteria. Then you could go with:

[my subform].RecordSource = "select * from myQuery where somevar like '*" & replace(myInput, " ", "*' or somevar like '*") & "*'"
[my subform].Requery

This does away with needing to loop through the words in the input string and picks up on Andre's suggestion of modifying the RecordSource for the subform rather than trying to modify the query that the RecordSource is pointing to.

Update query in subform

1 Answers