Good day All,
This database was created to assess each staff member on how they deliver customer service whether internally or externally. Certain strands were created to access each department. After each strand was created and added to the table the idea was to duplicate the record and modify the scores, date and if neccessary position and department.
The issues faced is that once the duplicate is created I am not able to:
- Change the date - once I try to update the date field it doesn't accept it
- If I change the position for the staff it brings up a new table to be updated with the criteria
The code to create the duplicate is below not sure what I'm missing. I will try to attach the database after I've compress it to the required 2mb.
Code:
Private Sub cmdDuplicateData_Click()
On Error GoTo Err_cmdDuplicateData_Click
Me.AllowEdits = True
Dim strSql As String
Dim lngID As Long
'Save any Edits First
If Me.Dirty = False Then
Me.Dirty = False
End If
'Make sure there is a record to duplicate
If Me.NewRecord Then
MsgBox "Select the record to duplicate. "
Else
'Duplicate the main record: add to form's clone
With Me.RecordsetClone
.AddNew
!StaffID = Me!StaffID
!StaffName = Me!StaffName
!DepartmentName = Me!DepartmentName
!StaffPosition = Me!StaffPosition
!StaffBDate = Me!StaffBDate
!StaffEDate = Me!StaffEDate
.Update
'.AllowEdits
'Save the primary key value, to use as the foreign key for the related records.
.Bookmark = .LastModified
lngID = !StaffApraisedID
'Duplicate the related records: append query
If Me.frmMeasureDetail.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [tblMeasure] (StaffApraisedID, MUserLoginID, MeasureName, MPositonName, MeasureScore, MeasureWeight, MeasureTotal, MeasureDesc)" & _
"SELECT " & lngID & " As NewID, MUserLoginID, MeasureName, MPositonName, MeasureScore, MeasureWeight, MeasureTotal, MeasureDesc " & _
"FROM [tblMeasure] WHERE StaffApraisedID = " & Me.StaffApraisedID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicate, but there were no related records."
End If
'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If
Exit_cmdDuplicateData_Click:
Exit Sub
Err_cmdDuplicateData:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDuplicateData_Click"
Resume Exit_cmdDuplicateData_Click
On Error GoTo Err_cmdDuplicateData_Click
DoCmd.GoToRecord , , acLast
Exit Sub
Err_cmdDuplicateData_Click:
MsgBox Error$
Resume Exit_cmdDuplicateData_Click:
End Sub
End Sub
Any help is welcomed!!
Nika