Duplicate three levels tables' records

  • Thread starter Thread starter hikaru
  • Start date Start date
H

hikaru

Hi,

I have three levels of tables:
Tbl_Project
Tbl_VariationOrder
Tbl_Obstruction

Tbl_VariationOrder has FK of Project_ID from Tbl_Project (1 to many)
Tbl_Obstruction has FK VO_ID from Tbl_VariationOrder (1 to many)

I have an "Add New" button in the Project Form (the form shows Project
details [Parent], and VO details [subForm]). this button copy the currently
viewed Project with its VOs. So far, everything works fine. what i want to do
is to make the "Add New" button also copy all the Obstructions related to
each VO of the viewed Project. I think I need to create a loop, but I'm suck
regarding Loops. Any help please?
 
For the first two levels, OpenRecordset and AddNew. This gives you access to
the newly assigned autonumber value.

This is now you can get the autonumber:

Set rs = db.OpenRecordset("Tbl_Project", dbOpenDynaset, dbAppendOnly)
rs.AddNew
rs!SomeField = SomeValue
rs!AnotherField = AnotherValue
rs.Update
rs.Bookmark = rs.LastModified
Debug.Print rs![YourIDField]
 
thanks for your help, but this is the way i can duplicate the first level
only, as i understood.
I've seen this page in your website: http://allenbrowne.com/ser-57.html
it was very helpful with the 1st two levels in my duplication. however, i
still don't know how to duplicate the third level since their could be more
than 1 VO and every VO can have more than 1 Obstruction. Here is the code I'm
using for my 1st two tables levels
(Tbl_Cont_Monthly_Change=Tbl_Projects)(Tbl_VO=Tbl_VariationOrder):

==================
Private Sub TagValues(GetTag As Boolean)
Dim varCtlNames As Variant
Dim lngX As Long
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
End Sub

Private Sub Form_AfterUpdate()
Call TagValues(False)
End Sub

Private Sub Form_Current()
Call TagValues(Me.NewRecord)
End Sub





Private Sub cmdNew_Click()
Dim rst As DAO.Recordset
Dim strSqlVO 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")

'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

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
 
Hmm. You're trying to use the tag properties. That could work for the first
level, but I can't see that going 2 more levels down.

Here's an example of using the RecordsetClone of the main form to add a new
record, and then executing an Append query statement to create the related
records:
http://allenbrowne.com/ser-57.html

In your case, you would need to OpenRecordset on the middle table, and
AddNew and Update each record in a loop. Inside the loop you would execute
the Append query to create the related records.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

hikaru said:
thanks for your help, but this is the way i can duplicate the first level
only, as i understood.
I've seen this page in your website: http://allenbrowne.com/ser-57.html
it was very helpful with the 1st two levels in my duplication. however, i
still don't know how to duplicate the third level since their could be
more
than 1 VO and every VO can have more than 1 Obstruction. Here is the code
I'm
using for my 1st two tables levels
(Tbl_Cont_Monthly_Change=Tbl_Projects)(Tbl_VO=Tbl_VariationOrder):

==================
Private Sub TagValues(GetTag As Boolean)
Dim varCtlNames As Variant
Dim lngX As Long
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
End Sub

Private Sub Form_AfterUpdate()
Call TagValues(False)
End Sub

Private Sub Form_Current()
Call TagValues(Me.NewRecord)
End Sub





Private Sub cmdNew_Click()
Dim rst As DAO.Recordset
Dim strSqlVO 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")

'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

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
Click to expand...
 
thanks a lot Allen..., as you said I opened a recordset on the middle table
(it was tough to figure how to do this [I found a function which was very
helpful]) and then I appended both the VO details and its related records in
a loop.

and here is the final code:


Private Sub TagValues(GetTag As Boolean)
Dim varCtlNames As Variant
Dim lngX As Long
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
End Sub

Private Sub Form_AfterUpdate()
Call TagValues(False)
End Sub

Private Sub Form_Current()
Call TagValues(Me.NewRecord)
End Sub





Private Sub cmdNew_Click()
Dim rst As DAO.Recordset
Dim strSqlVO 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")



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





Function Concatenate2(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
Dim SQLVO2 As String
Dim SQLVO_OBS As String
Dim SQLVO_NOC As String
Dim MaxMonRec As Integer
Dim MaxVO As Integer

MaxMonRec = DMax("Cont_Monthly_No", "Tbl_Cont_Monthly_Change")
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
'Debug.Print rs.Fields(0) & " .Fields(0)"
SQLVO2 = "INSERT INTO Tbl_VO ( VO_Desc, VO_Value,
VO_StartDate, VO_EndDate, VO_Ant_EndDate, VO_Overall, VO_Done, VO_Remarks,
VO_ImgFile, VO_LastNOC, VO_LastNOCDate, VO_NotRcvd_NOCs, Cont_Monthly_No )" &
_
" SELECT Tbl_VO.VO_Desc, Tbl_VO.VO_Value,
Tbl_VO.VO_StartDate, Tbl_VO.VO_EndDate, Tbl_VO.VO_Ant_EndDate,
Tbl_VO.VO_Overall, Tbl_VO.VO_Done, Tbl_VO.VO_Remarks, Tbl_VO.VO_ImgFile,
Tbl_VO.VO_LastNOC, Tbl_VO.VO_LastNOCDate, Tbl_VO.VO_NotRcvd_NOCs, " &
MaxMonRec & "" & _
" 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.VO_NO)=" & rs.Fields(0) & "));"
DBEngine(0)(0).Execute SQLVO2, dbFailOnError

MaxVO = DMax("VO_No", "Tbl_VO")

SQLVO_OBS = "INSERT INTO VO_Obstructions ( Obs_Desc,
Obs_Solved, Obs_ImgFile, VO_No )" & _
" SELECT VO_Obstructions.Obs_Desc,
VO_Obstructions.Obs_Solved, VO_Obstructions.Obs_ImgFile," & MaxVO & "" & _
" FROM Tbl_VO INNER JOIN VO_Obstructions ON
Tbl_VO.VO_No = VO_Obstructions.VO_No" & _
" WHERE (((VO_Obstructions.VO_No)=" &
rs.Fields(0) & ") AND (VO_Obstructions.Obs_Solved)='No');"
DBEngine(0)(0).Execute SQLVO_OBS, dbFailOnError
..MoveNext
Loop
End If
.close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate2 = strConcat
End Function
 
Allen,

where in the example on your webpage, you say : "So why did we use AddNew
in the main form, but an append query statement to duplicate the subform
records?"

If I wanted to copy the 'Main' record to an exisiting 'Main' record instead
of a new copy, and using a know Primary Key ID number using your example:
http://allenbrowne.com/ser-57.html

is it just a simple case of instead of using '.AddNew', I used '.Append', or
would I have to do something else to make it work?








Allen Browne said:
Hmm. You're trying to use the tag properties. That could work for the first
level, but I can't see that going 2 more levels down.

Here's an example of using the RecordsetClone of the main form to add a new
record, and then executing an Append query statement to create the related
records:
http://allenbrowne.com/ser-57.html

In your case, you would need to OpenRecordset on the middle table, and
AddNew and Update each record in a loop. Inside the loop you would execute
the Append query to create the related records.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

hikaru said:
thanks for your help, but this is the way i can duplicate the first level
only, as i understood.
I've seen this page in your website: http://allenbrowne.com/ser-57.html
it was very helpful with the 1st two levels in my duplication. however, i
still don't know how to duplicate the third level since their could be
more
than 1 VO and every VO can have more than 1 Obstruction. Here is the code
I'm
using for my 1st two tables levels
(Tbl_Cont_Monthly_Change=Tbl_Projects)(Tbl_VO=Tbl_VariationOrder):

==================
Private Sub TagValues(GetTag As Boolean)
Dim varCtlNames As Variant
Dim lngX As Long
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
End Sub

Private Sub Form_AfterUpdate()
Call TagValues(False)
End Sub

Private Sub Form_Current()
Call TagValues(Me.NewRecord)
End Sub





Private Sub cmdNew_Click()
Dim rst As DAO.Recordset
Dim strSqlVO 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")

'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

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
Click to expand...
Click to expand...
 
Back
Top