Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    30

    Unanswered: Append Query Error Message

    Hi, All!

    I really have 2 questions:

    1.) Is there a way to set the Auto Number back to 0 after completing the testing of the forms, etc?

    2.) I have this checkbox (Trans_Reconciled), when On Click runs a macro, which Open Query and appends the record to another table. When clicked, I ge the following error:

    ". . . Access can't append all of the records to the append query. . . . Access set 1 field(s) to null due to a type conversion failure . . . due to key violations . . . due to lock violations . . . due to validation rule violations. Do you want to run the action query anyway? . . . "

    I don't have any primary keys set, yet, I want to try and wait until the database is finished. Seem so many problem when you set the PK in the beginning!?

    The 2 tables have the same fields, but different names. I did a copy 'n paste.

    Is the PK the problem? I don't have any validation rules set, either!

    Other than that the append query works!

    Any help will be greatly appreciated!

    Thanks in advance,

    Bob

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Question in the first: When the table is cleared ('coz otherwise how would you have a pk=0), compact db.

    Question in the second: Huh? I guess I don't know what copy and paste means from your OnClick event code. Methinks if you had a problem with a primary key, you would know about it: Violation of PK, or Null can't be PK. Maybe one of your fields is going to the wrong datatype (type conversion failure). That's one of the most common I run into.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  3. #3
    Join Date
    Sep 2003
    Posts
    30
    Originally posted by HomerBoo
    Question in the first: When the table is cleared ('coz otherwise how would you have a pk=0), compact db.

    Question in the second: Huh? I guess I don't know what copy and paste means from your OnClick event code. Methinks if you had a problem with a primary key, you would know about it: Violation of PK, or Null can't be PK. Maybe one of your fields is going to the wrong datatype (type conversion failure). That's one of the most common I run into.
    Hi, HomerBoo!

    "copy and paste means from your OnClick event code." When I created tblPrevious_Balance, I only did a copy 'n paste of tblTransaction. I needed 2 tables with the same fields. Nothing involving OnClick.

    How can I get the AutoNumber to begin at 1 again? When I delete all the records (and the last one is, lets' say #12) and insert a new one, the new record has #13.

    Thanks in advance,

    Bob

  4. #4
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    I'm going to answer your most recent question, I guess I still don't understand the second original one then ---but i think it's still something to do with a datatype conversion error ----

    After you delete all the records, compact the db, the next time you go to put a record in your table, it will start over again with the PKing.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  5. #5
    Join Date
    Sep 2003
    Posts
    30
    Originally posted by HomerBoo
    I'm going to answer your most recent question, I guess I still don't understand the second original one then ---but i think it's still something to do with a datatype conversion error ----

    After you delete all the records, compact the db, the next time you go to put a record in your table, it will start over again with the PKing.
    HomerBoo,

    Thanks! The compacting worked and the AutoNumber is back at 1. this worked for Access 2000 if anybody is interested in version.

    Okay, back to my query/checkbox problem:

    I needed a table I could Append records to from another table. So I copied 'n pasted the original table. Same data type, etc. And created a Append Query!

    Just recently, I ran the query (qryArchive) from design view and it worked. No error messages!! When I checked the "Append To" table, the qry appended the one (1) record I had in the Original table (Append From) to the "Append To" table.

    I played with form and tables numerous times before posting this updated reply.

    When I tried it from the form (frmAccount_Transaction), clicking the checkbox, it worked fine. Checked again and it was there.

    NOW: When I have 2 records in the table I get the original error message:

    ". . . Access can't append all of the records to the append query. . . . Access set 1 field(s) to null due to a type conversion failure . . . due to key violations . . . due to lock violations . . . due to validation rule violations. Do you want to run the action query anyway? . . . "

    What's up with that?

    This isn't my day. I'm now geting "#Name?" from an textbox that Control Source is a qry. I used Expression Builder. =qryPrev_Balance!Previous_Balance

    I'm ready to go home, but I sometimes work from home and here I am.

    I need to get up and get some caffeine, I mean coffee! :-)

    Really need the help today.

    Thanks in advance,

    Bob

Posting Permissions

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