Change Query Property using VBA

[Brilstern]

For simplicities sake I don't have my SQL in my VBA. I am just using public functions to filter pre-built queries when needed. I have a new need to change the value of the Top Values property depending on the usage of the query.

So my question: Can you change the properties of a query in VBA?

I have tried this and similar methods:

    Set rst = db.OpenRecordset("queryName")
    rst.Properties("Top Values") = strTopValues

[Rabbit]

What you're looking for is the QueryDef object.

https://msdn.microsoft.com/en-us/library/bb177500%28v=office.12%29.aspx

[jforbes]

I don't think Top can be a Parameter. You might be able to edit the QueryDef to get this done. This is probably not the best method, but it may work for you. Some Sample code to update a QueryDef:

Dim oQD As QueryDef
Set oQD = CurrentDb.QueryDefs("qryTestFilter_DoNotDelete")
oQD.SQL = "SELECT * FROM " & sTable & " WHERE " & sWhere

[Brilstern]

@Rabbit
I checked out QueryDef a little but I'll jump more into it then.

@jForbes
I thought about editing the SQL string but the issue is it is a variable value. I guess I could reset it to a specific value every time at the end of that specify query script.

When I get home I'll see where I come out. Thanks gents.

[Brilstern]

So... I looked at using .MaxRecords and it doesn't seem to effect the query when I do it manually or with VBA.

Another weird thing I came across. If I set the Top Values to 10, or 20 it works fine. If I set it to 50 it is returning 53 records... not sure what is up with it.

[Rabbit]

I'm guessing there are ties so it returns all the ties.

Instead of the maxrecords property, you could try changing the SQL.

[NeoPa]

Look in the Help for the TOP predicate of the SELECT clause. It will explain why the number doesn't always match the one specified.

[Brilstern]

I just ended up setting up a variable loop to end the query after it hits the required amount. Thanks everyone! NeoPa, I see what you were talking about with the TOP predicate properties now. Good to know for future use.