VB to Update Query(s) without opening up each query

[santa12345]

Hello.
I am trying to automate the following.
I need to update Query 1, 2, 3, and 4 with a dealership ID #.
So instead of opening up each query and entering the dealership ID #, I was wondering if there is an easier way of automating this.
I used to use [Forms]![Form1]![dealerno] but this is not working anymore within (querying a Azure DB). Forms would call another table where I had that dealer # entered.
Any suggestions would be much appreciated. Thank you.

 

[Joe4]

There are a few ways you can go about this.

1. Create a "one-record" table where you store the dealership ID that you want, and include this table in your 4 queries (link to this table). That will automatically filter your 4 queries for that specific dealership ID. So any time you wanted to change it, you just change that one record in your table.

2. Use VBA to automatically rewrite the SQL code of your queries to add the filter your queries by that ID. Sometimes, if the queries are a bit long or complex, I will create a little "cheat". If will create a query that returns all my desired fields and does all my calculations, but does not have any dealership ID criteria. Then save it with a name like "subQuery1". Then create another query named "Query1". It doesn't really matter what you put in this query.

Then in my VBA code, I update the SQL code of Query1 to something to the effect of:

SELECT subQuery1.*
FROM subQuery1
WHERE DealershipID = '...'

So there are a few ways to accomplish what you need.

 

[santa12345]

Hi Joe.
Thank you.
#1 doesn't work. I've tried that, but the query runs forever, never finishes. Not sure if its something to do with linking to an Azure DB??
I can try #2 this morning.

 

[Micron]

I suggest you post the query sql to have your forms reference checked. If it works when you open it from the nav pane (when the form is open of course), it should work if you run it from code.
When you tried the first suggestion, did you remember to link the id fields from the 2 tables?

 

[Joe4]

When you tried the first suggestion, did you remember to link the id fields from the 2 tables?

Good point.

If the data is remotely stored, I wonder if a Pass-Through query may help with performance, as opposed to bringing everything back, and then querying on the Access side.
(I have never worked directly with Azure DB's, but I am assuming that you can do a Pass-Through Query from Access to Azure).

 

[welshgasman]

I would have just referred to a TempVar for the criteria and set that from the form if not an Azure DB.