Criteria for Pass-Through Query via SQL Server

I'm new to the Pass-Through Queries and I'm trying to understand the concept of them and how to incorporate a query criteria to them, or a parameter in SQL Server. I have a main form with 4 different subforms. All the subforms recordsources will be pass-through queries pulling data from my BE which is in SQL server. On the main form there is a combobox that a user can select a part number. Once that part number is selected I need the 4 subforms to requery the data and filter the results to only show records that have that part number in a specific field. The old way of doing it would just be to reference the forms combobox in the query criteria of that specific field. In learning a little about pass-through queries over the past couple of days I found out that you cannot reference a forms control in the query criteria. So I'm trying to figure out the best way to create these query criterias that are just like referencing a forms control. The pass-through queries are not all identical for starters. They all have different values since they are pulling from different tables in SQL. Secondly there are some pass-through queries that are the ground floor queries and then above them there are other queries that sum & group the data, so some of these are not the subforms direct recordsource. So I do not think I can go the route of creating these in the forms VBA and using them as the recordsource since the data still needs to be summed and grouped in other queries. Can anyone shed some light on how I can incorporate criterias that pull from the main forms combobox control? I hope I explained my situation clearly enough but if I didn't feel free to ask. I'm not asking for someone to create my solutions. I'm just looking for someone to help me walk through the best approach to adding criterias to my queries in some fashion.
LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
As i mentioned in a our earlier post, one method is to simply update the sql.  I've also done it with a Tbl_Parameters in SQL Server.  I link this table to Access and the use the after update event of controls to update those parameters.  You can then use those field by using syntax similar to:

Where [somefield] = (select dateparam from tbl_parameters)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I like that idea Dale. Thank you again for your help. That sounds like the perfect solution!
0
Dale FyeCommented:
The challenge with doing it that way is in multi-user environments, that does not work without some tweaking.

First, you also have to store a userID with your parameters.  Then you have to modify the pass-thru queries on the front end so that they use your user id.  So instead of the method I mentioned last night, from my iPhone, you would have to create a view on the SQL Server that joins the parameters table to your other table (maybe more than once if you have more than one parameter to test).  And you would have to include the UserID field in the view.  Then, you would have to modify the pass-thru query like:

SELECT * FROM vw_SomeView
WHERE UserID = 'yourUserID'
0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Ugh. You had to rain on my parade! This will be in a multi-user environment.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.