Two Similar Queries - one lookup field not working

B

Build or Die

I have a form w/ continuous subform that switches between queries for the
recordsource. The only difference between the two queries is that there is
an additional table in one of them. The result is that one of the fields
becomes locked and I cannot change my selection...but the other query works
great. Any suggestions. (access 2000)

SQL for the one that works:

SELECT tblProject.ProjectYear, tblProject.ProjectID, tblcontactdetails.Role,
tblCompanyOrVillage.CompanyName, tblProject.ProjectIDSubscript,
tblProject.ProjectName, tblProject.ProjectCity, tblProject.ProjectState,
[ProjectCity] & "," & " " & [ProjectState] AS CityState,
tblProject.ProjectType, tblProject.ProjectFileLocaton
FROM tblCompanyOrVillage INNER JOIN (tblProject INNER JOIN tblcontactdetails
ON tblProject.ProjectID = tblcontactdetails.ProjectID) ON
tblCompanyOrVillage.CompanyOrVillageID = tblcontactdetails.CompanyID
WHERE (((tblProject.ProjectYear) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![yearfilter]) AND
((tblcontactdetails.Role)=1) AND ((tblProject.ProjectType) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![typefilter])) OR
(((tblcontactdetails.Role)=1) AND ((tblProject.ProjectType) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![typefilter]) AND
((([tblProject].[ProjectYear]) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![yearfilter]) Is Null))
OR (((tblProject.ProjectYear) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![yearfilter]) AND
((tblcontactdetails.Role)=1) AND ((([tblProject].[ProjectType]) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![typefilter]) Is Null))
OR (((tblcontactdetails.Role)=1) AND ((([tblProject].[ProjectYear]) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![yearfilter]) Is Null)
AND ((([tblProject].[ProjectType]) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![typefilter]) Is Null))
ORDER BY tblProject.ProjectYear DESC , tblProject.ProjectID DESC;

SQL for the one that does not work:

SELECT tblProject.ProjectYear, tblProject.ProjectID, tblcontactdetails.Role,
tblCompanyOrVillage.CompanyName, tblProject.ProjectIDSubscript,
tblProject.ProjectName, tblProject.ProjectCity, tblProject.ProjectState,
[ProjectCity] & "," & " " & [ProjectState] AS CityState,
tblProject.ProjectType, tblProject.ProjectFileLocaton,
tblProjectPhaseDetails.DepartmentManager,
tblProjectPhaseDetails.PercentComplete, tblProjectPhaseDetails.Status
FROM tblCompanyOrVillage INNER JOIN ((tblProject INNER JOIN
tblcontactdetails ON tblProject.ProjectID = tblcontactdetails.ProjectID)
INNER JOIN tblProjectPhaseDetails ON tblProject.ProjectID =
tblProjectPhaseDetails.ProjectID) ON tblCompanyOrVillage.CompanyOrVillageID =
tblcontactdetails.CompanyID
WHERE (((tblProject.ProjectYear) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![yearfilter]) AND
((tblcontactdetails.Role)=1) AND ((tblProject.ProjectType) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![typefilter]) AND
((tblProjectPhaseDetails.DepartmentManager) Like
[Forms]![frmProjectQuickREF]![managerfilter]) AND
((tblProjectPhaseDetails.PercentComplete)<>100) AND
((tblProjectPhaseDetails.Status)<5 Or (tblProjectPhaseDetails.Status)>6)) OR
(((tblcontactdetails.Role)=1) AND ((tblProject.ProjectType) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![typefilter]) AND
((tblProjectPhaseDetails.PercentComplete)<>100) AND
((tblProjectPhaseDetails.Status)<5 Or (tblProjectPhaseDetails.Status)>6) AND
(([Forms]![frmProjectQuickREF]![managerfilter]) Is Null) AND
((([tblProject].[ProjectYear]) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![yearfilter]) Is Null))
OR (((tblProject.ProjectYear) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![yearfilter]) AND
((tblcontactdetails.Role)=1) AND ((tblProjectPhaseDetails.DepartmentManager)
Like [Forms]![frmProjectQuickREF]![managerfilter]) AND
((tblProjectPhaseDetails.PercentComplete)<>100) AND
((tblProjectPhaseDetails.Status)<5 Or (tblProjectPhaseDetails.Status)>6) AND
((([tblProject].[ProjectType]) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![typefilter]) Is Null))
OR (((tblcontactdetails.Role)=1) AND
((tblProjectPhaseDetails.PercentComplete)<>100) AND
((tblProjectPhaseDetails.Status)<5 Or (tblProjectPhaseDetails.Status)>6) AND
(([Forms]![frmProjectQuickREF]![managerfilter]) Is Null) AND
((([tblProject].[ProjectYear]) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![yearfilter]) Is Null)
AND ((([tblProject].[ProjectType]) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![typefilter]) Is Null))
OR (((tblcontactdetails.Role)=1) AND ((tblProject.ProjectType) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![typefilter]) AND
((tblProjectPhaseDetails.DepartmentManager) Like
[Forms]![frmProjectQuickREF]![managerfilter]) AND
((tblProjectPhaseDetails.PercentComplete)<>100) AND
((tblProjectPhaseDetails.Status)<5 Or (tblProjectPhaseDetails.Status)>6) AND
((([tblProject].[ProjectYear]) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![yearfilter]) Is Null))
OR (((tblcontactdetails.Role)=1) AND
((tblProjectPhaseDetails.DepartmentManager) Like
[Forms]![frmProjectQuickREF]![managerfilter]) AND
((tblProjectPhaseDetails.PercentComplete)<>100) AND
((tblProjectPhaseDetails.Status)<5 Or (tblProjectPhaseDetails.Status)>6) AND
((([tblProject].[ProjectYear]) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![yearfilter]) Is Null)
AND ((([tblProject].[ProjectType]) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![typefilter]) Is Null))
OR (((tblProject.ProjectYear) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![yearfilter]) AND
((tblcontactdetails.Role)=1) AND ((tblProject.ProjectType) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![typefilter]) AND
((tblProjectPhaseDetails.PercentComplete)<>100) AND
((tblProjectPhaseDetails.Status)<5 Or (tblProjectPhaseDetails.Status)>6) AND
(([Forms]![frmProjectQuickREF]![managerfilter]) Is Null))
ORDER BY tblProject.ProjectYear DESC , tblProject.ProjectID DESC;

The field that does not work is the projectfilelocation... the only
difference between the one that works and the one that does not is the
additional field (from an additional table) of DepartmentManager from the
table ProjectPhaseDetails. Any ideas? Thanks.
 
R

Roger Carlson

There are many ways that a query can become non-updateable. Queries based
on Joins are particularly vulnerable. You can see a complete list of when a
query is updateable or not in Access Help. Type "When can I update data
from a query?" in the Answer Wizard section.

Two thought come to mind:
1) In one of your Joins, the field on the "One" side of a One-To-Many
relationship is not a primary key (or have a unique index on it).
2) You have 3 tables in a Many-to-One-to-Many relationship. This type of
query is non-updateable in Access (as the help file says) but you may be
able to update a form based on it if you set the RecordsetType property of
the form to Dynaset (Inconsistent Updates).

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Build or Die said:
I have a form w/ continuous subform that switches between queries for the
recordsource. The only difference between the two queries is that there is
an additional table in one of them. The result is that one of the fields
becomes locked and I cannot change my selection...but the other query works
great. Any suggestions. (access 2000)

SQL for the one that works:

SELECT tblProject.ProjectYear, tblProject.ProjectID, tblcontactdetails.Role,
tblCompanyOrVillage.CompanyName, tblProject.ProjectIDSubscript,
tblProject.ProjectName, tblProject.ProjectCity, tblProject.ProjectState,
[ProjectCity] & "," & " " & [ProjectState] AS CityState,
tblProject.ProjectType, tblProject.ProjectFileLocaton
FROM tblCompanyOrVillage INNER JOIN (tblProject INNER JOIN tblcontactdetails
ON tblProject.ProjectID = tblcontactdetails.ProjectID) ON
tblCompanyOrVillage.CompanyOrVillageID = tblcontactdetails.CompanyID
WHERE (((tblProject.ProjectYear) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![yearfilter]) AND
((tblcontactdetails.Role)=1) AND ((tblProject.ProjectType) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![typefilter])) OR
(((tblcontactdetails.Role)=1) AND ((tblProject.ProjectType) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![typefilter]) AND
((([tblProject].[ProjectYear]) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![yearfilter]) Is Null))
OR (((tblProject.ProjectYear) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![yearfilter]) AND
((tblcontactdetails.Role)=1) AND ((([tblProject].[ProjectType]) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![typefilter]) Is Null))
OR (((tblcontactdetails.Role)=1) AND ((([tblProject].[ProjectYear]) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![yearfilter]) Is Null)
AND ((([tblProject].[ProjectType]) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![typefilter]) Is Null))
ORDER BY tblProject.ProjectYear DESC , tblProject.ProjectID DESC;

SQL for the one that does not work:

SELECT tblProject.ProjectYear, tblProject.ProjectID, tblcontactdetails.Role,
tblCompanyOrVillage.CompanyName, tblProject.ProjectIDSubscript,
tblProject.ProjectName, tblProject.ProjectCity, tblProject.ProjectState,
[ProjectCity] & "," & " " & [ProjectState] AS CityState,
tblProject.ProjectType, tblProject.ProjectFileLocaton,
tblProjectPhaseDetails.DepartmentManager,
tblProjectPhaseDetails.PercentComplete, tblProjectPhaseDetails.Status
FROM tblCompanyOrVillage INNER JOIN ((tblProject INNER JOIN
tblcontactdetails ON tblProject.ProjectID = tblcontactdetails.ProjectID)
INNER JOIN tblProjectPhaseDetails ON tblProject.ProjectID =
tblProjectPhaseDetails.ProjectID) ON
Click to expand...
tblCompanyOrVillage.CompanyOrVillageID =
tblcontactdetails.CompanyID
WHERE (((tblProject.ProjectYear) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![yearfilter]) AND
((tblcontactdetails.Role)=1) AND ((tblProject.ProjectType) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![typefilter]) AND
((tblProjectPhaseDetails.DepartmentManager) Like
[Forms]![frmProjectQuickREF]![managerfilter]) AND
((tblProjectPhaseDetails.PercentComplete)<>100) AND
((tblProjectPhaseDetails.Status)<5 Or (tblProjectPhaseDetails.Status)>6)) OR
(((tblcontactdetails.Role)=1) AND ((tblProject.ProjectType) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![typefilter]) AND
((tblProjectPhaseDetails.PercentComplete)<>100) AND
((tblProjectPhaseDetails.Status)<5 Or (tblProjectPhaseDetails.Status)>6) AND
(([Forms]![frmProjectQuickREF]![managerfilter]) Is Null) AND
((([tblProject].[ProjectYear]) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![yearfilter]) Is Null))
OR (((tblProject.ProjectYear) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![yearfilter]) AND
((tblcontactdetails.Role)=1) AND ((tblProjectPhaseDetails.DepartmentManager)
Like [Forms]![frmProjectQuickREF]![managerfilter]) AND
((tblProjectPhaseDetails.PercentComplete)<>100) AND
((tblProjectPhaseDetails.Status)<5 Or (tblProjectPhaseDetails.Status)>6) AND
((([tblProject].[ProjectType]) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![typefilter]) Is Null))
OR (((tblcontactdetails.Role)=1) AND
((tblProjectPhaseDetails.PercentComplete)<>100) AND
((tblProjectPhaseDetails.Status)<5 Or (tblProjectPhaseDetails.Status)>6) AND
(([Forms]![frmProjectQuickREF]![managerfilter]) Is Null) AND
((([tblProject].[ProjectYear]) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![yearfilter]) Is Null)
AND ((([tblProject].[ProjectType]) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![typefilter]) Is Null))
OR (((tblcontactdetails.Role)=1) AND ((tblProject.ProjectType) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![typefilter]) AND
((tblProjectPhaseDetails.DepartmentManager) Like
[Forms]![frmProjectQuickREF]![managerfilter]) AND
((tblProjectPhaseDetails.PercentComplete)<>100) AND
((tblProjectPhaseDetails.Status)<5 Or (tblProjectPhaseDetails.Status)>6) AND
((([tblProject].[ProjectYear]) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![yearfilter]) Is Null))
OR (((tblcontactdetails.Role)=1) AND
((tblProjectPhaseDetails.DepartmentManager) Like
[Forms]![frmProjectQuickREF]![managerfilter]) AND
((tblProjectPhaseDetails.PercentComplete)<>100) AND
((tblProjectPhaseDetails.Status)<5 Or (tblProjectPhaseDetails.Status)>6) AND
((([tblProject].[ProjectYear]) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![yearfilter]) Is Null)
AND ((([tblProject].[ProjectType]) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![typefilter]) Is Null))
OR (((tblProject.ProjectYear) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![yearfilter]) AND
((tblcontactdetails.Role)=1) AND ((tblProject.ProjectType) Like
[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB1]![typefilter]) AND
((tblProjectPhaseDetails.PercentComplete)<>100) AND
((tblProjectPhaseDetails.Status)<5 Or (tblProjectPhaseDetails.Status)>6) AND
(([Forms]![frmProjectQuickREF]![managerfilter]) Is Null))
ORDER BY tblProject.ProjectYear DESC , tblProject.ProjectID DESC;

The field that does not work is the projectfilelocation... the only
difference between the one that works and the one that does not is the
additional field (from an additional table) of DepartmentManager from the
table ProjectPhaseDetails. Any ideas? Thanks.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Continuous form with Listbox 0
Querying multple criteria but allowing them to not be blank 3
Report based on two similar queries 1
refer to field on subform not working 1
Duplicating values to empty field 6
engine does not recognize " as a valid field name 5
Matching Query not working 2
Combination of Field not empty 2

Top