Results 1 to 9 of 9
  1. #1
    shabbaranks is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    162

    Updating main table from temp table AND form value

    Hi guys,



    Im trying to update a main table with the below code

    Code:
    DoCmd.RunSQL "INSERT INTO PackageDetails_tbl ([Item], [Qty], [Description], [DeliveryNoteNumber]) VALUES (TempPackageDetails_tbl.Item, TempPackageDetails_tbl.QTY, TempPackageDetails_tbl.Description", " & DbNoteNumber & )"
    The only 2 values which are numbers are TempPackageDetails_tbl.QTY and & DbNoteNumber & (which I thought have been identified correctly?) but Im getting an error stating that an expressionb you entered is the wrong data type for one of the arguements - any ideas please?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    2 problems. Your quotes and ampersands are off. This may help:

    http://www.baldyweb.com/BuildSQL.htm

    Secondly, you can't get the temp table values that way. You want the other syntax of an append query:

    INSERT INTO TargetTableName(FieldList)
    SELECT FieldList
    FROM SourceTableName
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    shabbaranks is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    162
    Thanks but with this, how can you select from a source table name when the form value your referring to obviously isnt on a table?

  4. #4
    robsworld78 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    181
    Hi, try this.

    DoCmd.RunSQL "INSERT INTO PackageDetails_tbl (Item, Qty, Description, DeliveryNoteNumber)" & _
    "SELECT Item, " & " QTY, " & " Description, " & " DbNoteNumber " & _
    "FROM TempPackageDetails"

  5. #5
    shabbaranks is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    162
    Quote Originally Posted by robsworld78 View Post
    Hi, try this.

    DoCmd.RunSQL "INSERT INTO PackageDetails_tbl (Item, Qty, Description, DeliveryNoteNumber)" & _
    "SELECT Item, " & " QTY, " & " Description, " & " DbNoteNumber " & _
    "FROM TempPackageDetails"
    Thank you but I think you may have misunderstood how I am building the query. I have a temp table with the first 3 values in and the 4th value comes from a form so Im combining the two into a single query. From your example you have all records based on a string\form value which isnt the case - unless Im mistaken

  6. #6
    robsworld78 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    181
    oh, I don't think you can combine the two. Maybe do the insert with the first 3 values and then do and UPDATE with the other value.

    Or you could first do an insert on that missing value into the temp table then do the insert from the temp table to the main table.

  7. #7
    robsworld78 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    181
    what are you trying to accomplish? How many records are you inserting and why is some data in the temp table and not all of it?

  8. #8
    shabbaranks is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    162
    Its ok what I did was set the value in the subform table to the form field and then used a standard insert query to move the temp table data to the main table. Thanks for your help

  9. #9
    robsworld78 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    181
    Glad to hear you got it worked out

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 11-04-2012, 09:25 AM
  2. delete temp table
    By slimjen in forum Queries
    Replies: 1
    Last Post: 07-19-2012, 06:35 AM
  3. Replies: 2
    Last Post: 08-01-2011, 11:35 PM
  4. Query for temp linked table
    By compooper in forum Programming
    Replies: 8
    Last Post: 06-30-2011, 06:53 AM
  5. sql temp table creation for form
    By Ian_ in forum Forms
    Replies: 2
    Last Post: 06-17-2011, 03:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums