By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,936 Members | 1,396 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,936 IT Pros & Developers. It's quick & easy.

Change Query Property using VBA

Brilstern
100+
P: 208
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:
Expand|Select|Wrap|Line Numbers
  1.     Set rst = db.OpenRecordset("queryName")
  2.     rst.Properties("Top Values") = strTopValues
Mar 12 '15 #1
Share this Question
Share on Google+
7 Replies


Rabbit
Expert Mod 10K+
P: 12,430
What you're looking for is the QueryDef object.

https://msdn.microsoft.com/en-us/lib...ice.12%29.aspx
Mar 13 '15 #2

jforbes
Expert 100+
P: 1,107
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:
Expand|Select|Wrap|Line Numbers
  1. Dim oQD As QueryDef
  2. Set oQD = CurrentDb.QueryDefs("qryTestFilter_DoNotDelete")
  3. oQD.SQL = "SELECT * FROM " & sTable & " WHERE " & sWhere
  4.  
Mar 13 '15 #3

Brilstern
100+
P: 208
@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.
Mar 13 '15 #4

Brilstern
100+
P: 208
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.
Mar 13 '15 #5

Rabbit
Expert Mod 10K+
P: 12,430
I'm guessing there are ties so it returns all the ties.

Instead of the maxrecords property, you could try changing the SQL.
Mar 13 '15 #6

NeoPa
Expert Mod 15k+
P: 31,769
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.
Mar 15 '15 #7

Brilstern
100+
P: 208
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.
Mar 17 '15 #8

Post your reply

Sign in to post your reply or Sign up for a free account.