Duplicate three levels tables' records

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?
 
A

Allen Browne

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]
 
H

hikaru

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
 
A

Allen Browne

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...
 
H

hikaru

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
 
E

efandango

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Many to Many Forms question 1
many-to-one situation 1
Form design to add data to 3 tables in "Order Entry" style db 1
limit number of records in the many side 2
Utilizing existing tables while expanding 3
Error 3101 cannot find record in the table with key matching field 1
One table linked to three 4
Duplicate record in Form and Subform 0

Top