help with passing filter to vba

[ipk*]

hello,

can anybody help me out with this one:

here is what i do:

i filter a form and retrieve the resulting filter via me.filter:

here the resulting filter

((query_constructs_show.ProteinName Like "*tkt*"))

i then want to pass this filter to vba

StrSQL = "UPDATE tbl_constructs SET IsSelected = -1 WHERE " & Me.Filter & " ;"

which produces this:

UPDATE tbl_constructs SET IsSelected = -1 WHERE
((query_constructs_show.ProteinName Like "*tkt*")) ;

the where statement by itself works fine, if i paste it into an sql query.

but, when i try to execute the update query like this:

CurrentDb.Execute StrSQL, dbFailOnError

i get an error about two parameters expected and only one passed. the
problem to me clearly seem the double quotes in the update query (i may be
off here), but i am not sure how to resolve this, i.e. how to construct the
update query properly before passing it to currentdb.execute.

could anybody help me out here ?

this has to be easy ...

ingo

 

[Dale Fye]

Your problem is that your WHERE clause is referencing a query that is not
part of the Update query.

My guess is that what you want is something like:

UPDATE tbl_Constructs
SET IsSelected = -1
WHERE tbl_Constructs.ProteinName
tbl_Constructs.ProtienName Like "*tkt*"

With this example, you would need to change the name of name of the query
that is used as your RecordSource of your Form to the actual name of the
table. Or, you could change the name of the table in the update to the name
of the query (assuming that the query is updateable). In that case, it would
look like:

UPDATE query_constructs_show
SET IsSelected = -1
WHERE query_constructs_show.ProteinName Like "*tkt*"

 

[ipk*]

Duh ...

That sounds very reasonable. Will try this in a minute.

Thanks

Ingo

 

[ipk*]

yeye ...

that was it. this update query had worked before, when i had the query
specified as sql string in the datasource of the form. i had afterwards
decided to clean up a bit and saved the query, and then put the saved query
as datasource. and this broke the update query in my vba. i would probably
not figured that out until x-mas if not for your help.

1000 thanks

ingo

 

[Dale Fye]

glad I could help!