code to loop through values in subform

  • Thread starter Thread starter aft3rgl0w
  • Start date Start date
A

aft3rgl0w

I have a distribution table where we log CDs out. part of logging some of
these out requires a distribution report be sent with it. I have a form that
creates the distribution report, which includes the company name, contact,
the media numbers and quantities. there is a subform which lists which media
are being sent out, the number of copies and the description. here's what i
want to do: click a button, and have a record added to the distribution table
for each media. eg on form 2008-011, I have media numbers 1,2,3,4 going out.
in the distrbution table I want an entry for each 1, 2, 3 and 4. I have
some preliminary code but can't figure out how to cycle through the subform
media numbers. below is the code I have so far,
can anyone help with what i'm missing?

On Error GoTo Error_Add_Rec

Dim rs As dao.Recordset
Dim db As Database


Set db = CurrentDb()
Set rs = db.OpenRecordset("Distr1", dbOpenDynaset)

For Each d4 In (how do I refer to the numbers in the subform?)
With rs
.AddNew
!medianum = d4
!Name = attn
!Date = date_sent
!Tracking# = [tracking num]
![Media Copies] = xx (not sure how to get the qty from the
subtable)
.Update
End With
.Close

Next

rs.Close

Error_Add_Rec:

End Sub
 
When you want to treat the record source of a form as a recordset, use
the RecordsetClone property of the form.

Dim rs As dao.Recordset
Dim db As Database

Set db = CurrentDb()
Set rs = db.OpenRecordset("Distr1", dbOpenDynaset)

MySubform.Form.RecordsetClone.MoveFirst

Do Until MySubform.Form.RecordsetClone.EOF

With rs
.AddNew
!medianum = d4
!Name = attn
!Date = date_sent
!Tracking# = [tracking num]
![Media Copies] = MySubform.Form.RecordsetClone("qty")
.Update
End With
.Close

Loop
 
i tried it out but i'm getting a "Complie Error: invalid or unqualified
reference" when clicking the button, and in the code window it is
highlighting the .Close
do u know what's wrong?

n00b said:
When you want to treat the record source of a form as a recordset, use
the RecordsetClone property of the form.

Dim rs As dao.Recordset
Dim db As Database

Set db = CurrentDb()
Set rs = db.OpenRecordset("Distr1", dbOpenDynaset)

MySubform.Form.RecordsetClone.MoveFirst

Do Until MySubform.Form.RecordsetClone.EOF

With rs
.AddNew
!medianum = d4
!Name = attn
!Date = date_sent
!Tracking# = [tracking num]
![Media Copies] = MySubform.Form.RecordsetClone("qty")
.Update
End With
.Close

Loop





I have a distribution table where we log CDs out. part of logging some of
these out requires a distribution report be sent with it. I have a form that
creates the distribution report, which includes the company name, contact,
the media numbers and quantities. there is a subform which lists which media
are being sent out, the number of copies and the description. here's what i
want to do: click a button, and have a record added to the distribution table
for each media. eg on form 2008-011, I have media numbers 1,2,3,4 going out.
in the distrbution table I want an entry for each 1, 2, 3 and 4. I have
some preliminary code but can't figure out how to cycle through the subform
media numbers. below is the code I have so far,
can anyone help with what i'm missing?

On Error GoTo Error_Add_Rec

Dim rs As dao.Recordset
Dim db As Database

Set db = CurrentDb()
Set rs = db.OpenRecordset("Distr1", dbOpenDynaset)

For Each d4 In (how do I refer to the numbers in the subform?)
With rs
.AddNew
!medianum = d4
!Name = attn
!Date = date_sent
!Tracking# = [tracking num]
![Media Copies] = xx (not sure how to get the qty from the
subtable)
.Update
End With
.Close

Next

rs.Close

Error_Add_Rec:

End Sub
Click to expand...
Click to expand...
 
Back
Top