Unable to create duplicate record on subform

[nika.duncan]

Hi All,
I am trying to create duplicate records from a main form frmManagers which has a subform frmSubMeasure. I have placed the duplicate button on the main form. It creates a duplicate of the main form data and gives me the option to add new record to the sub. I want the duplicate to be created on the sub form for me to just edit the scores.

I don't know how to pass the sub form data to be duplicated I thought the append query which I used would update the tblSubMeasure table which created the subform frmSubMeasure.


In the sub the append query do update the form with the new MeasureID from the mainform and the form is available to enter new data. I want the subform data to be duplicated as well

In the query I included all the fields from the tblSubMeasure table and this is appended to the same table tblSubMeasure and I place a tag on the MeasureID using "[Forms]![frmManagers].[Tag]"

I have outlined the code below for anyone to have a look and let me know where I am going wrong. Thanks

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![MeasureId]


'Add new record to end of Recordset Object.

With Rst
.AddNew
!MUserLoginID = Me!MUserLoginID
!MeasureName = Me!MeasureName
!MPositonNumber = Me!MPositonNumber
!MeasureScore = Me!MeasureScore
'!MeasureTotal = Me!MeasureTotal
!MAssBDate = Me!MAssBDate
!MAssEDate = Me!MAssEDate



.Update
.Move 0, .LastModified

End With

Me.Bookmark = Rst.Bookmark

DoCmd.SetWarnings False
DoCmd.OpenQuery "Duplicate Measure Details"   ' Should perform an append query on tblSubMeasure and update the same table 
DoCmd.SetWarnings True

'Requery the subform
Me![frmSubMeasure].Requery

'!SubMeasureName = Me!SubMeasureName  'fields from tblSubMeasure not sure how to pass them
'!SubMeasureDesp = Me!SubMeasureDesp
Exit_btnDuplicate_Click:
Exit Sub

Err_btnDuplicate_Click:
Resume Exit_btnDuplicate_Click:
End Sub

Nika

[June7]

Here is another thread about using RecordsetClone to add record. https://www.accessforums.net/program...ror-39731.html

The new record for the main form RecordsetClone is successfully created?

If the button code is on main form and you need to pull values from subform, refer to the subform container control name. I always name subform container different from the object it holds, like ctrSubMeasure.

Me.ctrSubMeasure!SubMeasureName

How does the query know what values to use? Show the query SQL statement. I really don't understand why you need to pull values from tblSubMeasure after the Requery.

Need to clean up db issues noted in your other thread https://www.accessforums.net/access/...ing-39699.html

[nika.duncan]

Hi June7,
See sql view of the query below.
Thanks for the help.

INSERT INTO tblSubMeasure ( SubMeasure, SubMeasureName, SubMeasureDesp, SubMeasureScore, SubMeasureWeight, SubMeasureTotal, MeasureID )
SELECT tblSubMeasure.SubMeasure, tblSubMeasure.SubMeasureName, tblSubMeasure.SubMeasureDesp, tblSubMeasure.SubMeasureScore, tblSubMeasure.SubMeasureWeight, tblSubMeasure.SubMeasureTotal, CLng([Forms]![frmManagers]![MeasureID]) AS NewMeasureID
FROM tblSubMeasure
WHERE (((tblSubMeasure.MeasureID)=[Forms]![frmManagers].[Tag]));

Nika

[June7]

I have your db from other thread. Have you cleaned up issues identified there?

What is [Tag]?

[nika.duncan]

Hi June7,

I have cleaned up and change the relationship as requested but I am still don't know how to populate the userID textbox on frmLogin. I have attached the updated file. Although you've explained what needs to be done and I think I understand. I am not sure how to go about it.

Nika

[June7]

What is purpose of this db - performance evaluations? If this is the case, then people are evaluated, not positions. Position number should not be in both tblMeasure and tblUser. I think it should be in tblMeasure as this table appears to be a junction table to associate employee with position at time of evaluation. This will allow employee to occupy more than one position at different times due to promotion or transfer or rehire.

If MUserLoginID is the foreign key for UserID, why is there no relationship link to tblUser?

There are two procedures: btnDuplicate_Click and cmdDuplica_Click behind both frmManager and frmMMeasures. The button is associated with the first but the second has the code with the INSERT query. Neither reflects procedure code in original post.

However, I got this code behind frmManager to work:

Private Sub btnDuplicate_Click()
On Error GoTo Err_btnDuplicate_Click
Dim OldMeasureID As Integer, NewMeasureID As Integer
OldMeasureID = Me.MeasureId
'Add new record to end of Recordset Object.
With Me.RecordsetClone
.AddNew
!MUserLoginID = Me!MUserLoginID
!MeasureName = Me!MeasureName
!MPositonNumber = Me!MPositonNumber
!MeasureScore = Me!MeasureScore
!MAssBDate = Me!MAssBDate
!MAssEDate = Me!MAssEDate
NewMeasureID = !MainMeasureID
.Update
End With
CurrentDb.Execute "INSERT INTO tblSubMeasure(SubMeasureName, SubMeasureDesp, SubMeasureScore, SubMeasureWeight, SubMeasureTotal, MainMeasureID)" & _
"SELECT SubMeasureName, SubMeasureDesp, SubMeasureScore, SubMeasureWeight, SubMeasureTotal, " & NewMeasureID & " AS NewMeasureID " & _
"FROM tblSubMeasure " & _
"WHERE MainMeasureID=" & OldMeasureID & ";"
Me.MeasureName.SetFocus
DoCmd.GoToRecord , , acLast
Exit_btnDuplicate_Click:
Exit Sub
Err_btnDuplicate_Click:
MsgBox Error$
Resume Exit_btnDuplicate_Click:
End Sub

[nika.duncan]

Thanks for the help and based on your feedback I've gone through and have been changing the relationship. I am still struggling to pass the value from the frmManager to the frmMMeasures. Any help with that will be appreciated.

Thanks again it is really appreciated.

Nika

[June7]

The code I posted worked for me.

What exactly is the issue you are having? Did you try the code? What happens?

[nika.duncan]

Sorry to have confused you the code works fine. I am talking about the other thread: "Form not displaying data correctly when scrolling through" where I was trying to pass the MUserLoginID and MPositionNumber from frmManager to frmMMeasure and you told me that the way I was doing it makes no sense. You had made some suggestion but I have been unable to do it.

That is what I was referring to probably I shouldn't have post it here and for that I am sorry.

Nika

[June7]

Okay, this thread is resolved. Return to the other thread and post new info on the issue there.