UtterAccess Discussion Forums > Use Variable To Specify Bookmarks When Looping

Full Version: Use Variable To Specify Bookmarks When Looping

UtterAccess Discussion Forums > Microsoft® Access > Access Automation

fritz

Mar 22 2012, 02:57 AM

I'm using bookmarks to enter data into a word document template. The document has a table with 7 rows. I've created a recordset with the data I want to put in each row.

The bookmarks for each row are Test1, Test2...Test7 and the applicable code look like this:

CODE

Set objWord = CreateObject ("Word.Application")
With objWord
    .Documents.Add ("\\Svr-Db\Sub\{name of template")
    .visible = true
    .ActiveDocument.Bookmarks ("Test1").Select
    .Selection.Text = rst1!XYZ
End With

Works fine for the first record, but I'd like to loop through the recordset and have the above code increment by one on each pass:

CODE

    .ActiveDocument.Bookmarks ("Test2").Select
    .Selection.Text = rst1!XYZ
THEN
    .ActiveDocument.Bookmarks ("Test3").Select
    .Selection.Text = rst1!XYZ
THEN ...

Is there a way to do this?

Peter46

Mar 22 2012, 03:26 AM

I am guessing that all you need is..

dim x as integer

........other code

for x = 1 to 7 ' or however many bookmarks
.ActiveDocument.Bookmarks ("Test" & x).Select
.Selection.Text = rst1!XYZ
Next x

fritz

Mar 22 2012, 03:47 AM

Hi Peter 46,
Would it were so simple.

Error "Item cannot be found in the collection corresponding to the requested name or ordinal #3265"

I think there is some function that forces an evaluation of "test" & X which can then be used. But I can't recall the proper syntax

arnelgp

Mar 22 2012, 04:06 AM

You should have an error routine on what Peter46 gave you to test whether "test" & x, exists in your document.

jleach

Mar 22 2012, 04:51 AM

I'm not sure if it's the problem here, but when doing an implicit or explicit conversion from a number to a string, I'll wrap it in Trim(), as positive integers, in certain cases, turn into a string with a leading space.

So maybe:

.ActiveDocument.Bookmarks ("Test" & Trim(CStr(x))).Select

hth

fritz

Mar 22 2012, 05:06 PM

Thanks!!

arnelgp has it right - turns out the problem was with my naming of the of recordset object not the line in question (when will I learn to step through code rather than assume...

jleach I like your approach as my "x" is dim'd as Integer. Turns out though, Access vba doesn't care although I prefer converting as a policy.

BTW, do you know a way to have an error checking routine return the code which causes the problem? Here's the code loop I'm using with the red "rst1!Test4" being incorrect. I use the trapping routine generated by the button wizzard with err.Number added

CODE

 On Error GoTo Err_SSFbtn_Click
            intRecPage = 7 'Rows available in the word doc
                   Cntr = 1 'to track each row as completed
                        Do Until Cntr > intRecPage ' loop until the row counter exceeds rows available (out loop not shown generates a new page)
                            .ActiveDocument.Bookmarks("Test" & CStr(Cntr)).Select
                            .Selection.Text = rst1!Test4
                            rst1.MoveNext
                            If rst1.EOF = True Then
                                Cntr = intRecPage + 1
                            Else
                                Cntr = Cntr + 1
                            End If
                        Loop
Exit_SSFbtn_Click:
    Exit Sub

Err_SSFbtn_Click:

    MsgBox err.Description & "; #" & err.Number
    Resume Exit_SSFbtn_Click

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.