Changing The Way Data Is Displayed (for A Merge To MS Word)
Sep 23, 2004
Hi,
I am new to Access. I am a speech therapist and am building a database to keep up with my student's progress. I have set up the database satisfactorily (if rudimentarily, at this point), but am now trying to merge the data to a form on a MS word document that we use to file medicaid. Most of my tables and queries have one to many relationships because each student has several goals and in each therapy session we usually work on more than goal. On the medicaid form the information is organized by date. The only way I can envision to get the data to merge to the medicaid form is to use as the source a table or query where there is a single record for each date. But all the tables and queries I have figured out to build so far have multiple records with the same date, if we worked on more than goal in that session. (i.e. there is one record per each separate goal). For example, if you are visual like me.......
This is what I've got:
[date 1] [goal 1] [percentage for goal 1]
[date 1] [goal 2] [percentage for goal 2]
[date 1] [goal 3] [percentage for goal 3]
but this is what I need:
[date 1] [goal 1] [percentage for goal 1] [goal 2] [percentage for goal 2] [goal 3] [percentage for goal 3]
Is there any way to run a query that will reorganize the data in this way? I thought for a minute that a crosstab query would do it, but I don't need to perform a calculation, I just need to reorganize the way the data is displayed. .....perhaps something with a pivotform??? I haven't figured those out yet.
Thanks for any help.
Shan
View Replies
ADVERTISEMENT
Jul 13, 2005
I am using merge in Word to import a calculation from an Access query. The query calculation displays to 2 decimal points by setting format and decimal places - no problem here. In the Word merge field, the calculation is displayed to about 15 decimal places. I only want two decimal places. Any ideas?
Thanks
John
View 1 Replies
View Related
Mar 9, 2014
I 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.
View 1 Replies
View Related
Mar 11, 2014
I 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 download 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 wish to use VBA in access file to automatically perform the steps that I have outlined.
View 3 Replies
View Related
Feb 27, 2006
Hi everyone. I need help on this one
I have a form with a subform. The subform is basically the sale history of the item on the main form (The main form shows a record and then the subform reports its history). This all works fantastic, except that on the subform, every time a new record is entered, it goes to the bottom of the previous one, so after a while, if I want to enter more history into the subform, I have to scroll down through all the older ones to get to the more recent ones.
How can I do it so that in the subform the most recent entry is always at the top and each time a new record is entered, the older ones move down one. This way the most recent records are always on display and older ones move down as they become less important. Likewise, the empty record used to enter data is also at the top!
Thanks!!
View 14 Replies
View Related
Mar 7, 2006
I have a form that is linked to a table. tblContacts
On the form I have added a listbox which has every possible Contact (Name) listed. I wanted to be able to click on a contact name and then have the form bring up all the information related to the contact.
right now i have on the click of the listbox a msgbox that gives me the contact id associated with the contact name.
BTW, this is a project i am taking over from someone. If it was my choice I would be creating a web app.
View 2 Replies
View Related
Oct 6, 2005
I have set up a form which has 2 combos.
Combo A pulls from a query which gets surgery address1, address2, and surgeryID. It displays only the first 2 fields.
Combo B gets info from a query getting staff firstname, surname, title, staffID and surgeryID, and shows the first 3 fields.
I have the following code in the afterupdate of box A (surgery_combo)
Private Sub surgery_combo_AfterUpdate()
staff_combo = 0
staff_combo.Requery
End Sub
HERE IS THE PROBLEM:
The queries seem to work fine, as box B displays the staff that work at each surgery. However on selecting a staff member, you can only SELECT the ones who have a salutation (mr/mrs etc) in the first column. And when you DO select this - the combo shows a most unhelpful "Mr" whereas I want the name there.
How do I change the column that the combo displays once selected?
View 10 Replies
View Related
Apr 27, 2006
Im having a problem getting this code to work.
It is meant to fire up Word (which it does), open up a new doc based on the template (which it also does) and then populate a field in the doc with data from the query table (which it doesn't).
I've put the fields in the Word doc via firstly, defining them as Custom Document Properties in the File, Properties box, and then as "{ DOCPROPERTY "<fieldname>" *MERGEFORMAT }" codes in the text.
The field in the Word doc is type 'text' and the field in Access is type 'text', so I am stumped by the "Type Mismatch" error it gives when attempting to get/open the recordset.
It hasnt even got to transferring the values across to the doc.?
Ive not done this before (Access newbie) and even found creating the custom fields in Word not as straighforward as I expected!
Please can someone help?
Private Sub butDocPreview_Click()
Dim dbs As Database
Dim objDocs As Object
Dim objWord As Object
Dim prps As Object
Dim rst As Recordset
Dim strClient As String
Dim strAccountManager As String
On Error Resume Next
Set objWord = GetObject(, "Word.Application")
If Err.Number = 429 Then
'Word is not running; creating a Word object
Set objWord = CreateObject("Word.Application")
Err.Clear
End If
On Error GoTo cmdWord_ClickError
DoCmd.SetWarnings False
'Open query..
DoCmd.OpenQuery "qryClientDocHdr_Export"
intCount = DCount("*", "tmpClientDocHdr")
Debug.Print "Number of Text items: " & intCount
' Check that there is at least one line..
If intCount < 1 Then
MsgBox "No text to process; cancelling"
Exit Sub
End If
Set dbs = CurrentDb
'==stops here with "Type Mismatch" error==
Set rst = dbs.OpenRecordset("tmpClientDocHdr", dbOpenDynaset)
With rst
strClient = Nz(![Client])
strAccountManager = Nz(![AccountManager])
End With
rst.Close
...
...
...
View 3 Replies
View Related
Jul 8, 2005
I wonder if there is a way to link two tables or queries to a word doc for mail merge. I have an investigative memo and many findings associated with it. They all in Access for record keeping. Now I want to merge the main memo with all findings into a word document. The trick part is that the memo is also different so I cannot have a uniformed letter pre-defined in word. The content of memo is coming from Access too. I don't know if there is a way to do it or simply have two independent mail merges and combine two words doc in one (how?, hate to copy and paste).
Suggestions are appreciated.
Good day and night.
View 1 Replies
View Related
Aug 17, 2007
I want to use an Access Table for a mail merge but when i try to use the merge option I get a pop up asking for a password. I put in my administrator password for the database and it tells me I dont have acess to the database. It has something to do with the passwords i put on the database.
Anyone have any ideas to what I need to do to get this to work?
View 1 Replies
View Related
Feb 9, 2006
I have the below code behind a button to merge and print into MSWord and it's working wonderfully. However, how can I modify it to print 1 copy of page one, TWO copies of page 2 and one copy of page 3?
_________________________________________
Private Sub Print_Reconsideration_Click()
Dim objWord As Word.Application
'Start Microsoft Word 2000.
Set objWord = CreateObject("Word.Application")
With objWord
'Make the application visible.
.Visible = False
'Open the document.
.Documents.Open ("G:PharmacyPrior Auth Docs and DataRevised Pharmacy Denial Processes
econsideration.doc")
'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("bmkFirstName").Select
.Selection.Text = (CStr(Forms!frmDenial!MBRFirst))
.ActiveDocument.Bookmarks("bmkLastName").Select
.Selection.Text = (CStr(Forms!frmDenial!MBRLast))
.ActiveDocument.Bookmarks("bmkHRN").Select
.Selection.Text = (CStr(Forms!frmDenial!MemberNumber))
.ActiveDocument.Bookmarks("bmkAddress1").Select
.Selection.Text = (CStr(Forms!frmDenial!MBRAddress1))
.ActiveDocument.Bookmarks("bmkCity").Select
.Selection.Text = (CStr(Forms!frmDenial!MBRCity))
.ActiveDocument.Bookmarks("bmkState").Select
.Selection.Text = (CStr(Forms!frmDenial!MBRState))
.ActiveDocument.Bookmarks("bmkZip").Select
.Selection.Text = (CStr(Forms!frmDenial!ZipCode))
.ActiveDocument.Bookmarks("bmkDrug").Select
.Selection.Text = (CStr(Forms!frmDenial!DrugName2))
.ActiveDocument.Bookmarks("bmkStrength").Select
.Selection.Text = (CStr(Forms!frmDenial!Strength))
.ActiveDocument.Bookmarks("bmkDate").Select
.Selection.Text = (CStr(Forms!frmDenial!DateReceivedbyPlan))
.ActiveDocument.Bookmarks("bmkDate2").Select
.Selection.Text = (CStr(Forms!frmDenial!DateReceivedbyPlan))
.ActiveDocument.Bookmarks("bmkMDFirst").Select
.Selection.Text = (CStr(Forms!frmDenial!MDNameFirst))
.ActiveDocument.Bookmarks("bmkMDLast").Select
.Selection.Text = (CStr(Forms!frmDenial!MDLastName2))
.ActiveDocument.Bookmarks("bmkMDCred").Select
.Selection.Text = (CStr(Forms!frmDenial!Credential))
.ActiveDocument.Bookmarks("bmkFirstName2").Select
.Selection.Text = (CStr(Forms!frmDenial!MBRFirst))
.ActiveDocument.Bookmarks("bmkLastName2").Select
.Selection.Text = (CStr(Forms!frmDenial!MBRLast))
.ActiveDocument.Bookmarks("bmkMDFirst2").Select
.Selection.Text = (CStr(Forms!frmDenial!MDNameFirst))
.ActiveDocument.Bookmarks("bmkMDLast2").Select
.Selection.Text = (CStr(Forms!frmDenial!MDLastName2))
.ActiveDocument.Bookmarks("bmkMDCred2").Select
.Selection.Text = (CStr(Forms!frmDenial!Credential))
.ActiveDocument.Bookmarks("bmkFirstName3").Select
.Selection.Text = (CStr(Forms!frmDenial!MBRFirst))
.ActiveDocument.Bookmarks("bmkLastName3").Select
.Selection.Text = (CStr(Forms!frmDenial!MBRLast))
.ActiveDocument.Bookmarks("bmkAddress11").Select
.Selection.Text = (CStr(Forms!frmDenial!MBRAddress1))
.ActiveDocument.Bookmarks("bmkCity2").Select
.Selection.Text = (CStr(Forms!frmDenial!MBRCity))
.ActiveDocument.Bookmarks("bmkState2").Select
.Selection.Text = (CStr(Forms!frmDenial!MBRState))
.ActiveDocument.Bookmarks("bmkZip2").Select
.Selection.Text = (CStr(Forms!frmDenial!ZipCode))
.ActiveDocument.Bookmarks("bmkMDAddress1").Select
.Selection.Text = (CStr(Forms!frmDenial!MDAddress1))
.ActiveDocument.Bookmarks("bmkMDCity").Select
.Selection.Text = (CStr(Forms!frmDenial!MDCity))
.ActiveDocument.Bookmarks("bmkMDState").Select
.Selection.Text = (CStr(Forms!frmDenial!MDState))
.ActiveDocument.Bookmarks("bmkMDZip").Select
.Selection.Text = (CStr(Forms!frmDenial!MDZip))
End With
Print_Reconsideration_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
objWord.Application.Options.PrintBackground = False
objWord.Application.ActiveDocument.PrintOut
ActiveDocument.Close wdDoNotSaveChanges
End Sub
View 2 Replies
View Related
Apr 4, 2013
I have a access database with 125 names and addresses and need to merge and send out the a doc for each one. Does anyone have experience with the multiple factor of this process. I can merge one but then have to repeat several times.
View 5 Replies
View Related
Feb 18, 2015
I am preparing a Word document for a mail shot, and need to import data from my access file. I am trying to build a formula that will take a field from the Access file and, if it is true, print some data.
So far, I have =IF(Schedule 2014=40,full page). Schedule 2014 is a column in the query I am using in this mail merge, 40 is an amount of money in that column and full page is the text that should appear in the Word document. The result I am getting is!Syntax Error, 2014 .
The remainder of the information I am merging is working well. I am asking for the 40 to appear in the relevant recipient's letter which is fine, but I want it to print full page before it (or half page for 30, or quarter page for 20)
View 1 Replies
View Related
Oct 11, 2005
I have produced a A2k db for distribution to others. Part of the db is a function that links to some word docs via hyperlinks. The word docs have been set up as mail merge docs to a table in the db.
When used in Office 2000 this function is faultless but when installed on a computer running Office 2003 they sometimes get a problem when they open the doc in that the mail merge toolbar is greyed out. It seems as if the doc can't find the db. Has anyone else experienced this problem and know of any fix that can be applied.
I have searched the MS Knowledge base and this forum to no avail, although the MS KB did have references to similar problems (not a direct comparison of the problem) that required a service pack download to fix.
Before I tell someone to download a service pack I need to eliminate any other problem that may be causing it.
Thanks :confused:
View 2 Replies
View Related
Aug 22, 2006
I am wanting to do away with access 97 on a few computers here at work but we cant afford to buy everyone office 2003. Before I buy us access 2003 for a computer I need to know if word 97 will do a mail merge with access 2003. Or could I just buy Office 2003 package for the few computers and it will work that way for a bit more money. I think there would be a compatability issue...
View 3 Replies
View Related
Dec 13, 2006
Hi
I have written an application which uses a query for the current record and then opens a Word document, merges this record and shows the merged document for the user.
all works fine using Office 2003 - when i try it on a pc with Office 2002 - it diesn't work. Something is happening - WINWORD.EXE is running on the process list. But the document doesn't open. I have added the Microsoft Office 10 Office Library to the references but still no joy.
It's a killer - all testing was done on Office 2003 and now a key user wants to use the app - on 2002 and it doesn't work
Any ideas?
cheers
View 1 Replies
View Related
Nov 24, 2006
I have done several mail merges where you open Microsoft Word, select your data (inthis case a query from access) and them use mail merge to create the documents.
However, i need to do it the other way. I have a project in access where the user will be in access and need to do a mail merge to word, so i want the data to be sent from access to word while they are still in access, if that makes sense.
If there are any good turorials that ppl know about or what i could be looking for in order to achieve this then i would be very grateful to all who reply!!
Thanks
View 3 Replies
View Related
Aug 15, 2015
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.
View 8 Replies
View Related
Aug 16, 2012
I am trying to create a button which will merge data from specific fields from the active record in my impounded dog table/form into a pre-existing word template. I have the table & form set but am only trying to merge a few of the fields in the table. Things like the Impound ID (Primary Key), Time, Date, & Location of Seizure, Dog Breed, Colour, Markings, Age, Sex, & Collar.How would I put this in a macro or is it even possible to fit in a macro?
View 1 Replies
View Related
May 12, 2005
Hi I wonder if anyone can help? I have looked through prevous posts and cant find any answers that fit what I need to do. For reference I am using Access 97 and Word 97.
I want to be able to have a client record open in a form and click a button and for that particular record to merge with a preformatted word doc. I have created a query that gives me all the fields I need (as they are over multiple tables) and I can successfully combine this into a mail merge. But it currently takes *all* the records and I want it to only do the current one. I also want user to be able to click a button somewhere on the form that automates this process for him. Is this possible?
I have investigated transfertext and filled in what I think I should have but it seems to do nothing. It creates another (unopenable) file in the same folder as the word doc and does nothing else. But I am using the 'export word for windows merge' in the tranfertext action?
If I can only crack this I will be happy! Many thanks in advance for any help
DD
View 3 Replies
View Related
Aug 3, 2005
I had to delete a database so I exported selected records using export as a text file thinking I would get some sort of delimited text file that I could later use. Instead I got some sort of fixed length file with .............. between records and | between fields.
I'd like to convert this data to a tab delimited file.
How would I do this?
Thanks
View 12 Replies
View Related
Mar 9, 2008
I've got a mail merge letter in word using a booking system which I've built in Access, and I want to be able to access the mail merge letter (or the letter template) through that switchboard. Is this through running a certain macro, and if so, can anybody tell me what it is?
View 1 Replies
View Related
Sep 27, 2012
I have to coordinate a dbase in the US, one in the UK, and one in the KSA. I have multiple documents that may, or may not, be in each of the three dbases. I need to merge all three, and maintain them so that the document additions, as well as any changes associated with that document, (i.e. "revisions"), are updated. Having looked at access and having read through some of the Access forum questions, I understand that it can do that, but after having identified the information from the dbases that will go into access, I'm trying to establish a consistant process for maintaining and updating it. Can I export my information from each of the dbases, into an excel spreasheet, (1 for US, 1 for UK, and 1 for SA), and have the access dbase search for the document number, compare the data following the document number, and if it's the same ignore it, and if it's different, update it?
I'm trying to muddle through this and get access and excel to do as much of this as possible, as it's a very tedious and time consuming process to manually check each one, one or two times a week.
View 8 Replies
View Related
May 23, 2005
Hi guys!
I have my word template set up such that some data is copied from the open form into the document and some data is taken from a table which is created from a query based on the content of the form (the record number). This works well HOWEVER (there's always a however isn't there :rolleyes: ) where two users click on the button at exactly the same time or within a very short space of time the query only runs once and either the database crashes (bad) or the document is created for the second person with the wrong data (very bad).
How can I make it so that only one person can run the query at a time (ideally build in a delay in processing so that the query will run for them when the first person has finished...failing that a message to say please try again later)? They will always be working with a different record.
I'm a bit stuck as to what to search for in the forum for an answer to this so haven't really searched.....sorry :(
Many thanks
Jo
View 2 Replies
View Related
Jun 27, 2014
So I have the following query which works perfectly (and will be use to create a report) but when the query runs to create the report I also want it to mail merge some of the items into word to be used on address labels.
Code:
SELECT Dunmow.Date, Dunmow.Undeliverable, Dunmow.Duplicate, Dunmow.[OTM with XXXXXX], Dunmow.[Landlords Salutation], Dunmow.[LL Address 1], Dunmow.[LL Address 2], Dunmow.[LL Address 3], Dunmow.[LL Address 4], Dunmow.[LL Address 5], Dunmow.[LL Postcode], Dunmow.[Letter 1], Dunmow.[Letter 2], Dunmow.[Letter 3], Dunmow.[Letter 4], Dunmow.[Letter 5], Dunmow.[Letter 6], Dunmow.[Letter 7], Dunmow.[Letter 8]
FROM Dunmow
WHERE (((Dunmow.Date)=DateAdd("d",-7,Date())) AND ((Dunmow.Undeliverable)=False) AND ((Dunmow.Duplicate)=False) AND ((Dunmow.[OTM with XXXXXX])=False) AND ((Dunmow.[Letter 1])=False));
So thats my query, What I want it to do is to take the following
[Landlords Salutation]
[LL Address 1]
[LL Address 2]
[LL Address 3]
[LL Address 4]
[LL Address 5]
[LL Postcode]
and use it for a merge item to create the labels required.
I have a standard word document/label document I wish to paste this into.
View 3 Replies
View Related
Oct 7, 2013
I have a Word 2010 document linked to an Access 2010 data source. When a user clicks a button in Access, the Word document loads and performs a email merge using the below VBA code:
Private Sub Document_Open()
With ActiveDocument.MailMerge
.Destination = wdSendToEmail
.SuppressBlankLines = True
[Code] ....
However, as the .mailsubject part is not in the loop it is only retrieving the first Return Code. I have tried to integrate in the loop to no avail. Also, how do I add static text to the Subject, I need something like "Your Return Code" + "Return Code"..
View 6 Replies
View Related
