Change query parameter with vbscript

[juneau730]

I've searched the wonderful world of Google for a simple answer and wasn't able to find one.

Is there a simple way to have a standalone (manually executed) vbscript to change an Access query parameters?

I have the same parameter in three queries, which is Like"XXX"

I need to replace the XXX with a manually input three letter code

 

[Joe4]

Does a "Parameter Query", which asks for input at run-time, not work for you?
See: https://support.office.com/en-gb/ar...-a-query-c2806d3d-d500-45a8-8507-ec6af351b6ed

 

[juneau730]

No, unfortunately a "Parameter Query" isn't practical in this instance.
In this case, once the Query Parameter is set, during the initial setup of the dBase, it won't change again.
I know I could hard code the queries, but since this will be used by numerous different locations, each with a different three letter code, it's not practical for me to do that.
Unfortuneately, I can't be assured that the other users would be able to go into the query and change it properly, thus the desire for a vbscript, that pops up an input box.

 

[Joe4]

You don't need to hard-code it directly into the query. Create a single row table that holds settings, such as this, and have the query incorporate that.
You can make a form that holds this single line record, where it is extremely easy to update. Open it once, set the value, and they wouldn't need to touch it again.

 

[juneau730]

Because my data changes monthly (though the format does not) my table gets purged when the data is refreshed. The data contains user account info for multiple sites, but needs to be filtered down at each site, for that site, to allow them to audit only their data.

If I can do the change in the form, vice the query, that would be good too. I am open to any way to skin the cat and accomplish the goal.

However, there is concern that it may not be practical for many of my users to do, as some have difficulty just using the dBase as it is.

 

[Joe4]

This table would not be with your data table that gets updated monthly - it is a separate "settings" table.
I often use this as the Source of my Start-up Form (the form that you see when you open the database).
So, it would just show some default settings. Once that value is set for that particular location, it should never have to be changed. And those "Settings" values can be used in your queries to filter the records just for that location.

Note I should also preface that if you have different locations using the database, the database should be split into a back-end and front-end database. Every location should have their own copy of the front-end, so that any changes that they make to the settings would not affect other locations.

 

[juneau730]

Good morning Joe4, I think I am following you, but not sure how to set one of these up, do you have any pointers?

Currently every site runs their copy of the dBase locally, as the information is unique to each site and not shared among each other. So there is no worry of messing up the back end.

 

[Joe4]

What part aren't you sure of?
Do you know how to create queries between multiple tables, by joining them on field(s)?
You may be able to just join your Data table and Settings table on the value in that field.

It may be better if we can deal in an actual example. Can you post a very small snippet of your data, and give us an example of an actual parameter you would like to use?

 

[juneau730]

Yes, I do know how to link multiple tables, by fields in a query. I have a couple queries in the dBase built like that.

Here is a modified example of the raw data set. The format is the same as live data.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Display Name[/TD]
[TD]Description[/TD]
[TD]Smart Card Rqd[/TD]
[TD]NT Name[/TD]
[/TR]
[TR]
[TD]Snuffy0, Jayne[/TD]
[TD]Snuffy0, Jayne[/TD]
[TD]ADMIN: VHAPHOSnuffJ0[/TD]
[TD]TRUE[/TD]
[TD]VHAPHOSnuffJ0[/TD]
[/TR]
[TR]
[TD]Smith0V, Tom[/TD]
[TD]Smith0V, Tom[/TD]
[TD]ADMIN: VHAELPSmithT0V[/TD]
[TD]FALSE[/TD]
[TD]VHAELPSmithT0V[/TD]
[/TR]
</tbody>[/TABLE]

In the NT Name, the 4-6 letter designate the site code. When the RAW data is pulled from Active Directory, it pulls all sites in our Region, because these accounts reside in a Region level, vice site level OU.

The query is currently built to filter on that field as such Like "*XXX*" where the XXX would be the three letter site code, such as Like "*PHO*" thus only displaying accounts that fall under that site.

 

[juneau730]

Not sure if it will change anything, but there are a few other fields within the query that have filters on them. These are not changing in any fashion though, they will stay hard coded within the query as they are the same, required, for each site.