I have a database of film events, for which i have to do posters.
With the click of a button on a form, access opens word and puts all the fields in, including a field called [photo], which is the name of the image stored outside of the database.
Is there a way of getting that image into the word Document with all the other fields? Or have I just wasted an afternoon....
I am having the strangest results with my automated mailmerge. Basically it does work, but not all the time. The basic idea is to allow the user to dynamically create a query that produces a result list which fills a temporary table. The use then selects a prebuilt merge template and merge is executed against the temp table. The merge template are of the .doc type, but sme have been converted to .docx; the .doc files tend to work most often, but all of the will eventually get a Table is locked message... However if I run in test mode with code breaks and manually step through the process it always works... here is the heart of the code ...
Err_Pos = 10
Code:
DoCmd.SetWarnings False
' if tmp tbl left over from last run kill it
DoCmd.RunSQL "Drop table Word_Merge_Tmp_TBL"
Err_Pos = 12
[Code].....
There are many error cases in the error catch routine. That I have managed to make Access stop hanging when word has a problem or the table is locked. But I can't get the table to be free consistantly and why does it always work when I manually step through the code.
Dear Guru,
I have an issue which having read some previous threads may not be resolved simply. My database is password protected.
The password (presumably) is preventing the mailmerge from connecting the deata with the template. There is not even a password request shown. Is there any way that this can be overcome. There are a number of mailmerges but all are pulled from the same data query, can i unprotect this query only??
After convincing my boss that the dbase can easily run securely, your help will be very much appreciated.
Yours
Andrew - In sunny Hull, UK.
I just started having an issue with a mailmerge launching form in Access. A button I've set up, called "Order Form," uses the following code to launch Word, merge the data into the document, save the document, and close out the template. It's a large sample, and I think it may have come from this website at some point last year:
<---------START--------->
Private Sub OrderForm_Click()
'creates an SQL statement to be used in a query def
'On Error GoTo ErrorHandler
Dim val As String
Dim db As Database
Dim rec As DAO.Recordset
Dim strSQL As String
Dim strDocumentName As String 'name of the template document
Set db = CurrentDb
Set rec = db.OpenRecordset("SELECT Order_ID FROM tmpCurrentTGOrderID;")
While Not rec.EOF
val = rec("Order_ID")
rec.MoveNext
Wend
rec.Close
'Select all records from the record table were the table's Order_ID field matches
'that of the temporary table's.
'qry = "SELECT TG_Orders.Order_ID, TG_Orders.Order_Date, TG_Orders.Order_HonoredPerson, TG_Orders.Order_TreeGiver, TG_Orders.Order_PlantingState, TG_Orders.Order_Product, TG_Orders.Order_Type, TG_Orders.Order_Line1, TG_Orders.Order_Line2, TG_Orders.Order_Card, TG_Orders.Order_Occasion, TG_Orders.Order_First, TG_Orders.Order_Middle, TG_Orders.Order_Last, TG_Orders.Order_Have, TG_Orders.Order_Digits, TG_Orders.Order_CardType, TG_Orders.Order_Comments, TG_Orders.Order_Donate, TG_Customers.*, TG_Shipping.* FROM (TG_Customers INNER JOIN TG_Orders ON TG_Customers.Customer_ID = TG_Orders.Customer_ID) INNER JOIN TG_Shipping ON TG_Orders.Order_ID = TG_Shipping.Order_ID;"
strDocumentName = "TG_OrderForm.doc"
strSQL = "SELECT TG_Orders.Order_ID, TG_Orders.Order_Date, TG_Orders.Order_Confirmation, TG_Orders.Order_HonoredPerson, TG_Orders.Order_TreeGiver, TG_Orders.Order_PlantingState, TG_Orders.Order_Product, TG_Orders.Order_Type, TG_Orders.Order_Line1, TG_Orders.Order_Line2, TG_Orders.Order_Card, TG_Orders.Order_Occasion, TG_Orders.Order_First, TG_Orders.Order_Middle, TG_Orders.Order_Last, TG_Orders.Order_Have, TG_Orders.Order_Digits, TG_Orders.Order_CardType, TG_Orders.Order_Comments, TG_Orders.Order_Donate, TG_Customers.*, TG_Shipping.*, TG_Orders.Order_Cost, TG_Orders.Order_Remembrance "
strSQL = strSQL + "FROM (TG_Customers INNER JOIN TG_Orders ON TG_Customers.Customer_ID=TG_Orders.Customer_ID) INNER JOIN TG_Shipping ON TG_Orders.Order_ID=TG_Shipping.Order_ID "
strSQL = strSQL + "WHERE TG_Orders.Order_ID=" + val
Call SetQuery("TG_OrderFormQuery", strSQL)
Dim strNewName As String 'name to save merged document as
strNewName = "Order " & Format(CStr(Date), "MMM dd yyyy")
Call OpenMergedDoc(strDocumentName, strSQL, strNewName)
Exit Sub
ErrorHandler:
MsgBox "Error #" & Err.Number & " occurred. " & Err.Description, vbOKOnly, "Error"
Exit Sub
End Sub
Private Sub SetQuery(strQueryName As String, strSQL As String)
On Error GoTo ErrorHandler
'set the query from which the merge document will pull its info
Dim qdfNewQueryDef As QueryDef
Set qdfNewQueryDef = CurrentDb.QueryDefs(strQueryName)
qdfNewQueryDef.SQL = strSQL
qdfNewQueryDef.Close
RefreshDatabaseWindow
Exit Sub
ErrorHandler:
MsgBox "Error #" & Err.Number & " occurred. " & Err.Description, vbOKOnly, "Error"
Exit Sub
End Sub
Private Sub OpenMergedDoc(strDocName As String, strSQL As String, strReportType As String)
On Error GoTo WordError
'opens an instance of word, opens a merge template which has its data source
'already linked to a query in this database, merges the template,
'saves the merged file with a descriptive name, then closes the merge template
'Set the directory for any labels generated
Const strDir As String = "D:LOA-DataMerge Templates"
Dim objWord As New Word.Application
Dim objDoc As Word.Document
objWord.Application.Visible = True
Set objDoc = objWord.Documents.Open(strDir & strDocName)
' Make Word visible so that if any errors occur, you can close the instance of Word manually
objWord.Application.Visible = True
'merge to a new document
'if you are not sure of the SQLStatement to use in your OpenDataSource string, uncomment the following four lines to have the 'current SQLstatement print in the immediate window. You can then copy the returned string to your code
'Debug.Print objWord.Application.ActiveDocument.MailMerge.DataS ource.QueryString
'objWord.Quit
'Set objWord = Nothing
'Exit Sub
objDoc.MailMerge.OpenDataSource _
name:="D:LOA-DataLOAv817.mdb", _
LinkToSource:=True, AddToRecentFiles:=False, _
Connection:="QUERY TG_OrderFormQuery", _
SQLStatement:="SELECT * FROM `TG_OrderFormQuery`"
'notice that this is not the SQL statement that makes up the QueryDef of the query. It
'is the SQL statement that tells Word whether to use all the records returned by the
'Query. Notice also the funky single quotes – this is what DataSource.QueryString returned
'to me in the immediate window. I’ve also seen the query name written in 'brackets [ ],
'but have never tested this code with them.
objDoc.MailMerge.Destination = wdSendToNewDocument
objDoc.MailMerge.Execute
'save the merged document with a descriptive name
'you can delete this line if you want to leave the document with the default name “Labels 1” or “Letters 1”
objWord.Application.Documents(1).SaveAs (strDir & "" & strReportType & ".doc")
'close the merge template
objWord.Application.Documents(2).Close wdDoNotSaveChanges
'release the variables
Set objWord = Nothing
Set objDoc = Nothing
Exit Sub
WordError:
MsgBox "Err #" & Err.Number & " occurred." & Err.Description, vbOKOnly, "Word Error"
objWord.Quit
End Sub
<---------END--------->
Just today, this code started auto-minimizing the MS Word window, causing user error (after the documents been minimized, instead of clicking on the task bar, the user would click the button again, prompting that the document is already in use, would you like to save changes before closing the document, etc). Is there anything in this code that is telling Word to minimize? It's only happening on this one button, and there are 7 other mailmerge buttons on the same form, using the same code, that work fine.
Any help would be greatly appreciated,
Nate
We need to replicate an Access report we have in Microsoft Word. The report has a fixed, small image in the header and so we embedded it in the report (it is not in an external file). To put this image in the Word document the only way we have come up with is shown in the code below.
Code:
Dim apWord As Word.Application
Dim doc As Word.Document
Set apWord = CreateObject("Word.application")
doc.Shapes.AddPicture "G:ImagesSinful Banner.bmp", False, True, 0, 0, 540, 42
Which requires an external image file. We really would like to avoid this. We could make a template Word document, but that too would be an external file. We know how to put this image in a table as an OLE object, but can't find any way to get it from the table into the Word document.
If I use the word template named "ABC.dot" as attached and write the following piece of code to print off the letters with different appropriate background , it works perfectly fine:
Code:
Sub PrintLetters()
Application.DisplayAlerts = False
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Long
Set cn = New ADODB.Connection
[Code] ....
But if I combine two templates together in one template as attached "Capita.dot" then it doesn't display background picture at all in any of the cases. I am using the same piece of code but the background image is not displaying. The background image shows logos for each letter like For capita letters , the logo will be capita . For Friends Life letters the logo background will be different.
I have been printing reports in pdf format, and the jpg company logo is blurry; my boss did not approve.....
Yet the same image in Word or Excel prints very clear.
I tried bmp, png, tif, etc., with no luck.
I finally figured out that if I check "Standard" publishing in Access then my images are clear.
But, I have to check the Standard box every time I print.
Is there a way to change the default pdf printing in Access to Standard???
I have an intermediate level experience in access.I am a private investigator and have created a beautiful access database to manage my case intakes, case progress, investigators, clients 8nvoices, expenses and a whole much more for my business.
I have integrated word merge in my database to generate final reports etc.
how can i merge an image that is attached to a record to word?
Basically i create a new case and input all pertinate information for that specific case and in that case I add an image of the subject, google maps for the residence and other images such as facebook screenshots etc.. and what i want to do is when I merge the case with word to also have a page in word to include this images.
I need to know if exist the system in Access 2007 to output a report in word document including images. Normally the output is "*.rtf", with a quite good quality but due to the file type it doesn't include the pictures.
View 1 Replies View RelatedHi
Can anyone help?
I am trying to perform a mail merge from Access database table and using Word letter setup with all the necessary variables.
I am using VBA in access to perform this task.
All works ok to the point but the first snag I had when
the Access was not open in Read-Only mode and where
'OpenDataSource' statement was refusing to merge the database to the letter
I managed to overcome this by opening Access in Read-Only mode.
However I hit the second snag.
When running 'OpenDataSource' statement the pop up box comes with option to select a table I want to make connection too.
The table is listed in the within the parameters for the statement, so it does not make sense why is this question.
This is my code:-
Dim WordApp As Word.Application
Set WordApp = CreateObject("Word.Application")
MergePATH = Application.CurrentProject.Path & ""
With WordApp
.DisplayAlerts = wdAlertsNone
.Visible = False
.Documents.Open (MergePATH & "MTSheet3.DOC")
With WordApp.ActiveDocument.MailMerge
.OpenDataSource _
Name:=MergePATH & "io.mdb", _
LinkToSource:=True, AddToRecentFiles:=False, _
Connection:="TABLE tmpTable"
.Destination = wdSendToNewDocument
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute
End With
.ActiveDocument.SaveAs (MergePATH & "Dummy.doc")
.ActiveDocument.Saved = True
.ActiveDocument.Close
.Quit
End With
Set WordApp = Nothing
Can anyone explain what the problem ?
Thanks
The is holding up progress of a big project
I'm looking to add a button to my Customers form which will mailmerge the current record to a Word template and then save the Word doc as a new file (Ideally the customer's name).
I've looked at the Super Easy Mailmerge but I can't work out how to implement it without all of the variables (selecting documents etc.).
The files will all be saved to one location (C:CustomersExports) and this won't change.
This is also the location of the mailmerge template (C:CustomersExportsTemplate.docx)
Please help
I am creating a form that merges data in text fields to a word document using the bookmark method. For one part of the form i need to browse for images and then insert them into a word document. Is there any way i could create a subform that will store multiple image pathways and then merge the images to a word document using a command button.
Any suggestions would be fantastic.
Please help me i feel like crying.
Thanks
Half the man i use to be
Chris
I have a form that I would like to update a picture on using VBA. The source of the picture path is in part a query that is not bound to the form. So far I have the following code that is pretty much working, but with a couple flaws.
Code:
Private Sub Form_Current()
LoadDefaultPicture
End Sub
Sub LoadDefaultPicture()
Dim db As DAO.Database
[Code] ....
This is working. However, when I change the record the picture flashes the current picture once and then loads the new picture. It is like it reloads the current picture then loads the new one. I'm hoping there is a way to get rid of the flash.
Also, the code fails here:
strDefaultPictureName = rs.Fields("AttachmentName")
When the query does not return a record. I can definitely fix this by adding an if statement to check for a record, but I'm kind of perplexed at why it is failing at that line. I would expect it to assign an empty string to that variable name and then fail on the next command where I try and set the ".Picture" property of the image.
I have code that automatically send emails out from an Access Customer Contacts Database. I am using Access and Outlook 2007 but the code needs to work with later versions of Access and Outlook.
I have very poor knowledge of coding and usually manage to cobble something together from looking at other code on the net but don't understand most of it.
I have the following code which works perfectly except I want to be able to embed an image in the email body (not have the image as an attachment but actually show it in the body of the email).
Most of the code I have found around this topic is too complex for me to understand and utilise within the context of the code I have.
Ideally I want to take the image from an attachment field in a table returned by the "tblMailingList_Query".
Code:
Private Sub Command10_Click()
Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
[Code] .....
I'm trying to create an export to word but am having some trouble. I keep getting the following error;
Run time error '91'; variable or object of With block not defined.
This error occurs at Set TblWord = docNew.Tables.Add(Selection.Range, 3, 5)
I have a feeling its 'Selection' that is causing the problem but I can't work it out!
Code:
Dim AppWord As Word.Application
Dim DocWord As Word.Document
Dim TblWord As Word.Table
[Code]......
I have just started to develop a database that will export data directly into a word template. I have used Word automation quite a lot but I'm new to trying to automate Word from Access.
It's going OK at the moment, I have got the db to open up the template, write data and then close. My objective is to add the data to multiple tables within word. So I have created several tables in my word template and then tried to select these tables and write to the them. Everything is thing for the first table but for any other table I get an error message saying that the member of the collection doesn't exit i.e. the table isn't there. I select the table using:
Code:
objWord.selection.tables (2).select
I then used:
Code:
objWord.selection.tables.count
To show how many tables were in the document and it doesn't matter how many there are, it always says there is 1 table.
why it can only see 1 table and what I can do to get around it?
I'm doing a project for my work. I created a few reports in Access. Some of these reports are simple graphic bars. How can I insert these reports into a word document template?
View 2 Replies View RelatedI often create contract using mail merge. I have an access file that I want to use as data source for word file. But it does not automatically.
Please see the attached file !
If there are 1 customer and 1 property, I do not need to do anything. Conversely, if there are many customers and many properties, I take time to manipulate.
Firstly, I open the word file. I have to copy and paste paragraphs that I want. Highlight of the original paragraphs is blue.
Secondly, I click 'Insert Word Field' -> select 'Next Record'.
In short, I want to use VBA in access file to automatically perform the steps that I have outlined.
I need to create a word file (it needs to be word) based on template (not a very complicated template) max 1 to 1 and 1/2 pages long.
the word doc needs to be named from the recordset but for now assume 12345.doc and the next one will be 12346.doc etc (I have a unique number system - available from tables /query .
i can either make the word doc in code or use a template (template would be better) recordset could have 20-30 in it each time i run it - but to be run every week
second half is how to email this out. i had in mind a email system
email to . test@testhotmail.xyz
file attach = this folder where the docs are store and attach 12345
in a loop
so either
create word file - email word file in a loop each time or create all the word files and then send them individual
I was tasked to create an application where by the user enters keywords into an Access form, and when he clicks the button, it will run the keywords against the file names stored in the table and automatically open the Word document that is the best match.
I have created a table query called Directory, which contains FPath (Z:), FName (Document1.doc) and Directory (Z:Document1.doc).
Code:
Private Sub Command2_Click()
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim filepath As String
Dim strSearch As String
If IsNull(Me.txtSearch) Or Me.txtSearch = "" Then
MsgBox "Please type in your search keyword.", vbOKOnly, "Keyword Needed"
[Code] ....
This is the code that I am currently using to search and open the Word documents, however, this code only manages to open Microsoft Word program without loading any documents. Also, there are no error messages when I click the submit button.
I have a form that I have exported certain fields into a word doc (it is up and running just fine). I created bookmarks in word and put some VBA into my access form, so when I click on 'Create word report' it pops up and automatically populates the record I am on. Here is the tough question,
How to make this work with a continuous form? My main form has several subforms, one being a continuous form. The main form shows one bridge at a time. The sub continuous form shows information for all of the bridges spans (could be anywhere from 1 to 9).
I like to do following task using access VBA:
1. Open outlook.
2. Select word file(with tables and graphics) and insert into new outlook email.
I am running an Access 2003 report that outputs to an Excel Spreadsheet The parameter query has two paramerters First Date and Last Date. The report runs from an Button OnClick event. I need to include the two dates in the 'name' of the spreadsheet as below
Private Sub btn_report_between_dates_Click()
DoCmd.OutputTo acOutputQuery, "qry_all_calls_between_dates", acFormatXLS, "Calls By Between Dates " First Date" and " Last Date" - Date Report Run " & Format(Date, "dd-mm-yyyy") & ".xls", True
End Sub
(btw I know it is preferable to use the TransferSpreadsheet method, but I've not got around to that way yet)
Is it possible to update the data for a chart graph in a word document from Access using VBA?
View 4 Replies View RelatedI have a Form with multiple comboboxes and listboxes whose selections are assembled into a query. The combo-box selection goes into an IF-ELSE statement for a selection check (IsNull) and if there is no selection made, it is supposed give me all values (Blanks & Non Blanks)
Here is a sample of my code:
Code:
If IsNull(Me.cbReg.Value) Then
RegStrng = " Like '*'"
Else
RegStrng = Me.cbReg.Value
RegStrng = "= " & RegStrng
End If
I have several If-Else statements here and a final query assembly at the bottom of the code page which is as follows
Code:
MasterSql = "SELECT DISTINCT blah-blah-blah" & _
" INTO some more blah-blah" & _
" FROM even more blah-blah-blah" & _
" WHERE dbo_mytable.[Reg#]" & RegStrng & _
" AND the results from other If-Else statements similar to above"
Here is where the problem comes in:
I see the mistake in my If-Else statement
Code:
If IsNull(Me.cbReg.Value) Then
RegStrng = " Like '*'"
Like * means it will show me all rows where there are NON-Blanks. However, it skips all Blank Data.
What should the If IsNull() statement look like if I want to show all the values?
If there was only one combo-box and there was no selection made, then the resultant query should show me all results rather than only the results where there is some sort of data within the column filtered by the combobox.
I'm about to connect my DB to word. So I made a form where the user has to choose what entry to export. The data is inserted in a word file in different bookmarks. But there is one special task to insert the rich text so in this case the html formatted text is displayed like this:
Code:
<html><div>asdf</div></html>
My Text is inserted like this:
Code:
wdApp.ActiveDocument.Bookmarks("-Bookmarkname-").Range.Text = Lrs("-ColumnName-")
Lrs is a Recordset.
So how is it possible to display the html code right in the Word doc?