UtterAccess Discussion Forums > Run-Time 438: Object doesn't support this property or method

Full Version: Run-Time 438: Object doesn't support this property or method

UtterAccess Discussion Forums > Microsoft® Access > Access Automation

KStarosta

Jun 29 2010, 11:57 AM

Good afternoon. I am in a real fix here, and need to get this figured out. In an attempt to automate the process of dumping the results set of a query to a Word template, I got the following error: Run-Time 438: Object doesn't support this property or method. The block of code is below, with the offending line highlighted in red. Any assistance to why this is happening, and more importantly...how to overcome it, would be HUGELY appreciated!!

CODE

rst1.Open "[qryLoc_SubLoc]", , adOpenKeyset, adLockOptimistic, adCmdTable

If rst1.EOF And rst1.BOF Then
    MsgBox "No Location details to transfer.", , "No Records"
    End
Else
    basicModule.ActiveDocument.Bookmarks.item("Location_SubLocation").Select

    [color="#FF0000"][b]basicModule.ActiveDocument.Selection.TypeText[/b][/color]
End If

rst1.Close
Set rst1 = Nothing

---------------------------------------------------------------------------------------------------
EDIT: I left the tagging in the code block, just so you could still see the problem child
---------------------------------------------------------------------------------------------------

Thank you very much!!

Regards,

Keith

Doug Steele

Jun 29 2010, 01:04 PM

Not sure whether this is the issue or not, but the TypeText method requires an argument indicating what text is supposed to be inserted. In other words, you need something like

CODE

basicModule.ActiveDocument.Selection.TypeText Text:="some text"

CODE

basicModule.ActiveDocument.Selection.TypeText Text:=rst1!SomeField

Incidentally, I think you can simplify the code to

CODE

basicModule.ActiveDocument.Bookmarks.item("Location_SubLocation").TypeText Text:="some text"

LPurvis

Jun 29 2010, 01:06 PM

Presumably "basicModule" is an object variable pointing to a Word.Application object?
(An odd naming convention I have to admit).

The Document object doesn't have a Selection object - only the application object.
So while you can use the ActiveDocument object to select a particular Bookmark, you'll need to use the Application's Selection to use TypeText.
i.e. just

basicModule.Selection.TypeText "Some Text"

(You were also missing the text argument that you're inserting - but I'm assuming that was just a typo in the question?)

Cheers.

KStarosta

Jun 29 2010, 01:13 PM

Thank you very much for the replies, guys. I appreciate it!

A follow-on question would be, how can I get the data contained in the results set "rst1" put into a table in the word template? Can I loop through rst1, placing each value into a cell in a table? This is ULTIMATELY what I have to do. Might you have an example of how I can do this?

Thanks!

KStarosta

Jun 29 2010, 01:19 PM

QUOTE (Doug Steele @ Jun 29 2010, 02:04 PM)

Not sure whether this is the issue or not, but the TypeText method requires an argument indicating what text is supposed to be inserted. In other words, you need something like

CODE

basicModule.ActiveDocument.Selection.TypeText Text:="some text"

CODE

basicModule.ActiveDocument.Selection.TypeText Text:=rst1!SomeField

Incidentally, I think you can simplify the code to

CODE

basicModule.ActiveDocument.Bookmarks.item("Location_SubLocation").TypeText Text:="some text"

Doug (or anybody for that matter),
I used your suggestion for the simplified code, but got a compile error at the bolded section below:

basicModule.ActiveDocument.Bookmarks.item("Location_SubLocation").TypeText Text:="rst1"

Any idea why that method wouldn't compile?

- Keith

LPurvis

Jun 29 2010, 01:25 PM

>> Can I loop through rst1, placing each value into a cell in a table?

If you're asking what I think you're asking, I can't give that kind of time at the moment to answer it fully.
Someone else might help you out in the mean time. If not, I'll catch you after. (In a "Mr Miyagi" sense of "after")

Cheers.

KStarosta

Jun 29 2010, 01:36 PM

QUOTE (LPurvis @ Jun 29 2010, 02:25 PM)

OK. Thanks for your help.

Doug Steele

Jun 29 2010, 01:59 PM

What was the exact error you got?

KStarosta

Jun 29 2010, 02:09 PM

QUOTE (Doug Steele @ Jun 29 2010, 02:59 PM)

What was the exact error you got?

Doug, I don't remember the exact error I was getting, but I did somehow manage to get around it by modifying my code as follows:

CODE

rst1.Open "[qryLoc_SubLoc]", , adOpenKeyset, adLockOptimistic, adCmdTable

If rst1.EOF And rst1.BOF Then
    MsgBox "No Location details to transfer.", , "No Records"
    End
Else
    For i = 0 To rst1.RecordCount - 1

        basicModule.ActiveDocument.Bookmarks.item("Location_SubLocation").Select

        basicModule.Selection.TypeText Text:=rst1!Loc_Name '& vbCrLf & vbCrLf
        basicModule.Selection.TypeText Text:=rst1!SubLoc_Name & vbCrLf & vbCrLf

    Next i
End If

rst1.Close
Set rst1 = Nothing

That code produces some output, but not the way I need it to. I am hoping to loop through rst1, and list out each row in the results set. Currently, it goes through the results set the appropriate number of times (if rst1 has 4 rows of data, I get 4 rows output to the template), but it doesn't leave the first row. I get the same value 4 times. Why is it not moving to the next row? Also, Doug...might you know how I can place each piece of data (Loc_Name, SubLoc_Name) in their own cells within a table? I'm assuming that I would have to "build" the table as it went through the loop. Any thoughts?

Thanks!

Doug Steele

Jun 29 2010, 02:20 PM

Replace

CODE

    For i = 0 To rst1.RecordCount - 1

        basicModule.ActiveDocument.Bookmarks.item("Location_SubLocation").Select

        basicModule.Selection.TypeText Text:=rst1!Loc_Name '& vbCrLf & vbCrLf
        basicModule.Selection.TypeText Text:=rst1!SubLoc_Name & vbCrLf & vbCrLf

    Next i

with

CODE

    Do Until rst1.EOF
        basicModule.ActiveDocument.Bookmarks.item("Location_SubLocation").Select
        basicModule.Selection.TypeText Text:=rst1!Loc_Name '& vbCrLf & vbCrLf
        basicModule.Selection.TypeText Text:=rst1!SubLoc_Name & vbCrLf & vbCrLf
        rst1.MoveNext
    Loop

KStarosta

Jun 29 2010, 02:25 PM

YES! Thank you very much!!

Not to be a pest, but............any thoughts on getting that data into a table?

- Keith

Doug Steele

Jun 29 2010, 02:41 PM

Sorry, like Leigh I don't have sample code to do that readily available, and I'm afraid I'm a little pressed for time at the moment.

Try recording a macro while doing it manually to see what the VBA code looks like.

KStarosta

Jun 29 2010, 02:45 PM

QUOTE (Doug Steele @ Jun 29 2010, 03:41 PM)

Will do, Doug. I really appreciate your help this afternoon. Have a great day!

- Keith

LPurvis

Jun 30 2010, 01:14 AM

CODE

    Dim objWord As Word.Application
    Dim objDoc As Word.document
    Dim objTbl As Word.Table
    Dim rst As ADODB.Recordset
    Dim intI As Integer

    Set objWord = GetObject(, "Word.Application")

    Set rst = CurrentProject.Connection.Execute("qryLoc_SubLoc")

    If objWord.Documents.Count = 0 Then
        Set objDoc = objWord.Documents.Add
    Else
        Set objDoc = objWord.ActiveDocument
    End If

    Set objTbl = objDoc.Tables.Add(Range:=objWord.Selection.Range, NumRows:=1, NumColumns:=3 _
        , AutoFitBehavior:=wdAutoFitFixed)

    With objTbl
        .Cell(1, 1).Range.Text = "Row"
        .Cell(1, 2).Range.Text = "Loc Name"
        .Cell(1, 3).Range.Text = "Sub Loc Name"
        Do Until rst.EOF
            .Rows(.Rows.Count).Select
            objWord.Selection.InsertRowsBelow 1
            .Cell(.Rows.Count, 1).Range.Text = CStr(.Rows.Count - 1)
            .Cell(.Rows.Count, 2).Range.Text = Nz(rst("Loc_Name"), "")
            .Cell(.Rows.Count, 3).Range.Text = Nz(rst("SubLoc_Name"), "")
            rst.MoveNext
        Loop
        .Rows(1).Range.Font.Bold = True
    End With

    rst.Close
    Set rst = Nothing
    Set objTbl = Nothing
    Set objDoc = Nothing
    Set objWord = Nothing

Cheers.

KStarosta

Jun 30 2010, 03:54 AM

Thank you SO MUCH for this, Leigh!!!

I took what you posted, and modified it a bit to fit my situation. The code does everything I want it to, except place the table at the proper template bookmark. I'm pretty sure I know where this happens. Here is what I have...

CODE

'Prepare the Location/Sub-Location data
rst1.Open "[qryLoc_SubLoc]", , adOpenKeyset, adLockOptimistic, adCmdTable

Set objTbl = basicModule.ActiveDocument.Tables.Add(Range:=basicModule.Selection.Range, NumRows:=1, NumColumns:=2 _
    , AutoFitBehavior:=wdAutoFitFixed)

With objTbl
    .Cell(1, 1).Range.Text = "Region/Location Name"
    .Cell(1, 2).Range.Text = "Sub Loc Name"
    Do Until rst1.EOF
        basicModule.ActiveDocument.Bookmarks.item("Location_SubLocation").Select
        .Rows(.Rows.Count).Select
        basicModule.Selection.InsertRowsBelow 1
        .Cell(.Rows.Count, 1).Range.Text = Nz(rst1("Loc_Name"), "")
        .Cell(.Rows.Count, 2).Range.Text = Nz(rst1("SubLoc_Name"), "")
        rst1.MoveNext
    Loop
    .Rows(1).Range.Font.Bold = True
End With

rst1.Close
Set rst1 = Nothing

The first line after the "Do Until" statement is where I am trying to say, "Put the table at the bookmark named 'Location_SubLocation'". I think that the line after that is where the bookmark call gets lost. Once the code finishes, and the populated template is visible, the table appears at the very beginning of the document, above the agency logo, on the title page...instead of at its bookmark. How might I tweak this code just a bit to fix that? I tried to incorporate the ".Rows(.Rows.Count).Select" line into the line above it, but couldn't get the syntax right.

Again, thank you very much for your help!

Regards,

Keith

LPurvis

Jun 30 2010, 04:24 AM

You'd put the Bookmark selecting code before the table creation.
i.e. the second line in your procedure as shown.

Cheers.

KStarosta

Jun 30 2010, 04:59 AM

QUOTE (LPurvis @ Jun 30 2010, 05:24 AM)

You'd put the Bookmark selecting code before the table creation.
i.e. the second line in your procedure as shown.

Cheers.

Perfect!!! You've made my day!! Thanks.

-Keith

LPurvis

Jun 30 2010, 05:08 AM

No worries.

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