MS Access/VB Gurus required

dtw · 2006-05-24 15:55:30

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.

murffatksig · 2006-05-24 18:07:05

On this line,

dtw wrote:

   objWord.MailMerge.OpenDataSource _
      Name:="M:LEARNINGADMINLibraryLibrary.mdb", _
      LinkToSource:=True, _
      Connection:="QUERY Overdue Books"

dont you need something like this

SQLStatement:= "Select blah blah blah......", _

dtw · 2006-05-25 07:10:09

Ahhhhhh! Yeah, possibly! A SELECT * FROM [Overdue Books]

type thing?

dtw · 2006-05-25 12:42:01

Nope, syntax error...

murffatksig · 2006-05-25 14:19:33

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.

dtw · 2006-06-15 11:46:33

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!

dtw · 2006-06-15 12:38:51

      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 :?

Arch Linux

#1 2006-05-24 15:55:30

MS Access/VB Gurus required

#2 2006-05-24 18:07:05

Re: MS Access/VB Gurus required

#3 2006-05-25 07:10:09

Re: MS Access/VB Gurus required

#4 2006-05-25 12:42:01

Re: MS Access/VB Gurus required

#5 2006-05-25 14:19:33

Re: MS Access/VB Gurus required

#6 2006-06-15 11:46:33

Re: MS Access/VB Gurus required

#7 2006-06-15 12:38:51

Re: MS Access/VB Gurus required

Board footer