Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2008
    Posts
    54

    Unanswered: Appending problem

    Hi everyone,
    I have a 2 tables, tblA and tblB and a form bound to tblA. On clicking a button, cmdAppend all records in tblA are “copied and pasted” to tblB. However when I click the button a second time the whole records from tblA is again copied to tblB. I only wanted a NEW record in tblA to be appended to tblB without deleting all records (in tblB) and appending all records from tblA to tblB. Is this possible?

    Fields in tblA Type
    AID Autonumber
    Name Text
    Geog Number
    English Number
    Maths Number

    Fields in tblB Type
    BID Number
    Geog Number
    English Number
    Maths Number

    The code:
    Private Sub cmdAppend()
    On Error GoTo Err_cmdAppend
    DoCmd.Openquery “qryAppend”

    Exit_cmdAppend:
    Exit Sub

    Err_cmdAppend:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_cmdAppend
    End Sub

    qryAppend:
    INSERT INTO tblB( Geog, English, Maths )
    SELECT tblA.Geog, tblA.English, [Maths]*1.2 AS EMaths
    FROM tblA;

    Please help. Thanks

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    What you are asking here, if you click the button twice it appends tblA to tblB twice? Yes it will
    unless you either empty/delete the data from that table or enter some criteria to identify which are new Recordset.

    Can you explain a little more?

  3. #3
    Join Date
    Jan 2008
    Posts
    54
    yes if I were to click twice I get all the records from tblA appending to tblB twice. What I wanted is when a new record is added to tblA I only want the new record to append to tblB, not the entire tblA's records. Probably I need is some codes which I am not sure how to do. Thank for your reply.

  4. #4
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    In your TblB you will need to set a Primary Key based on Geog Number,English Number and Maths Number and allow no duplicates.

    In your code add the code for SetWarnings. This will suppress messages regarding the append action etc.

    Private Sub cmdAppend()
    On Error GoTo Err_cmdAppend

    docmd.setwarnings false
    DoCmd.Openquery “qryAppend”
    docmd.setwarnings true

    Exit_cmdAppend:
    Exit Sub

  5. #5
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    I dont get it, why do you want two tables with the same records?

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    there is a flag in MSaccess call NewRecord has true/false value

    also there is a me.dirty
    whether the current record has been modified since it was last saved.

    I use both of these heaps of times
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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