Add Form's filter to underyling query

[evanscamman]

When I add a filter to my form, the underlying query, of course, does not include the additional criteria from the form.

I need to use the underlying query to supply data to an update query.
But the update query must only affect records shown on my form.
Is there any way to feed the form's filter back to the query?

I can create a filtered recordset in VBA using the original query and the form's filter, but I can't figure out how to use this recordset in an update query - so I may be using a completely wrong approach.

I also tried putting a dynamic reference to the form's filter in a column of my query, but couldn't get that to work either - it always evaluated to True.

Any ideas?

Thank you for your help,
Evan

 

[wazz]

i think you'll need vba and two recordsets.

pseudo:

rsOrig (qry, tbl...)
rsFiltered (form.rs)
lngID
 
with rsFiltered
    .movefirst
    do while not .eof
        lngID = !IDfield
 
        with rsOrig
            .findfirst(lngID) 
            .fields(n) = rsFiltered.fields(n)
            .update
        end with
 
        .movenext
    loop
end with

 

[evanscamman]

My update query uses the original query, pulls in data from a couple of other tables, compares it, and then performs an update.

If I have to do this record by record in VBA i would need to create and close a 3rd query for each record in my filtered query - line by line... very slow, I imagine.

Is there any way in VBA to run an SQL statement, or create a querydef form an existing temp querydef?

Thank you,
Evan

 

[wazz]

yes. for ex:

[COLOR=teal]  'create a query. create your own sql string; or [/COLOR]
[COLOR=teal]  'get the Sql string from a rs or qdf (qdf.Sql).[/COLOR]
    db.CreateQueryDef("qdfName","sSql")
    '''
    Set db = CurrentDb
[COLOR=teal]  'set/select the qdf to be executed.[/COLOR]
    Set qdf = db.QueryDefs("qdfName")    
    [COLOR=#008080]'run the query.[/COLOR]
    qdf.Execute dbFailOnError    
    Set qdf = Nothing

one approach would be to create a new (temp) table that has all the fields you require (from the form's rs).

create a query that joins the original table with the new temp table (or qdfs of the tables). add all the fields from the original table. use this query as the basis for your update query.

in vba, when you want to get only the filtered records,
- delete all rows in the temp table;
- append new rows to the temp table usinge the rs of the filtered form;
- the query you created above will limit the rows of the original table;
- run your update query.

hth.

 

[evanscamman]

Wazz - thanks for the help. Will a temp table work in a MDE or read-only database Front end database?

Thank you,
Evan

 

[evanscamman]

Your idea about a temporary table got me thinking about a Temporary Stored Query. I pointed all my update queries to the Temporary stored query instead of the original.

Then in VBA i loaded the sql from the original query, inserted the form's filter into the sql's where statement, and then saved this sql over the Temporary query's sql. Now I can run update queries all day long...

Thanks for the help!
Evan

 

[wazz]

great idea!

 

[evanscamman]

well... It seemed like a good idea at the time.
But with a read-only front end you can't mess with a stored query - and you can't link to a query like you do to table in the back-end....
so I'm stuck again. If I can't figure out how to "link" to a query, I'll probably have to do a temp table like you originally suggested.

Evan

 

[wazz]

i would have thought that changing a sql string might be better/more feasible because using the temp table involves deleting rows and updating it. i'm not sure if you'll have more luck with that approach or not in a read-only situation.

do you have queries in the back end? are both front and back read-only?

 

[evanscamman]

I don't have any queries in the back end because I can't link to them to use in the front end.
Just the front end is read-only.