0

I'm trying to pass data from a form with a subform into a microsoft word document, form and subform in this image:enter image description here

I have code for passing the form data, but I'm not sure how I reference or request the data from the subform? Ideally the string returned would contain every piece of subform data for the referenced column.

What I have so far is as follows:

Private Sub Command90_Click()

Dim objWRD As Word.Application
Dim objDoc As Word.Document
Set objWRD = CreateObject("Word.Application")
objWRD.Visible = True
objWRD.Activate
Set objDoc = objWRD.Documents.Add("C:\WordTemplate.dot", , , True)
Set objRange = objDoc.Bookmarks("AccountCode").Range
objRange.Text = "" & Me.[Account Code / Project Number].Column(1)
Set objRange = objDoc.Bookmarks("Consignto").Range
objRange.Text = "" & Me.[Consign To]

End Sub

I did try adding this following code, but of course it only returns the first record of the subform, whereas I want it to return all of the subform price records that are displayed:

Set objRange = objDoc.Bookmarks("Price").Range
objRange.Text = "" & Me.[Order Details].Form.[Price]

Edit:

Closest I've come to a solution is this:

Private Sub Command90_Click()

Dim rst1 As DAO.Recordset
MsgBox "" & Me.[Order Details].Form.RecordsetClone.RecordCount

Set rst1 = Me.[Order Details].Form.RecordsetClone
rst1.MoveFirst

Do Until rst1.EOF
MsgBox "" & rst1.[Price]
rst1.MoveNext

rst1.Close

Loop
End Sub

MsgBox "" & rst1.[Price] is wrong and MsgBox "" & Me.[Order Details].Form.[Price] just gives the first value multiple times and doesn't appear to move down rows

Andy
  • 135
  • 2
  • 14

1 Answers1

1

So the price is a dropdown- but you don't care about that. The important thing here are the data: the recordset which populates the dropdown rows.

Since rst1 is a recordset, it has some fields, let's call them field0, field1. You call field1 in this way: rst1![field1], or rst1!("field1"), by name, or you can say rst1!Fields(1).

If you have to refer to the control in the form, the way is Me.[Price].Column(1) but, as you said, this only returns the currently selected value in the control. Use a recordset instead.

RecordsetClone isn't the only way to open a recordset. You have examples of how to use DAO in Access' Help. Search for DAO at the table of contents.

Hope this helps

Clon
  • 1,035
  • 8
  • 12
  • If the price is a dropdown and I wanted the column with the text returned and not the ID then how would I do that with the DAO? – Andy Jun 20 '13 at 17:06
  • To add to that, I mean that MsgBox "" & rst1.[Price].Column(1) isn't valid – Andy Jun 20 '13 at 17:34