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.
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.
!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
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.OpenQuery "Duplicate Order Details"
'Requery the subform to display the newly appended records.
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.
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
X = 0
Do Until .EOF
Y = !TalentID
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
Me.txt_Result = sString
Set sRS = Nothing
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.