Solved update query not working (1 Viewer)

rehanemis

Registered User.
Local time
Today, 18:44
Joined
Apr 7, 2014
Messages
195
Hi,
I am trying to add the query to update Table 2 using Table 1 data but it is not working
Code:
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?
 

Attachments

  • New Microsoft Access Database.accdb
    992 KB · Views: 257

June7

AWF VIP
Local time
Today, 06:44
Joined
Mar 9, 2014
Messages
5,423
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

Registered User.
Local time
Today, 18:44
Joined
Apr 7, 2014
Messages
195
Yes this is working great!
So I did mistake not added Inner Join?
 

rehanemis

Registered User.
Local time
Today, 18:44
Joined
Apr 7, 2014
Messages
195
Yes this is working great!
So I did mistake not added Inner Join?
Code:
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

..forever waiting... waiting for jellybean!
Local time
Today, 22:44
Joined
May 7, 2009
Messages
19,169
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

AWF VIP
Local time
Today, 06:44
Joined
Mar 9, 2014
Messages
5,423
Why do you even need an UPDATE action? Why are you duplicating data in two tables?
 

rehanemis

Registered User.
Local time
Today, 18:44
Joined
Apr 7, 2014
Messages
195
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.
 

Attachments

  • update query question.png
    952.3 KB · Views: 240

June7

AWF VIP
Local time
Today, 06:44
Joined
Mar 9, 2014
Messages
5,423
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

Registered User.
Local time
Today, 18:44
Joined
Apr 7, 2014
Messages
195
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!
 

Attachments

  • test db.accdb
    588 KB · Views: 253

June7

AWF VIP
Local time
Today, 06:44
Joined
Mar 9, 2014
Messages
5,423
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

Registered User.
Local time
Today, 18:44
Joined
Apr 7, 2014
Messages
195
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

AWF VIP
Local time
Today, 06:44
Joined
Mar 9, 2014
Messages
5,423
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

Registered User.
Local time
Today, 18:44
Joined
Apr 7, 2014
Messages
195
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.
 

Attachments

  • 1.JPG
    1.JPG
    131.6 KB · Views: 255

Users who are viewing this thread

Top Bottom