none
Transaction query RRS feed

  • Question

  • I am attempting to perform a transaction, but I receive the following error:  Microsoft Access can't append all the records in the append query.  Microsoft Access set 0 fields to Null due to a type conversion failure ... 

    I believe this has to do with the second part and that a child key is not added the query (the two table are related).  

    Please assist me if you can.

    Private Sub cmdMakePayment_Click()
    
    Dim SQL1 As String
    Dim SQL2 As String
    
    SQL1 = "INSERT INTO [Transaction Header] ( Vendor, [Transaction Amount], [Entry Date], [Memo], [idsBill ID] )" & _
            "SELECT qryUnCleared.Bill, qryUnCleared.Amount, qryUnCleared.[Date Paid], qryUnCleared.Notes, qryUnCleared.[Bill ID] " & _
            "FROM qryUnCleared " & _
            "WHERE (((qryUnCleared.[Bill ID])=[FORMS]![Bill]![Bill ID]))"
    
    SQL2 = "INSERT INTO [Transaction Items] ( [Entry Title], [Transaction Amount], [Entry Date], [From Account] ) " & _
            "SELECT qryUnCleared.Bill, qryUnCleared.Amount, qryUnCleared.[Date Paid], qryUnCleared.Method " & _
            "FROM qryUnCleared " & _
            "WHERE (((qryUnCleared.[Bill ID])=[Forms]![Bill]![Bill ID]))"
            
    BeginTrans
        DoCmd.RunSQL SQL1, True
            BeginTrans
                DoCmd.RunSQL SQL2, True
        CommitTrans
    CommitTrans
     
     
    End Sub

    Sunday, December 31, 2017 9:14 PM

Answers

  • Hi NoviceVBAuser1775,

    can you run each query separately?

    then you can check which query has the issue.

    since we don't have your table designs and also we don't have idea which data you pass in your query it is difficult for us to find the issue.

    you can also try to add the data manually and check whether you can add it or not.

    if any type conversion needed then try to do it in your code before passing the value in query.

    you can also post your sample database here.

    we will try to run the query and try to reproduce the issue.

    than we will try to provide better suggestions to solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 1, 2018 4:58 AM
    Moderator

All replies

  • How are the tables related?  Please state the field names and field types in each table, and indicate which fields are primary  and foreign keys.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Sunday, December 31, 2017 10:55 PM
  • Hi NoviceVBAuser1775,

    can you run each query separately?

    then you can check which query has the issue.

    since we don't have your table designs and also we don't have idea which data you pass in your query it is difficult for us to find the issue.

    you can also try to add the data manually and check whether you can add it or not.

    if any type conversion needed then try to do it in your code before passing the value in query.

    you can also post your sample database here.

    we will try to run the query and try to reproduce the issue.

    than we will try to provide better suggestions to solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 1, 2018 4:58 AM
    Moderator
  • The primary key to the parent table "Transaction Header" is [ID].  It is an AutoNumber and a long integer.  The foreign key to the child table "Transaction Items" is [Transaction Header ID].  It is a Number and also a long integer  The table are a one to many relation.

    Monday, January 1, 2018 4:12 PM
  • Make sure the Formats of the ID and Transaction Header ID are the same in both tables. For example Currency or Standard etc. even if the Field Sizes (long Integer) are the same.

    Monday, January 1, 2018 4:59 PM
  • The primary key to the parent table "Transaction Header" is [ID].  It is an AutoNumber and a long integer.  The foreign key to the child table "Transaction Items" is [Transaction Header ID].  It is a Number and also a long integer  The table are a one to many relation.


    Firstly, you are inserting values into Transaction Amount and Entry Date columns in both the referenced and the referencing tables, which makes no sense.  Each must be an attribute of one but not the other.  My assumption would be that Entry Date is an attribute of the referenced Transaction Header, and Transaction Amount is an attribute of the referencing Transaction Items.

    As you are inserting values from multiple rows  per transaction, the SQL statement to insert a row into Transaction Header should include the DISTINCT predicate.

    To maintain referential integrity a value must be inserted into the Transaction Header ID foreign key column in the referencing Transaction Items table, which is not the case in your SQL statement.  You would therefore have to firstly determine the value of the autonumber inserted into Family Header so that the value can then be inserted into the foreign key in the referencing table.  A simple way to do this is not to allow the number to be inserted automatically, but to firstly look up the maximum value on the primary key column by means of the DMax function, and then add 1 to give you a known value to insert into the primary key ID column as a literal number.  This, however, assumes that each row of a set is inserted individually into Transaction Header followed by the insertion of a set of rows into the referenced table Transaction Items, which means you would have to loop through a recordset of all relevant transactions and insert each followed by the referencing rows at each iteration of the loop.

    In essence all you have to do is, for each DISTINCT transaction returned by the qryUnCleared query insert a row with a known ID value into Transaction Header, and then insert a subset of rows from the same query into Transaction Items, inserting the same known value into the foreign key column.  Each subset would be the items relating to the transaction just inserted.

    Ken Sheridan, Stafford, England

    Monday, January 1, 2018 5:08 PM
  • Hi NoviceVBA,

    For your issue, it is more related with your table design and records, I would suggest you share us a simple database file which could reproduce your issue, and then we could work closely with you, and provide the exact solution.

    Regards,

    Tony


    Help each other

    Tuesday, January 2, 2018 5:56 AM