The Recordset is Not Updateable

J

jeanhurtado

Hi, I'm Jean writing about the weird issue. I have 2 tables one is
called "CostTable" and the other one "CycleCount". I have create a
query called "Cycle Count Report", this query is created because I
need to join the 2 tables above mentioned. The query has several
caculated fields and one has a filter "Ticket Number" to only shows
records wich contains not null values. I have a form "Cycle Count
Entry System" that is use is for Data Entry porpouses when I try to
enter data in the form the error comes "The Recordset is Not
Updateable ". I have the query SQL code if you need it. Can you help
me I have try searching in internet but no so clear to me. Thanks for
yours efforts to help me.


Sql code of the query:
***********************************************************************************************************************************

SELECT CycleCount.[Ticket Number], CycleCount.[Part Number],
COSTTABLE.MAT1000, COSTTABLE.LAB1000, ([COSTTABLE]![LAB1000]+
[COSTTABLE]![MAT1000])/1000 AS [UNIT COST], CycleCount.ONHAND,
CycleCount.AVAILABLE, CycleCount.[RECOUNT QTY], IIf([RECOUNT
QTY]<=[ONHAND] And [RECOUNT QTY]>=[AVAILABLE],0,IIf([RECOUNT
QTY]>[ONHAND],[RECOUNT QTY]-[ONHAND],[RECOUNT QTY]-[AVAILABLE])) AS
VAR, IIf([ONHAND]=0,0,[VAR]/[ONHAND]) AS [VAR %], ([VAR]*[UNIT COST])
AS [VAR $], IIf(Abs([VAR %])>0.0075 Or Abs([VAR $])>50,"True","False")
AS ADJUSTMENT, ([RECOUNT QTY]*[UNIT COST]) AS [RECOUNT $], IIf([VAR
$]>=0 And [ADJUSTMENT]="True",[VAR $],0) AS [PLUS ADJ $], IIf([VAR
$]<0 And [ADJUSTMENT]="True",[VAR $],0) AS [MINUS ADJ $],
CycleCount.TICKETDATE, CycleCount.[UNIT OF MEASURE], CycleCount.ABC
FROM COSTTABLE INNER JOIN CycleCount ON COSTTABLE.[Part Number] =
CycleCount.[Part Number]
WHERE (((([CycleCount].[Ticket Number])=[Ticket Number]) Is Not Null))
ORDER BY CycleCount.[Ticket Number];

***********************************************************************************************************************************

If you need more info just let me know. Thanks

JC
 
A

Allen Browne

You joined COSTTABLE to CycleCount on the [Part Number] field.

In one of those tables, the Part Number must have a unique index (or be the
primary key.)

Without that index, the query will not be updatable.

Other possible reasons here:
Why is my query read-only?
at:
http://allenbrowne.com/ser-61.html
though I don't see other issues that apply.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi, I'm Jean writing about the weird issue. I have 2 tables one is
called "CostTable" and the other one "CycleCount". I have create a
query called "Cycle Count Report", this query is created because I
need to join the 2 tables above mentioned. The query has several
caculated fields and one has a filter "Ticket Number" to only shows
records wich contains not null values. I have a form "Cycle Count
Entry System" that is use is for Data Entry porpouses when I try to
enter data in the form the error comes "The Recordset is Not
Updateable ". I have the query SQL code if you need it. Can you help
me I have try searching in internet but no so clear to me. Thanks for
yours efforts to help me.


Sql code of the query:
***********************************************************************************************************************************
Click to expand...
SELECT CycleCount.[Ticket Number],
CycleCount.[Part Number],
COSTTABLE.MAT1000,
COSTTABLE.LAB1000,
([COSTTABLE]![LAB1000]+[COSTTABLE]![MAT1000])/1000 AS [UNIT COST],
CycleCount.ONHAND,
CycleCount.AVAILABLE,
CycleCount.[RECOUNT QTY],
IIf([RECOUNT QTY]<=[ONHAND] And [RECOUNT QTY]>=[AVAILABLE],0,
IIf([RECOUNT QTY]>[ONHAND],[RECOUNT QTY]-[ONHAND],
[RECOUNT QTY]-[AVAILABLE])) AS VAR,
IIf([ONHAND]=0,0,[VAR]/[ONHAND]) AS [VAR %],
([VAR]*[UNIT COST]) AS [VAR $],
IIf(Abs([VAR %])>0.0075 Or Abs([VAR $])>50,
"True","False") AS ADJUSTMENT,
([RECOUNT QTY]*[UNIT COST]) AS [RECOUNT $],
IIf([VAR $]>=0 And [ADJUSTMENT]="True",[VAR $],0) AS [PLUS ADJ $],
IIf([VAR $]<0 And [ADJUSTMENT]="True",[VAR $],0) AS [MINUS ADJ $],
CycleCount.TICKETDATE,
CycleCount.[UNIT OF MEASURE],
CycleCount.ABC
FROM COSTTABLE INNER JOIN CycleCount
ON COSTTABLE.[Part Number] = CycleCount.[Part Number]
WHERE (((([CycleCount].[Ticket Number])=[Ticket Number]) Is Not Null))
ORDER BY CycleCount.[Ticket Number];
 
J

jeanhurtado

You joined COSTTABLE to CycleCount on the [Part Number] field.

In one of those tables, the Part Number must have a unique index (or be the
primary key.)

Without that index, the query will not be updatable.

Other possible reasons here:
Why is my query read-only?
at:
http://allenbrowne.com/ser-61.html
though I don't see other issues that apply.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


called "CostTable" and the other one "CycleCount". I have create a
query called "Cycle Count Report", this query is created because I
need to join the 2 tables above mentioned. The query has several
caculated fields and one has a filter "Ticket Number" to only shows
records wich contains not null values. I have a form "Cycle Count
Entry System" that is use is for Data Entry porpouses when I try to
enter data in the form the error comes "The Recordset is Not
Updateable ". I have the query SQL code if you need it. Can you help
me I have try searching in internet but no so clear to me. Thanks for
yours efforts to help me.
Click to expand...
Sql code of the query:
***************************************************************************­********************************************************
Click to expand...

SELECT CycleCount.[Ticket Number],
CycleCount.[Part Number],
COSTTABLE.MAT1000,
COSTTABLE.LAB1000,
([COSTTABLE]![LAB1000]+[COSTTABLE]![MAT1000])/1000 AS [UNIT COST],
CycleCount.ONHAND,
CycleCount.AVAILABLE,
CycleCount.[RECOUNT QTY],
IIf([RECOUNT QTY]<=[ONHAND] And [RECOUNT QTY]>=[AVAILABLE],0,
IIf([RECOUNT QTY]>[ONHAND],[RECOUNT QTY]-[ONHAND],
[RECOUNT QTY]-[AVAILABLE])) AS VAR,
IIf([ONHAND]=0,0,[VAR]/[ONHAND]) AS [VAR %],
([VAR]*[UNIT COST]) AS [VAR $],
IIf(Abs([VAR %])>0.0075 Or Abs([VAR $])>50,
"True","False") AS ADJUSTMENT,
([RECOUNT QTY]*[UNIT COST]) AS [RECOUNT $],
IIf([VAR $]>=0 And [ADJUSTMENT]="True",[VAR $],0) AS [PLUS ADJ $],
IIf([VAR $]<0 And [ADJUSTMENT]="True",[VAR $],0) AS [MINUS ADJ $],
CycleCount.TICKETDATE,
CycleCount.[UNIT OF MEASURE],
CycleCount.ABC
FROM COSTTABLE INNER JOIN CycleCount
ON COSTTABLE.[Part Number] = CycleCount.[Part Number]
WHERE (((([CycleCount].[Ticket Number])=[Ticket Number]) Is Not Null))
ORDER BY CycleCount.[Ticket Number];


***************************************************************************­********************************************************
Click to expand...
If you need more info just let me know. Thanks
Click to expand...
JC- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks Mr. Browne. I resolved this issue. I have similar problem with
the form that it will be use for the data entry the form is called
"Cycle Count Entry System". When I open the form to make the data
entry it show in blank with no records and is supposed to show 3800
records. The record source are the query with the issue. When I open
the form there is nothing no records and all the fills blank. I want
to make the query updateable using the form. Can you help me?Thanks so
much.
 
A

Allen Browne

If the query shows the records, but the form does not, try setting the
form's Data Entry property to No.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

You joined COSTTABLE to CycleCount on the [Part Number] field.

In one of those tables, the Part Number must have a unique index (or be
the
primary key.)

Without that index, the query will not be updatable.

Other possible reasons here:
Why is my query read-only?
at:
http://allenbrowne.com/ser-61.html
though I don't see other issues that apply.


Hi, I'm Jean
writing about the weird issue. I have 2 tables one is
called "CostTable" and the other one "CycleCount". I have create a
query called "Cycle Count Report", this query is created because I
need to join the 2 tables above mentioned. The query has several
caculated fields and one has a filter "Ticket Number" to only shows
records wich contains not null values. I have a form "Cycle Count
Entry System" that is use is for Data Entry porpouses when I try to
enter data in the form the error comes "The Recordset is Not
Updateable ". I have the query SQL code if you need it. Can you help
me I have try searching in internet but no so clear to me. Thanks for
yours efforts to help me.
Click to expand...
Sql code of the query:
***************************************************************************­********************************************************
Click to expand...

SELECT CycleCount.[Ticket Number],
CycleCount.[Part Number],
COSTTABLE.MAT1000,
COSTTABLE.LAB1000,
([COSTTABLE]![LAB1000]+[COSTTABLE]![MAT1000])/1000 AS [UNIT COST],
CycleCount.ONHAND,
CycleCount.AVAILABLE,
CycleCount.[RECOUNT QTY],
IIf([RECOUNT QTY]<=[ONHAND] And [RECOUNT QTY]>=[AVAILABLE],0,
IIf([RECOUNT QTY]>[ONHAND],[RECOUNT QTY]-[ONHAND],
[RECOUNT QTY]-[AVAILABLE])) AS VAR,
IIf([ONHAND]=0,0,[VAR]/[ONHAND]) AS [VAR %],
([VAR]*[UNIT COST]) AS [VAR $],
IIf(Abs([VAR %])>0.0075 Or Abs([VAR $])>50,
"True","False") AS ADJUSTMENT,
([RECOUNT QTY]*[UNIT COST]) AS [RECOUNT $],
IIf([VAR $]>=0 And [ADJUSTMENT]="True",[VAR $],0) AS [PLUS ADJ $],
IIf([VAR $]<0 And [ADJUSTMENT]="True",[VAR $],0) AS [MINUS ADJ $],
CycleCount.TICKETDATE,
CycleCount.[UNIT OF MEASURE],
CycleCount.ABC
FROM COSTTABLE INNER JOIN CycleCount
ON COSTTABLE.[Part Number] = CycleCount.[Part Number]
WHERE (((([CycleCount].[Ticket Number])=[Ticket Number]) Is Not Null))
ORDER BY CycleCount.[Ticket Number];


***************************************************************************­********************************************************
Click to expand...
If you need more info just let me know. Thanks
Click to expand...
JC- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks Mr. Browne. I resolved this issue. I have similar problem with
the form that it will be use for the data entry the form is called
"Cycle Count Entry System". When I open the form to make the data
entry it show in blank with no records and is supposed to show 3800
records. The record source are the query with the issue. When I open
the form there is nothing no records and all the fills blank. I want
to make the query updateable using the form. Can you help me?Thanks so
much.
 
J

jeanhurtado

If the query shows the records, but the form does not, try setting the
form's Data Entry property to No.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


You joined COSTTABLE to CycleCount on the [Part Number] field.
Click to expand...
In one of those tables, the Part Number must have a unique index (or be
the
primary key.)
Click to expand...
Without that index, the query will not be updatable.
Click to expand...
Other possible reasons here:
Why is my query read-only?
at:
http://allenbrowne.com/ser-61.html
though I don't see other issues that apply.
Click to expand...
<[email protected]> wrote in message
Click to expand...
Hi, I'm Jean
writing about the weird issue. I have 2 tables one is
Click to expand...
SELECT CycleCount.[Ticket Number],
CycleCount.[Part Number],
COSTTABLE.MAT1000,
COSTTABLE.LAB1000,
([COSTTABLE]![LAB1000]+[COSTTABLE]![MAT1000])/1000 AS [UNIT COST],
CycleCount.ONHAND,
CycleCount.AVAILABLE,
CycleCount.[RECOUNT QTY],
IIf([RECOUNT QTY]<=[ONHAND] And [RECOUNT QTY]>=[AVAILABLE],0,
IIf([RECOUNT QTY]>[ONHAND],[RECOUNT QTY]-[ONHAND],
[RECOUNT QTY]-[AVAILABLE])) AS VAR,
IIf([ONHAND]=0,0,[VAR]/[ONHAND]) AS [VAR %],
([VAR]*[UNIT COST]) AS [VAR $],
IIf(Abs([VAR %])>0.0075 Or Abs([VAR $])>50,
"True","False") AS ADJUSTMENT,
([RECOUNT QTY]*[UNIT COST]) AS [RECOUNT $],
IIf([VAR $]>=0 And [ADJUSTMENT]="True",[VAR $],0) AS [PLUS ADJ $],
IIf([VAR $]<0 And [ADJUSTMENT]="True",[VAR $],0) AS [MINUS ADJ $],
CycleCount.TICKETDATE,
CycleCount.[UNIT OF MEASURE],
CycleCount.ABC
FROM COSTTABLE INNER JOIN CycleCount
ON COSTTABLE.[Part Number] = CycleCount.[Part Number]
WHERE (((([CycleCount].[Ticket Number])=[Ticket Number]) Is Not Null))
ORDER BY CycleCount.[Ticket Number];
***************************************************************************­­********************************************************
If you need more info just let me know. Thanks
JC- Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

Thanks Mr. Browne. I resolved this issue. I have similar problem with
the form that it will be use for the data entry the form is called
"Cycle Count Entry System". When I open the form to make the data
entry it show in blank with no records and is supposed to show 3800
records. The record source are the query with the issue. When I open
the form there is nothing no records and all the fills blank. I want
to make the query updateable using the form. Can you help me?Thanks so
much.- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks you so much wow it works. Thanks it work fantastic. How will
imagine that settings that proterty to no it will make this work? Also
setting the part number to primary key resolve greatly the The
Recordset is Not Updateable. Thanks for all.


JC
 

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

Recordset is not updateable after adding a query 7
recordset is not updateable 4
Using IIF to count the is not null records in a table 8

Top