Creating a new record but passing information

Discussion in 'Access Forms Coding' started by Colin Weir, Jul 22, 2012.

  1. Colin Weir

    Colin Weir Guest

    Hi there

    I am wanting to place a button on a form that would create a new record of information but keeping the same customer details. I want it to pass the name etc over the the new one. Is this possible?

    Thanks

    Colin
     
    Colin Weir, Jul 22, 2012
    #1
    1. Advertisements

  2. Yes, it can be done, with Visual Basic for Applications code (or macros).

    However, more likely, you should restructure your data and include a foreign
    key in both records to the same customer record in a separate customer
    table. There are good reasons for following good relational database design
    principles (aka "normalizing" data base design) -- makes design and
    implementation (and modification) a lot easier.

    --
    Larry Linson
    Microsoft Office Access MVP
    Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

    "Colin Weir" <> wrote in message
    news:...
    > Hi there
    >
    > I am wanting to place a button on a form that would create a new record of
    > information but keeping the same customer details. I want it to pass the
    > name etc over the the new one. Is this possible?
    >
    > Thanks
    >
    > Colin
     
    Access Developer, Jul 22, 2012
    #2
    1. Advertisements

  3. Colin Weir

    Colin Weir Guest

    Hi

    Thanks for your reply. In this instance I would like to create s new record in the same table. I have the following in the database to pass information from one table to another

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Issues and Concerns"
    strPassValues = Me.[Reference Number] & "~" & Me.Surname & "~" & Me.[First Name] & "~" & Me.Town & "~" & Me.Address & "~" & Me.[Address (2)] & "~" & Me.[address(3)] & "~" & Me.Postcode & "~" & Me.[Telephone No:]
    stLinkCriteria = "[Reference Number]=" & Me![Reference Number]
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , , strPassValues

    However adding this to a record opperation button where the DoCmd is GoToRecord doesn't work. i.e the following
    DoCmd.OpenForm , , , , stLinkCriteria, , , strPassValues

    Do you have any tips for this?

    Thanks again

    Colin
     
    Colin Weir, Jul 22, 2012
    #3
  4. Colin Weir

    Bob Quintal Guest

    Colin Weir <> wrote in
    news::

    > Hi
    >
    > Thanks for your reply. In this instance I would like to create s
    > new record in the same table. I have the following in the
    > database to pass information from one table to another
    >
    > Dim stDocName As String
    > Dim stLinkCriteria As String
    >
    > stDocName = "Issues and Concerns"
    > strPassValues = Me.[Reference Number] & "~" & Me.Surname & "~"
    > & Me.[First Name] & "~" & Me.Town & "~" & Me.Address & "~" &
    > Me.[Address (2)] & "~" & Me.[address(3)] & "~" & Me.Postcode &
    > "~" & Me.[Telephone No:] stLinkCriteria = "[Reference
    > Number]=" & Me![Reference Number] DoCmd.OpenForm stDocName, ,
    > , stLinkCriteria, , , strPassValues
    >
    > However adding this to a record opperation button where the DoCmd
    > is GoToRecord doesn't work. i.e the following
    > DoCmd.OpenForm , , , , stLinkCriteria, , , strPassValues
    >
    > Do you have any tips for this?
    >
    > Thanks again
    >
    > Colin


    Re-READ mr Linson's reply.

    Boyce and Codd's rules for database design say you should only need
    to pass one value to the new record, and that is the key value from
    the the persons table. [surname], [first name], [town] and the other
    values already exist and need not be passed, which is why Access does
    not have an easy functionality to do this.

    Besides, your code does not define the stLinkCriteria, which you
    should not use attempting to add a record, but you should set the
    Datamode parameter to the acFormAdd constant, so that Access knows
    that it you want it to be going to a new record. You would use the
    OpenArgs to pass the value(s) you need, but you need the form's on
    load event to parse that (those) value(s) to the cointrols where they
    belong.

    --
    Bob Q.
    PA is y I've altered my address.
     
    Bob Quintal, Jul 22, 2012
    #4
  5. I don't have recommendations for adding or editing Records using code to
    drive Forms, because it is rare that it's not the most inefficient and
    clumsy way to manipulate data.

    Assuming "Reference Number" is a unique identifier for the record, it
    appears you are trying to open another form displaying the same record, when
    what you would need

    If the name and address information in your example is the customer
    information, my previous recommendation still holds -- it is redundant to
    carry that same information in multiple Records... it should be in a
    separate Table, with a "foreign key" in this Table identifying the Record in
    that separate customer Table. (If you do that, you'll only need to pass that
    foreign key to the new record.)

    You are putting the values in the OpenArgs argument of OpenForm, but you are
    not opening the Form with a DataMode of acFormAdd (to open to add a new
    record and acFormAdd is not the default for that argument). If you open the
    new Form in DataMode of acAddNew, you can put VBA code in the FormLoad (not
    the Open) event to pick up the OpenArgs data and enter it into the
    corresponding _Control_ on the Form (you say nothing about code to pick up
    the OpenArgs values and do something with them)... that may work for you.
    But, CAVEAT EMPTOR, I rarely use Forms with VBA code to manipulate data, and
    I can't take the time just now to construct a DB to test this
    recommendation, so it's untested speculation.

    --
    Larry Linson
    Microsoft Office Access MVP
    Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

    "Colin Weir" <> wrote in message
    news:...
    > Hi
    >
    > Thanks for your reply. In this instance I would like to create s new
    > record in the same table. I have the following in the database to pass
    > information from one table to another
    >
    > Dim stDocName As String
    > Dim stLinkCriteria As String
    >
    > stDocName = "Issues and Concerns"
    > strPassValues = Me.[Reference Number] & "~" & Me.Surname & "~" &
    > Me.[First Name] & "~" & Me.Town & "~" & Me.Address & "~" & Me.[Address
    > (2)] & "~" & Me.[address(3)] & "~" & Me.Postcode & "~" & Me.[Telephone
    > No:]
    > stLinkCriteria = "[Reference Number]=" & Me![Reference Number]
    > DoCmd.OpenForm stDocName, , , stLinkCriteria, , , strPassValues
    >
    > However adding this to a record opperation button where the DoCmd is
    > GoToRecord doesn't work. i.e the following
    > DoCmd.OpenForm , , , , stLinkCriteria, , , strPassValues
    >
    > Do you have any tips for this?
    >
    > Thanks again
    >
    > Colin
     
    Access Developer, Jul 22, 2012
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. LadyAmethyst

    How do I pass information to create a new record.

    LadyAmethyst, Oct 30, 2004, in forum: Access Forms Coding
    Replies:
    19
    Views:
    126
    Marshall Barton
    Nov 6, 2004
  2. Olly Bowman

    Passing values to a blank record?

    Olly Bowman, Nov 18, 2004, in forum: Access Forms Coding
    Replies:
    0
    Views:
    68
    Olly Bowman
    Nov 18, 2004
  3. digger27

    Copy record information to another record

    digger27, May 16, 2005, in forum: Access Forms Coding
    Replies:
    7
    Views:
    145
  4. John D
    Replies:
    0
    Views:
    187
    John D
    Mar 22, 2007
  5. Maarten

    Insert specific record information into new record

    Maarten, Feb 18, 2008, in forum: Access Forms Coding
    Replies:
    1
    Views:
    143
    George Nicholson
    Feb 18, 2008
  6. Replies:
    3
    Views:
    84
  7. Mark Kubicki
    Replies:
    1
    Views:
    382
    strive4peace
    Jan 16, 2009
  8. John S. Ford, MD

    Passing information from one form to another

    John S. Ford, MD, Apr 7, 2009, in forum: Access Forms Coding
    Replies:
    5
    Views:
    88
    jamaul
    Apr 7, 2009
Loading...