Results 1 to 2 of 2
  1. #1
    CecilMcVey is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2020
    Posts
    1

    Update 2 table w/ 1 form Runtime error 3346 Number of Query Fields do not match destination

    Ok so I am completely new to access, and what I am trying to do is build an inventory database for warehoused items. In one table INBOUND_INV_TBL I want the form to add the fields that are filled out, there are a total of 11 Fields in the first table. We already know some of the data that is in table 1 but need to add additional information as items come in. In my second Table CURRENT_INV_TBL, it has no entries yet and has a total of 9 fields and I Just need those fields filled out in the second table as items come in. I have created a form that has all 11 fields and made a button with SQL coded into it. The code I used is,

    Private Sub Command67_Click()

    DoCmd.RunSQL "INSERT INTO CURRENT_INV_TBL VALUES ([RR_Nmbr].Value,[Vendor_Name].Value,[Item_Number].Value,[Product_Description].Value,[Item_Model].Value,[Item_Weight].Value,[Received_Qty].Value,[RR_Attachment].Value,[Missing/Damage_Item_Notes].Value)"




    DoCmd.RunSQL "INSERT INTO INBOUND_INV_TBL VALUES ([Purchase_Order].Value,[RR_Nmbr].Value,[Vendor_Name].Value,[Item_Number].Value,[Product_Description].Value,[Item_Model].Value,[Item_Weight].Value,[Expected_Qty].Value,[Received_Qty].Value,[RR_Attachment].Value,[Missing/Damage_Item_Notes].Value)"


    End Sub



    Now when I hit the button I get the "runtime error 3346 Number of query fields do not match the Destination Fields" and I am not sure why. I have double checked that all of the field names match on both tables, and on the form. Also, not sure if it matters but the [RR_Attachment].Value is an attachment and not any kind of text/number value. I have also included screenshots of both tables and the form In attachments. Any help with this would really be appreciated.


    Attached Thumbnails Attached Thumbnails Screenshot (8).jpg   Screenshot (9).jpg   Screenshot (10).jpg   Screenshot (11).jpg  

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why are you using VBA and INSERT action to create records? Why not simply bound forms and controls? And since your form does appear to bound, code should be entirely unnecessary.

    If you must use VBA then must concatenate variable references. Reference to form control is a variable. Will also need data delimiters. Text type uses apostrophe and date/time uses # characters, no delimiter for number type. Not necessary to specify Value property as it is the default property for data controls. Example:
    Code:
    DoCmd.RunSQL "INSERT INTO tablename (field1, field2, field3) VALUES(#" & Me.tbxDate & "#,'" & Me.tbxText & "'," & Me.tbxNumber & ")"
    Alternative is to open a recordset object and append record to recordset.

    Cannot enter into an attachment type field this way. Requires special coding after record is created. Attachment field is a type of multi-value field. Embedded objects use up Access 2GB size limit. Most developers would leave file external and just save filepath to text field.

    Why are you duplicating data between these two tables?

    Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Record Number in Table and Form No Longer Match
    By amyrose1978 in forum Access
    Replies: 3
    Last Post: 09-21-2016, 08:05 AM
  2. Replies: 9
    Last Post: 02-02-2016, 06:27 AM
  3. Replies: 1
    Last Post: 12-30-2013, 09:11 AM
  4. Replies: 19
    Last Post: 08-08-2013, 01:17 PM
  5. Replies: 10
    Last Post: 12-15-2010, 11:12 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