what's wrong with my query

C

cho

Hi,

SELECT [AC Jan No].[Jan No], [AC Fan Line].[Date], [AC Fan Line].[Model],
[AC Fan Line].[Lot No], [AC Fan Line].[OQC Function], [AC Fan Line].[OQC
Appearance], [AC Fan Line].[Shift], [AC Fan Line].[Line], [AC Fan
Line].[RoHS Status], [AC Fan Line].[OK Quantity], [AC Fan Line].[Striking],
[AC Fan Line].[Striking II], [AC Fan Line].[BB Noise], [AC Fan Line].[Race
Noise], [AC Fan Line].[Vibration], [AC Fan Line].[Current Wave], [AC Fan
Line].[Sensor], [AC Fan Line].[Lock Ng], [AC Fan Line].[No Rotation], [AC
Fan Line].[Slow Rotation], [AC Fan Line].[Dead Point], [AC Fan Line].[Hi Lo
Current], [AC Fan Line].[Hi Lo RPM], [AC Fan Line].[Prop Imp come out], [AC
Fan Line].[Prop Imp Touch], [AC Fan Line].[PCB Up], [AC Fan
Line].[Intermittent], [AC Fan Line].[Missing Parts], [AC Fan Line].[re
missing], [AC Fan Line].[Extra Parts], [AC Fan Line].[re extra], [AC Fan
Line].[Wrong Parts], [AC Fan Line].[re wrong], [AC Fan Line].[Vent Hous
App], [AC Fan Line].[re vent], [AC Fan Line].[Prop Imp App], [AC Fan
Line].[re prop], [AC Fan Line].[Lead assy Tube Cable tie App], [AC Fan
Line].[re lsa], [AC Fan Line].[name Plate App], [AC Fan Line].[re np], [AC
Fan Line].[Label Barcode App], [AC Fan Line].[re barcode], [AC Fan
Line].[Screw Hole Size], [AC Fan Line].[re size], [AC Fan Line].[Screw App],
[AC Fan Line].[re screw], [AC Fan Line].[Conn Pin App], [AC Fan Line].[re
conn], [AC Fan Line].[Metal Plate Case Cover App], [AC Fan Line].[re metal],
[AC Fan Line].[Stamp App], [AC Fan Line].[re stam], [AC Fan Line].[Flap
App], [AC Fan Line].[re flap], [AC Fan Line].[Seal App], [AC Fan Line].[re
seal], [AC Fan Line].[Paint App], [AC Fan Line].[re paint], [AC Fan
Line].[Nut App], [AC Fan Line].[re nut], [AC Fan Line].[sillicone App], [AC
Fan Line].[re sillione], [AC Fan Line].[Jet Melt App], [AC Fan Line].[re
jetmelt], [AC Fan Line].[Spray App], [AC Fan Line].[re sparay], [AC Fan
Line].[Lead Asy Tube Cable tie Length], [AC Fan Line].[re dimensi], [AC Fan
Line].[Others], [AC Fan Line].[re others], [AC Fan Line].[Remark Catatan]
FROM [AC Jan No] INNER JOIN [AC Fan Line] ON [AC Jan No].[Jan No]=[AC Fan
Line].[Registration No];

That is the SQL view of my query that become controlsource of my form.I
define a relationship between field of "Jan No" from table AC Jan No to the
field "Register No" from table AC Fan Line,after completed the form AC Fan
Line.Then I deleted field Registration No from the query and change with the
field Jan No,but the field become unupdateable so with the others
field.What's happen to my form.Would somebody lke to help.
 
K

Ken Snell \(MVP\)

My guess is that [Registration No] is the primary key, and without it in the
query, the query cannot identify a unique record in the table -- therefore,
you cannot update data via the query.

See these articles for information about what can make a query nonupdatable
(watch for line-wrapping):

When can I update data from a query?
http://msdn.microsoft.com/library/d...l/acconDeterminingWhenCanUpdateDataQueryS.asp

Harnessing the Power of Updatable Queries
http://msdn.microsoft.com/library/default.asp?url=/archive/en-us/dnaraccgen/html/msdn_harness.asp

ACC2000: Cannot Edit or Update Record on a Form
http://support.microsoft.com/default.aspx?scid=kb;en-us;209571&Product=acc

ACC2000: Status Bar Displays "Recordset Not Updateable" Message When You Try
to Update a Linked Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;304179&Product=acc

INFO: Troubleshooting Errors That May Occur When You Update Data in Queries
and in Forms
http://support.microsoft.com/default.aspx?scid=kb;en-us;328828&Product=acc

ACC: Update Query Based on Totals Query Fails
http://support.microsoft.com/default.aspx?scid=kb;en-us;116142&Product=acc


--

Ken Snell
<MS ACCESS MVP>


cho said:
Hi,

SELECT [AC Jan No].[Jan No], [AC Fan Line].[Date], [AC Fan Line].[Model],
[AC Fan Line].[Lot No], [AC Fan Line].[OQC Function], [AC Fan Line].[OQC
Appearance], [AC Fan Line].[Shift], [AC Fan Line].[Line], [AC Fan
Line].[RoHS Status], [AC Fan Line].[OK Quantity], [AC Fan
Line].[Striking],
[AC Fan Line].[Striking II], [AC Fan Line].[BB Noise], [AC Fan Line].[Race
Noise], [AC Fan Line].[Vibration], [AC Fan Line].[Current Wave], [AC Fan
Line].[Sensor], [AC Fan Line].[Lock Ng], [AC Fan Line].[No Rotation], [AC
Fan Line].[Slow Rotation], [AC Fan Line].[Dead Point], [AC Fan Line].[Hi
Lo
Current], [AC Fan Line].[Hi Lo RPM], [AC Fan Line].[Prop Imp come out],
[AC
Fan Line].[Prop Imp Touch], [AC Fan Line].[PCB Up], [AC Fan
Line].[Intermittent], [AC Fan Line].[Missing Parts], [AC Fan Line].[re
missing], [AC Fan Line].[Extra Parts], [AC Fan Line].[re extra], [AC Fan
Line].[Wrong Parts], [AC Fan Line].[re wrong], [AC Fan Line].[Vent Hous
App], [AC Fan Line].[re vent], [AC Fan Line].[Prop Imp App], [AC Fan
Line].[re prop], [AC Fan Line].[Lead assy Tube Cable tie App], [AC Fan
Line].[re lsa], [AC Fan Line].[name Plate App], [AC Fan Line].[re np], [AC
Fan Line].[Label Barcode App], [AC Fan Line].[re barcode], [AC Fan
Line].[Screw Hole Size], [AC Fan Line].[re size], [AC Fan Line].[Screw
App],
[AC Fan Line].[re screw], [AC Fan Line].[Conn Pin App], [AC Fan Line].[re
conn], [AC Fan Line].[Metal Plate Case Cover App], [AC Fan Line].[re
metal],
[AC Fan Line].[Stamp App], [AC Fan Line].[re stam], [AC Fan Line].[Flap
App], [AC Fan Line].[re flap], [AC Fan Line].[Seal App], [AC Fan Line].[re
seal], [AC Fan Line].[Paint App], [AC Fan Line].[re paint], [AC Fan
Line].[Nut App], [AC Fan Line].[re nut], [AC Fan Line].[sillicone App],
[AC
Fan Line].[re sillione], [AC Fan Line].[Jet Melt App], [AC Fan Line].[re
jetmelt], [AC Fan Line].[Spray App], [AC Fan Line].[re sparay], [AC Fan
Line].[Lead Asy Tube Cable tie Length], [AC Fan Line].[re dimensi], [AC
Fan
Line].[Others], [AC Fan Line].[re others], [AC Fan Line].[Remark Catatan]
FROM [AC Jan No] INNER JOIN [AC Fan Line] ON [AC Jan No].[Jan No]=[AC Fan
Line].[Registration No];

That is the SQL view of my query that become controlsource of my form.I
define a relationship between field of "Jan No" from table AC Jan No to
the
field "Register No" from table AC Fan Line,after completed the form AC Fan
Line.Then I deleted field Registration No from the query and change with
the
field Jan No,but the field become unupdateable so with the others
field.What's happen to my form.Would somebody lke to help.
Click to expand...
 
C

cho

No Ken,
I have removed the primary key before.And define the relationship
And in the status bar I saw something like"the join key of table "AC Fan
Line" is not in the recordset"


Ken Snell (MVP) said:
My guess is that [Registration No] is the primary key, and without it in the
query, the query cannot identify a unique record in the table -- therefore,
you cannot update data via the query.

See these articles for information about what can make a query nonupdatable
(watch for line-wrapping):

When can I update data from a query?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/htm
l/acconDeterminingWhenCanUpdateDataQueryS.asp

Harnessing the Power of Updatable Queries
http://msdn.microsoft.com/library/default.asp?url=/archive/en-us/dnaraccgen/
html/msdn_harness.asp

ACC2000: Cannot Edit or Update Record on a Form
http://support.microsoft.com/default.aspx?scid=kb;en-us;209571&Product=acc

ACC2000: Status Bar Displays "Recordset Not Updateable" Message When You Try
to Update a Linked Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;304179&Product=acc

INFO: Troubleshooting Errors That May Occur When You Update Data in Queries
and in Forms
http://support.microsoft.com/default.aspx?scid=kb;en-us;328828&Product=acc

ACC: Update Query Based on Totals Query Fails
http://support.microsoft.com/default.aspx?scid=kb;en-us;116142&Product=acc


--

Ken Snell
<MS ACCESS MVP>


cho said:
Hi,

SELECT [AC Jan No].[Jan No], [AC Fan Line].[Date], [AC Fan Line].[Model],
[AC Fan Line].[Lot No], [AC Fan Line].[OQC Function], [AC Fan Line].[OQC
Appearance], [AC Fan Line].[Shift], [AC Fan Line].[Line], [AC Fan
Line].[RoHS Status], [AC Fan Line].[OK Quantity], [AC Fan
Line].[Striking],
[AC Fan Line].[Striking II], [AC Fan Line].[BB Noise], [AC Fan Line].[Race
Noise], [AC Fan Line].[Vibration], [AC Fan Line].[Current Wave], [AC Fan
Line].[Sensor], [AC Fan Line].[Lock Ng], [AC Fan Line].[No Rotation], [AC
Fan Line].[Slow Rotation], [AC Fan Line].[Dead Point], [AC Fan Line].[Hi
Lo
Current], [AC Fan Line].[Hi Lo RPM], [AC Fan Line].[Prop Imp come out],
[AC
Fan Line].[Prop Imp Touch], [AC Fan Line].[PCB Up], [AC Fan
Line].[Intermittent], [AC Fan Line].[Missing Parts], [AC Fan Line].[re
missing], [AC Fan Line].[Extra Parts], [AC Fan Line].[re extra], [AC Fan
Line].[Wrong Parts], [AC Fan Line].[re wrong], [AC Fan Line].[Vent Hous
App], [AC Fan Line].[re vent], [AC Fan Line].[Prop Imp App], [AC Fan
Line].[re prop], [AC Fan Line].[Lead assy Tube Cable tie App], [AC Fan
Line].[re lsa], [AC Fan Line].[name Plate App], [AC Fan Line].[re np], [AC
Fan Line].[Label Barcode App], [AC Fan Line].[re barcode], [AC Fan
Line].[Screw Hole Size], [AC Fan Line].[re size], [AC Fan Line].[Screw
App],
[AC Fan Line].[re screw], [AC Fan Line].[Conn Pin App], [AC Fan Line].[re
conn], [AC Fan Line].[Metal Plate Case Cover App], [AC Fan Line].[re
metal],
[AC Fan Line].[Stamp App], [AC Fan Line].[re stam], [AC Fan Line].[Flap
App], [AC Fan Line].[re flap], [AC Fan Line].[Seal App], [AC Fan Line].[re
seal], [AC Fan Line].[Paint App], [AC Fan Line].[re paint], [AC Fan
Line].[Nut App], [AC Fan Line].[re nut], [AC Fan Line].[sillicone App],
[AC
Fan Line].[re sillione], [AC Fan Line].[Jet Melt App], [AC Fan Line].[re
jetmelt], [AC Fan Line].[Spray App], [AC Fan Line].[re sparay], [AC Fan
Line].[Lead Asy Tube Cable tie Length], [AC Fan Line].[re dimensi], [AC
Fan
Line].[Others], [AC Fan Line].[re others], [AC Fan Line].[Remark Catatan]
FROM [AC Jan No] INNER JOIN [AC Fan Line] ON [AC Jan No].[Jan No]=[AC Fan
Line].[Registration No];

That is the SQL view of my query that become controlsource of my form.I
define a relationship between field of "Jan No" from table AC Jan No to
the
field "Register No" from table AC Fan Line,after completed the form AC Fan
Line.Then I deleted field Registration No from the query and change with
the
field Jan No,but the field become unupdateable so with the others
field.What's happen to my form.Would somebody lke to help.
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

If your query contains this clause:

FROM [AC Jan No] INNER JOIN [AC Fan Line] ON [AC Jan No].[Jan No]=[AC
Fan Line].[Registration No];


Then Registration No field must remain ni the query.
--

Ken Snell
<MS ACCESS MVP>



cho said:
No Ken,
I have removed the primary key before.And define the relationship
And in the status bar I saw something like"the join key of table "AC Fan
Line" is not in the recordset"


Ken Snell (MVP) said:
My guess is that [Registration No] is the primary key, and without it in the
query, the query cannot identify a unique record in the table -- therefore,
you cannot update data via the query.

See these articles for information about what can make a query nonupdatable
(watch for line-wrapping):

When can I update data from a query?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/htm
l/acconDeterminingWhenCanUpdateDataQueryS.asp

Harnessing the Power of Updatable Queries
http://msdn.microsoft.com/library/default.asp?url=/archive/en-us/dnaraccgen/
html/msdn_harness.asp

ACC2000: Cannot Edit or Update Record on a Form
http://support.microsoft.com/default.aspx?scid=kb;en-us;209571&Product=acc

ACC2000: Status Bar Displays "Recordset Not Updateable" Message When You Try
to Update a Linked Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;304179&Product=acc

INFO: Troubleshooting Errors That May Occur When You Update Data in Queries
and in Forms
http://support.microsoft.com/default.aspx?scid=kb;en-us;328828&Product=acc

ACC: Update Query Based on Totals Query Fails
http://support.microsoft.com/default.aspx?scid=kb;en-us;116142&Product=acc


--

Ken Snell
<MS ACCESS MVP>


cho said:
Hi,

SELECT [AC Jan No].[Jan No], [AC Fan Line].[Date], [AC Fan Line].[Model],
[AC Fan Line].[Lot No], [AC Fan Line].[OQC Function], [AC Fan
Line].[OQC
Appearance], [AC Fan Line].[Shift], [AC Fan Line].[Line], [AC Fan
Line].[RoHS Status], [AC Fan Line].[OK Quantity], [AC Fan
Line].[Striking],
[AC Fan Line].[Striking II], [AC Fan Line].[BB Noise], [AC Fan Line].[Race
Noise], [AC Fan Line].[Vibration], [AC Fan Line].[Current Wave], [AC
Fan
Line].[Sensor], [AC Fan Line].[Lock Ng], [AC Fan Line].[No Rotation], [AC
Fan Line].[Slow Rotation], [AC Fan Line].[Dead Point], [AC Fan
Line].[Hi
Lo
Current], [AC Fan Line].[Hi Lo RPM], [AC Fan Line].[Prop Imp come out],
[AC
Fan Line].[Prop Imp Touch], [AC Fan Line].[PCB Up], [AC Fan
Line].[Intermittent], [AC Fan Line].[Missing Parts], [AC Fan Line].[re
missing], [AC Fan Line].[Extra Parts], [AC Fan Line].[re extra], [AC
Fan
Line].[Wrong Parts], [AC Fan Line].[re wrong], [AC Fan Line].[Vent Hous
App], [AC Fan Line].[re vent], [AC Fan Line].[Prop Imp App], [AC Fan
Line].[re prop], [AC Fan Line].[Lead assy Tube Cable tie App], [AC Fan
Line].[re lsa], [AC Fan Line].[name Plate App], [AC Fan Line].[re np], [AC
Fan Line].[Label Barcode App], [AC Fan Line].[re barcode], [AC Fan
Line].[Screw Hole Size], [AC Fan Line].[re size], [AC Fan Line].[Screw
App],
[AC Fan Line].[re screw], [AC Fan Line].[Conn Pin App], [AC Fan Line].[re
conn], [AC Fan Line].[Metal Plate Case Cover App], [AC Fan Line].[re
metal],
[AC Fan Line].[Stamp App], [AC Fan Line].[re stam], [AC Fan Line].[Flap
App], [AC Fan Line].[re flap], [AC Fan Line].[Seal App], [AC Fan Line].[re
seal], [AC Fan Line].[Paint App], [AC Fan Line].[re paint], [AC Fan
Line].[Nut App], [AC Fan Line].[re nut], [AC Fan Line].[sillicone App],
[AC
Fan Line].[re sillione], [AC Fan Line].[Jet Melt App], [AC Fan
Line].[re
jetmelt], [AC Fan Line].[Spray App], [AC Fan Line].[re sparay], [AC Fan
Line].[Lead Asy Tube Cable tie Length], [AC Fan Line].[re dimensi], [AC
Fan
Line].[Others], [AC Fan Line].[re others], [AC Fan Line].[Remark Catatan]
FROM [AC Jan No] INNER JOIN [AC Fan Line] ON [AC Jan No].[Jan No]=[AC Fan
Line].[Registration No];

That is the SQL view of my query that become controlsource of my form.I
define a relationship between field of "Jan No" from table AC Jan No
to
the
field "Register No" from table AC Fan Line,after completed the form AC Fan
Line.Then I deleted field Registration No from the query and change
with
the
field Jan No,but the field become unupdateable so with the others
field.What's happen to my form.Would somebody lke to help.
Click to expand...
Click to expand...
Click to expand...
 
Top