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