Continuous form data

[Steven811]

Hi

I have created mail merge code that works fine. However, one of the subforms (subform4) is a continous form and I now wish to print all of the records from the active subform.

These are date related notes associated with the subform3 record set

The code for identifying the current field is as follows

[.ActiveDocument.Bookmarks("Comments").Select
.Selection.Text = (CStr(Me!subform1.Form!subform2.Form!subform3.Form!subform4.Form!Comments))]

How do I modify the code to identify all of the records with data so that I can export it for a mail merge.

Thanks

Steven811

 

[kjv1611]

Have you tried looping through the recordset the subform is getting data from? Something like this?

Dim rs as DAO.Recordset
Dim db as DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("tblMyTableName")

With rs
  Do While Not .EOF
    ~your code above, or similar here...
    .MoveNext
  Loop
End With

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[Steven811]

Hi

I'm using A2k and I get an error message that says "compile error, user defined type not defined"

I thought that this version used DAO but it could be using ADO to prompt that error message. But I could be wrong and this is getting past the limits of my knowledge.

Any pointers would be appreciated.

Steven811

 

[kjv1611]

Make sure you have the reference set to DAO 3.6 library:
Tools - > References

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[Steven811]

I've identified that I'm using ADO rather than DAO. I'm reluctant to change it without being aware of the implications first. Do you know what problems I might encounter?

Steven811

 

[Steven811]

Hi

I've backed up the db, changed the reference to DAO 3.6 and when I run the code it stops at my merge text .ActiveDocument and says that "method or data member not found" and I'm unsure whether this is as a result of me changing from ADO to DAO.

Anu suggestions would be appredciated.

Steven811

 

[PHV]

Seems you are confused with some With imbrication.
Replace this:
With rs
Do While Not .EOF
~your code above, or similar here...
.MoveNext
Loop
End With
By this:
Do While Not rs.EOF
~your code above, or similar here...
rs.MoveNext
Loop

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Steven811]

Thanks for taking the time to help.

You're right I am confused.

I don't understand the ref to 'some' that you make. I understand imbrication and loop.

My view was that I needed a link to the other records in the tbl through the active form via the IDkey. My knowledge of code is very limited as you can see and I can't find an example that I can edit and use.

I inculded the code that you suggested, not sure if it's correct or not, it doesn't work for me:

[Do While Not rs.EOF
.ActiveDocument.Bookmarks("field1").Select
.Selection.Text = (CStr(Me!subform1.Form!subform2.Form _
!subform3.Form!subform4.Form!field1))
rs.MoveNext
Loop]

Thanks

Steven811

For reference and info for anyone else that may want to mail merge from a form/subform I enclose the full code that I am using successfully.

[Private Sub MergeButton_Click()
On Error GoTo MergeButton_Err

Dim objWord As Word.Application


'Start Microsoft Word.
Set objWord = CreateObject("Word.Application")

With objWord
'Make the application visible.
.Visible = True

'Open the document.
.Documents.Open ("C:\MyMerge.doc")

'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("CompanyName").Select
.Selection.Text = (CStr(Forms!forCustomerDetails2!CompanyName))


'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("SiteName").Select
.Selection.Text = (CStr(Me!forSiteName.Form!SiteName))

.ActiveDocument.Bookmarks("cboDesignation").Select
.Selection.Text = (CStr(Me!forSiteName.Form!cboDesignation))

.ActiveDocument.Bookmarks("SiteAddress1").Select
.Selection.Text = (CStr(Me.forSiteName.Form!SiteAddress1))


.ActiveDocument.Bookmarks("cboManager").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!cboManager.Column(1)))

.ActiveDocument.Bookmarks("Contact11stName").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!Contact11stName))

.ActiveDocument.Bookmarks("Contact12ndName").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!Contact12ndName))

.ActiveDocument.Bookmarks("Contact1DDTelNoExt").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!Contact1DDTelNoExt))

.ActiveDocument.Bookmarks("Contact1mb").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!Contact1mb))

.ActiveDocument.Bookmarks("Contact1emailaddress").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!Contact1emailaddress))

.ActiveDocument.Bookmarks("JobNo").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!JobNo))

.ActiveDocument.Bookmarks("Description").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!Description))


.ActiveDocument.Bookmarks("cboSupplier").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!cboSupplier.Column(1)))

.ActiveDocument.Bookmarks("cboSupplier1").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!cboSupplier1.Column(1)))

.ActiveDocument.Bookmarks("cboSupplier2").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!cboSupplier2.Column(1)))

.ActiveDocument.Bookmarks("cboSupplier3").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!cboSupplier3.Column(1)))

.ActiveDocument.Bookmarks("cboSupplier4").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!cboSupplier4.Column(1)))

.ActiveDocument.Bookmarks("cboCIS").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!cboCIS))

.ActiveDocument.Bookmarks("RMIssued").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!RMIssued))

.ActiveDocument.Bookmarks("RMCustApproved").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!RMCustApproved))

.ActiveDocument.Bookmarks("RMContractorApproved").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!RMContractorApproved))

.ActiveDocument.Bookmarks("DateofComment").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!forProject2.Form!DateofComment))

'By this:
Do While Not rs.EOF
.ActiveDocument.Bookmarks("Comments").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!forProject2.Form!Comments))
rs.MoveNext
Loop


.ActiveDocument.Bookmarks("Action").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!forProject2.Form!Action))

.ActiveDocument.Bookmarks("ActionByDate").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!forProject2.Form!ActionByDate))

.ActiveDocument.Bookmarks("cboByWhom").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!forProject2.Form!cboByWhom))


'Move to each bookmark and insert text from the form.




End With

'Print the document in the foreground so Microsoft Word will not close
'until the document finishes printing.
' objWord.ActiveDocument.PrintOut Background:=False

'Close the document without saving changes.
' objWord.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
'
' 'Quit Microsoft Word and release the object variable.
' objWord.Quit
' Set objWord = Nothing
' Exit Sub

MergeButton_Err:
'If a field on the form is empty, remove the bookmark text, and
'continue.
If Err.Number = 94 Then
objWord.Selection.Text = ""
Resume Next
End If

Exit Sub
End Sub]

 

[PHV]

it doesn't work for me:
Any error message ? Unexpected behaviour ?
Which line is highlighted when debug ?
What is the code you use that raise the error ?
Please, feel free to read either faq in my sig.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Steven811]

Hi

There are no error messages and compile/debug runs okay. The mail merge stops on the comments bookmark and fails to bring the data into the document.

Regards

Steven811

 

[Zion7]

Steven811,
I took a very quick look, I noticed that once you're in the loop, you need to extract the desired value, from the recordset control , not the Form Control.

Do While Not rs.EOF
.ActiveDocument.Bookmarks("Comments").Select
.Selection.Text = (CStr(rs!Comments))
rs.MoveNext
Loop

...so your recordset, should be opening the table for subForm4.

again, this was a quick look...

Hope this helps, good luck!

 

[Steven811]

Hi

Thanks for taking a look.

Is this what you mean?

[Do While Not rs.EOF
.ActiveDocument.Bookmarks("Comments").Select
.Selection.Text = (CStr(Me!forSiteName.Form!forContactsJobTracking.Form _
!forJobTracking2.Form!forProject2.Form!Comments))
rs.MoveNext
Loop

Do While Not rs.EOF
.ActiveDocument.Bookmarks("Comments").Select
.Selection.Text = (CStr(re!Comments))
rs.MoveNext
Loop]

 

[Zion7]

Yes, otherwise the value you choose for rs.Selection.Text, will always be from the same record (record with focus, on the form).

The point of looping, is to iterate through each record, extracting the relavent value from each record. So the value you're looking for, must be from the recordset, not the form. (in this case, the form is stagnant).

Hope this helps, Good Luck!

 

[Steven811]

Hi

Please forgive my lack of understanding, I try hard but it's a struggle.

I thought that the data held in a form with more than one source was a record set.

The continuous form holds the data, in some cases 3 records sometimes 12.

It would help me if you could explain how the code goes through the record set/form as I can't relate the 2nd bit of code to the form.

Finally, what happens to the data when I move it over. The merge doc has one bookmark, this I can obviously add to if necessary. Do the number of bookmarks have to match the number records that are coming across to Word?

Rgds

Steven811

 

[Zion7]

good question Steve, about the bookmarks. I haven't worked with automation very much, to be able to answer your question, if it's necessary to have a bookmark, for every record. my 1st instinct is to say yes. (looking at the code you posted, reinforces that).

When you say, your form has more than 1 source, what do you mean? A joined table?

A recordset opened with ADO (as in this case), is a separate entity, from the form (very convenient, in many cases). Similiar concept to Me.RecordSetClone (identitical actually).
Navigating through a recordset, does not affect the form. But you could get the same results using the DoCmd.GoToRecord,,acmoveNext method.
But since there is no need to distract the user, by manipulating the Interface, why not work behind the scenes, getting the same results, with a recordset.

So regardless of how mwny records you have, 3 or 12 or 1000, the recordset will iterate through them ALL, extracting any value(s), you request/code, from each record.

You might be confusing the terminology with the forms "Recordsource"?
But, Just for the record (as I found out recently), you can Set a forms recordset, to an ADO recordset, but I don't think that's relavent right now...?

But Steve, also you must declare your variables And set them, for the recordset, as KJV pointed out. I didn't see that in your code.

I'm going to use KJV's code, to give you the idea...

Dim rs as DAO.Recordset
Dim db as DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("tblMyTableName")


  Do While Not rs.EOF
                  .ActiveDocument.Bookmarks("Comments").Select
        .Selection.Text = (CStr(rs!Comments))
    rs.MoveNext
  Loop

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

"tblMyTableName" is to be replaced, with the table/query, which is your "Recordsource" for the 4th subform (forProject2).
Try this, in a standalone module to give you an idea of the results...

Sub RecordsetValues()
Dim rs as DAO.Recordset
Dim db as DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("tblMyTableName")


  Do While Not rs.EOF
                Debug.Print CStr(rs!Comments)
    rs.MoveNext
  Loop

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

Type "RecordsetValues" in immediate window, the press enter.

Good Luck!

PS, thank-you for the code you posted, I am going to try it out, to familiarize myself more with Word automation!