Results 1 to 6 of 6

To add a duplicate form of an existing record to a new record

  1. #1
    Sandi is offline Novice
    Windows 98/ME Access 2003
    Join Date
    May 2011
    Posts
    7

    To add a duplicate form of an existing record to a new record

    I use MS access 2003, so what Iím looking at doing for my database is add a duplicate form (duplicating some fields in the form) of an exiting record to a new record, but the new ID number assigned should be from a sequence (ie. unsed ID numbers) and would be automatically be inputted into the ID field (PK) of the duplicate form.

    Iíve found two pieces of code online which I canít seem to modify to suit what I require but with very limited knowledge of access I assumed these codes are very relevant or I could be wrong.

    Code 1:

    Private Sub btnDuplicate___Click()

    Dim dbs As DAO.Database, Rst As DAO.Recordset
    Dim F As Form

    ' Return Database variable pointing to current database.
    Set dbs = CurrentDb
    Set Rst = Me.RecordsetClone

    On Error GoTo Err_btnDuplicate_Click

    ' Tag property to be used later by the append query.
    Me.Tag = Me![OrderID]

    ' Add new record to end of Recordset object.
    With Rst
    .AddNew
    !CustomerID = Me!CustomerID
    !EmployeeID = Me!EmployeeID
    !OrderDate = Me!OrderDate
    !RequiredDate = Me!RequiredDate
    !ShippedDate = Me!ShippedDate
    !ShipVia = Me!ShipVia
    !Freight = Me!Freight
    !ShipName = Me!ShipName
    !ShipAddress = Me!ShipAddress
    !ShipCity = Me!ShipCity
    !ShipRegion = Me!ShipRegion
    !ShipPostalCode = Me!ShipPostalCode
    !ShipCountry = Me!ShipCountry
    .Update ' Save changes.
    .Move 0, .LastModified
    End With
    Me.Bookmark = Rst.Bookmark

    ' Run the Duplicate Order Details append query which selects all
    ' detail records that have the OrderID stored in the form's
    ' Tag property and appends them back to the detail table with
    ' the OrderID of the duplicated main form record.

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Duplicate Order Details"
    DoCmd.SetWarnings True

    'Requery the subform to display the newly appended records.
    Me![Orders Subform].Requery

    Exit_btnduplicate_Click:
    Exit Sub

    Err_btnDuplicate_Click:
    MsgBox Error$
    Resume Exit_btnduplicate_Click:

    End Sub

    The code above also takes into account an append query named ďDuplicate order detailsĒ, a field column in the design view of the query is NewOrderID: CLng(Forms!Orders!OrderID) Append to ďOrder IDĒ and the OrderID column has in its criteria [Forms]![Orders].[Tag].

    So the above code produces command button on the form, where if the button is clicked on an existing record, a new record and ID is created with the same fields of the existing record BUT does not satisfy the requirement of assigning a new ID from the a sequence.

    Code 2:

    Private Sub btn_Find_Click()
    Dim sString As String
    Dim sSql As String
    Dim sRS As New ADODB.Recordset
    Dim sConn As New ADODB.Connection
    Dim X As Integer
    Dim Y As Integer

    Me.txt_Result = ""
    sString = ""
    sSql = "Select TalentID From tbl_talent_database Order by TalentID"
    Set sConn = CurrentProject.Connection
    sRS.Open sSql, sConn, adOpenKeyset, adLockOptimistic
    If Not sRS.EOF Then
    With sRS
    X = 0
    .MoveFirst
    Do Until .EOF
    Y = !TalentID
    ChkSeq:
    X = X + 1
    If Y <> X Then 'chk to see if TalentID is sequential
    sString = sString & X & " " 'if it is not, then record the non sequential number into the string
    GoTo ChkSeq
    End If
    .MoveNext
    Loop
    End With
    End If
    Me.txt_Result = sString
    Set sRS = Nothing
    End Sub

    Code 2 creates a command button on the form so when I click the button in the text box all the unused numbers all displayed (ie. unused numbers refers to ID numbers not assigned to a record).

    In the end Iím not access capable to modify or join these codes to add a duplicate form of an existing form to a new record however the new record ID number coming from a sequence.

    Greatly appreciate your help.

  2. #2
    orange's Avatar
    orange is online now VIP
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    5,842
    Can you describe - in non Access terms - what exactly your application is, and what exactly you're trying to do with the second form? Perhaps there are options, but until we understand your situation as well as you do, we can not offer much advice.

  3. #3
    Sandi is offline Novice
    Windows 98/ME Access 2003
    Join Date
    May 2011
    Posts
    7
    Quote Originally Posted by orange View Post
    Can you describe - in non Access terms - what exactly your application is, and what exactly you're trying to do with the second form? Perhaps there are options, but until we understand your situation as well as you do, we can not offer much advice.
    Ok, sure I can explain in non access terms, so in basic words I would like to be able to create a duplicate form (duplicating some fields in a form) of an existing form, but the new ID number(PK) assigned to the duplicate form should be from a sequence of the unused ID numbers and should automatically be inputted into the ID field (PK) of the duplicate form.

    Is this clear? I hope so..

  4. #4
    orange's Avatar
    orange is online now VIP
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    5,842
    Sorry that doesn't clear anything for me.

    If you had 2 minutes to tell someone in a line up at McDonalds who doesn't you or Access what you're trying to do - what would you say?

    Forget Ids and PKs and sequences...............

  5. #5
    Sandi is offline Novice
    Windows 98/ME Access 2003
    Join Date
    May 2011
    Posts
    7
    Quote Originally Posted by orange View Post
    Sorry that doesn't clear anything for me.

    If you had 2 minutes to tell someone in a line up at McDonalds who doesn't you or Access what you're trying to do - what would you say?

    Forget Ids and PKs and sequences...............
    I'l try to be short and sweet....here goes...I want to create a duplicate form from an existing form, that duplicate form should be assigned an unused record number.... ???

  6. #6
    orange's Avatar
    orange is online now VIP
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    5,842
    Hmmm. Data resides in tables as records. Records have unique identifiers. The field or fields that make a record unique is called the primary key.
    Forms are typically the way a user interacts with data from one or more tables. Said differently, data from one or more tables is presented to the user in some context by means of one or more forms.


    Are you familiar with queries?
    What is the purpose, exactly, of your second form?

    You have found vba code on the internet that doesn't seem to be doing what you want.

    You could have multiple forms looking at all or part of the data from one or more tables. But you still haven't made it clear as to what it is in business terms that you are trying to achieve.

    Have you designed your database? Are the tables Normalized? Can you post your relationships diagram as a jpg or gif so that we can understand a little more about your database?

    This is confusing
    .....but the new ID number assigned should be from a sequence (ie. unsed ID numbers) and would be automatically be inputted into the ID field (PK) of the duplicate form.

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

Similar Threads

  1. Replies: 6
    Last Post: 02-21-2012, 01:34 PM
  2. Replies: 4
    Last Post: 05-30-2011, 07:20 PM
  3. Replies: 0
    Last Post: 02-25-2011, 07:40 AM
  4. Duplicate last record to next record in form
    By jdhaldane in forum Forms
    Replies: 4
    Last Post: 12-17-2010, 02:22 PM
  5. Replies: 0
    Last Post: 01-06-2009, 12:17 PM

Posting Permissions

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