Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2007
    Posts
    91

    Unanswered: saving data from a form using tableA to tableB

    Hi guys

    Can you please help me with the syntax to save data within a form using tblA to tblB (whicheva changes i make on the form, i want saved on both tables)

    i've tried this whenever i click the done command but it's not workin

    CODE:
    DoCmd.RunCommand acCmdSaveRecord

    Dim strAll As String
    Dim insertHistory As String

    strAll = "SELECT almost.*"
    strAll = strAll & "FROM almost, History"
    strAll = strAll & "WHERE [forms]![molo]![Customercode] = almost.customercode "


    insertHistory = "INSERT INTO history ( SELECT strAll.* FROM strAll )"
    End Sub

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    insertHistory = "INSERT INTO history ( SELECT strAll.* FROM strAll )"
    Incorrect syntax.
    That's selecting from a table called strAll... which I believe would be incorrect.
    Don't forget that you have to use string concatenators to build up a dynamic sql statement (as used when initiall building strAll!).
    Code:
    insertHistory = "INSERT INTO history ( SELECT "
    insertHistory = insertHistory & strAll
    insertHistory = insertHistory & ".* FROM "
    insertHistory = insertHistory & strAll
    insertHistory = insertHistory &" )"
    This will still fail, because the SQL syntax is still incorrect...

    to see what I mean simply add the following line to the end of the code:
    Code:
    MsgBox insertHistory
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2007
    Posts
    91
    thanx i'll try dat

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hint: SELECT SELECT wont work.
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2007
    Posts
    91
    yep i saw that. and i fixed it but i still get no results on tblB, but i'll work on it some mo & let u know if i got it or not

    Thanks

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Well, if you get stuck, post your full INSERT SQL statement.
    EDIT: You won't get stuck
    George
    Home | Blog

  7. #7
    Join Date
    Mar 2007
    Posts
    91
    Here's ma code :
    insertHistory = "INSERT INTO History.Customercode (SELECT almost.customercode"
    insertHistory = insertHistory & " FROM almost, History)"
    insertHistory = insertHistory & " WHERE me.Customercode = almost.customercode)"

    MsgBox (insertHistory)
    DoCmd.RunSQL insertHistory

    it's fine but d part in red crashes wit "Syntax error in INSERT INTO statement. (Error 3134) "

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Syntax:
    INSERT [ INTO ] { table | view_name } [ ( column1 [ , column2 [ , ... ] ] ) ]
    VALUES ( value1 [ , value2 [ , ... ] ] )
    George
    Home | Blog

  9. #9
    Join Date
    Mar 2007
    Posts
    91
    Dim insertHistory As String

    insertHistory = "INSERT INTO History (CustomerCode, Unique_ID, CustomerName, ADDRESS_1, ADDRESS_2, CITY, POSTAL_CODE, CONTACT, PHONE_NO, SALESMAN_NAME, SALESMAN_NO, E_MAIL_ADDRESS, CELL_NO, Owner, Buyer, Manager, Phisical_Address, Physical_Postal_code, Prefix, Reps_mail_Add, Last_week, Two_weeks, Three_weeks, [Month], Other_Cont, Other, happy, not_happy, SendRep, not_sendRep, PlaceOder, Not_Placeorder, convenient, Spoke_with, Explorer, Comment, [Order], [date], Current_yr_sales, Previous_yr_sales, Redbull, Campbells, Lindt, Mentos, CapriSun, MentosGum, ActII, CarpeDiem, FinnCrisp, Katjes)"
    insertHistory = insertHistory & " SELECT Almost.CustomerCode, Almost.Unique_ID, Almost.CustomerName, Almost.ADDRESS_1, Almost.ADDRESS_2, Almost.CITY, Almost.POSTAL_CODE, Almost.CONTACT, Almost.PHONE_NO, Almost.SALESMAN_NAME, Almost.SALESMAN_NO, Almost.E_MAIL_ADDRESS, Almost.CELL_NO, Almost.Owner, Almost.Buyer, Almost.Manager, Almost.Phisical_Address, Almost.Physical_Postal_code, Almost.Prefix, Almost.Reps_mail_Add, Almost.[Last week], Almost.[Two weeks ago], Almost.[Three weeks ago], Almost.[Month ago], Almost.Other_Cont, Almost.Other, Almost.Service_Yes, Almost.Service_No, Almost.Rep_Yes, Almost.Rep_No, Almost.Order_Yes, Almost.Order_No, Almost.[Convenint time to talk?], Almost.spoke_with, Almost.Agent, Almost.Comment, Almost.Order, Almost.Date, Almost.YTD_current_yr.RandSales, Almost.YTD_previous_yr.RandSales, Almost.RedBull, Almost.Campbells, Almost.Lindt, Almost.Mentos, Almost.Capri_Sun, Almost.Mentos_Gum, Almost.ActII, Almost.Carpe_Diem, Almost.Finn_Crisp, Almost.Katjes"
    insertHistory = insertHistory & " FROM Almost, History"
    insertHistory = insertHistory & " WHERE [forms]![molo]![Customercode] = almost.customercode "

    MsgBox (insertHistory)

    DoCmd.RunSQL insertHistory

    Doesn't crash & does get executed , which means it the syntax is fine but it still doesn't insert the data into tblB

    Finally got it... took out the part in red within d code
    Last edited by noks; 04-04-07 at 10:53.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I don't know why I have to tidy your query, but here it is:
    Code:
    INSERT INTO History
    	(
    	CustomerCode,				Unique_ID,			CustomerName,				ADDRESS_1,
    	ADDRESS_2,				CITY,				POSTAL_CODE,				CONTACT,
    	PHONE_NO,				SALESMAN_NAME,			SALESMAN_NO,				E_MAIL_ADDRESS,
    	CELL_NO, Owner,				Buyer, Manager,			Phisical_Address,			Physical_Postal_code,
    	Prefix, 				Reps_mail_Add,			Last_week, Two_weeks,			Three_weeks,
    	[Month],				Other_Cont,			Other,					happy,
    	not_happy,				SendRep,			not_sendRep,				PlaceOder,
    	Not_Placeorder,				convenient,			Spoke_with,				Explorer,
    	Comment,				[Order],			[date],					Current_yr_sales,
    	Previous_yr_sales,			Redbull,			Campbells,				Lindt,
    	Mentos,					CapriSun,			MentosGum,				ActII,
    	CarpeDiem,				FinnCrisp,			Katjes
    	)
    
    SELECT 	Almost.CustomerCode, 			Almost.Unique_ID,		Almost.CustomerName,			Almost.ADDRESS_1,
    	Almost.ADDRESS_2, 			Almost.CITY, 			Almost.POSTAL_CODE, 			Almost.CONTACT, 
    	Almost.PHONE_NO, 			Almost.SALESMAN_NAME, 		Almost.SALESMAN_NO, 			Almost.E_MAIL_ADDRESS, 
    	Almost.CELL_NO, 			Almost.Owner, 			Almost.Buyer, 				Almost.Manager, 
    	Almost.Phisical_Address,		Almost.Physical_Postal_code, 	Almost.Prefix, 				Almost.Reps_mail_Add,
    	Almost.[Last week], 			Almost.[Two weeks ago], 	Almost.[Three weeks ago],		Almost.[Month ago],
    	Almost.Other_Cont, 			Almost.Other,			Almost.Service_Yes,			Almost.Service_No,
    	Almost.Rep_Yes, 			Almost.Rep_No,	 		Almost.Order_Yes, 			Almost.Order_No,
    	Almost.[Convenint time to talk?],	Almost.spoke_with, 		Almost.Agent, 				Almost.Comment,
    	Almost.Order, 				Almost.Date, 			Almost.YTD_current_yr.RandSales,	Almost.YTD_previous_yr.RandSales,
    	Almost.RedBull, 			Almost.Campbells, 		Almost.Lindt, 				Almost.Mentos,
    	Almost.Capri_Sun,			Almost.Mentos_Gum,		Almost.ActII,				Almost.Carpe_Diem,
    	Almost.Finn_Crisp,			Almost.Katjes
    
    FROM 	Almost,
    	History
    
    WHERE 	[Forms]![Molo]![Customercode] = Almost.customercode
    I notice that your INSERT and SELECT have a different number of columns in them: Any reason why?

    I also notice that you appear to have a rediculous table structure... History table contains fields: happy & not_happy
    What's wrong with a simple Yes/No or True/False field?

    Also, are you inserting the values into two tables? If so, WHY?
    George
    Home | Blog

  11. #11
    Join Date
    May 2005
    Posts
    1,191
    Hey George, congrats on being engaged! Blessings on that, eh!
    Me.Geek = True

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Thank you very much Nick
    George
    Home | Blog

  13. #13
    Join Date
    Mar 2007
    Posts
    91
    Congradulations George. Was wondering where's the "She said yes " coming from

    Yep am inserting it into 2 coz i wanna keep history (and no i don't want audit trail) Am doin it like this so that if the user wants to see what was done in a specific day they could see the screen (not only the data). That's how they want it

    Thanks

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    So identical data going into two tables of identical structure... All you want to do is run the darn thing twice but with the different table names in each statement - EASY.
    Code:
    Dim InsA, InsB, InsStart, InsEnd As String
    
    InsStart = "INSERT INTO "
    InsEnd = " ( CustomerID.... etc etc blah blah )"
    
    InsA = InsStart & " tblA " & InsEnd
    InsB = InsStart & " tblB " & InsEnd
    
    DoCmd.RunSQL InsA
    DoCmd.RunSQL InsB
    NOTE: This should simplify your SQL aswell.
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •