Read Only Query, need a little help

D

DawnTreader

Hello All

i have a query that i need to make editable. i want to be able to use it to
change some of the columns in the second table in my query. anything in the
tblMasterPartList needs to be editable, but the results from the SQL below
are Read only.

SELECT
dbo_PART.ID AS VisualID,
tblMasterPartList.ID AS MPLPN,
dbo_PART.DESCRIPTION AS PartDesc,

[UNIT_MATERIAL_COST]+[UNIT_LABOR_COST]+[UNIT_BURDEN_COST]+[UNIT_SERVICE_COST]
AS CurCDNCost,
dbo_PART.UNIT_PRICE AS VisualList,
dbo_PART.PRODUCT_CODE,
dbo_PART.COMMODITY_CODE,
tblMasterPartList.Filter,
tblMasterPartList.[1000Maint],
tblMasterPartList.[5000Rebuild],
tblMasterPartList.[10000Rebuild],
tblMasterPartList.[15000Rebuild],
tblMasterPartList.[20000Rebuild],
tblMasterPartList.[25000Rebuild],
dbo_PART.FABRICATED,
dbo_PART.PURCHASED,
dbo_PART.CONSUMABLE,
dbo_PART.DETAIL_ONLY
FROM
(dbo_PART LEFT JOIN tblMasterPartList ON dbo_PART.ID =
tblMasterPartList.ID)
LEFT JOIN utblServRepMasterPartListFilter ON tblMasterPartList.ID =
utblServRepMasterPartListFilter.IMWPartID
WHERE
(((dbo_PART.ID) Between "200000" And "400000"
And (dbo_PART.ID) Like
[Forms]![frmMasterPartListwRepCalcs]![txtPartNumberFilter] & "*")
AND ((dbo_PART.DESCRIPTION) Like "*" &
[Forms]![frmMasterPartListwRepCalcs]![txtDescriptionHidden] & "*")
AND
(([UNIT_MATERIAL_COST]+[UNIT_LABOR_COST]+[UNIT_BURDEN_COST]+[UNIT_SERVICE_COST])>0)
AND ((dbo_PART.UNIT_PRICE)>0)
AND ((dbo_PART.PRODUCT_CODE) Not Like "ROLLFORM"
And (dbo_PART.PRODUCT_CODE) Not Like "ROLLAFTER"
And (dbo_PART.PRODUCT_CODE) Not Like "RETAIL"
And (dbo_PART.PRODUCT_CODE) Not Like "REFORMER"
And (dbo_PART.PRODUCT_CODE) Not Like "RAWMAT"
And (dbo_PART.PRODUCT_CODE) Not Like "PRESSPROD")
And ((dbo_PART.COMMODITY_CODE) Not Like "tool"
And (dbo_PART.COMMODITY_CODE) Not Like "RAW MATERIAL"
And (dbo_PART.COMMODITY_CODE) Not Like "STRUCTURAL")
AND ((tblMasterPartList.Filter) In
(IIf([Forms]![frmMasterPartListwRepCalcs]![chkFilterOnOff] Is Null,-1,

(IIf([Forms]![frmMasterPartListwRepCalcs]![chkFilterOnOff]=-1,0,-1))),(IIf([Forms]![frmMasterPartListwRepCalcs]![chkFilterOnOff]=0,-1,0))))
AND ((tblMasterPartList.[1000Maint]) In
(IIf([Forms]![frmMasterPartListwRepCalcs]![chk1000HrsOnOff] Is Null,0,

(IIf([Forms]![frmMasterPartListwRepCalcs]![chk1000HrsOnOff]=-1,-1,0))),(IIf([Forms]![frmMasterPartListwRepCalcs]![chk1000HrsOnOff]=0,0,-1))))
AND ((tblMasterPartList.[5000Rebuild]) In
(IIf([Forms]![frmMasterPartListwRepCalcs]![chk5000HrsOnOff] Is Null,0,

(IIf([Forms]![frmMasterPartListwRepCalcs]![chk5000HrsOnOff]=-1,-1,0))),(IIf([Forms]![frmMasterPartListwRepCalcs]![chk5000HrsOnOff]=0,0,-1))))
AND ((tblMasterPartList.[10000Rebuild]) In
(IIf([Forms]![frmMasterPartListwRepCalcs]![chk10000HrsOnOff] Is Null,0,

(IIf([Forms]![frmMasterPartListwRepCalcs]![chk10000HrsOnOff]=-1,-1,0))),(IIf([Forms]![frmMasterPartListwRepCalcs]![chk10000HrsOnOff]=0,0,-1))))
AND ((tblMasterPartList.[15000Rebuild]) In
(IIf([Forms]![frmMasterPartListwRepCalcs]![chk15000HrsOnOff] Is Null,0,

(IIf([Forms]![frmMasterPartListwRepCalcs]![chk15000HrsOnOff]=-1,-1,0))),(IIf([Forms]![frmMasterPartListwRepCalcs]![chk15000HrsOnOff]=0,0,-1))))
AND ((tblMasterPartList.[20000Rebuild]) In
(IIf([Forms]![frmMasterPartListwRepCalcs]![chk20000HrsOnOff] Is Null,0,

(IIf([Forms]![frmMasterPartListwRepCalcs]![chk20000HrsOnOff]=-1,-1,0))),(IIf([Forms]![frmMasterPartListwRepCalcs]![chk20000HrsOnOff]=0,0,-1))))
AND ((tblMasterPartList.[25000Rebuild]) In
(IIf([Forms]![frmMasterPartListwRepCalcs]![chk25000HrsOnOff] Is Null,0,

(IIf([Forms]![frmMasterPartListwRepCalcs]![chk25000HrsOnOff]=-1,-1,0))),(IIf([Forms]![frmMasterPartListwRepCalcs]![chk25000HrsOnOff]=0,0,-1))))
AND ((dbo_PART.CONSUMABLE) Not Like "Y")
AND ((dbo_PART.DETAIL_ONLY) Not Like "y"))
OR (((tblMasterPartList.ID) Is Null))
ORDER BY
tblMasterPartList.ID;

so the question is, what do i need to do different?
 
D

Duane Hookom

Are these primary key fields or at least have a unique index:
tblMasterPartList.ID
utblServRepMasterPartListFilter.IMWPartID

--
Duane Hookom
Microsoft Access MVP


DawnTreader said:
Hello All

i have a query that i need to make editable. i want to be able to use it to
change some of the columns in the second table in my query. anything in the
tblMasterPartList needs to be editable, but the results from the SQL below
are Read only.

SELECT
dbo_PART.ID AS VisualID,
tblMasterPartList.ID AS MPLPN,
dbo_PART.DESCRIPTION AS PartDesc,

[UNIT_MATERIAL_COST]+[UNIT_LABOR_COST]+[UNIT_BURDEN_COST]+[UNIT_SERVICE_COST]
AS CurCDNCost,
dbo_PART.UNIT_PRICE AS VisualList,
dbo_PART.PRODUCT_CODE,
dbo_PART.COMMODITY_CODE,
tblMasterPartList.Filter,
tblMasterPartList.[1000Maint],
tblMasterPartList.[5000Rebuild],
tblMasterPartList.[10000Rebuild],
tblMasterPartList.[15000Rebuild],
tblMasterPartList.[20000Rebuild],
tblMasterPartList.[25000Rebuild],
dbo_PART.FABRICATED,
dbo_PART.PURCHASED,
dbo_PART.CONSUMABLE,
dbo_PART.DETAIL_ONLY
FROM
(dbo_PART LEFT JOIN tblMasterPartList ON dbo_PART.ID =
tblMasterPartList.ID)
LEFT JOIN utblServRepMasterPartListFilter ON tblMasterPartList.ID =
utblServRepMasterPartListFilter.IMWPartID
WHERE
(((dbo_PART.ID) Between "200000" And "400000"
And (dbo_PART.ID) Like
[Forms]![frmMasterPartListwRepCalcs]![txtPartNumberFilter] & "*")
AND ((dbo_PART.DESCRIPTION) Like "*" &
[Forms]![frmMasterPartListwRepCalcs]![txtDescriptionHidden] & "*")
AND
(([UNIT_MATERIAL_COST]+[UNIT_LABOR_COST]+[UNIT_BURDEN_COST]+[UNIT_SERVICE_COST])>0)
AND ((dbo_PART.UNIT_PRICE)>0)
AND ((dbo_PART.PRODUCT_CODE) Not Like "ROLLFORM"
And (dbo_PART.PRODUCT_CODE) Not Like "ROLLAFTER"
And (dbo_PART.PRODUCT_CODE) Not Like "RETAIL"
And (dbo_PART.PRODUCT_CODE) Not Like "REFORMER"
And (dbo_PART.PRODUCT_CODE) Not Like "RAWMAT"
And (dbo_PART.PRODUCT_CODE) Not Like "PRESSPROD")
And ((dbo_PART.COMMODITY_CODE) Not Like "tool"
And (dbo_PART.COMMODITY_CODE) Not Like "RAW MATERIAL"
And (dbo_PART.COMMODITY_CODE) Not Like "STRUCTURAL")
AND ((tblMasterPartList.Filter) In
(IIf([Forms]![frmMasterPartListwRepCalcs]![chkFilterOnOff] Is Null,-1,

(IIf([Forms]![frmMasterPartListwRepCalcs]![chkFilterOnOff]=-1,0,-1))),(IIf([Forms]![frmMasterPartListwRepCalcs]![chkFilterOnOff]=0,-1,0))))
AND ((tblMasterPartList.[1000Maint]) In
(IIf([Forms]![frmMasterPartListwRepCalcs]![chk1000HrsOnOff] Is Null,0,

(IIf([Forms]![frmMasterPartListwRepCalcs]![chk1000HrsOnOff]=-1,-1,0))),(IIf([Forms]![frmMasterPartListwRepCalcs]![chk1000HrsOnOff]=0,0,-1))))
AND ((tblMasterPartList.[5000Rebuild]) In
(IIf([Forms]![frmMasterPartListwRepCalcs]![chk5000HrsOnOff] Is Null,0,

(IIf([Forms]![frmMasterPartListwRepCalcs]![chk5000HrsOnOff]=-1,-1,0))),(IIf([Forms]![frmMasterPartListwRepCalcs]![chk5000HrsOnOff]=0,0,-1))))
AND ((tblMasterPartList.[10000Rebuild]) In
(IIf([Forms]![frmMasterPartListwRepCalcs]![chk10000HrsOnOff] Is Null,0,

(IIf([Forms]![frmMasterPartListwRepCalcs]![chk10000HrsOnOff]=-1,-1,0))),(IIf([Forms]![frmMasterPartListwRepCalcs]![chk10000HrsOnOff]=0,0,-1))))
AND ((tblMasterPartList.[15000Rebuild]) In
(IIf([Forms]![frmMasterPartListwRepCalcs]![chk15000HrsOnOff] Is Null,0,

(IIf([Forms]![frmMasterPartListwRepCalcs]![chk15000HrsOnOff]=-1,-1,0))),(IIf([Forms]![frmMasterPartListwRepCalcs]![chk15000HrsOnOff]=0,0,-1))))
AND ((tblMasterPartList.[20000Rebuild]) In
(IIf([Forms]![frmMasterPartListwRepCalcs]![chk20000HrsOnOff] Is Null,0,

(IIf([Forms]![frmMasterPartListwRepCalcs]![chk20000HrsOnOff]=-1,-1,0))),(IIf([Forms]![frmMasterPartListwRepCalcs]![chk20000HrsOnOff]=0,0,-1))))
AND ((tblMasterPartList.[25000Rebuild]) In
(IIf([Forms]![frmMasterPartListwRepCalcs]![chk25000HrsOnOff] Is Null,0,

(IIf([Forms]![frmMasterPartListwRepCalcs]![chk25000HrsOnOff]=-1,-1,0))),(IIf([Forms]![frmMasterPartListwRepCalcs]![chk25000HrsOnOff]=0,0,-1))))
AND ((dbo_PART.CONSUMABLE) Not Like "Y")
AND ((dbo_PART.DETAIL_ONLY) Not Like "y"))
OR (((tblMasterPartList.ID) Is Null))
ORDER BY
tblMasterPartList.ID;

so the question is, what do i need to do different?
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

is this query with a subquery, a union query, normal? 1
Requerying Automatically 1
Question about Queries with Subqueries 1
How to use WHERE NOT EXISTS 2
Getting rid of a Having clause 1
Heavy data munching - is there a more efficient way of doing this? 8
Expression Exceeds 1024 character limit of design view, HELP!? 1
division by Zero and expression to complex 2

Top