LOOPING

A

ash

I have 3 forms. Table 2 is a subform (single form) and can have many records
that are linked to table 1 (single form). Table 3 is a subform (continuous
form) of Table 2 (single form) and for each record in table 2, there can be
many records in table 3.
I tried using a do loop for table 2, but it still would only capture one
record in the form when there is suppose to be 2 records and i think it is
because of the single form structure.

This is what I have, please let me know what's wrong with it.

duplicating table 1 form (this works fine)
With Me.RecordsetClone
.AddNew
!MAIN_PART_NUMBER = Me.MAIN_PART_NUMBER
!REVISION = Me.REVISION
.Update
.Bookmark = .LastModified
lngMPNID = !ID

duplicating multiple records in table 2 (not working- somehow not picking up
the second record because of the single form, any way to make it move to the
next record?)
Dim rst As Recordset
Set rst = Me.table2.Form.RecordsetClone
With Me.J_NUMBER_subform.Form.RecordsetClone
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF
.AddNew
!ID = lngMPNID
!J_NUMBER = Me.table2.Form.J_NUMBER
!VARIANT = Me.table2.Form.VARIANT
.Update
.Bookmark = .LastModified
NEWJ_ID = !J_ID

duplicating multiple records in table 3 for each record in table 2 (works)
If Me.table 2.Form.table3.Form.RecordsetClone.RecordCount > 0 Then
strSQL = "INSERT INTO table3 (J_ID, NEXT_HIGHER_ASSEMBLY ) " & _
"SELECT " & NEWJ_ID & " as J_ID, table3.NEXT_HIGHER_ASSEMBLY " & _
"FROM table3 WHERE (table2.J_ID = " & Me.table2.Form.J_ID & ");"
db.Execute strSQL, dbFailOnError
End If

..MoveNext
Loop
End If
End With
Set rst = Nothing
 
N

naty

ash said:
I have 3 forms. Table 2 is a subform (single form) and can have many
records
that are linked to table 1 (single form). Table 3 is a subform (continuous
form) of Table 2 (single form) and for each record in table 2, there can
be
many records in table 3.
I tried using a do loop for table 2, but it still would only capture one
record in the form when there is suppose to be 2 records and i think it is
because of the single form structure.

This is what I have, please let me know what's wrong with it.

duplicating table 1 form (this works fine)
With Me.RecordsetClone
.AddNew
!MAIN_PART_NUMBER = Me.MAIN_PART_NUMBER
!REVISION = Me.REVISION
.Update
.Bookmark = .LastModified
lngMPNID = !ID

duplicating multiple records in table 2 (not working- somehow not picking
up
the second record because of the single form, any way to make it move to
the
next record?)
Dim rst As Recordset
Set rst = Me.table2.Form.RecordsetClone
With Me.J_NUMBER_subform.Form.RecordsetClone
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF
.AddNew
!ID = lngMPNID
!J_NUMBER = Me.table2.Form.J_NUMBER
!VARIANT = Me.table2.Form.VARIANT
.Update
.Bookmark = .LastModified
NEWJ_ID = !J_ID

duplicating multiple records in table 3 for each record in table 2
(works)
If Me.table 2.Form.table3.Form.RecordsetClone.RecordCount > 0 Then
strSQL = "INSERT INTO table3 (J_ID, NEXT_HIGHER_ASSEMBLY ) " & _
"SELECT " & NEWJ_ID & " as J_ID, table3.NEXT_HIGHER_ASSEMBLY " & _
"FROM table3 WHERE (table2.J_ID = " & Me.table2.Form.J_ID & ");"
db.Execute strSQL, dbFailOnError
End If

.MoveNext
Loop
End If
End With
Set rst = Nothing
 

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

Top