Access query - 'enter parameter value'

Dear experts - I am trying to create a query where the user may enter one of several criteria. The problem is that each of these criteria hang off a different table.
Here is the query graphically - I add the DISTINCT keyword, since there are multiple related records in several of the tables:


query
NOW - I am creating a string, which, for example, includes this:


If Nz(Me!cboProject_ID, 0) <> 0 Then
     strFilter = strFilter & " And projects.project_ID =" & Me!cboProject_ID
End If


NOTE that there is no field in the results called 'project_ID' - I am actually able to run the query as a standalone and it produces perfect results:
SELECT DISTINCT Vendors.Vendor_ID, Vendors.VendorCode, Vendors.CompanyName, Vendors.Forename, Vendors.Surname, Vendors.Country_ID, Vendors.Tel, Vendors.Cell, Vendors.Email, Vendors.Status, Vendors.Agency, Vendors.LinguisticVendor, Vendors.MedicalVendor, Vendors.Eval_Alert, Vendors.LanguageSource_ID, Vendors.LanguageTarget_ID, Vendors.Language_Details, Vendors.ScreenInitialEval, Vendors.ScreenBy, stripaccents(Nz([vendors].[companyname],"")) AS strippedco, stripaccents(Nz([vendors.forename],"")) AS strippedfirst, stripaccents(Nz([vendors.middlename],"")) AS strippedmiddle, stripaccents(Nz([vendors.surname],"")) AS strippedlast, PICK_Countries.abbreviation AS homecountry, source.abbrev AS source, target.abbrev AS target, source.language_ID AS SourceLangOnly_ID, target.language_ID AS TargetLangOnly_ID, IIf([EVAL_ALERT]=1,"RED FLAG",IIf([eval_alert]=2,"AMBER","")) AS Alert, Switch([Vendors].[status]=1,"Active",[Vendors].[status]=2,"Inactive",[Vendors].[status]=3,"Decommissioned (Inactive)") AS StatusText, GetSubjects([Vendors].[Vendor_ID]) AS Subjects, GetActions([Vendors].[Vendor_ID]) AS Actions, qryVendorActionCount_Last6mos.countofexpense_ID AS CountOfExpense_ID
FROM Projects RIGHT JOIN (Project_Parts RIGHT JOIN (((((Vendors LEFT JOIN PICK_Countries ON Vendors.Country_ID = PICK_Countries.ID) LEFT JOIN qryVendorActionCount_Last6mos ON Vendors.Vendor_ID = qryVendorActionCount_Last6mos.Person_ID) LEFT JOIN LanguagePlusLocale_byID AS source ON Vendors.LanguageSource_ID = source.LanguagePlusLocale_ID) LEFT JOIN LanguagePlusLocale_byID AS target ON Vendors.LanguageTarget_ID = target.LanguagePlusLocale_ID) LEFT JOIN Project_Lines_Expenses ON Vendors.Vendor_ID = Project_Lines_Expenses.Person_ID) ON Project_Parts.Part_ID = Project_Lines_Expenses.Part_ID) ON Projects.Project_ID = Project_Parts.Project_ID
WHERE (((Projects.Project_ID)=2000));

Open in new window


Now, I run the above query WITHIN THE FORM, and I get a ENTER PARAMETER  ('projects.project_ID') error.

error message
Any guidance is greatly appreciated.
terpsichoreAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
The query itself looks OK.

Therefore, my best guess is that one of the earlier queries to which this is linked uses "projects.project_ID" incorrect.

One of the reasons I say this is that the above screenshot uses "projects.project_ID" (lower case), whereas your query says "Projects.Project_ID" (upper case).

Try opening each of the queries on which this depends. This is especially true if any of the earlier queries are crossqueries.
Dale FyeOwner, Developing Solutions LLCCommented:
I would be inclined to agree with Phillip, to start your investigation with those other queries.  However, you state:

"Now, I run the above query WITHIN THE FORM, and I get a ENTER PARAMETER  ('projects.project_ID') error."

How are you running this query "within the form"?  Are you attempting to open a recordset?  Have you reformatted the query as a sql string?
terpsichoreAuthor Commented:
in the VBA, I assemble a string - when I output the total string in the immediate window, it displays:

SELECT qryVendorDashboard1.* FROM qryVendorDashboard1  WHERE [LinguisticVendor]=True And [vendors].[Status]=1 And [projects].[project_ID] =4437 ;


which looks ok to me -
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Not to me it doesn't - you don't have the projects table in the FROM section of the string query.
Dale FyeOwner, Developing Solutions LLCCommented:
does "qryVendorDashboard1" contain a parameter definition (either in the Criteria of the query itself, or a defined parameter)?

You indicate when you run it from the query designer it works fine, which implies that it asks you for that parameter.  If you remove that parameter from the query itself, then it should run fine.
terpsichoreAuthor Commented:
1) [for Mr. Burton] The Projects table IS in the underlying query - see the definition in the original question - this query is called qryVendorDashboard1.
2) [for Mr. Rye] The query does not contain a parameter definition; also, when i run it from the query designer it doesn't ask me for that, i had put it in manually into there query (unchecking the checkbox so it is only 'where' and not output as a field).
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You said:

in the VBA, I assemble a string - when I output the total string in the immediate window, it displays:

SELECT qryVendorDashboard1.* FROM qryVendorDashboard1  WHERE [LinguisticVendor]=True And [vendors].[Status]=1 And [projects].[project_ID] =4437
;

qryVendorDashboard1 does not include in the SELECT statement the field [projects].[project_ID]; the only place it is contained in is in the FROM and WHERE. Therefore, qryVendorDashboard1 does not include as part of the output [projects].[project_ID].

In any case, even if it did, what you are doing would not make sense, because in qryVendorDashboard1 you want only those rows where (Projects.Project_ID)=2000, and now you want [projects].[project_ID] =4437. That would be zero rows.
terpsichoreAuthor Commented:
1) Sorry, I had that where clause in the query temporarily. the query reads like this:


SELECT DISTINCT Vendors.Vendor_ID, Vendors.VendorCode, Vendors.CompanyName, Vendors.Forename, Vendors.Surname, Vendors.Country_ID, Vendors.Tel, Vendors.Cell, Vendors.Email, Vendors.Status, Vendors.Agency, Vendors.LinguisticVendor, Vendors.MedicalVendor, Vendors.Eval_Alert, Vendors.LanguageSource_ID, Vendors.LanguageTarget_ID, Vendors.Language_Details, Vendors.ScreenInitialEval, Vendors.ScreenBy, stripaccents(Nz([vendors].[companyname],"")) AS strippedco, stripaccents(Nz([vendors.forename],"")) AS strippedfirst, stripaccents(Nz([vendors.middlename],"")) AS strippedmiddle, stripaccents(Nz([vendors.surname],"")) AS strippedlast, PICK_Countries.abbreviation AS homecountry, source.abbrev AS source, target.abbrev AS target, source.language_ID AS SourceLangOnly_ID, target.language_ID AS TargetLangOnly_ID, IIf([EVAL_ALERT]=1,"RED FLAG",IIf([eval_alert]=2,"AMBER","")) AS Alert, Switch([Vendors].[status]=1,"Active",[Vendors].[status]=2,"Inactive",[Vendors].[status]=3,"Decommissioned (Inactive)") AS StatusText, GetSubjects([Vendors].[Vendor_ID]) AS Subjects, GetActions([Vendors].[Vendor_ID]) AS Actions, qryVendorActionCount_Last6mos.countofexpense_ID AS CountOfExpense_ID
FROM Projects RIGHT JOIN (Project_Parts RIGHT JOIN (((((Vendors LEFT JOIN PICK_Countries ON Vendors.Country_ID = PICK_Countries.ID) LEFT JOIN qryVendorActionCount_Last6mos ON Vendors.Vendor_ID = qryVendorActionCount_Last6mos.Person_ID) LEFT JOIN LanguagePlusLocale_byID AS source ON Vendors.LanguageSource_ID = source.LanguagePlusLocale_ID) LEFT JOIN LanguagePlusLocale_byID AS target ON Vendors.LanguageTarget_ID = target.LanguagePlusLocale_ID) LEFT JOIN Project_Lines_Expenses ON Vendors.Vendor_ID = Project_Lines_Expenses.Person_ID) ON Project_Parts.Part_ID = Project_Lines_Expenses.Part_ID) ON Projects.Project_ID = Project_Parts.Project_ID;

Open in new window


2) More importantly, it was my understanding that the field project_id did NOT have to be in the output - and I have tested this independently of the VBA code and it seems to work just fine.
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
It does not need to be in the output, unless you refer to it later (which you are doing), or you have a JOIN to it later.
Dale FyeOwner, Developing Solutions LLCCommented:
As you indicated here, you "unchecked" the Project_ID field so that it would not display in the query.

In order to use that parameter in your VBA SQL statement, the query must include that field.
terpsichoreAuthor Commented:
I guess I'm still confused - the joins are defined in the query, and that includes the table projects. If i can run a query using a where clause but not including it as an output field, I guess I'm confused as to why this wouldn't work programmatically using VBA...
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
It's nothing to do about whether you can do it programmatically using VBA.

You are using the Project_ID field in Query1. It works in Query1. You happen to be using it in the FROM and WHERE, but not the SELECT. Therefore, Query1 does not output the Project_ID field.

You then develop Query2. You expect Query2 to use Query1. You expect Query2 to see the Project_ID field from Query1. However, Query1 does not output the Project_ID field. Therefore, Query2 cannot see it in Query1.

It would be exactly the same if you were writing a new query or doing it in VBA.
terpsichoreAuthor Commented:
I'm still confused - it's the same query, isn't it? the query I pasted directly above qryVendorDashboard1 is the same query i'm invoking in VBA. That query has the projects table in the linkage; it doesn't output the project_ID field, that's correct.
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
It's not the same query.

Let's try this another way:

Projects.Project_ID goes into Query1 (it is in the FROM).
Projects.Project_ID does not go out of Query1 (is it not in the SELECT).
Therefore, Query2 cannot reference Projects.Project_ID from Query1, because it does not go out of Query1.

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
terpsichoreAuthor Commented:
ok - maybe that is the best solution - instead of using a defined query, I will simply put the whole SQL into the VBA, that way there is no issue.
Thanks - this has led me to a simple solution.
Dale FyeOwner, Developing Solutions LLCCommented:
@terpsichore,

You need to understand this concept!

When you create a saved query, in order to use that query as the source of another query, and set a criteria at run time, you must include all of the fields in the saved query that will be referenced in the criteria you create at run time.

However, depending on what you want to do with that SQL string you created at run time, you might be able to save the query (query1) as a parameter query, where you include the parameter [Enter Project ID] as a defined parameter, and set the criteria of that query just like you did when you ran it from the query grid:

WHERE [Projects].Project_ID = [Enter Project ID]

Then, in your code, if you wanted to create a recordset based on that query, you would do something like:

Dim db as DAO.Database
Dim qdf as DAO.QueryDef
Dim rs as DAO.Recordset

Set db = currentdb
set qdf = db.querydefs("yourQueryName")
qdf.Parameters("Enter Project ID") = 4437

set rs = qdf.openrecordset
terpsichoreAuthor Commented:
much appreciated Dale!!
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
Microsoft Access

From novice to tech pro — start learning today.