[RESOLVED] Word automation will not display form data

[jgriffiths]

I've been throught the forums and they've been most helpful in helping me understand automation but I can't find any thread with a solution to my problem. I want to display the contents of a form that is based on a query, not a base table. I mention this because I'm not certain it isnt relevant. On the click of the button in the Access 2003 form, i can open up word, the right template opens but no form data is passed. I can send static data that I've typed in, but not anything from the form. This is the code, can anyone point out the crucial bit I have got wrong or missed?

Public Function mergeToWord()
On Error Resume Next
Dim rsExam As Recordset, iTemp As Integer
Set rsExam = DBEngine(0).Databases(0).OpenRecordset("single emq exam query", dbOpenTable)

rsExam.FindFirst "[ID]"
  If rsExam.NoMatch Then
   MsgBox "Invalid ID Number", vbOKOnly
  Exit Function
   End If

Dim WordObj
Set WordObj = GetObject(, "Word.Application")
If Err.Number <> 0 Then
    Set WordObj = CreateObject("Word.Application")
End If
WordObj.Visible = True

WordObj.Documents.Add Template:="H:\Medicine_Exam.dot", NewTemplate:=False
WordObj.Selection.Goto what:=wdGoToBookmark, Name:="record_id"
WordObj.Selection.TypeText Forms!EMQexam![ID]
    DoEvents

WordObj.Activate

Exit Function
End Function

"single emq exam query" is the name of the query, and EMQExam is the form.
Have I totally mixed it all up?!
Any pointers would be much appreciated.

[jgriffiths]

Apologies, the I've changed "single exam emq query" to the name of the table it draws the data from, according to a source I just read. When it was the latter I was getting an error but with the table name it is again the original problem I wrote about above.

[si_the_geek]

Welcome to VBForums

To use a query I suspect that you need to change dbOpenTable to something else (as I don't use DAO, I'm not sure what it should be instead).

Your use of FindFirst looks odd to me.. I would expect something to the effect of "[ID] = 2"

To pass values from the recordset, change Forms!EMQexam![ID] to rsExam.Fields("ID").Value

[jgriffiths]

Thanks for replying Si, this is confusing me like mad!

I used FindFirst because I read on a forum that you can't use Seek with a query, but since I changed the rsExam parameter to the Questions table rather than the query, I changed it to Seek but I'm still not getting any further. No errors, but no data appearing in the word document either!

I was wondering - and this might expose my total lack of Access and VB experience - why can't I just send over the text that appears in the form fields when the record is displayed? Why do I need a recordset - when I can pass the text in a field to another form, can I just pass it to Word in the same way? Would it be something like

WordObj.Selection.TypeText Forms!EMQExam!txtID

or am I barking up the wrong tree? I did try this but no data in the word doc so maybe I am..?

[si_the_geek]

For either FindFirst or Seek, I would expect to see something to the effect of fieldname = value , just a field name seems odd - as you a searching for something, but not specifying what.

I suspect that if you remove "On Error Resume Next" (which you should almost never use - see our Classic VB FAQs for an explanation) you will get an error in this part of the code.


You can pass Form values as you had it, assuming that Forms!EMQexam![ID] contains an appropriate value. Doing that means that the recordset (rsExam) is not needed at all.

If you want to show values from the recordset, you will need to do something like I suggested.

[jgriffiths]

Ah ok, I see what you mean with the FindFirst and Seek bits. I'm not really sure what value I would be searching for there - I think the whole recordset thing has confused me because I was following an example that used all the records straight from 1 table and I was using a query which was using was filtering records from 2 tables and I'm just bewildered by it!

I think I better dispense with the recordset if I can do it just as well with the form values!

I've tried it using the example you gave me and it works using this code for a textfield, which I am very very happy about

Dim question
question = Forms!EMQexam![Qnumber]
WordObj.Selection.Goto what:=wdGoToBookmark, Name:="questionNo"
WordObj.Selection.TypeText [question]

However, one of the values is in a drop down box, and this code doesnt work for that but does when I pass it to another form to test it using the same code to declare and set the variable. Do I need to specify something like 'the value that is displayed/selected'? Or is there a way of NOT having the drop down box in the form, just a regular textfield, even though the field in the base table is a combo box?

[si_the_geek]

You shouldn't be using square brackets around question, that is only for referencing form/control names that may contain spaces.

I don't use VB in Access, but I think you need to use something like: combo1.value

[jgriffiths]

Thanks so much for your help Si, I will give the solution you suggested a go. I'm so pleased that I've been able to get this far with it. Thanks again - and for your quick replies, it is much appreciated.