UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Run-Time 438: Object doesn't support this property or method    
 
   
KStarosta
post Jun 29 2010, 11:57 AM
Post #1

UtterAccess Addict
Posts: 246
From: Fredericksburg, VA
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
Go to the top of the page
 
+
Doug Steele
post Jun 29 2010, 01:04 PM
Post #2

UtterAccess VIP
Posts: 17,629
From: Don Mills, ON (Canada)
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"


or

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"
Go to the top of the page
 
+
LPurvis
post Jun 29 2010, 01:06 PM
Post #3

UtterAccess Editor
Posts: 13,753
From: England (North East / South Yorks)
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.
Go to the top of the page
 
+
KStarosta
post Jun 29 2010, 01:13 PM
Post #4

UtterAccess Addict
Posts: 246
From: Fredericksburg, VA
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!
Go to the top of the page
 
+
KStarosta
post Jun 29 2010, 01:19 PM
Post #5

UtterAccess Addict
Posts: 246
From: Fredericksburg, VA
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"


or

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
Go to the top of the page
 
+
LPurvis
post Jun 29 2010, 01:25 PM
Post #6

UtterAccess Editor
Posts: 13,753
From: England (North East / South Yorks)
>> 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.
Go to the top of the page
 
+
KStarosta
post Jun 29 2010, 01:36 PM
Post #7

UtterAccess Addict
Posts: 246
From: Fredericksburg, VA
QUOTE (LPurvis @ Jun 29 2010, 02: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.



OK. Thanks for your help.
Go to the top of the page
 
+
Doug Steele
post Jun 29 2010, 01:59 PM
Post #8

UtterAccess VIP
Posts: 17,629
From: Don Mills, ON (Canada)
What was the exact error you got?
Go to the top of the page
 
+
KStarosta
post Jun 29 2010, 02:09 PM
Post #9

UtterAccess Addict
Posts: 246
From: Fredericksburg, VA
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!
Go to the top of the page
 
+
Doug Steele
post Jun 29 2010, 02:20 PM
Post #10

UtterAccess VIP
Posts: 17,629
From: Don Mills, ON (Canada)
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
Go to the top of the page
 
+
KStarosta
post Jun 29 2010, 02:25 PM
Post #11

UtterAccess Addict
Posts: 246
From: Fredericksburg, VA
YES! Thank you very much!! (IMG:style_emoticons/default/thumbup.gif)


Not to be a pest, but............any thoughts on getting that data into a table? (IMG:style_emoticons/default/iconfused.gif)



- Keith
Go to the top of the page
 
+
Doug Steele
post Jun 29 2010, 02:41 PM
Post #12

UtterAccess VIP
Posts: 17,629
From: Don Mills, ON (Canada)
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.
Go to the top of the page
 
+
KStarosta
post Jun 29 2010, 02:45 PM
Post #13

UtterAccess Addict
Posts: 246
From: Fredericksburg, VA
QUOTE (Doug Steele @ Jun 29 2010, 03: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.



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

- Keith
Go to the top of the page
 
+
LPurvis
post Jun 30 2010, 01:14 AM
Post #14

UtterAccess Editor
Posts: 13,753
From: England (North East / South Yorks)
Hi

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.
Go to the top of the page
 
+
KStarosta
post Jun 30 2010, 03:54 AM
Post #15

UtterAccess Addict
Posts: 246
From: Fredericksburg, VA
(IMG:style_emoticons/default/notworthy.gif) (IMG:style_emoticons/default/notworthy.gif) (IMG:style_emoticons/default/notworthy.gif)

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
Go to the top of the page
 
+
LPurvis
post Jun 30 2010, 04:24 AM
Post #16

UtterAccess Editor
Posts: 13,753
From: England (North East / South Yorks)
You'd put the Bookmark selecting code before the table creation.
i.e. the second line in your procedure as shown.

Cheers.
Go to the top of the page
 
+
KStarosta
post Jun 30 2010, 04:59 AM
Post #17

UtterAccess Addict
Posts: 246
From: Fredericksburg, VA
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. (IMG:style_emoticons/default/thumbup.gif)

-Keith
Go to the top of the page
 
+
LPurvis
post Jun 30 2010, 05:08 AM
Post #18

UtterAccess Editor
Posts: 13,753
From: England (North East / South Yorks)
No worries.
Go to the top of the page
 
+
« Next Oldest · Access Automation · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 21st May 2013 - 01:14 PM