Ms Access Form - Error - MS access can't append all the records in the append query

Question

Hello,

I have created a form in which when the user enter data that record is stored in temporary table and when the Run Query button is press that record should be append with the main table and delete from temporary table. When i press the Run Query button it gives error which i am not able to sort out , i check my form properties and related things but i don't understand why it is so.

Can any one help me in this. Thank you in advance for your efforts and time.

Error further details : Microsoft Access can't append all the records in the append query.

Microsoft Access set 0 fields to Null due to type conversion failure, and it didn't add 1 records to the table due to key violation, 0 records due to lock violation, and 0 records due to validation rule violation. 

Do you want to run the query anyway?

Answer 1

This is the solution to my Problem:

...

The autonumber seed may be corrupted.  You will be able to tell if this is the case by opening the table in datasheet view.  Sort the table by autonumberID.  Go to the end of the data and start typing in the empty row.  If the generated autonumber is less than the highest existing number, the seed is corrupted.  Here is the code to fix it.  You will need to add a reference to the ADO library.  Open any code module.  Then Tools/References and choose the  newest ADO library you have installed.

To reset the seed, 

Call ResetSeed("yourtablename")

Function ResetSeed(strTable As String) As String

    'Purpose:   Reset the Seed of the AutoNumber, using ADOX.

    Dim strAutoNum As String    'Name of the autonumber column.

    Dim lngSeed As Long         'Current value of the Seed.

    Dim lngNext As Long         'Next unused value.

    Dim strSql As String

    Dim strResult As String

    lngSeed = GetSeedADOX(strTable, strAutoNum)

    If strAutoNum = vbNullString Then

        strResult = "AutoNumber not found."

    Else

        lngNext = Nz(DMax(strAutoNum, strTable), 0) + 1

        If lngSeed = lngNext Then

            strResult = strAutoNum & " already correctly set to " & lngSeed & "."

        Else

            Debug.Print lngNext, lngSeed

            strSql = "ALTER TABLE [" & strTable & "] ALTER COLUMN [" & strAutoNum & "] COUNTER(" & lngNext & ", 1);"

            Debug.Print strSql

            CurrentProject.Connection.Execute strSql

            strResult = strAutoNum & " reset from " & lngSeed & " to " & lngNext

        End If

    End If

    ResetSeed = strResult

End Function

''--------------------------------------------------------------------------------

Function GetSeedADOX(strTable As String, Optional ByRef strCol As String) As Long

    'Purpose:   Read the Seed of the AutoNumber of a table.

    'Arguments: strTable the table to examine.

    '           strCol = the name of the field. If omited, the code finds it.

    'Return:    The seed value.

    Dim cat As New ADOX.Catalog 'Root object of ADOX.

    Dim tbl As ADOX.Table       'Each Table in Tables.

    Dim col As ADOX.Column      'Each Column in the Table.

    'Point the catalog to the current project's connection.

    Set cat.ActiveConnection = CurrentProject.Connection

    Set tbl = cat.Tables(strTable)

    'Loop through the columns to find the AutoNumber.

    For Each col In tbl.Columns

        If col.Properties("Autoincrement") Then

            strCol = "[" & col.Name & "]"

            GetSeedADOX = col.Properties("Seed")

            Exit For    'There can be only one AutoNum.

        End If

    Next

    'Clean up

    Set col = Nothing

    Set tbl = Nothing

    Set cat = Nothing

End Function

''--------------------------------------------------------------------------------

Answer 2

Hello Stellar,

• "If your table has a primary key field and you did not supply a unique value for the field in each record"

This was my error so i remove the Primary Key ID from temporary , but still the error is there.In main/core table ID is there but in Temporary table ID is not there , when button is click it should append to core table but it still gives same error.

Answer 3

I remove ID from temporary table , but still the error is there . Now i am trying to append record from Temporary to core table without ID but still it's not working.

Answer 4

Is the primary key field in the target table an Autonumber?

Can you share the SQL view of the query you are running?

What are the primary/foreign key relationships in the target table?

Answer 5

I would question why you are going through this convoluted routine in the first place.  Why not simply bind a form to the main operational table and dispense with the temporary table.  By setting the form's DataEntry property to True (Yes) and putting the following in its AfterInsert event procedure:

    Me.Requery

the form will open at an empty new record, and once that record is saved will be cleared, ready for the insertion of a new record.

By forcing the user to save the record by means of a command button only, you would be replicating the functionality which you are currently trying to achieve by executing an 'append' query.  You'll find an example of the use of such a button in SaveDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In this little demo file the form includes buttons to save the record, undo (to abort the insertion of a new record), or close the form.  The relevant buttons are enabled/disabled by code in the form's module, depending on the current state of the form.  My form's DataEntry property is False, and it does not call the Requery method after inserting a new record, but if you amend the form to do so it will behave as described above.

If you attempt to insert a new record in which the data violates a unique index you will still get the error which you are currently experiencing, but that is a data issue, not a design issue.