Access query - 'enter parameter value'

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.

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?

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 -

Not to me it doesn't - you don't have the projects table in the FROM section of the string query.

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.

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

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.

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;

Select allOpen 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.

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.

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.

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

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.

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.

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.

@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

much appreciated Dale!!