Hi All,
First time on this forum and fairly new to programming in Access. I am working with a sub-form where once a staff member enters there sub measure I would want to create a duplicate of that record. The problem I am having is that once you enter the sub-form and click the duplicate button it creates a duplicate of the record selected but overwrites the first record in the table. I want it to create a new SubMeasure Number which is the primary key and assigns the record the next available number.
Also if I try to add another record after one has been added I get runtime error "3021" - No current record. I would have to close the form and reopen for it to be able to add again.
Please if anyone can assist because I am not sure what must be done to eliminate the problem.
I have attached the code below
Code:
Private Sub cmdDuplicate_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
If Me.Dirty Then
Me.Dirty = False
End If
'On Error GoTo Err_cmdDuplicate_Click
'Tag property to be used later by the append query.
Me.Tag = Me![MeasureID] 'Links Main form to sub-form
'Add new record to the end of Recordset object
With Rst
.Edit
!SubMeasureName = Me!SubMeasureName
!SubMeasureDesp = Me!SubMeasureDesp
!SubMeasureScore = Me!SubMeasureScore
!SubMeasureWeight = Me!SubMeasureWeight
!SubMeasureTotal = Me!SubMeasureTotal
.Update
.Move 0, .LastModified
End With
Me.Bookmark = Rst.Bookmark
'Run the Duplicate Measure Detail append query which will
'selects all detail records that have the MUserLoginID stored
'in the form's Tag property and appends the back to the detail table
'with the MUserLoginID of the duplicated main form record
DoCmd.SetWarnings False
DoCmd.OpenQuery "Duplicate Measure Detail"
DoCmd.SetWarnings True
'Requery the subform to display the newly appended records.
Me.Requery
Exit_cmdDuplicate_Click:
Exit Sub
Err_cmdDuplicateClick:
MsgBox Error$
Resume Exit_cmdDuplicate_Click:
Me.SubMeasure = Nz(DMax("SubMeasure", "tblSubMeasure"), 0) + 1 'Suppose to find next available number and assign to record
End Sub