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

    Unanswered: Update On Append Query Error Message

    Hi, All!

    Good news update.

    I created an Event Procedure, which runs in the check box OnClick =
    event.

    Private Sub Trans_Reconciled_Click()
    DoCmd.OpenQuery "qryArchive"
    End Sub

    What I need to do now, is "fix" it to just append the current record in =
    the form and not all of the records in the table. Not quite sure how to =
    accomplish this!?

    Thanks to those who are helping this somewhat newbie!

    Thanks in advance=20

    Bob

  2. #2
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244

    Re: Update On Append Query Error Message

    You'll need to make use of the primary key field from your table. Put this onto the form (you can hide it if you like). In the query, insert the same field, and for its criteria enter:

    Forms!TheForm!ThePrimaryKey field

    ..where "TheForm" is the name of your form and "ThePrimaryKeyField" is..um..the name of your PK field.

    Doing this means that the query will append only the record where the PK value is the one in the current form record.

    Hope this helps



    Originally posted by vbprogwb
    Hi, All!

    Good news update.

    I created an Event Procedure, which runs in the check box OnClick =
    event.

    Private Sub Trans_Reconciled_Click()
    DoCmd.OpenQuery "qryArchive"
    End Sub

    What I need to do now, is "fix" it to just append the current record in =
    the form and not all of the records in the table. Not quite sure how to =
    accomplish this!?

    Thanks to those who are helping this somewhat newbie!

    Thanks in advance=20

    Bob
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  3. #3
    Join Date
    Sep 2003
    Posts
    30

    Re: Update On Append Query Error Message

    Originally posted by andybriggs
    You'll need to make use of the primary key field from your table. Put this onto the form (you can hide it if you like). In the query, insert the same field, and for its criteria enter:

    Forms!TheForm!ThePrimaryKey field

    ..where "TheForm" is the name of your form and "ThePrimaryKeyField" is..um..the name of your PK field.

    Doing this means that the query will append only the record where the PK value is the one in the current form record.

    Hope this helps
    Hi, andybriggs!

    Thanks for responding. I played with the query a little bit and:

    I changed the original (I added the Where) query (Append Query) to the following:

    INSERT INTO tblBalance ( Trans_ID, Trans_Date, Trans_Amount, Trans_Type, Check_Number, ATM_Ref_Number, Trans_Reconciled )
    SELECT tblTransaction.Trans_ID, tblTransaction.Trans_Date, tblTransaction.Trans_Amount, tblTransaction.Trans_Type, tblTransaction.Check_Number, tblTransaction.ATM_Ref_Number, tblTransaction.Trans_Reconciled
    FROM tblTransaction
    WHERE ((tblTransaction!Trans_ID=Forms!frmAccount_Transac tion!Trans_ID));

    I noticed in the query's Design View the expression is now missing in the Criteria: section of the query.

    When I click the checkbox (Trans_Reconciled) I get:

    "You are about to run . . . Are you sure . . ."
    Then
    "You are about to append 1 row(s). . . ." Which is what I want to do.
    Then
    I get this
    ". . . 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? . . . To ignore the error(s) and run the query, click Yes. . . .click Help" When I click Help all I get is a definition of the different queries. When I click Yes it does append the current record in the form to the Append To table ignoring the others in the Append From

    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!?

    I hope I didn't go into more detail than necessary.

    Any help would be greatly appreciated.

    Thanks in advance,

    Bob

  4. #4
    Join Date
    Sep 2003
    Posts
    30

    Re: Update On Append Query Error Message

    Originally posted by andybriggs
    You'll need to make use of the primary key field from your table. Put this onto the form (you can hide it if you like). In the query, insert the same field, and for its criteria enter:

    Forms!TheForm!ThePrimaryKey field

    ..where "TheForm" is the name of your form and "ThePrimaryKeyField" is..um..the name of your PK field.

    Doing this means that the query will append only the record where the PK value is the one in the current form record.

    Hope this helps
    Hi, All!

    Solution to problem. I deleted the Append To table and then selected the Append From table and did a Save As. The query worked with no error messages. For some reason the copy 'n paste didn't create an identical table as the Save As did. I'm a little confused. Any suggestions as to why?

    Also, the suggestion to buy MS Money or Quickbook are legitimate ones or would be, if I was dealing with a "normal" employer.

    Thanks to all who responded!

    Later,

    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
  •