CODEWITHSUNDEEP
ms-accessvba
Carl
Carl

Reputation: 5779

Update query in subform

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?

Upvotes: 1

Views: 941

Answers (1)

Skippy
Skippy

Reputation: 1590

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.

Upvotes: 1

Related Questions