Query just beeps when updating

S

SHIPP

I am using Access 2003. Just today a query that has been working religiously
to update a table only beeps now when I try to update a field. What does the
beep mean? I can change the query to an update query and it will update all
records. However on an individual record and manual update it simply beeps.
Please advise.
 
J

Jeanette Cunningham

Hi,
to do some troubleshooting, create a select query using the ID of the
particular record you are trying to update in the criteria row and switch to
datasheet view. See if you can spot any reason for that particular record
refusing to be updated.

Possible reasons
--a unique index that won't allow duplicate values
--incorrect data type for the value you are trying to update to for example
trying to put a text string in a numeric field
--a change in the relationship between tables since the time when it used to
work

perhaps someone else will suggest more reasons

Jeanette Cunningham
 
S

SHIPP

I appreciate the response. I wasn't clear before. It beeps on no matter which
field you are trying to update. I did create a query that gave me one record.
I didn't see anything funny. Any other ideas?
 
J

Jeanette Cunningham

Hi,
Is this query updatable?
Open the query in datasheet view and look at the navigation buttons at the
bottom.
Can you add new records to this query, or is the new record button greyed
out?
Some queries are not able to be updated, maybe this is the case with your
query.
Some examples are queries that use group by or distinct.

Jeanette Cunningham
 
S

SHIPP

No, this query was not meant to be able to add records. Here is the SQL. I
know it's long but if you see anything I would appreciate your input.

SELECT tblRoughCutHdr.RoughCutID, tblProd.SchedItem,
tblRoughCutHdr.WeekStartDate, tblProd.UPCFip13, tblRoughCutHdr.ProdID,
tblProdTemplate.TemplateDesc, tblProd.SeqNo, tblProd.ProdDesc,
tblProd.TemplateID, tblProd.DaysToCover, tblProd.Tolerance,
tblProd.MinimumOrder, tblProd.IncrementOrder, tblProd.ProdUnitsHr,
tblSchedDtl.WeekNo,
IdentifyPromo([tblSchedDtl].[SchedDate],[tblProd].[UPCFip13]) AS SunPromo,
tblSchedDtl.SchedDate AS SunDte, tblSchedDtl.ProduceIt AS SunProduceIt,
tblSchedDtl.BegInvAdj AS SunBegInvAdj, tblSchedDtl.Forecast AS SunForecast,
tblSchedDtl.ForecastType AS SunForecastType, tblSchedDtl.SalesOrdAdj AS
SunSalesOrdAdj, tblSchedDtl.SalesOthAdj AS SunSalesOthAdj, tblSchedDtl.Prod
AS SunProd, tblSchedDtl.ProdAdj AS SunProdAdj, tblSchedDtl.EndInv AS
SunEndInv, tblSchedDtl.ProdCalc AS SunProdCalc, tblSchedDtl.ErrMsg AS
SunErrMsg, [SunProdAdj]/[ProdUnitsHr] AS SunLineHrs,
IdentifyPromo([tblSchedDtl_1].[SchedDate],[tblProd].[UPCFip13]) AS MonPromo,
tblSchedDtl_1.SchedDate AS MonDte, tblSchedDtl_1.ProduceIt AS MonProduceIt,
tblSchedDtl_1.BegInvAdj AS MonBegInvAdj, tblSchedDtl_1.Forecast AS
MonForecast, tblSchedDtl_1.ForecastType AS MonForecastType,
tblSchedDtl_1.SalesOrdAdj AS MonSalesOrdAdj, tblSchedDtl_1.SalesOthAdj AS
MonSalesOthAdj, tblSchedDtl_1.Prod AS MonProd, tblSchedDtl_1.ProdAdj AS
MonProdAdj, tblSchedDtl_1.EndInv AS MonEndInv, tblSchedDtl_1.ProdCalc AS
MonProdCalc, tblSchedDtl_1.ErrMsg AS MonErrMsg, [MonProdAdj]/[ProdUnitsHr] AS
MonLineHrs, IdentifyPromo([tblSchedDtl_2].[SchedDate],[tblProd].[UPCFip13])
AS TuePromo, tblSchedDtl_2.SchedDate AS TueDte, tblSchedDtl_2.ProduceIt AS
TueProduceIt, tblSchedDtl_2.BegInvAdj AS TueBegInvAdj, tblSchedDtl_2.Forecast
AS TueForecast, tblSchedDtl_2.ForecastType AS TueForecastType,
tblSchedDtl_2.SalesOrdAdj AS TueSalesOrdAdj, tblSchedDtl_2.SalesOthAdj AS
TueSalesOthAdj, tblSchedDtl_2.Prod AS TueProd, tblSchedDtl_2.ProdAdj AS
TueProdAdj, tblSchedDtl_2.EndInv AS TueEndInv, tblSchedDtl_2.ProdCalc AS
TueProdCalc, tblSchedDtl_2.ErrMsg AS TueErrMsg, [TueProdAdj]/[ProdUnitsHr] AS
TueLineHrs, IdentifyPromo([tblSchedDtl_3].[SchedDate],[tblProd].[UPCFip13])
AS WedPromo, tblSchedDtl_3.SchedDate AS WedDte, tblSchedDtl_3.ProduceIt AS
WedProduceIt, tblSchedDtl_3.BegInvAdj AS WedBegInvAdj, tblSchedDtl_3.Forecast
AS WedForecast, tblSchedDtl_3.ForecastType AS WedForecastType,
tblSchedDtl_3.SalesOrdAdj AS WedSalesOrdAdj, tblSchedDtl_3.SalesOthAdj AS
WedSalesOthAdj, tblSchedDtl_3.Prod AS WedProd, tblSchedDtl_3.ProdAdj AS
WedProdAdj, tblSchedDtl_3.EndInv AS WedEndInv, tblSchedDtl_3.ProdCalc AS
WedProdCalc, tblSchedDtl_3.ErrMsg AS WedErrMsg, [WedProdAdj]/[ProdUnitsHr] AS
WedLineHrs, IdentifyPromo([tblSchedDtl_4].[SchedDate],[tblProd].[UPCFip13])
AS ThuPromo, tblSchedDtl_4.SchedDate AS ThuDte, tblSchedDtl_4.ProduceIt AS
ThuProduceIt, tblSchedDtl_4.BegInvAdj AS ThuBegInvAdj, tblSchedDtl_4.Forecast
AS ThuForecast, tblSchedDtl_4.ForecastType AS ThuForecastType,
tblSchedDtl_4.SalesOrdAdj AS ThuSalesOrdAdj, tblSchedDtl_4.SalesOthAdj AS
ThuSalesOthAdj, tblSchedDtl_4.Prod AS ThuProd, tblSchedDtl_4.ProdAdj AS
ThuProdAdj, tblSchedDtl_4.EndInv AS ThuEndInv, tblSchedDtl_4.ProdCalc AS
ThuProdCalc, tblSchedDtl_4.ErrMsg AS ThuErrMsg, [ThuProdAdj]/[ProdUnitsHr] AS
ThuLineHrs, IdentifyPromo([tblSchedDtl_5].[SchedDate],[tblProd].[UPCFip13])
AS FriPromo, tblSchedDtl_5.SchedDate AS FriDte, tblSchedDtl_5.ProduceIt AS
FriProduceIt, tblSchedDtl_5.BegInvAdj AS FriBegInvAdj, tblSchedDtl_5.Forecast
AS FriForecast, tblSchedDtl_5.ForecastType AS FriForecastType,
tblSchedDtl_5.SalesOrdAdj AS FriSalesOrdAdj, tblSchedDtl_5.SalesOthAdj AS
FriSalesOthAdj, tblSchedDtl_5.Prod AS FriProd, tblSchedDtl_5.ProdAdj AS
FriProdAdj, tblSchedDtl_5.EndInv AS FriEndInv, tblSchedDtl_5.ProdCalc AS
FriProdCalc, tblSchedDtl_5.ErrMsg AS FriErrMsg, [FriProdAdj]/[ProdUnitsHr] AS
FriLineHrs, IdentifyPromo([tblSchedDtl_6].[SchedDate],[tblProd].[UPCFip13])
AS SatPromo, tblSchedDtl_6.SchedDate AS SatDte, tblSchedDtl_6.ProduceIt AS
SatProduceIt, tblSchedDtl_6.BegInvAdj AS SatBegInvAdj, tblSchedDtl_6.Forecast
AS SatForecast, tblSchedDtl_6.ForecastType AS SatForecastType,
tblSchedDtl_6.SalesOrdAdj AS SatSalesOrdAdj, tblSchedDtl_6.SalesOthAdj AS
SatSalesOthAdj, tblSchedDtl_6.Prod AS SatProd, tblSchedDtl_6.ProdAdj AS
SatProdAdj, tblSchedDtl_6.EndInv AS SatEndInv, tblSchedDtl_6.ProdCalc AS
SatProdCalc, tblSchedDtl_6.ErrMsg AS SatErrMsg, [SatProdAdj]/[ProdUnitsHr] AS
SatLineHrs, tblProd.Curd,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl].[ProdAdj]*[FillQty],0) AS SunLgCurd,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl_1].[ProdAdj]*[FillQty],0) AS MonLgCurd,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl_2].[ProdAdj]*[FillQty],0) AS TueLgCurd,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl_3].[ProdAdj]*[FillQty],0) AS WedLgCurd,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl_4].[ProdAdj]*[FillQty],0) AS ThuLgCurd,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl_5].[ProdAdj]*[FillQty],0) AS FriLgCurd,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl_6].[ProdAdj]*[FillQty],0) AS SatLgCurd,
IIf([tblProd].[Curd]=No,[tblSchedDtl].[ProdAdj]*[FillQty],0) AS SunSmCurd,
IIf([tblProd].[Curd]=No,[tblSchedDtl_1].[ProdAdj]*[FillQty],0) AS MonSmCurd,
IIf([tblProd].[Curd]=No,[tblSchedDtl_2].[ProdAdj]*[FillQty],0) AS TueSmCurd,
IIf([tblProd].[Curd]=No,[tblSchedDtl_3].[ProdAdj]*[FillQty],0) AS WedSmCurd,
IIf([tblProd].[Curd]=No,[tblSchedDtl_4].[ProdAdj]*[FillQty],0) AS ThuSmCurd,
IIf([tblProd].[Curd]=No,[tblSchedDtl_5].[ProdAdj]*[FillQty],0) AS FriSmCurd,
IIf([tblProd].[Curd]=No,[tblSchedDtl_6].[ProdAdj]*[FillQty],0) AS SatSmCurd,
tblSchedDtl.ForecastFip,
IIf([tblProd].[Curd]=No,[tblSchedDtl].[ForecastFip]*[FillQty],0) AS
FipFcstSmLbs,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl].[ForecastFip]*[FillQty],0) AS
FipFcstLgLbs, tblProd.TypeProd
FROM (tblSchedDtl AS tblSchedDtl_6 INNER JOIN (tblSchedDtl AS tblSchedDtl_5
INNER JOIN (tblSchedDtl AS tblSchedDtl_4 INNER JOIN (tblSchedDtl AS
tblSchedDtl_3 INNER JOIN ((((tblRoughCutHdr INNER JOIN tblSchedDtl ON
tblRoughCutHdr.SunID = tblSchedDtl.SchedDtlID) INNER JOIN tblSchedDtl AS
tblSchedDtl_1 ON tblRoughCutHdr.MonID = tblSchedDtl_1.SchedDtlID) INNER JOIN
tblProd ON tblRoughCutHdr.ProdID = tblProd.ProdID) INNER JOIN tblSchedDtl AS
tblSchedDtl_2 ON tblRoughCutHdr.TueID = tblSchedDtl_2.SchedDtlID) ON
tblSchedDtl_3.SchedDtlID = tblRoughCutHdr.WedID) ON tblSchedDtl_4.SchedDtlID
= tblRoughCutHdr.ThuID) ON tblSchedDtl_5.SchedDtlID = tblRoughCutHdr.FriID)
ON tblSchedDtl_6.SchedDtlID = tblRoughCutHdr.SatID) INNER JOIN
tblProdTemplate ON tblProd.TemplateID = tblProdTemplate.TemplateID
WHERE (((tblRoughCutHdr.RoughCutID)=10004) AND ((tblProd.SchedItem)=-1) AND
((tblRoughCutHdr.WeekStartDate)>=DateAdd("d",-Weekday(Date())+1-7,Date())))
ORDER BY tblProdTemplate.TemplateDesc, tblProd.SeqNo;
 
J

Jeanette Cunningham

Hi Shipp,
Yes this is quite a long query, I would have thought that a query with
aliases would be for viewing and not be updateable.

Perhaps someone with more expertise on queries can help you with this query.

Jeanette Cunningham




SHIPP said:
No, this query was not meant to be able to add records. Here is the SQL. I
know it's long but if you see anything I would appreciate your input.

SELECT tblRoughCutHdr.RoughCutID, tblProd.SchedItem,
tblRoughCutHdr.WeekStartDate, tblProd.UPCFip13, tblRoughCutHdr.ProdID,
tblProdTemplate.TemplateDesc, tblProd.SeqNo, tblProd.ProdDesc,
tblProd.TemplateID, tblProd.DaysToCover, tblProd.Tolerance,
tblProd.MinimumOrder, tblProd.IncrementOrder, tblProd.ProdUnitsHr,
tblSchedDtl.WeekNo,
IdentifyPromo([tblSchedDtl].[SchedDate],[tblProd].[UPCFip13]) AS SunPromo,
tblSchedDtl.SchedDate AS SunDte, tblSchedDtl.ProduceIt AS SunProduceIt,
tblSchedDtl.BegInvAdj AS SunBegInvAdj, tblSchedDtl.Forecast AS
SunForecast,
tblSchedDtl.ForecastType AS SunForecastType, tblSchedDtl.SalesOrdAdj AS
SunSalesOrdAdj, tblSchedDtl.SalesOthAdj AS SunSalesOthAdj,
tblSchedDtl.Prod
AS SunProd, tblSchedDtl.ProdAdj AS SunProdAdj, tblSchedDtl.EndInv AS
SunEndInv, tblSchedDtl.ProdCalc AS SunProdCalc, tblSchedDtl.ErrMsg AS
SunErrMsg, [SunProdAdj]/[ProdUnitsHr] AS SunLineHrs,
IdentifyPromo([tblSchedDtl_1].[SchedDate],[tblProd].[UPCFip13]) AS
MonPromo,
tblSchedDtl_1.SchedDate AS MonDte, tblSchedDtl_1.ProduceIt AS
MonProduceIt,
tblSchedDtl_1.BegInvAdj AS MonBegInvAdj, tblSchedDtl_1.Forecast AS
MonForecast, tblSchedDtl_1.ForecastType AS MonForecastType,
tblSchedDtl_1.SalesOrdAdj AS MonSalesOrdAdj, tblSchedDtl_1.SalesOthAdj AS
MonSalesOthAdj, tblSchedDtl_1.Prod AS MonProd, tblSchedDtl_1.ProdAdj AS
MonProdAdj, tblSchedDtl_1.EndInv AS MonEndInv, tblSchedDtl_1.ProdCalc AS
MonProdCalc, tblSchedDtl_1.ErrMsg AS MonErrMsg, [MonProdAdj]/[ProdUnitsHr]
AS
MonLineHrs,
IdentifyPromo([tblSchedDtl_2].[SchedDate],[tblProd].[UPCFip13])
AS TuePromo, tblSchedDtl_2.SchedDate AS TueDte, tblSchedDtl_2.ProduceIt AS
TueProduceIt, tblSchedDtl_2.BegInvAdj AS TueBegInvAdj,
tblSchedDtl_2.Forecast
AS TueForecast, tblSchedDtl_2.ForecastType AS TueForecastType,
tblSchedDtl_2.SalesOrdAdj AS TueSalesOrdAdj, tblSchedDtl_2.SalesOthAdj AS
TueSalesOthAdj, tblSchedDtl_2.Prod AS TueProd, tblSchedDtl_2.ProdAdj AS
TueProdAdj, tblSchedDtl_2.EndInv AS TueEndInv, tblSchedDtl_2.ProdCalc AS
TueProdCalc, tblSchedDtl_2.ErrMsg AS TueErrMsg, [TueProdAdj]/[ProdUnitsHr]
AS
TueLineHrs,
IdentifyPromo([tblSchedDtl_3].[SchedDate],[tblProd].[UPCFip13])
AS WedPromo, tblSchedDtl_3.SchedDate AS WedDte, tblSchedDtl_3.ProduceIt AS
WedProduceIt, tblSchedDtl_3.BegInvAdj AS WedBegInvAdj,
tblSchedDtl_3.Forecast
AS WedForecast, tblSchedDtl_3.ForecastType AS WedForecastType,
tblSchedDtl_3.SalesOrdAdj AS WedSalesOrdAdj, tblSchedDtl_3.SalesOthAdj AS
WedSalesOthAdj, tblSchedDtl_3.Prod AS WedProd, tblSchedDtl_3.ProdAdj AS
WedProdAdj, tblSchedDtl_3.EndInv AS WedEndInv, tblSchedDtl_3.ProdCalc AS
WedProdCalc, tblSchedDtl_3.ErrMsg AS WedErrMsg, [WedProdAdj]/[ProdUnitsHr]
AS
WedLineHrs,
IdentifyPromo([tblSchedDtl_4].[SchedDate],[tblProd].[UPCFip13])
AS ThuPromo, tblSchedDtl_4.SchedDate AS ThuDte, tblSchedDtl_4.ProduceIt AS
ThuProduceIt, tblSchedDtl_4.BegInvAdj AS ThuBegInvAdj,
tblSchedDtl_4.Forecast
AS ThuForecast, tblSchedDtl_4.ForecastType AS ThuForecastType,
tblSchedDtl_4.SalesOrdAdj AS ThuSalesOrdAdj, tblSchedDtl_4.SalesOthAdj AS
ThuSalesOthAdj, tblSchedDtl_4.Prod AS ThuProd, tblSchedDtl_4.ProdAdj AS
ThuProdAdj, tblSchedDtl_4.EndInv AS ThuEndInv, tblSchedDtl_4.ProdCalc AS
ThuProdCalc, tblSchedDtl_4.ErrMsg AS ThuErrMsg, [ThuProdAdj]/[ProdUnitsHr]
AS
ThuLineHrs,
IdentifyPromo([tblSchedDtl_5].[SchedDate],[tblProd].[UPCFip13])
AS FriPromo, tblSchedDtl_5.SchedDate AS FriDte, tblSchedDtl_5.ProduceIt AS
FriProduceIt, tblSchedDtl_5.BegInvAdj AS FriBegInvAdj,
tblSchedDtl_5.Forecast
AS FriForecast, tblSchedDtl_5.ForecastType AS FriForecastType,
tblSchedDtl_5.SalesOrdAdj AS FriSalesOrdAdj, tblSchedDtl_5.SalesOthAdj AS
FriSalesOthAdj, tblSchedDtl_5.Prod AS FriProd, tblSchedDtl_5.ProdAdj AS
FriProdAdj, tblSchedDtl_5.EndInv AS FriEndInv, tblSchedDtl_5.ProdCalc AS
FriProdCalc, tblSchedDtl_5.ErrMsg AS FriErrMsg, [FriProdAdj]/[ProdUnitsHr]
AS
FriLineHrs,
IdentifyPromo([tblSchedDtl_6].[SchedDate],[tblProd].[UPCFip13])
AS SatPromo, tblSchedDtl_6.SchedDate AS SatDte, tblSchedDtl_6.ProduceIt AS
SatProduceIt, tblSchedDtl_6.BegInvAdj AS SatBegInvAdj,
tblSchedDtl_6.Forecast
AS SatForecast, tblSchedDtl_6.ForecastType AS SatForecastType,
tblSchedDtl_6.SalesOrdAdj AS SatSalesOrdAdj, tblSchedDtl_6.SalesOthAdj AS
SatSalesOthAdj, tblSchedDtl_6.Prod AS SatProd, tblSchedDtl_6.ProdAdj AS
SatProdAdj, tblSchedDtl_6.EndInv AS SatEndInv, tblSchedDtl_6.ProdCalc AS
SatProdCalc, tblSchedDtl_6.ErrMsg AS SatErrMsg, [SatProdAdj]/[ProdUnitsHr]
AS
SatLineHrs, tblProd.Curd,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl].[ProdAdj]*[FillQty],0) AS
SunLgCurd,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl_1].[ProdAdj]*[FillQty],0) AS
MonLgCurd,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl_2].[ProdAdj]*[FillQty],0) AS
TueLgCurd,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl_3].[ProdAdj]*[FillQty],0) AS
WedLgCurd,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl_4].[ProdAdj]*[FillQty],0) AS
ThuLgCurd,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl_5].[ProdAdj]*[FillQty],0) AS
FriLgCurd,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl_6].[ProdAdj]*[FillQty],0) AS
SatLgCurd,
IIf([tblProd].[Curd]=No,[tblSchedDtl].[ProdAdj]*[FillQty],0) AS SunSmCurd,
IIf([tblProd].[Curd]=No,[tblSchedDtl_1].[ProdAdj]*[FillQty],0) AS
MonSmCurd,
IIf([tblProd].[Curd]=No,[tblSchedDtl_2].[ProdAdj]*[FillQty],0) AS
TueSmCurd,
IIf([tblProd].[Curd]=No,[tblSchedDtl_3].[ProdAdj]*[FillQty],0) AS
WedSmCurd,
IIf([tblProd].[Curd]=No,[tblSchedDtl_4].[ProdAdj]*[FillQty],0) AS
ThuSmCurd,
IIf([tblProd].[Curd]=No,[tblSchedDtl_5].[ProdAdj]*[FillQty],0) AS
FriSmCurd,
IIf([tblProd].[Curd]=No,[tblSchedDtl_6].[ProdAdj]*[FillQty],0) AS
SatSmCurd,
tblSchedDtl.ForecastFip,
IIf([tblProd].[Curd]=No,[tblSchedDtl].[ForecastFip]*[FillQty],0) AS
FipFcstSmLbs,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl].[ForecastFip]*[FillQty],0) AS
FipFcstLgLbs, tblProd.TypeProd
FROM (tblSchedDtl AS tblSchedDtl_6 INNER JOIN (tblSchedDtl AS
tblSchedDtl_5
INNER JOIN (tblSchedDtl AS tblSchedDtl_4 INNER JOIN (tblSchedDtl AS
tblSchedDtl_3 INNER JOIN ((((tblRoughCutHdr INNER JOIN tblSchedDtl ON
tblRoughCutHdr.SunID = tblSchedDtl.SchedDtlID) INNER JOIN tblSchedDtl AS
tblSchedDtl_1 ON tblRoughCutHdr.MonID = tblSchedDtl_1.SchedDtlID) INNER
JOIN
tblProd ON tblRoughCutHdr.ProdID = tblProd.ProdID) INNER JOIN tblSchedDtl
AS
tblSchedDtl_2 ON tblRoughCutHdr.TueID = tblSchedDtl_2.SchedDtlID) ON
tblSchedDtl_3.SchedDtlID = tblRoughCutHdr.WedID) ON
tblSchedDtl_4.SchedDtlID
= tblRoughCutHdr.ThuID) ON tblSchedDtl_5.SchedDtlID =
tblRoughCutHdr.FriID)
ON tblSchedDtl_6.SchedDtlID = tblRoughCutHdr.SatID) INNER JOIN
tblProdTemplate ON tblProd.TemplateID = tblProdTemplate.TemplateID
WHERE (((tblRoughCutHdr.RoughCutID)=10004) AND ((tblProd.SchedItem)=-1)
AND
((tblRoughCutHdr.WeekStartDate)>=DateAdd("d",-Weekday(Date())+1-7,Date())))
ORDER BY tblProdTemplate.TemplateDesc, tblProd.SeqNo;

--
M. Shipp


Jeanette Cunningham said:
Hi,
Is this query updatable?
Open the query in datasheet view and look at the navigation buttons at
the
bottom.
Can you add new records to this query, or is the new record button greyed
out?
Some queries are not able to be updated, maybe this is the case with your
query.
Some examples are queries that use group by or distinct.

Jeanette Cunningham
Click to expand...
Click to expand...
 
R

Ron2006

Hi Shipp,
Yes this is quite a long query, I would have thought that a query with
aliases would be for viewing and not be updateable.

Perhaps someone with more expertise on queries can help you with this query.

Jeanette Cunningham




No, this query was not meant to be able to add records. Here is the SQL.I
know it's long but if you see anything I would appreciate your input.
Click to expand...
SELECT tblRoughCutHdr.RoughCutID, tblProd.SchedItem,
tblRoughCutHdr.WeekStartDate, tblProd.UPCFip13, tblRoughCutHdr.ProdID,
tblProdTemplate.TemplateDesc, tblProd.SeqNo, tblProd.ProdDesc,
tblProd.TemplateID, tblProd.DaysToCover, tblProd.Tolerance,
tblProd.MinimumOrder, tblProd.IncrementOrder, tblProd.ProdUnitsHr,
tblSchedDtl.WeekNo,
IdentifyPromo([tblSchedDtl].[SchedDate],[tblProd].[UPCFip13]) AS SunPromo,
tblSchedDtl.SchedDate AS SunDte, tblSchedDtl.ProduceIt AS SunProduceIt,
tblSchedDtl.BegInvAdj AS SunBegInvAdj, tblSchedDtl.Forecast AS
SunForecast,
tblSchedDtl.ForecastType AS SunForecastType, tblSchedDtl.SalesOrdAdj AS
SunSalesOrdAdj, tblSchedDtl.SalesOthAdj AS SunSalesOthAdj,
tblSchedDtl.Prod
AS SunProd, tblSchedDtl.ProdAdj AS SunProdAdj, tblSchedDtl.EndInv AS
SunEndInv, tblSchedDtl.ProdCalc AS SunProdCalc, tblSchedDtl.ErrMsg AS
SunErrMsg, [SunProdAdj]/[ProdUnitsHr] AS SunLineHrs,
IdentifyPromo([tblSchedDtl_1].[SchedDate],[tblProd].[UPCFip13]) AS
MonPromo,
tblSchedDtl_1.SchedDate AS MonDte, tblSchedDtl_1.ProduceIt AS
MonProduceIt,
tblSchedDtl_1.BegInvAdj AS MonBegInvAdj, tblSchedDtl_1.Forecast AS
MonForecast, tblSchedDtl_1.ForecastType AS MonForecastType,
tblSchedDtl_1.SalesOrdAdj AS MonSalesOrdAdj, tblSchedDtl_1.SalesOthAdj AS
MonSalesOthAdj, tblSchedDtl_1.Prod AS MonProd, tblSchedDtl_1.ProdAdj AS
MonProdAdj, tblSchedDtl_1.EndInv AS MonEndInv, tblSchedDtl_1.ProdCalc AS
MonProdCalc, tblSchedDtl_1.ErrMsg AS MonErrMsg, [MonProdAdj]/[ProdUnitsHr]
AS
MonLineHrs,
IdentifyPromo([tblSchedDtl_2].[SchedDate],[tblProd].[UPCFip13])
AS TuePromo, tblSchedDtl_2.SchedDate AS TueDte, tblSchedDtl_2.ProduceIt AS
TueProduceIt, tblSchedDtl_2.BegInvAdj AS TueBegInvAdj,
tblSchedDtl_2.Forecast
AS TueForecast, tblSchedDtl_2.ForecastType AS TueForecastType,
tblSchedDtl_2.SalesOrdAdj AS TueSalesOrdAdj, tblSchedDtl_2.SalesOthAdj AS
TueSalesOthAdj, tblSchedDtl_2.Prod AS TueProd, tblSchedDtl_2.ProdAdj AS
TueProdAdj, tblSchedDtl_2.EndInv AS TueEndInv, tblSchedDtl_2.ProdCalc AS
TueProdCalc, tblSchedDtl_2.ErrMsg AS TueErrMsg, [TueProdAdj]/[ProdUnitsHr]
AS
TueLineHrs,
IdentifyPromo([tblSchedDtl_3].[SchedDate],[tblProd].[UPCFip13])
AS WedPromo, tblSchedDtl_3.SchedDate AS WedDte, tblSchedDtl_3.ProduceIt AS
WedProduceIt, tblSchedDtl_3.BegInvAdj AS WedBegInvAdj,
tblSchedDtl_3.Forecast
AS WedForecast, tblSchedDtl_3.ForecastType AS WedForecastType,
tblSchedDtl_3.SalesOrdAdj AS WedSalesOrdAdj, tblSchedDtl_3.SalesOthAdj AS
WedSalesOthAdj, tblSchedDtl_3.Prod AS WedProd, tblSchedDtl_3.ProdAdj AS
WedProdAdj, tblSchedDtl_3.EndInv AS WedEndInv, tblSchedDtl_3.ProdCalc AS
WedProdCalc, tblSchedDtl_3.ErrMsg AS WedErrMsg, [WedProdAdj]/[ProdUnitsHr]
AS
WedLineHrs,
IdentifyPromo([tblSchedDtl_4].[SchedDate],[tblProd].[UPCFip13])
AS ThuPromo, tblSchedDtl_4.SchedDate AS ThuDte, tblSchedDtl_4.ProduceIt AS
ThuProduceIt, tblSchedDtl_4.BegInvAdj AS ThuBegInvAdj,
tblSchedDtl_4.Forecast
AS ThuForecast, tblSchedDtl_4.ForecastType AS ThuForecastType,
tblSchedDtl_4.SalesOrdAdj AS ThuSalesOrdAdj, tblSchedDtl_4.SalesOthAdj AS
ThuSalesOthAdj, tblSchedDtl_4.Prod AS ThuProd, tblSchedDtl_4.ProdAdj AS
ThuProdAdj, tblSchedDtl_4.EndInv AS ThuEndInv, tblSchedDtl_4.ProdCalc AS
ThuProdCalc, tblSchedDtl_4.ErrMsg AS ThuErrMsg, [ThuProdAdj]/[ProdUnitsHr]
AS
ThuLineHrs,
IdentifyPromo([tblSchedDtl_5].[SchedDate],[tblProd].[UPCFip13])
AS FriPromo, tblSchedDtl_5.SchedDate AS FriDte, tblSchedDtl_5.ProduceIt AS
FriProduceIt, tblSchedDtl_5.BegInvAdj AS FriBegInvAdj,
tblSchedDtl_5.Forecast
AS FriForecast, tblSchedDtl_5.ForecastType AS FriForecastType,
tblSchedDtl_5.SalesOrdAdj AS FriSalesOrdAdj, tblSchedDtl_5.SalesOthAdj AS
FriSalesOthAdj, tblSchedDtl_5.Prod AS FriProd, tblSchedDtl_5.ProdAdj AS
FriProdAdj, tblSchedDtl_5.EndInv AS FriEndInv, tblSchedDtl_5.ProdCalc AS
FriProdCalc, tblSchedDtl_5.ErrMsg AS FriErrMsg, [FriProdAdj]/[ProdUnitsHr]
AS
FriLineHrs,
IdentifyPromo([tblSchedDtl_6].[SchedDate],[tblProd].[UPCFip13])
AS SatPromo, tblSchedDtl_6.SchedDate AS SatDte, tblSchedDtl_6.ProduceIt AS
SatProduceIt, tblSchedDtl_6.BegInvAdj AS SatBegInvAdj,
tblSchedDtl_6.Forecast
AS SatForecast, tblSchedDtl_6.ForecastType AS SatForecastType,
tblSchedDtl_6.SalesOrdAdj AS SatSalesOrdAdj, tblSchedDtl_6.SalesOthAdj AS
SatSalesOthAdj, tblSchedDtl_6.Prod AS SatProd, tblSchedDtl_6.ProdAdj AS
SatProdAdj, tblSchedDtl_6.EndInv AS SatEndInv, tblSchedDtl_6.ProdCalc AS
SatProdCalc, tblSchedDtl_6.ErrMsg AS SatErrMsg, [SatProdAdj]/[ProdUnitsHr]
AS
SatLineHrs, tblProd.Curd,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl].[ProdAdj]*[FillQty],0) AS
SunLgCurd,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl_1].[ProdAdj]*[FillQty],0) AS
MonLgCurd,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl_2].[ProdAdj]*[FillQty],0) AS
TueLgCurd,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl_3].[ProdAdj]*[FillQty],0) AS
WedLgCurd,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl_4].[ProdAdj]*[FillQty],0) AS
ThuLgCurd,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl_5].[ProdAdj]*[FillQty],0) AS
FriLgCurd,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl_6].[ProdAdj]*[FillQty],0) AS
SatLgCurd,
IIf([tblProd].[Curd]=No,[tblSchedDtl].[ProdAdj]*[FillQty],0) AS SunSmCurd,
IIf([tblProd].[Curd]=No,[tblSchedDtl_1].[ProdAdj]*[FillQty],0) AS
MonSmCurd,
IIf([tblProd].[Curd]=No,[tblSchedDtl_2].[ProdAdj]*[FillQty],0) AS
TueSmCurd,
IIf([tblProd].[Curd]=No,[tblSchedDtl_3].[ProdAdj]*[FillQty],0) AS
WedSmCurd,
IIf([tblProd].[Curd]=No,[tblSchedDtl_4].[ProdAdj]*[FillQty],0) AS
ThuSmCurd,
IIf([tblProd].[Curd]=No,[tblSchedDtl_5].[ProdAdj]*[FillQty],0) AS
FriSmCurd,
IIf([tblProd].[Curd]=No,[tblSchedDtl_6].[ProdAdj]*[FillQty],0) AS
SatSmCurd,
tblSchedDtl.ForecastFip,
IIf([tblProd].[Curd]=No,[tblSchedDtl].[ForecastFip]*[FillQty],0) AS
FipFcstSmLbs,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl].[ForecastFip]*[FillQty],0) AS
FipFcstLgLbs, tblProd.TypeProd
FROM (tblSchedDtl AS tblSchedDtl_6 INNER JOIN (tblSchedDtl AS
tblSchedDtl_5
INNER JOIN (tblSchedDtl AS tblSchedDtl_4 INNER JOIN (tblSchedDtl AS
tblSchedDtl_3 INNER JOIN ((((tblRoughCutHdr INNER JOIN tblSchedDtl ON
tblRoughCutHdr.SunID = tblSchedDtl.SchedDtlID) INNER JOIN tblSchedDtl AS
tblSchedDtl_1 ON tblRoughCutHdr.MonID = tblSchedDtl_1.SchedDtlID) INNER
JOIN
tblProd ON tblRoughCutHdr.ProdID = tblProd.ProdID) INNER JOIN tblSchedDtl
AS
tblSchedDtl_2 ON tblRoughCutHdr.TueID = tblSchedDtl_2.SchedDtlID) ON
tblSchedDtl_3.SchedDtlID = tblRoughCutHdr.WedID) ON
tblSchedDtl_4.SchedDtlID
= tblRoughCutHdr.ThuID) ON tblSchedDtl_5.SchedDtlID =
tblRoughCutHdr.FriID)
ON tblSchedDtl_6.SchedDtlID = tblRoughCutHdr.SatID) INNER JOIN
tblProdTemplate ON tblProd.TemplateID = tblProdTemplate.TemplateID
WHERE (((tblRoughCutHdr.RoughCutID)=10004) AND ((tblProd.SchedItem)=-1)
AND
((tblRoughCutHdr.WeekStartDate)>=DateAdd("d",-Weekday(Date())+1-7,Date())))
ORDER BY tblProdTemplate.TemplateDesc, tblProd.SeqNo;
Click to expand...
"Jeanette Cunningham" wrote:
Click to expand...

- Show quoted text -
Click to expand...

Are your warnings set off. If they are, set them on and then run the
query to see if it will give you a reason.
 
R

Ron2006

Have you tried running the query with the setwarnings flag set OFF?

Maybe then it will tell you why it is NOT updating the record.

Ron
 
R

Ron2006

Have you tried running the query with the setwarnings flag set OFF?

Maybe then it will tell you why it is NOT updating the record.

Ron
Click to expand...

I just read what I wrote and what you want is to set warnings flags ON
not off.

Sorry

Ron
 

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

Query beeps when entering value in field 3
Recordset not updateable 3
Change data in a query, locked? 5
Update primary key field with multiple "where" clauses 0
Changing data from source tables using queries? 8
Update Query - Convert % to Number Format 4
I can't edit a field's value in a query 2
Select, update query--same query, different result 7

Top