Populate Word Fields from Access Continuous Form (1 Viewer)

Hey All,
I have a form that I have exported certain fields into a word doc (it is up and running just fine). I created bookmarks in word and put some VBA into my access form, so when I click on 'Create word report' it pops up and automatically populates the record I am on. Here is the tough question,

Does anyone know how to make this work with a continuous form? My main form has several subforms, one being a continuous form. The main form shows one bridge at a time. The sub continuous form shows information for all of the bridges spans (could be anywhere from 1 to 9).

Any help would be much appreciated!!

Thanks all.

use the query in the subform. That has the data you need.

Following Ranman256's suggestion, if there's a filter applied at form level you can use the form's Recordset Clone and remember to save the changes before calling it. Here's some pseudocode:

Not sure if I was totally clear on what I am trying to do or if I just don't understand the suggestions given to me. There is no filter on my forms and I have all of the information that I need in my forms. I am exporting certain fields in my form to a word doc. In the word doc I created bookmarks for where I want those fields to go. Below I inserted the code I used to accomplish this, with "fldDeckType" as an example of one of the bookmarks. I am trying to figure out if I can export information from a continuous form. So, if for one bridge I have two spans, I want those two span types to show up in a table and continue to fill the table until all spans are filled in. The recordsetclone sounds likes it on the right track, just not sure how to incorporate that into my code.


Private Sub cmdPrint_Click()
'Print customer slip for current customer.
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("U:\58038 - HSR - Railroad Retainer #2\Tech_Prod\Hudson Line Inspection - 2015\Database\template.docx", , True)
With doc.FormFields("fldDeckType").Result = [Forms]![frm_Data_Entry]![sbfrm_Data_Entry_Span]![Deck_Type]
.Visible = True
.Activate
End With
Set doc = Nothing
Set appWord = Nothing
Exit Sub
errHandler:
MsgBox Err.Number & ": " & Err.Description
End Sub

Have you researched the methods proposed?

A form is a recordset, a recordset is like a table/query but one that is accessible through code. The effective way of looping through the records in your continuous form is by getting a hold of the recordset (i.e. the table/query) that's bound to your form. The snippet of code retrieves the table/query bound to your form and allows you to loop through it. Makes sense?

With regards filters, your users are allowed to apply filters on the form aren't they? If they apply any filters the recordset of your form can only be retrieved using the RecordsetClone.

The Dirty part just takes care of any unsaved changes.

vbaInet said:

Following Ranman256's suggestion, if there's a filter applied at form level you can use the form's Recordset Clone and remember to save the changes before calling it. Here's some pseudocode:

Code:Copy to clipboard

dim rs as dao.recordset

if me.dirty then me.dirty = false

set rs = me.recordsetclone

do while not rs.eof
[COLOR="Blue"]    ... do what you want to do here...[/COLOR]
    rs.movenext
loop

set rs = nothing

Click to expand...

I'm a newbie trying to understand VBA; mind if I try to break down your code and see if I understand it well enough?

declaring "rs" as a record set data access object

What is the point of this? If the form has been modified since it was last saved, this automatically saves the form?

Setting the variable "rs" as a clone of all the data currently in the form?


This is pretty much you telling Access to go through each record in the continuous form until it reaches the end, correct? That's how .eof with loops work?

I haven't learned how to go about it, but I would imagine this could print word documents for each record in the record set, correct?

Is the point of this just so if he wants to go to another record in the form and wants to start this whole process over again, it gives a blank slate? Would this code not still work if this weren't there?

Thank you in advance.

BlueJacket, without taking this thread off on a tangent, answers to your questions.
"declaring "rs" as a record set data access object" Yes

"this automatically saves the form?" Yes

"Setting the variable "rs" as a clone of all the data currently in the form?" Not exactly. Me.recordsetclone contains all the records making up the form's recordsource. Setting rs = Me.recordsetclone creates another recordset containing all the same records ie a copy.

"this is pretty much you telling Access to go through each record in the continuous form until it reaches the end" Yes

"set rs = nothing" This releases all reference to the recordset and clears memory allocated to it. This should happen when the object goes out of scope. I'm old school; if I open an object, I close it when done, if I create an object I set it to nothing when done.

The OP should get the message from all of this that instead of using the value of the control on the continuous form, loop through the underlying recordset.