Link to home
Start Free TrialLog in
Avatar of kenabbott
kenabbottFlag for United Kingdom of Great Britain and Northern Ireland

asked on

MS Access VBA changing criteria of saved query

Hi

I need to achieve the following in Access 2010

1.  Change the criteria values in a saved query in VBA
2. Get the value of a column from the edited query

I am currently using the attached code which deletes the existing query and then creates the new one.  However is this the best way of achieving this using ADO and what is the best way of the getting the desired value from the query?

 'delete existing query
        Set cat.ActiveConnection = CurrentProject.Connection
        cat.Views.Delete "MyQuery"
     
   
    'build new query
        strSQL = "SELECT .... where field = new value"

    'recreate query with new criteria
        cmd.CommandText = strSQL
        cat.Views.Append "MyQuery", cmd

Many thanks
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Why ADO?

In DAO you can just do..

Currentdb.querydefs("myquery").SQL = "sql string here"

To get a single value from tha query you would follow that with something like..

Myvalue = dlookup("fieldname", "MyQuery")  ' if there is only one record in the query or all the values are the same.

or

Myvalue = dlookup("fieldname", "MyQuery", "RecordID= " & number)   'if you want the vaklue from a specific record
Avatar of kenabbott

ASKER

Hi

Many thanks

I have used the DAO method before but am presently learning ADO thus my reason for doing it this way.

Also I am trying to avoid using DLookup to get the value as the query returns a large number of records and in the context of the whole procedure DLookup is a little slow.
"query returns a large number of records"
I'm afraid this begs the obvious question of 'why?' again
If you want a specific value from the query why not make the query return just the value you want?
ASKER CERTIFIED SOLUTION
Avatar of ScriptAddict
ScriptAddict
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
"ADO is generally considered better then DAO"

Not in Access it isn't.
And from Access2007 onwards, it is DAO that is is being updated to deal with new Access features - it is now part of the Access object library and doesn't have a separate existence.
"query returns a large number of records"
I'm afraid this begs the obvious question of 'why?' again
If you want a specific value from the query why not make the query return just the value you want? "

You would need to understand the whole process - there may be better ways overall which I'll address as I develop this but the key aim of my post is to concentrate on this one aspect.

Many thanks
Dlookup will be faster than any other method if all you want is one value.

The alternative is to open a recordset based on your query and search the recordset for the value.  Or, of course, build a recordset by creating another query in code that selects from the first query and does just return the value you want.