asked on

using access data in word ?

i need to make a report in word (a word report). I need data from an access database I have made - the first set is student details and needs to look roughly like a mail merge label (everything must be on one page though). It is just one set of student details which is determined by an input query (parameter query). I don' tknow if i have to do this in access or in word. Below this is a set of units which the student is enrolled in - the enrolment table has foreign keys (one to many) from both the student table and the units table:
Units ---> Enrolment <--- Student
The parameter required is the StudentId - (primary key) and just one student record is returned to Word.
There may be several enrolled units listed for the student, and they must all be on the one page (seemingly ruling out mail merge ?)
I have been sifting through access and word help for hours now, and have not found a suitable solution.
It is not necessary for the form button in access to magically produce the word document on the screen (thank god), but the more seamless the solution is the better.
I hope I have provided enough information for you to help me, and eagerly await any comments you can give
thanks
adam

ASKER CERTIFIED SOLUTION

Anne Troy

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

adamjsawyerj

ASKER

sorry dream that page won't load for me :(
i think i'll do the report in access (only take 5 mins) - i agree it's stoopid to do it in word
(it's an assignment)
if the lecturer agrees to this i'll pop back and accept your answer,
but until then other comments could be useful

thanks heaps
adam

Anne Troy

Adam:

Not to push, but the Meister's way is really the only way. While you might not be able to get to that link now, you should be able to get to it later. It does work.

She also refers to MS KB article: http://support.microsoft.com/?kbid=105888

And has a couple of sample downloads.

adamjsawyerj

ASKER

i have resolved some of the issues through fiddling around myself - I made the query in access and am using mail merge in word to display the records.
Now I am trying to make a macro to read the studentID and adjust the mail merge query to match only those records with that studentID
I am however receiving run time error 5638 - (something about being unable to parse the sql statement)
Keep in mind that in the access database StudentId is a Long Integer, and the variable "stid" i have declared in the macro code is Long.
here is the code: (very raw at the moment, will rewrite more elegantly when I get it working - just a test macro right now)

-------------------------------------------------------------------------------------------------------------------
Sub temp()
'
' temp Macro
' Macro recorded 3/11/2003 by Adam
'
Dim stid As Long

stid = CLng(InputBox("Enter student number", "Student Number Input"))
ActiveDocument.MailMerge.DataSource.QueryString = _
"SELECT * FROM [qryStudentEnrolmentDetails-StudentInfoQuery] WHERE (([Student_Id] = stid))" _
& ""
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With
End Sub
--------------------------------------------------------------------------------------------------------------------

any ideas are most welcome
cheers
adam

Anne Troy

Sorry that I couldn't help you more, Adam. I don't personally code, but I know lots of resources. Anyway, I couldn't comment on your code.

I hope you eventually found what you needed and, if you did, would you mind posting what fixed your runtime error?

adamjsawyerj

ASKER

i actually did heaps of research in vba help, msdn, other forums and have built up some passable coding skills. I have another question i recently posted (only 65 points - i am sooo poor :( ) asking how to check the results of the query in the macro before trying to mail merge it in order to avoid having to use an error handler after the fact. if noone can help me i'll get by with what i have, but having had extensive experience in several programming languages it just doesn't feel right. My problem really is lack of resources - the help in word for vba doesn't give a list of flags and what values they represent :( i appreciate your help - in fact you've successfully answered just about every office related question i've posted here (sadly i only just recently worked out how to "accept" an answer)
If you know someone with vba coding experience who can help i'd be very grateful
ta heaps
adam

Anne Troy

Consider it done. We'll get someone in here.

I'm really glad I could help.
Don't worry about being poor. We all are in some way!
:)

Anne Troy

Experts: Anyone helping Adam toward a solution to his problem here will receive a "Points for" question from me for 500 points and an A grade (of course!).

adamjsawyerj

ASKER

you rock dreamboat!
btw - how can i get more points without buying them... can i do web surveys or some such thing, or is a cc the only answer...?
how often do my points go up naturally...
thanx
adam

Suat M. Ozgur

Hi Adam,
Hi Dreamy,

First of all, code should be like following I think:

'----Code Start----
Sub temp()
'
' temp Macro
' Macro recorded 3/11/2003 by Adam
'
Dim stid As Long

stid = CLng(InputBox("Enter student number", "Student Number Input"))
ActiveDocument.MailMerge.DataSource.QueryString = "SELECT * FROM [qryStudentEnrolmentDetails-StudentInfoQuery] WHERE (([Student_Id] =" & stid & "))"
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With
End Sub
'----Code End---

I don't know if I could visit your question tonight again (it's my birthday :) ), but you can ask here to make it more useful for you. I'll try to help in my VBA & DB knowledge.

Suat

Anne Troy

Suat:

Happy birthday, and please get your 500 points at https://www.experts-exchange.com/questions/20787907/Points-for-smozgur.html

You earned them!!

adamjsawyerj

ASKER

dreamy,
in response to your earlier posting about the run-time error:

the 5638 was due to me not constructing the sql query statement correctly:
i was saying " studentid = stid" but stid is a variable name so it needed to be "studentid = " & stid = " bla bla bla

further down the track i was getting run-time error 5631, which was when the query returned no results due to either the student being not enrolled in any units, or no student with that student number. I gave up trying to run the query with no error, I just couldn't do it. So I just wrote an error handling routine that gave a friendly message instead of the raw crash that was happening. I didn't bother to re-prompt for another studentid or anything like that - i just couldn't be bothered. While I was on this track, I handled another type of error (different error code) where the user had entered something like text instead of a student number - us programmers have to cater for the most stupid of users - but hey, that's half the fun! ;)

Anyway it's all sorted now, and I hope other people can learn from this experience.
Following is the complete set of code:

Sub LoadMailMerge()
'
' LoadMailMerge Macro
' Macro created 4/11/2003 by Adam
'
Dim MsgInt As Integer

' Turn off errors to handle manually - defer error to error handling routine

On Error GoTo 0
On Error GoTo ErrorHandler

' Open the mail merge document

Documents.Open FileName:=ActiveDocument.Path & "\MailMerge-Enrolments.doc"

Dim stid As String

stid = InputBox("Enter student ID for enrolment details listing", "Student ID Entry Box")

' Get query according to the entered student number

ActiveDocument.MailMerge.DataSource.QueryString = _
"SELECT * FROM [qryStudentEnrolmentDetails-StudentInfoQuery] WHERE (([Student_Id] = " _
& stid & "))" & ""

' Perform the mail merge

' 3/11/03 - MUST FIND A WAY TO NICELY BREAK OUT OF INVALID QUERY!!!!!!!!!!
' 5/11/03 - Have now got an error handler but want to find a way to check the query
' before the error happens in the first place, then maybe give the user
' another go at entering a valid student number

ActiveDocument.MailMerge.DataSource.QueryString = _
"SELECT * FROM [qryStudentEnrolmentDetails-StudentInfoQuery] WHERE (([Student_Id] = " _
& stid & "))" & ""

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With

.Execute Pause:=True
End With

' Close the original booting document, leaving the mailmerge document
' and the merged document

Documents("Word Report - Student Enrolment Details.doc").Close SaveChanges:=wdDoNotSaveChanges

' Avoid error handling if there is no error

Exit Sub

' Error handler

ErrorHandler:

'Error 5631 is when the student ID does not return any results from the query

If Err.Number = 5631 Then
MsgInt = MsgBox("The student is not enrolled in any competencies", vbOKOnly)
Else
If Err.Number = 5638 Then
MsgInst = MsgBox("You did not enter a valid Student ID!", vbOKOnly)
Else
If Err.Number <> 0 Then
MsgInt = MsgBox("Error #" & CStr(Err.Number) & " has occurred.", vbOKOnly)
End If
End If
End If

End Sub

Suat M. Ozgur

Thanks, Dreamy!

It seems asker already solved it, so I haven't deserved any the points :) But really nice to be seem in the thread LOL!!
(it was about stid variable that the asker already mentioned in response to you)

Suat

Anne Troy

Just take the points, SUAT!!
:)