append query and update field problem

[hikaru]

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

How do I find your original post?

If you go back to my original post, it will explain what you need for the
main form.
Since the list of controls will be different for each subform, each will
have to have it's own version of the sub or you will have to modify the sub
to determine which form is calling and have a list of controls for each sub.
You would also have to make it Public in a standard module.

I am guessing you don't have 25 subforms loaded at the same time, so you
probably load each subform dynamically. In that case, if you need to copy
some value from the main form, you will need to to that in the subform's load
event. Now there is one problem with that. If when you load the main form,
you have a subform control that has a form in it's recordsource, it may not
work correctly for that form. The reason being (and I have no idea why) a
subform actually loads before the main form, so if you reference controls on
the main form, they may not be instansiated yet.

So, my recommendation would be that you ensure no subform loads with the
main form. Then when you load a subform, it's load event can get the values
from the main form. As to populating values from a previous record in a
subform, you have to have at least one record to copy the values from.

OK... so I'm having the same issues and I'm trying to follow this procedure,
but I'm having difficulty understanding 'what' goes 'where'. My Main Form has

[quoted text clipped - 23 lines]

Set rst = Forms![Frm_Cont_Monthly_Details_Edit].RecordsetClone
rst.MoveLast