Solved update query not working

[rehanemis]

Hi,
I am trying to add the query to update Table 2 using Table 1 data but it is not working

UPDATE [Table 2] SET [Table 2].Name_ = [Table1].[Name_], [Table 2].Result = [Table1].[Result]
WHERE ((([Table 2].[roll no])=[Forms]![Form1]![cboRoll]));

I would like to update the Table 2 fields where Roll No is matched in both tables.

any suggestion?

 

[June7]

Why do you want a duplicate table?

Don't update the Roll_No field. Add JOIN link.

UPDATE [Table 2] INNER JOIN Table1 ON [Table 2].[roll no] = Table1.[roll no] SET [Table 2].Name_ = [Table1].[Name_], [Table 2].Result = [Table1].[Result]
WHERE ((([Table 2].[roll no])=[Forms]![Form1]![cboRoll]));

 

[rehanemis]

Yes this is working great!
So I did mistake not added Inner Join?

 

[rehanemis]

Yes this is working great!
So I did mistake not added Inner Join?

UPDATE tbl_Saved_ShirtStyle INNER JOIN tbl_ShirtStyle ON tbl_Saved_ShirtStyle.OrderNo_ItemNo = tbl_ShirtStyle.OrderNo_ItemNo SET tbl_ShirtStyle.StyleType = [tbl_Saved_ShirtStyle]![StyleType], tbl_ShirtStyle.Collar = [tbl_Saved_ShirtStyle]![Collar], tbl_ShirtStyle.[Mandarin Collar] = [tbl_Saved_ShirtStyle]![Mandarin Collar], tbl_ShirtStyle.[Wing Tip collar] = [tbl_Saved_ShirtStyle]![Wing Tip collar], tbl_ShirtStyle.[Removeable collar stays] = [tbl_Saved_ShirtStyle]![Removeable collar stays], tbl_ShirtStyle.CollarComments = [tbl_Saved_ShirtStyle]![CollarComments], tbl_ShirtStyle.ShirtCuff = [tbl_Saved_ShirtStyle]![ShirtCuff], tbl_ShirtStyle.[Button on sleeve vent] = [tbl_Saved_ShirtStyle]![Button on sleeve vent], tbl_ShirtStyle.Pocket = [tbl_Saved_ShirtStyle]![Pocket], tbl_ShirtStyle.CommentsPocket = [tbl_Saved_ShirtStyle]![CommentsPocket], tbl_ShirtStyle.[Front Plaquet] = [tbl_Saved_ShirtStyle]![Front Plaquet], tbl_ShirtStyle.[Removeable Button panel for Tuxedo Shirt] = [tbl_Saved_ShirtStyle]![Removeable Button panel for Tuxedo Shirt], tbl_ShirtStyle.[Tuxedo Full front fabric] = [tbl_Saved_ShirtStyle]![Tuxedo Full front fabric], tbl_ShirtStyle.FrontPlaquetComments = [tbl_Saved_ShirtStyle]![FrontPlaquetComments], tbl_ShirtStyle.ShirtBack = [tbl_Saved_ShirtStyle]![ShirtBack], tbl_ShirtStyle.ShirtBackComments = [tbl_Saved_ShirtStyle]![ShirtBackComments], tbl_ShirtStyle.Monogram = [tbl_Saved_ShirtStyle]![Monogram], tbl_ShirtStyle.MonoStyle = [tbl_Saved_ShirtStyle]![MonoStyle], tbl_ShirtStyle.Colour = [tbl_Saved_ShirtStyle]![Colour], tbl_ShirtStyle.Placement = [tbl_Saved_ShirtStyle]![Placement], tbl_ShirtStyle.MonoLetters = [tbl_Saved_ShirtStyle]![MonoLetters], tbl_ShirtStyle.MonoComments = [tbl_Saved_ShirtStyle]![MonoComments], tbl_ShirtStyle.[Fabric Inside Collar] = [tbl_Saved_ShirtStyle]![Fabric Inside Collar], tbl_ShirtStyle.[Fabric Inside Cuffs] = [tbl_Saved_ShirtStyle]![Fabric Inside Cuffs], tbl_ShirtStyle.FabricInsidePlaquet = [tbl_Saved_ShirtStyle]![FabricInsidePlaquet], tbl_ShirtStyle.SpecialTrimComments = [tbl_Saved_ShirtStyle]![SpecialTrimComments], tbl_ShirtStyle.ButtonColour = [tbl_Saved_ShirtStyle]![ButtonColour], tbl_ShirtStyle.ButtonHoleThreadColour = [tbl_Saved_ShirtStyle]![ButtonHoleThreadColour], tbl_ShirtStyle.FabricID = [tbl_Saved_ShirtStyle]![FabricID], tbl_ShirtStyle.FabricDetail = [tbl_Saved_ShirtStyle]![FabricDetail]
WHERE (((tbl_ShirtStyle.StyleType)=Forms!frm_SelectStyleShirt!cboStyle) And ((tbl_ShirtStyle.OrderNo_ItemNo)=Forms!frm_SelectStyleShirt!txtOrderNoItemNo) And ((tbl_ShirtStyle.PieceNo)=Forms!frm_SelectStyleShirt!cboPieceNo));

My real query is not working as I added inner join too.

 

[arnelgp]

UPDATE [Table 2] RIGHT JOIN Table1 ON [Table 2].[roll no] = Table1.[roll no]
SET [TABLE 2].[roll no] = TABLE1.[roll no], [TABLE 2].Name_ = TABLE1.Name_, [TABLE 2].Result = Table1.Result
WHERE (((Table1.[roll no])=[Forms]![Form1]![cboRoll]))

 

[June7]

Why do you even need an UPDATE action? Why are you duplicating data in two tables?

 

[rehanemis]

Actually I want to update the data in same table upon user choice. So I have table ShirtStyle which contains records and one Field called StyleType. I would like to apply same style to other Pieces. For this i created another table to save Each StyleType and then to get the same style to update other record.

 

[June7]

None of this makes any sense. Data should not be in both tables. Should have a lookup table of shirt styles and then another table for saving shirt styles associated with an order. Order Number should be in tbl_Saved_ShirtStyle as well as key for record of tbl_ShirtStyles, not all the other info about shirt. The nature of relational database is to avoid duplication.

 

[rehanemis]

Please give a look on the sample database.
an single order might have many pieces of shirts and many pieces might be with same styleType. I would like to apply the same shirt style to other pieces on a click. That why I created same structured table to copy the style from and update tbl_ShirtStyle.

Hope that make sense and I can have good option to accomplish this.
Much thanks!

 

[June7]

Again, should not duplicate data.

If you want to allow users to add new shirt style lookup table record "on the fly" during data entry, use a combobox for selecting shirt styles and utilize combobox NotInList event to create new record in tbl_ShirtStyle. Review https://blueclawdatabase.com/notinlist-event-code/

 

[rehanemis]

have you pleased gave a look on db attached?

 

[June7]

I have. Doesn't alter my comments.

 

[rehanemis]

Ok, Suppose I would like to apply Style A (means all fields information)to Piece no 1,2,3,4,5 how can I do it?

 

[June7]

Bind form to tbl_Selected_ShirtStyle. Bind comboboxes to fields. Have a field for record ID of tbl_ShirtStyle. Do data entry.

Where is table for Orders that tbl_Selected_ShirtStyle would be related to? This db is not a complete schema.

 

[rehanemis]

I am not expert in MS Access Design and development so I Used a drop-down that take style type from saved_style table and using dlookup to apply the whole records to new records. This working good for me.