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
.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 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.
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.
I have started having problems with MailMerge in my database - it has worked before but now I get this error:
Runtime error 5922
"Word was unable to open data source"
Here is my code:
Function MailMerge(strMMTemplate As String) Dim objWord As Word.Application Dim objDoc As Word.Document
Set objWord = CreateObject("Word.Application") Set objDoc = objWord.Documents.Open(fldMailmerge & strMMTemplate)
'Make Word visible. objWord.Application.Visible = True 'Set the mail merge data source objDoc.MailMerge.OpenDataSource Name:="C:cmoscmos.mdb", LinkToSource:=True, Connection:="TABLE tblMailMerge_Temp_Full", SQLStatement:="SELECT * FROM tblMailMerge_Temp_Full" 'Execute the mail merge. objDoc.MailMerge.Execute objDoc.Close
Set objWord = Nothing Set objDoc = Nothing End Function
I have searched high an low through the forum and web - but no luck.
I am using Access 2003 and have secured my database, so that it now consists of an .mdb and an .mdw file. The .mdb is opened using the security settings in .mdw using a shortcut.
I want to set up a mail merge into Word 2003, but whenever I try selecting my database as the source of the data I want to merge, it won't let me log in.
Can anyone advise me how I can get this to work correctly?
i have a stores database for all items in a warehouse. i need to be able to have a button on my form that sends the current record to a word template to be printed out on labels. i need to be able to specify the amount of labels for each record, and i need to be able to send multiple records to the same template. i have absolutely no idea how to do this.
ive got the template set up, but currently it is mailmerged the normal way, meaning it would just print every record in the DB on the labels. i was thinking of having some kind of check box in the DB so that the user can use the form to find all the records they want to print, check the box for each (and also enter a number for the amount of times they want that record printing on the sheet) and then have a button to send the final document to the printer. this brings up another issue - how do i empty the check box for every record each time the form is used so that all the previously printed records do not get reprinted?
If anyone has got any idea how to overcome any of these problems i would be extremely grateful.
This mailmerge document draws its personal information from a query in my database. And sends off personal emails to a number of donors.
I want to attach a personal document called 'Allocations.docx' which is also a mailmerge document drawing its personal information from the same query. How can I automate the attachment of this document to the email so that each donor gets their own email plus their own attachment?
Can something be added to the existing line of VB which will do what I want? If so, what do I need to add?
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;"
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.
I am trying to generate letters from ACC2003 db into Word2003. Reason for Word is so client can edit letters if required. I have set up a maketable query to generate a temporary table in the Db and the mailmerge master document all formatted and pointed at the temporary table. I still need to get the automation running so that the letters merge and print on a command button. My immediate issue is that the address lines are intentionally spread across 5 fields Addr1, Addr2, Addr3, Town, Postcode. Not all fields in every record are full, and with the query it would be impossible to eliminate fields on the chance they might be empty. As a result my mailmerge has blank address lines which do not look professional. Can anyone point me to how I might solve this given my proposed use - if it was a report then I have a little code module that neatly eliminates blank lines but I cannot see how I could integrate this into the mailmerge process. Thanks for any help or pointers
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)
I have installed Microsoft Access 2003 and Microsoft Access 2003 Developer Extensions. I can create Access application by using the Package Wizard. But after I upgrade from Microsoft Access 2003 to Microsoft Access 2007. I can't use the Package Wizard.
Hi, I have been using "fSetAccessWindow (SW_HIDDEN)" in the open event of my main switchboard to hide the grey Access screen from being visible behind forms, reports, etc in this db. But when I started using a .vbs script file to launch the db, the Access window stopped being hidden. (The reason I use the script for launching is that it temporarily sets the db's security level to 1 which stops the macro security warning from coming up.) Apparently MS knows about this happening and released a technique to rectify the situation, but I don't understand their instructions. It looks like they expect one to have more understanding than I have. Could someone help me get this impliemented? Here is the article: http://support.microsoft.com/kb/167659/en-us Thanks!
Hi! This is Kishore, working on VB Project which is using MS-Access95 as backend. Now, i want to change the Database login Password. Could anyone guide me in this context.
Using Access's User and Group Accounts or Using a Login Form to access database?
I've been researching on how to make a database secure. How to create User and Group Accounts on access, I see the step by step instructions and tried it out myself.
I also saw some sites where they give an example of a Login Form and how to create one.
My question is do you need to create both. First create the users and groups to permit or deny access to certain forms and then have a login form?
But would that mean that they'd have to login twice? Once when the database opens because it activates the db security that was created and then login again in the login form that was created?
Also when the user logins in and clicks on the cmd button on the form which opens up another from, frmWorkLog, I have an Employee field. This field I want it to have the user's name entered automatically and "locked". So that info, employee name, is extracted from the user's login. So then the user can only see his or her records only and no one elses.
How would I go about creating that. Hope I made my explanation clear.
I have two database applications and they are: - the (A) application is for administration use. - the (B) application is for normal users use.
the idea is that: I made the (A) application for administrators who have full control over the database objects (tables, forms, queries, and so on ...).
the (B) application I have created for normal users who will have only to use forms to insert some data and display data only.
but the two applications has a respective table called "vacation request" table. where I linked them, so the both administrators and users can share the data.
The real question is that: How can I prevent the users from seeing the database objects in their application. I used the database options which have helped me in hidding the database objectives when the users open the application, but unfortunately they managed to access to the database objects by pressing the special keys.
I would like to have an access to the (B) application when I want to make some modifications to the forms and then lock it from users where they only have to use the forms for requesting vacations and view the vacations.
I have a simple access search form , that's based on a query that fill parameters from the form textboxes, when the access form loads its keeps prompting for parameters value which looks ugly .. I want to open the form, displaying all the records in the table and filter when i click search ..
I have a data entry entry form, that i want to generate success message after successful insert in database. I have done it in the button event if no error happens, still if i left all fields blank and clicked save, it displays the message ..
I need to change that to display please fill the textboxes then click save , and display success message when the row is actually inserted ..
I've been using the following code successfully in Access 2003 & now I need to migrate to Access 2010. The purpose of the code is to use the items that the user selects in the list box to build the criteria of a query. Access 2010 keeps giving me a syntax error when I try to run the query & I don't know why:
My code is:
On Error GoTo Err_Command151_Click
' Declare variables Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim varItem As Variant Dim strCriteria As String Dim strSQL As String
[Code] .....
The syntax error I get in Access 2010 is:
Syntax Error in query expression 'SELECT * FROM qryContractListSummarybyDateContract3TYPEBREAK WHERE qryContractListSummarybyDateContract3TYPEBREAK.Rep ortableName IN('Adbri Masonry NSW');'
Attached is a screenshot of the relationships in a database I built a couple of years ago. It's worked absolutely fine in Access 2003 and currently has over 18,000 customers with associated information in it.
However, when I open the database in Access 2007 the performance is awful. All the forms are very slow to respond when tabbing between form elements. I've experimented by reducing the number of form elements calling on related data on a given page and whilst this improves performance it reduces usability - something I don't want to compromise on especially since Access 2007 should be able to cope with this.
My next question is therefore whether I've got the most efficient underlying table design and I can't see any other way of doing it than my current method so I'd be grateful for any feedback or advice anyone has.
I have a stock control database which i have nearly completed. This has Manufacturer, which is linked to products, which is linked to Sub Product(which also has field partCode). i.e. Manufacturer1 can have 3 products, and each of these products could have 5 subsystems and partcodes. Each partcode is unique to that subsystem/product/manufacturer.
I then have a pricing spreadsheet in excel, which has many tabs. A new column has been added for each item for Manufacturer,Product,Subsystem and Partcode.
I need to import these manufacturers,products,subsystems and partcodes, but into the tables with the correct relationships, i.e. product1 and product2 are products of manufacturer1 and so cannot come under manufacturer2, and so on.
I hope this makes sense, Thanks in advance for any help you can give!
I am working on a massave aplication that has been running in Access 2000, but recently several of the file sharing users have installed Access 2003 because of the limited availability of Access 2000. All the users are using the same file off the server.
The problem we are having is that when we reference a subform in the "[Forms]![FormName]![SubformName]![FeildName]" Access 2003 does not recognize it and returns an error. I have found that if I will modify it to "[Forms]![FormName]![SubformName].[Form]![FeildName]" it is recognized in both 2000 and 2003.
To try and change every instance of a subform reference will take forever and I am garuteed to overlook something. We reference subforms all over our program, missing any one of them would be a disaster. Before I went to the tedious task of looking through everything I just wanted to throw the situation out there and see if any of you had any great ideas on how to get it fixed efficiently. I would apreciate any ideas.
I have made a form on access which will be used by other employees within the company, however they have never used access before and they are wanting to access only just the form as I feel with Access and all the tools in the background will confuse them, plus I don't want them editing the data base its self.
Is there a way to only bring up the form, unless I obviously need to edit the date base.
I've done some research on Google and it seems I need to use:
SW_SHOWMINNOACTIVE
However I can not seem to find how I would use this or where?
and also if i did find a way to only bring up the form, how would I be able to switch it from that veiw to the veiw I edit in?
I upgraded a 2000 db to 2003 recently. When I ran the function below it gave me an error on the line in green. Any thoughts? Thanks. Const FIRSTROW = 13
Dim wsp As Workspace, dbv As DAO.Database, tblResolve As DAO.Recordset Dim tblVchs As DAO.Recordset, tblImpTmp As DAO.Recordset Dim ObjXLApp As New Excel.Application Dim FileToOpen As String, WhereCriteria As String, ImportTemp As String, tmpVch As String Dim TotalDupes As Integer, TotalGood As Integer, TotalRejects As Integer Dim TotalBlank As Integer, R As Integer, C As Integer Dim BadSheet As Boolean, ImpFail As Boolean Dim tmp, tmpType, tmpCtr, tmpRsn
If Forms![Import Block Vouchers]![Import Program] = "0" Then MsgBox ("You must select a Program to load.") ObjXLApp.Quit 'Exit from MS Excel Exit Function End If
I have an Access DB that I created in Access XP. It works perfectly when used in Access XP. When used in an Access 2003 environment some of the functions don't work anymore. I have users who use Office XP and users who use Office 2003. Does anybody know how to make this DB work in Access 2003 without having to do wholesale code changes?
I have already changed the security to "Low" to bypass the security feature built in in Jet 4.0 but it didn't help. I might have to uninstall Windows XP SP2 to make it work because I know that the application works in an Office 2003 environment with Windows XP SP1 only. But I'm trying to avoid having to uninstall XP SP2 because it's a pain. Any ideas would be greatly appreciated.
Thank you.
My workstation environment is as follows:
OS: Windows XP SP2 Office: Office 2003 SP1 Jet: 4.0
I've created a database in access 2003 with the default file format being Access 2000. Everything works fine on my computer and many others, all of whom are running different versions of access (2000, 2002 & 2003).
I have not had any problems with this database until recently and in the last few weeks we have had 2 users, both of them being Access 2000 users who have had the same problem.
The problem they are having is that when they click on any of the switchboard items, the buttons depress an then come back up again when the cursor is moved away, but nothing else happens - no action, no error message - nothing!!! Its the same for all of the buttons on the switchboard.
Does anyone know what is causing this? I have another computer with Access 2000 on it and this works fine, as it does on several other computers runiing Access 2000, I just cannot understand why it is not working on these 2 particular machine.......
I have recently installed access 2003 w/access 97 installed both are on my hard drive. When I create a new db in access 2003 it saves it in an access 2000 format. Does any one know why it does this?
