Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2001

    Unanswered: Create new record

    What would be the best way to select fields from a table, display them
    in a form and then add them back into the same table as a "new"

    For example, when a user clicks a button they are asked to enter an
    invoice no. Based on that invoice no. I want to populate the fields on
    the form with the data in the table with that invoice number. The user
    then has the option of changing the data or leaving it as is and then
    save the data as a new record under a new invoice number.

  2. #2
    Join Date
    Dec 2004
    Madison, WI
    This would be the way I might do it. I think you'll get some different answers though.

    1. I have an autonumber field in my main Invoice data table (say it's called InvoiceID). And InvoiceNumber would be a primary key to not allow duplicates.

    2. I have a "search" form which has a combobox (ie. MyInvoiceCombobox) with the InvoiceID and InvoiceNumber. InvoiceID being the bound column (or InvoiceNumber - either one). When the user selects an invoicenumber from the combobox, I open the form....
    i.e. Docmd.openform "MyInvoiceForm",,,"[InvoiceID] = " & me!MyInvoiceCombobox & ""

    InvoiceID would be a field on my MyInvoiceForm.

    3. On the MyInvoiceForm, I have a button which when they click it, it generates a new invoice number (i.e. in the field InvoiceNumber on the form) or a popup form asking them to enter the new InvoiceNumber and then runs an append query to the main Invoice data table which has criteria in it where InvoiceID = Forms!MyInvoiceForm!InvoiceID (left unchecked in the query so it doesn't append this field - since it's an autonumber field) and also has an expression column (ie. InvoiceNum: Forms!MyInvoiceForm!InvoiceNumber (or from my popup form) which appends to the InvoiceNumber field.) and all the other fields would be in the query for the append (except of course InvoiceNumber because it's appending the expression to the InvoiceNumber field.) (This all also could easily be done via the "search" form where the user selects an invoiceNumber to "clone" (InvoiceID being the bound column in the combobox/listbox) and another field for the "new" InvoiceNumber - which the expression in the query will point to.)

    The point here is that the append query has criteria pointing to some InvoiceID (my autonumber field) on a form and an expression from some form with the new InvoiceNumber.

    OR I would write it out in code verses doing a query (depends on how many fields and if I'm in the mood to write some code verses a query.)

    4. From there, I might reopen the form with criteria of the new InvoiceNumber or do a gotorecord or find....etc..
    Last edited by pkstormy; 07-08-07 at 02:17.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Sep 2001
    Thanks - I will give it a try.

Posting Permissions

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