Results 1 to 5 of 5
  1. #1
    kje7713 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2013
    Posts
    3

    Add Record Button Form

    I need to create an add record button on a form that pulls in data for 3 fields from a table (tbl A) into another table (tbl B), where all other fields in tbl B stay blank. The intent is the user needs to be able to add a new record while inside a form, and then the form can scroll to the next record for additional data entry.

    Fields tbl A
    LOG #
    Date
    Posted By

    Fields tbl B
    LOG #
    Record #
    Date
    Posted By


    Data Field 1
    etc

    I currently am using an append table query that is executed through a macro by clicking a button. It creates a new entry in tbl B that has the same LOG #, as the record open in the form, but with a unique Record # (an autonumber field).

    When I try to add an additional record I now get a squaring effect. Because there are two records with the same LOG # (as necessity) when executing the button again it creates 2 additional records, then 4, 8, 16, 32 etc.

    Any help on how to have a button create one new row in a table and assigned specific data from tbl a into 3 fields, and leave the rest blank would be great. I assume there must be some VBA I could use in execute procedure for the button.

    Thanks - Kevin

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If the data would match the record on the form, you can add a criteria to your append query that points to the form.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    kje7713 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2013
    Posts
    3
    Yes, it does this already - but after it executes - two records then exist with the LOG# the same. The the form has two record open for the LOG#, and if you hit add record again, it creates 4 records - 2 records open, 2 records appended to tbl B results in 4 records with the same LOG #, when I only wanted 3.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I mean I would use whatever field uniquely identifies the record, so only one will ever get appended.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    From what you've said, you should only have one Record with a given Log# in each of the Tables, but it sounds as if you're creating a duplicate Record in the original Form/Table as well as appending a Record to the second Table. Did you, perhaps, use the Command Button Wizard to create the 'add record button?'

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 06-12-2012, 08:56 AM
  2. Replies: 2
    Last Post: 05-11-2012, 11:52 AM
  3. Replies: 11
    Last Post: 01-26-2012, 01:22 PM
  4. Replies: 1
    Last Post: 11-21-2011, 07:58 AM
  5. Replies: 1
    Last Post: 03-04-2011, 08:53 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums