• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

Bookmark on Sub Form

I have a form A with a subform B.  Subform B has as its record source a table in the database. The records displayed on Subform B can be filtered.  Subform B displays 3 graphs relating to the data in each record to be displayed.

I need to click a button on form A and run a routine that loops through the filtered records from Form B and copies the graphs from Access to Word.  As I do this it is necessary to do a "Move Next" on Form B so that each subsequent record gets displayed before copying the graphs.

I am trying to use a recordsetclone of form B to control the loop.  I believe I should be able to accomplish the "Move Next" on Form B by looping through the recordsetclone and setting the bookmark of Subform B.  Then, when I repaint Subform B the new graphs will be displayed and I can then copy them.

Here is my code.  I get an error when I try to set the bookmark of Subform B.

1. Is this an acceptable way to do this?
2. What change do I need to my code to get the display to update?

    Dim sText As String
    Set myTable = Me.SubFormB.Form.RecordsetClone
    myTable.MoveFirst
    With myTable
        If Not .EOF Then
            .MoveFirst
        End If
        Do Until myTable.EOF
            Set GraphObj = Me.SubformB.Form.Vhart1.Object.Application.Chart
            GraphObj.ChartArea.Copy
            Set rng = wdApp.Selection.Range
            wdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteOLEObject, Placement _
                :=wdInLine, DisplayAsIcon:=False
            Set rng = wdApp.Selection.Range
            sText = " "
            wdApp.Selection.TypeText (sText)
            Set GraphObj = Me.SubformB.Chart2.Object.Application.Chart
            GraphObj.ChartArea.Copy
            Set rng = wdApp.Selection.Range
            wdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteOLEObject, Placement _
                :=wdInLine, DisplayAsIcon:=False
            Set rng = wdApp.Selection.Range
            sText = " "
            wdApp.Selection.TypeText (sText)
            Set GraphObj = Me.SubformB.Form.Chart3.Object.Application.Chart
            GraphObj.ChartArea.Copy
            Set rng = wdApp.Selection.Range
            wdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteOLEObject, Placement _
                :=wdInLine, DisplayAsIcon:=False
            Set rng = wdApp.Selection.Range
            sText = " "
            wdApp.Selection.TypeText (sText)
           
            sText = vbCrLf
            wdApp.Selection.TypeText (sText)
            sText = vbCrLf
            wdApp.Selection.TypeText (sText)
   
            .MoveNext
VVVVVVVVVVVVVVVVVVVVVVVVVVVVHere is where I need the help
            Set Me.SubformB.Form.Bookmark = myTable.Bookmark
'The above statement gets an error about Object required
            Me.SubformB.Form.Repaint
^^^^^^^^^^^^^^^^^^^^^^^^^Here is where I need the help
            DoEvents 'So the computer can do other things while this loop is processing
        Loop
    End With
    myTable.Close
    Set myTable = Nothing
0
wsturdev
Asked:
wsturdev
  • 10
  • 10
1 Solution
 
mbizupCommented:
Have you listed all your code for this?  I think you need to explicitly declare myTable as a DAO Recordset at the top of the code:

Dim myTable as DAO.Recordset

0
 
wsturdevAuthor Commented:
I have done that, but just did not list it here.  When I run the code without the Bookmark commands, it runs but does not advance the screen to each record in turn, so what gets transferred to Word is the same set of three graphs for as many records as there are in the recordsetclone.

I need to get the subform to display each record in turn so that the copy of the graphs is correct for each subsequent record.
0
 
mbizupCommented:
Have you tried moving directly to the next record, rather than using a bookmark and repaint?

VVVVVVVVVVVVVVVVVVVVVVVVVVVVHere is where I need the help
            Set Me.SubformB.Form.Bookmark = myTable.Bookmark
'The above statement gets an error about Object required
            Me.SubformB.Form.Repaint
^^^^^^^^^^^^^^^^^^^^^^^^^

Try this instead:

docmd.GotoRecord,,AcNext
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mbizupCommented:
I beleive that would work, provided the focus is in the subform.
0
 
wsturdevAuthor Commented:
docmd.GotoRecord,,AcNext     gets an error: You Can't go to the specified record

I also tried it with me.SubformB.SetFocus first.  Same result.
0
 
mbizupCommented:
Try this variation on the same theme... I'm able to step through records on a subform using this code on a mainform:

Me.SubformB.Form.Recordset.MoveNext
0
 
wsturdevAuthor Commented:
Me.SubformB.Form.Recordset.MoveNext  accomplishes the movement to the next record, but the charts don't repaint before the copy is attempted.  it is as if the "Me.SubformB.Form.Repaint" is skipped.
0
 
mbizupCommented:
Okay - Lets go back to square 1.  I added this code to a test setup, similar configuration, but without the Graph:
   
   Dim rst As DAO.Recordset
    Set rst = Me.subfrm1.Form.RecordsetClone
    rst.MoveFirst
    Do Until rst.EOF
        Me.subfrm1.Form.Bookmark = rst.Bookmark
        rst.MoveNext
    Loop
    Set rst = Nothing

This is almost identical to your code... If the .movenext comes before setting the bookmark, the last record causes an error.  
Also, How are you 'filtering' your records?  Are you filtering them through the filter property, or actually modifying the recordsource of the subform?  Have you tried running this code on an unfiltered recordset?
0
 
mbizupCommented:
>If the .movenext comes before setting the bookmark, the last record causes an error.
I guess it would be more correct to say that the EOF  would cause that error.  If the last bookmark is set after the last .movenext, you are at EOF, not the last record.  (The EOF would be detected at the beginning of the next cycle, causing the loop to exit)
0
 
wsturdevAuthor Commented:
in my original code above, I had this command:

            Set Me.SubformB.Form.Bookmark = myTable.Bookmark

and it got an error.

When I changed it to (getting rid of the "set"):

            Me.SubformB.Form.Bookmark = myTable.Bookmark

the error went away and the movement from one record to another worked.

So, now I can cause the sub form to go to the next record (I can tell it does because a form header record fills in properly) by using either:

Me.SubformB.Form.Bookmark = myTable.Bookmark

OR

Me.SubformB.Form.Recordset.MoveNext

BUT, the redisplay of the graphs for that next record does not happen before the copy statement executed.

How do I "pause" the code long enough for the graphs to redisplay with new values before letting the code continue?
0
 
mbizupCommented:
This statement will pause the code while any events are executing (I haven't used many graphs in my apps, but I think this should work with any refresh or repaint of any form object):

DoEvents
0
 
wsturdevAuthor Commented:
This does not work.

In another post some time ago, I had a similar situation, but it involved displaying a different subform each time, and I controlled it with a DoEvents inside a loop to check if a calculated field on the subform was filled in or not.

In this case, the subform is just going from one record to the next and there is no calculated field.

So, I added a hidden one in order to try the same technique, but that did not work either.

Bottom line is I am successfully moveing the subform from one record to the other, but the graphs are not displaying.  In fact, what happens is the graphs are showing when I slick the button.  As the movement happens from one record to the next, each of the header fields fills in, but the graph space goes to gray, and then only finally fills in at the end of the routine behind the button.
0
 
mbizupCommented:
A couple of other things to try...

Me.Refresh

and also... What happens if  you  place the movenext afer the Bookmark and repaint?  
0
 
wsturdevAuthor Commented:
Me.Refresh somehow screws up the recordsetclone and I get an object error on the Do Until myTable.EOF.

As for your question, the result is the same.
0
 
wsturdevAuthor Commented:
Since the graphs are MSGraph objects, is there a way to test if they have completed their redraw?

I have tried doing a repaint on them, but to no avail.
0
 
wsturdevAuthor Commented:
We can stop beating our heads against the wall...

Here is a link that has a good explanation that confirms my suspicion -- that there is a timing conflict between Access and MS Graph.

http://www.experts-exchange.com/Databases/MS_Access/Q_20686350.html?query=graph+object+not+displaying&topics=39

In the above link is a another link to an article on the Microsoft Help and Support site, and inside that article, there is the following statement:

"Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article."

That statement refers back to another statement at the top of the article:

"This behavior occurs only on computers that are running Microsoft Windows 2000 or Microsoft Windows XP."

Since you have technically solved my original problem of how to move a subform from one record to the next, I am awarding you the points and closing this topic.
0
 
wsturdevAuthor Commented:
And by the way, so far, neither of the 2 suggested methods in the MS Help and Support article work.  As they say in that article, "NOTE: These methods may not work in all situations. "
0
 
mbizupCommented:
Thanks for the credit, and for posting the URL.  That is good to know.  
0
 
wsturdevAuthor Commented:
In all fairness, the following works:

            For I = 1 To 750
                DoEvents
            Next I


but it slows processing down a lot.
0
 
mbizupCommented:
You may want to also consider a timer event, but I have had limited success with them.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 10
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now