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

[Sandi]

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.

[orange]

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.

[Sandi]

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..

[orange]

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...............

[Sandi]

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.... ???

[orange]

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.