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?


    Colin Weir, Jul 22, 2012
    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.
    Access Developer, Jul 22, 2012
    1. Advertisements

  3. Colin Weir

    Colin Weir Guest


    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 Weir, Jul 22, 2012
  4. Colin Weir

    Bob Quintal Guest

    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
    Bob Quintal, Jul 22, 2012
  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.
    Access Developer, Jul 22, 2012
    1. Advertisements

Ask a Question

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.