Export data from Access to Word

[awaham]

Remove alert | Edit | Delete | Change type
Question
You cannot vote on your own post
0
Hi

I am hoping someone can help me. I have designed a database in access to enter patient data for a research study. I want to use some of the data recorded to generate patient letters (in Word) which can be printed and sent to patients. I have googled and being a complete novice at VB I have generated this code which does seem to work for the most part (see below). However, I am getting stuck at one particular point which is that 2 of the fields in the Access database I created using the 'look up wizard' which refers to another table in Access to get the lookup list. One of these is a list of 40 different medications (Field names AHT1 to AHT7) that can be chosen and the other being the frequency (AHT1 Freq to AHT7 Freq) these medications are taken. So the problem is now that using this bookmark methods the extracted data is all correct except the two fields I mentioned above (medication name and frequency). The extracted data for these fields are being displayed as integers, which I think is the unique ID for the medication/frequency in the corresponding lookup tables. I wonder if there is a code to display the actual text names rather then their codes being extracted over into the Word document.

Also, some of the fields are calculated and I want to only display data up to say 1 decimal point but I am getting a full 9 decimal point data extracted over, again is there a code for this that I could use?

Any help would be much appreciated.

Public Sub ExportStudyData()

Dim wApp As Word.Application
Dim wDoc As Word.Document
Dim rs As DAO.Recordset

Set wApp = New Word.Application
Set wDoc = wApp.Documents.Open("C:\Users\awais\Dropbox\Work\Observational Study\Clinic Letters\Study Visit.docx")
Set rs = CurrentDb.OpenRecordset("Study Data")

If Not rs.EOF Then rs.MoveFirst

Do Until rs.EOF
wDoc.Bookmarks("HTNDuration").Range.Text = Nz(rs![HTN Duration], "")
wDoc.Bookmarks("SmokingStatus").Range.Text = Nz(rs![Smoking Status], "")
wDoc.Bookmarks("PackYears").Range.Text = Nz(rs![Smoking pack years], "")
wDoc.Bookmarks("AlcoholIntake").Range.Text = Nz(rs![Alcohol intake], "")
wDoc.Bookmarks("AHT1").Range.Text = Nz(rs!AHT1, "")
wDoc.Bookmarks("AHT1Dose").Range.Text = Nz(rs![AHT1 Dose], "")
wDoc.Bookmarks("AHT1Freq").Range.Text = Nz(rs![AHT1 Freq], "")
wDoc.Bookmarks("AHT2").Range.Text = Nz(rs!AHT2, "")
wDoc.Bookmarks("AHT2Dose").Range.Text = Nz(rs![AHT2 Dose], "")
wDoc.Bookmarks("AHT2Freq").Range.Text = Nz(rs![AHT2 Freq], "")
wDoc.Bookmarks("AHT3").Range.Text = Nz(rs!AHT3, "")
wDoc.Bookmarks("AHT3Dose").Range.Text = Nz(rs![AHT3 Dose], "")
wDoc.Bookmarks("AHT3Freq").Range.Text = Nz(rs![AHT3 Freq], "")
wDoc.Bookmarks("AHT4").Range.Text = Nz(rs!AHT4, "")
wDoc.Bookmarks("AHT4Dose").Range.Text = Nz(rs![AHT4 Dose], "")
wDoc.Bookmarks("AHT4Freq").Range.Text = Nz(rs![AHT4 Freq], "")
wDoc.Bookmarks("AHT5").Range.Text = Nz(rs!AHT5, "")
wDoc.Bookmarks("AHT5Dose").Range.Text = Nz(rs![AHT5 Dose], "")
wDoc.Bookmarks("AHT5Freq").Range.Text = Nz(rs![AHT5 Freq], "")
wDoc.Bookmarks("AHT6").Range.Text = Nz(rs!AHT6, "")
wDoc.Bookmarks("AHT6Dose").Range.Text = Nz(rs![AHT6 Dose], "")
wDoc.Bookmarks("AHT6Freq").Range.Text = Nz(rs![AHT6 Freq], "")
wDoc.Bookmarks("AHT7").Range.Text = Nz(rs!AHT7, "")
wDoc.Bookmarks("AHT7Dose").Range.Text = Nz(rs![AHT7 Dose], "")
wDoc.Bookmarks("AHT7Freq").Range.Text = Nz(rs![AHT7 Freq], "")
wDoc.Bookmarks("OtherMeds").Range.Text = Nz(rs![Other Drugs], "")
wDoc.Bookmarks("SBPTru").Range.Text = Nz(rs![SBPTru Avg], "")
wDoc.Bookmarks("DBPTru").Range.Text = Nz(rs![DBPTru Avg], "")
wDoc.Bookmarks("BMI").Range.Text = Nz(rs!BMI, "")
wDoc.Bookmarks("WHR").Range.Text = Nz(rs![WH Ratio], "")
wDoc.SaveAs2 "C:\Users\awais\Dropbox\Work\Observational Study\Clinic Letters\First Letter" & rs![Anonymous ID] & "_Study Visit.docx"

wDoc.Bookmarks("HTNDuration").Range.Delete wdCharacter, Len(Nz(rs![HTN Duration], ""))
wDoc.Bookmarks("SmokingStatus").Range.Delete wdCharacter, Len(Nz(rs![Smoking Status], ""))
wDoc.Bookmarks("PackYears").Range.Delete wdCharacter, Len(Nz(rs![Smoking pack years], ""))
wDoc.Bookmarks("AlcoholIntake").Range.Delete wdCharacter, Len(Nz(rs![Alcohol intake], ""))
wDoc.Bookmarks("AHT1").Range.Delete wdCharacter, Len(Nz(rs!AHT1, ""))
wDoc.Bookmarks("AHT1Dose").Range.Delete wdCharacter, Len(Nz(rs![AHT1 Dose], ""))
wDoc.Bookmarks("AHT1Freq").Range.Delete wdCharacter, Len(Nz(rs![AHT1 Freq], ""))
wDoc.Bookmarks("AHT2").Range.Delete wdCharacter, Len(Nz(rs!AHT2, ""))
wDoc.Bookmarks("AHT2Dose").Range.Delete wdCharacter, Len(Nz(rs![AHT2 Dose], ""))
wDoc.Bookmarks("AHT2Freq").Range.Delete wdCharacter, Len(Nz(rs![AHT2 Freq], ""))
wDoc.Bookmarks("AHT3").Range.Delete wdCharacter, Len(Nz(rs!AHT3, ""))
wDoc.Bookmarks("AHT3Dose").Range.Delete wdCharacter, Len(Nz(rs![AHT3 Dose], ""))
wDoc.Bookmarks("AHT3Freq").Range.Delete wdCharacter, Len(Nz(rs![AHT3 Freq], ""))
wDoc.Bookmarks("AHT4").Range.Delete wdCharacter, Len(Nz(rs!AHT4, ""))
wDoc.Bookmarks("AHT4Dose").Range.Delete wdCharacter, Len(Nz(rs![AHT4 Dose], ""))
wDoc.Bookmarks("AHT4Freq").Range.Delete wdCharacter, Len(Nz(rs![AHT4 Freq], ""))
wDoc.Bookmarks("AHT5").Range.Delete wdCharacter, Len(Nz(rs!AHT5, ""))
wDoc.Bookmarks("AHT5Dose").Range.Delete wdCharacter, Len(Nz(rs![AHT5 Dose], ""))
wDoc.Bookmarks("AHT5Freq").Range.Delete wdCharacter, Len(Nz(rs![AHT5 Freq], ""))
wDoc.Bookmarks("AHT6").Range.Delete wdCharacter, Len(Nz(rs!AHT6, ""))
wDoc.Bookmarks("AHT6Dose").Range.Delete wdCharacter, Len(Nz(rs![AHT6 Dose], ""))
wDoc.Bookmarks("AHT6Freq").Range.Delete wdCharacter, Len(Nz(rs![AHT6 Freq], ""))
wDoc.Bookmarks("AHT7").Range.Delete wdCharacter, Len(Nz(rs!AHT7, ""))
wDoc.Bookmarks("AHT7Dose").Range.Delete wdCharacter, Len(Nz(rs![AHT7 Dose], ""))
wDoc.Bookmarks("AHT7Freq").Range.Delete wdCharacter, Len(Nz(rs![AHT7 Freq], ""))
wDoc.Bookmarks("OtherMeds").Range.Delete wdCharacter, Len(Nz(rs![Other Drugs], ""))
wDoc.Bookmarks("SBPTru").Range.Delete wdCharacter, Len(Nz(rs![SBPTru Avg], ""))
wDoc.Bookmarks("DBPTru").Range.Delete wdCharacter, Len(Nz(rs![DBPTru Avg], ""))
wDoc.Bookmarks("BMI").Range.Delete wdCharacter, Len(Nz(rs!BMI, ""))
wDoc.Bookmarks("WHR").Range.Delete wdCharacter, Len(Nz(rs![WH Ratio], ""))

rs.MoveNext
Loop

wDoc.Close False
wApp.Quit

Set wDoc = Nothing
Set wApp = Nothing
Set rs = Nothing
End Sub

 

[pbaldy]

I haven't exported to Word, but I export to Excel a lot. I assume you're exporting a table? Instead, create a query that joins that table to the lookup table so you can return the text value instead of the number. You can also use the Round() function to export a rounded value. Then export that query instead of the table.

 

[Cronk]

Alternatively, use the dLookup() function.

I'm guessing at the names of the fields in your lookup tables but say the the lookup table is called tblMedications and the field names are
AHT1 => ID value
MedicationName

Then you would put

wDoc.Bookmarks("AHT1").Range.Text = Nz(dLookup("MedicationName", tblMedications", "AHT1=" & rs!AHT1), "")

 

[awaham]

Hi Cronk. I have tried putting the code as you suggested but I get this error:
Runtime error '2471':
The expression you entered as a query parameter produced this error: 'AHT1'

Any idea what this means?

BTW my lookup table is named 'Drugs' and it has 2 columns 'ID' and 'DrugName'.

 

[Cronk]

So you have
wDoc.Bookmarks("AHT1").Range.Text = Nz(dLookup("DrugName", "Drugs", "ID=" & rs!AHT1), "")
Is this the line that generates the error?

 

[Mark_]

awaham,

As you have seven sets of fields prefaced with "ATH", I would suggest you look into normalizing your data. Redundant data like this in one table normally scream "Put me in a child table" which can reduce a LOT of your work. It will also help when you start running into more than seven values that you need to deal with.