How to send subform data in table form to a Word document with VBA

[Josha]

So I currently have a database for my science students to record their examination notes.






On the 'examination' form the students fill out their case and item number and the lab room number. These all are static fields.... But then I have a related table that stores the students names... So I built a subform in to the main form where the students can record their names as well as their partners names using their initials, ABC (for example). Most of the time they work in partners but sometimes they are in groups of 3 and rarely in groups of 4.



Now I have a separate Word template document where the students need to add their photos of the examination (I don't want photos stored in my Access database). I get the students to press a button and it runs the following VBA code;

   

Private Sub cmd_contrecrdv2_Click()

Me.Refresh


Dim appWord As Word.Application
Dim DOC As Word.Document


'Avoid error 429, when Word isn’t open.
        On Error Resume Next
        Err.Clear
        'Set appWord object variable to running instance of Word.
        Set appWord = GetObject(, "Word.Application")
        If Err.Number <> 0 Then
        'If Word isn’t open, create a new instance of Word.
        Set appWord = New Word.Application
        End If
        Set DOC = appWord.Documents.Open(C:\Users\TeacherJDS\Examinations\2. Templates\Imaging Record.docx", , True)
        With DOC
            
            .Bookmarks("date").Range.Text = Me![Dateofexamination]
            .Bookmarks("CaseNo").Range.Text = Me![FSDCaseNum]
          


.Visible = True
.Activate
End With


Set DOC = Nothing
Set appWord = Nothing
Exit Sub


End Sub

Here is a screenshot of the an example of the Word document....



The code works beautifully - BUT I can't send the subform data (that contains a list of the student names and their partners) to a bookmark in Word??? Does anyone know how to do this? I would like it to be in a table straight underneath the header on that Word document... And it would look like...

Examined by:
ABC
XYZ
CRS

Then the students would add their photos and any other drawings.

Any help would be much appreciated

[June7]

Don't need to store photos in database in order to use them in forms and reports. Store photos in a folder next to db. Store photo name in table field. Load images using Image control.

Code can allow user to select an image and then copy it into the db images folder.

So why do you want to use Word for what can be produced with an Access report?

What do you mean by 'photos of the examination'? What are the drawing files?

Could you provide the Word template file?

[Josha]

Thanks for your reply!

I did know about Image Control.... However.... It'll be easier if I give an example...

I get the students to work in pairs to dissect 'cliche' a frog.......

I want my students to write their examination notes in Access (which works flawlessly).... As they are examining the frog I expect my students to take photos so that they can paste photos in the attached Word document, and then they can use arrows and text boxes to label organs / what they see and ect....

I definitely could do what you mentioned about the Image Control, but once the students make the report in Access they wouldn't have access to the controls such as drawing arrows / shapes and text boxes like they would in Word. That's why I want to keep it separate from my main database....


And then a student of mine had the brilliant idea to get the database to create the Word document for them by pasting basic information to the header of the template such as date and case number - which I could successfully do..... However, we want to it go a step further by sending the subform which contains a list of the students name that worked on the frog to Word in a table format..... I can't figure this part out? Obviously I can get the students to manually fill it out in Word, but surely Access can do this?

[June7]

Yes, Access can do it. Here is a start. Using your template, I am able to populate the two existing rows of students table. For an unknown number of students, code will have to be modified. Now dynamically pulling student names could involve a recordset or referencing controls on a form. If you want to provide db for analysis, I could analyze and modify code.

Sub PopulateWordTable()Dim wrdApp As Word.Application, wrdDoc As Word.Document, x As Integer
Set wrdApp = CreateObject("Word.Application")
Set wrdDoc = wrdApp.Documents.Open("folderpathhere\Imaging Record.docx")
wrdApp.Visible = True
For x = 2 To 3
    wrdDoc.Tables(1).Cell(x, 1).Range.text = "Student" & x - 1
Next
End Sub

More info https://docs.microsoft.com/en-us/pre...rom=MSDN#Y1254

[Josha]

Thanks again for your reply and your time - I really appreciate it!

Hmmmm I don't know what I'm doing wrong??

So I had a meeting with my team leaders and they don't want me to share the database here, even though I'm pretty sure there is a way remove confidential information (such as student names ect)...

So instead of I made a really simple stripped down version of the same database we use it has the same concepts .... It will be attached... It's got very little data in it.... If you could show me where you put your code in to make it work that would be great!

Again thank you for your time

[June7]

That example procedure I posted was just in a general module and I ran it manually. You need to adapt it into your existing code.

Suggest you modify the Word template so the Students table does not have any blank rows and code adds rows as needed. Or change template table to have as many rows as could possibly be needed and code just fills with whatever data. That would likely leave blank rows in the table.

This works:

Private Sub Command13_Click()
Dim wrdApp As Word.Application, wrdDoc As Word.Document, x As Integer, rs As DAO.Recordset
Set wrdApp = CreateObject("Word.Application")
Set wrdDoc = wrdApp.Documents.Open("C:\Users\Owner\June\Forums\Imaging Record.docx")

Me.Refresh

wrdApp.Visible = True
Set rs = Me.Exby_subform.Form.RecordsetClone
rs.MoveLast
rs.MoveFirst
x = 2
With wrdDoc
    .Bookmarks("date").Range.Text = Me![Dateofex]
    .Bookmarks("CaseNo").Range.Text = Me![CaseNo]
    Do While Not rs.EOF
        .Tables(1).Rows.Add
        .Tables(1).Cell(x, 1).Range.Text = rs!Student
        x = x + 1
        rs.MoveNext
    Loop
End With

Set wrdDoc = Nothing
Set wrdApp = Nothing

End Sub

[Josha]

�� I put the code in the wrong button so I was actually testing a different code that I was experimenting....

Haha but your code worked! I can't believe it!

Thanks again! Man you know your stuff - I really appreciate your time and effort





---------------------------
Thanks again!

It worked flawlessly in my test database I sent you.... SO I copied it exactly into the real student database, and it looks like it is working because I can see adds the right amount of rows to the table, however the cells are blank???? It's weird, but in my test database it works perfectly??

What could it be?

[June7]

Glad it worked.

It was a learning experience for me as well. Had to find sample code I could build from.
And I didn't have to figure out the bookmark stuff since you already had it.

[Josha]

What if the student table had two columns, one column for their initials (like you did above) but now there is a second column with their student number?

Private Sub Command13_Click()Dim wrdApp As Word.Application, wrdDoc As Word.Document, x As Integer, rs As DAO.Recordset
Set wrdApp = CreateObject("Word.Application")
Set wrdDoc = wrdApp.Documents.Open("C:\Users\Owner\June\Forums\Imaging Record.docx")

Me.Refresh

wrdApp.Visible = True
Set rs = Me.Exby_subform.Form.RecordsetClone
rs.MoveLast
rs.MoveFirst
x = 2
With wrdDoc
    .Bookmarks("date").Range.Text = Me![Dateofex]
    .Bookmarks("CaseNo").Range.Text = Me![CaseNo]
    Do While Not rs.EOF
        .Tables(1).Rows.Add
        .Tables(1).Cell(x, 1).Range.Text = rs!Student
        .Tables(1).Cell(x, 1).Range.Text = rs!Snumber
        x = x + 1
        rs.MoveNext
    Loop
End With

Set wrdDoc = Nothing
Set wrdApp = Nothing

But what happened was it put the student number in the first column, and ignored the student's initials???

Weird... Is this something you can think of a workaround for as well?

[Welshgasman]

You are using the same column number? which is 1

[June7]

x increments the row. The column is static. So as @Welshgasman noted, use 2 for the Snumber cell column reference.

How can this work if you have not changed the folder path for the Word document?

[Josha]

hahaha - why didn't I think of that!

Of course it worked

Thanks!

[Josha]

I just copied the code from above and modified - haha!

Thanks again for the help