Reply to thread

Message: [QUOTE="hikaru, post: 11972209"] FINALLY, the code works fine now, and here are the final codes [all in the main form], I hope this will work with you brutuss100.... And thank you very very much Klatuu for your time and efforts: BTW: I'm using AutoNumbers for Tbl_Cont_Monthly_Change and the subforms' tables,, *************** Private Sub cmdNew_Click() Dim rst As DAO.Recordset Dim strSqlVO As String Dim strSqlObs As String Dim strSqlLtr As String Dim bkmrkdCont As Integer Dim MaxRec As Integer On Error GoTo Err_cmdNew_Click Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone 'move to last record 'to copy its details rst.MoveLast 'bookmark the last record Forms![Frm_Cont_Monthly_Details_Edit].Bookmark = rst.Bookmark 'takes the value of the bookmarked Cont_Monthly_No for subforms' sql statements bkmrkdCont = Me.RecordsetClone!Cont_Monthly_No 'add the new record based on Function: TagValues DoCmd.GoToRecord , , acNewRec Form.Refresh 'takes the value of greatest (last saved which is of the Tbl_Cont_Monthly_Change report that we've just created) Cont_Monthly_No MaxRec = DMax("Cont_Monthly_No", "Tbl_Cont_Monthly_Change") 'Debug.Print MaxRec 'sql to insert the VOs of the last record (last Tbl_Cont_Monthly_Change report) into the new record strSqlVO = "INSERT INTO Tbl_VO ( VO_Desc, VO_Value, VO_Remarks, Cont_Monthly_No )" & _ " SELECT Tbl_VO.VO_Desc, Tbl_VO.VO_Value, Tbl_VO.VO_Remarks," & MaxRec & "" & _ " FROM Tbl_Cont_Monthly_Change INNER JOIN Tbl_VO ON Tbl_Cont_Monthly_Change.Cont_Monthly_No = Tbl_VO.Cont_Monthly_No" & _ " WHERE (((Tbl_VO.Cont_Monthly_No)=" & bkmrkdCont & "));" DBEngine(0)(0).Execute strSqlVO, dbFailOnError 'sql to insert the Obstructions of the last record (last Tbl_Cont_Monthly_Change report) into the new record strSqlObs = "INSERT INTO Tbl_Obstructions ( Obs_Desc, Cont_Monthly_No )" & _ " SELECT Tbl_Obstructions.Obs_Desc," & MaxRec & "" & _ " FROM Tbl_Cont_Monthly_Change INNER JOIN Tbl_Obstructions ON Tbl_Cont_Monthly_Change.Cont_Monthly_No = Tbl_Obstructions.Cont_Monthly_No" & _ " WHERE (((Tbl_Obstructions.Cont_Monthly_No)=" & bkmrkdCont & "));" DBEngine(0)(0).Execute strSqlObs, dbFailOnError 'sql to insert the Letters of the last record (last Tbl_Cont_Monthly_Change report) into the new record strSqlLtr = "INSERT INTO Tbl_Letters ( Ltr_Subject, Ltr_Date, Cont_Monthly_No )" & _ " SELECT Tbl_Letters.Ltr_Subject, Tbl_Letters.Ltr_Date," & MaxRec & "" & _ " FROM Tbl_Cont_Monthly_Change INNER JOIN Tbl_Letters ON Tbl_Cont_Monthly_Change.Cont_Monthly_No = Tbl_Letters.Cont_Monthly_No" & _ " WHERE (((Tbl_Letters.Cont_Monthly_No)=" & bkmrkdCont & "));" DBEngine(0)(0).Execute strSqlLtr, dbFailOnError Form.Refresh Debug.Print bkmrkdCont Debug.Print Me.Cont_Monthly_No Exit_cmdNew_Click: Exit Sub Err_cmdNew_Click: MsgBox Err.Description, vbInformation, "Invalid Move" Resume Exit_cmdNew_Click End Sub Private Sub TagValues(GetTag As Boolean) Dim varCtlNames As Variant Dim lngX As Long Dim contmonthID As Integer varCtlNames = Array("Cont_Month", "Cont_Name", "Cont_Org_Value", "Cont_Apr_Value", _ "Cont_Start_Date", "Cont_Comp_Date", "Cont_Contractor", _ "Cont_Consultant", "Cont_Overall", "Cont_Comments", "Contract_No") For lngX = 0 To UBound(varCtlNames) If GetTag Then Me.Controls(varCtlNames(lngX)) = Me.Controls(varCtlNames(lngX)).Tag Else Me.Controls(varCtlNames(lngX)).Tag = _ Nz(Me.Controls(varCtlNames(lngX)), vbNullString) End If Next lngX If GetTag Then Me.Cont_Month = DateAdd("m", 1, Me.Cont_Month) End If contmonthID = Me.Cont_Monthly_No 'Debug.Print contmonthID End Sub Private Sub Form_AfterUpdate() Call TagValues(False) End Sub Private Sub Form_Current() Call TagValues(Me.NewRecord) End Sub [/QUOTE]

Verification