Change query parameter with vbscript

juneau730

Board Regular
Joined
Jun 7, 2018
Messages
69
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
 

juneau730

Board Regular
Joined
Jun 7, 2018
Messages
69
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
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

Board Regular
Joined
Jun 7, 2018
Messages
69
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
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

Board Regular
Joined
Jun 7, 2018
Messages
69
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
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

Board Regular
Joined
Jun 7, 2018
Messages
69
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.

NameDisplay NameDescriptionSmart Card RqdNT Name
Snuffy0, JayneSnuffy0, JayneADMIN: VHAPHOSnuffJ0TRUEVHAPHOSnuffJ0
Smith0V, TomSmith0V, TomADMIN: VHAELPSmithT0VFALSEVHAELPSmithT0V

<tbody>
</tbody>

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

Board Regular
Joined
Jun 7, 2018
Messages
69
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.
 

Forum statistics

Threads
1,082,132
Messages
5,363,340
Members
400,727
Latest member
Raynman

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top