Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Apr 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Append query not working (Access 2000)

    I am trying to append data from a field in one table to a field with the same name in another blank table by means of an append query, yet I keep getting a validation key error. As far as I can see the fields in the two tables are identical in design - same data type, length, etc. Neither field is a key field. I have deleted all the relationships for both tables. Can anyone lend me a clue?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append query not working (Access 2000)

    Are you trying to populate only one field?
    If so, does the field contain Null (empty) values in the source table? That might cause a problem
    Does the target table have other fields beside the field you are trying to populate? There might be required fields etc.

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Append query not working (Access 2000)

    I used to run into same problem. Assuming you already checked the usual stuff (primary keys, relationships, etc) as mentioned, the usual culprit is the Allow Zero Length property - if set to No, you will not be able to append any records where the field is blank. So what I did (after finally figuring out reason for append query not working) was to run this sub for any table where I had problem:

    Public Sub SetAllowZeroLengthProperty(ByRef tblName As String)
    On Error GoTo Err_Handler

    Dim db As DAO.Database
    Dim tbl As DAO.TableDef
    Dim fld As DAO.Field
    Dim strMsg As String

    Set db = CurrentDb
    Set tbl = db.TableDefs(tblName)

    For Each fld In tbl.Fields
    If fld.Type = dbText Then 'Text field
    fld.AllowZeroLength = True
    End If
    Next fld

    ' Test msg:
    strMsg = "The Allow Zero Length property has been set to Yes for all text fields " & _
    "in the " & tbl.Name & " table."
    MsgBox strMsg, vbInformation, "ALLOW ZERO LENGTH RESET"

    Exit_Sub:
    Set db = Nothing
    Set tbl = Nothing
    Set fld = Nothing
    Exit Sub

    Err_Handler:
    Select Case Err.Number
    ' Case 0
    ' Resume Next
    Case Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    Beep
    MsgBox strMsg, vbExclamation, "ERROR MESSAGE"
    Resume Exit_Sub
    End Select

    End Sub

    This sets the Allow Zero Length property to Yes for all text fields in specified table. If you have a lot of tables or fields you may want to use something like this, or just reset property "manually", and see if that helps any.

    HTH

Posting Permissions

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