You are not logged in.
Pages: 1
I have a form which shows a record. I have a query that grabs other information based on teh record shown in that form:
SELECT contacts.contact_first_name, contacts.contact_second_name, contacts.contact_address_1, contacts.contact_address_2, contacts.contact_address_3, contacts.contact_address_4, contacts.contact_postcode, books.book_title, loans.loan_date, loans.due_date
FROM contacts INNER JOIN (books INNER JOIN loans ON books.book_id = loans.book_id) ON contacts.contact_id = loans.contact_id
WHERE (((loans.contact_id)=[Forms]![Contact Borrowing]![form_field_contact_id]) AND (([loans]![due_date])<Now()));
I then have a VB function that is _supposed_ to use the results of that query in a Mail Merge within Word.
Function MergeItNew()
Dim objWord As Word.Document
Set objWord = GetObject("M:LEARNINGADMINLibraryOverdue Letter.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the database.
objWord.MailMerge.OpenDataSource _
Name:="M:LEARNINGADMINLibraryLibrary.mdb", _
LinkToSource:=True, _
Connection:="QUERY Overdue Books"
' Execute the mail merge.
objWord.MailMerge.Execute
End Function
It worked great to begin with, as long as I had a record showing in [Contact Borrowing]![form_field_contact_id] but then it just suddenly...stoppped working. Now when it opens the Word doc to merge it asks which table I want to merge from rather than directly passing the query. I'm baffled.
Offline
On this line,
objWord.MailMerge.OpenDataSource _ Name:="M:LEARNINGADMINLibraryLibrary.mdb", _ LinkToSource:=True, _ Connection:="QUERY Overdue Books"
dont you need something like this
SQLStatement:= "Select blah blah blah......", _
"Oh, they have the internet on computers now."
Offline
Ahhhhhh! Yeah, possibly! A SELECT * FROM [Overdue Books]
type thing?
Offline
Nope, syntax error...
Offline
hmm, the line should be ...
objWord.MailMerge.OpenDataSource _
Name:="M:LEARNINGADMINLibraryLibrary.mdb", _
LinkToSource:=True, _
Connection:="QUERY Overdue Books", _
SQLStatement:= "SELECT contacts.contact_first_name, contacts.contact_second_name, contacts.contact_address_1, contacts.contact_address_2, contacts.contact_address_3, contacts.contact_address_4, contacts.contact_postcode, books.book_title, loans.loan_date, loans.due_date
FROM contacts INNER JOIN (books INNER JOIN loans ON books.book_id = loans.book_id) ON contacts.contact_id = loans.contact_id
WHERE (((loans.contact_id)=[Forms]![Contact Borrowing]![form_field_contact_id]) AND (([loans]![due_date])<Now()))"
What i'm not sure about is if you need an underscore when each line of the SQLStatement wraps to the next line. Try it both ways.
"Oh, they have the internet on computers now."
Offline
It doesn't seem to like the line breaks at ALL! I have tried various things but my searchs for line breaks in VB come up with nothing of any use.
What I have also tried is changingthe query to a create table query and then merging from that table - that works just fine.
What I really need to do now though is:
1) Link the (create table query and) merge function to a button on a form
2) After the merge, delete the temporary query table and close the mail merge datasource (it opens the whole dbase again with permission errors :S) and close the word document.
Any help on any bits of this would be GREATLY appreciated!
Offline
Connection:="TABLE loans books contacts", _
SQLStatement:="SELECT contacts.contact_first_name, contacts.contact_second_name, contacts.contact_address_1, contacts.contact_address_2, contacts.contact_address_3, contacts.contact_address_4, contacts.contact_postcode, books.book_title, loans.loan_date, loans.due_date" _
& "FROM contacts INNER JOIN (books INNER JOIN loans ON books.book_id = loans.book_id) ON contacts.contact_id = loans.contact_id" _
& "WHERE (((loans.contact_id)=[Forms]![Contact Borrowing]![form_field_contact_id]) AND (([loans]![due_date])<Now()))"
Ok - I fixed the line breaks but I get a string length error now :?
Offline
Pages: 1