?
Solved

Bookmark on Sub Form

Posted on 2006-04-17
20
Medium Priority
?
386 Views
Last Modified: 2011-09-20
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
Comment
Question by:wsturdev
  • 10
  • 10
20 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 16470726
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
 
LVL 1

Author Comment

by:wsturdev
ID: 16471058
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
 
LVL 61

Expert Comment

by:mbizup
ID: 16471302
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 61

Expert Comment

by:mbizup
ID: 16471312
I beleive that would work, provided the focus is in the subform.
0
 
LVL 1

Author Comment

by:wsturdev
ID: 16471388
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
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 16471513
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
 
LVL 1

Author Comment

by:wsturdev
ID: 16473143
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
 
LVL 61

Expert Comment

by:mbizup
ID: 16473717
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
 
LVL 61

Expert Comment

by:mbizup
ID: 16473989
>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
 
LVL 1

Author Comment

by:wsturdev
ID: 16474777
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
 
LVL 61

Expert Comment

by:mbizup
ID: 16474977
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
 
LVL 1

Author Comment

by:wsturdev
ID: 16479902
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
 
LVL 61

Expert Comment

by:mbizup
ID: 16483484
A couple of other things to try...

Me.Refresh

and also... What happens if  you  place the movenext afer the Bookmark and repaint?  
0
 
LVL 1

Author Comment

by:wsturdev
ID: 16486939
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
 
LVL 1

Author Comment

by:wsturdev
ID: 16486957
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
 
LVL 1

Author Comment

by:wsturdev
ID: 16487416
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
 
LVL 1

Author Comment

by:wsturdev
ID: 16487451
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
 
LVL 61

Expert Comment

by:mbizup
ID: 16487557
Thanks for the credit, and for posting the URL.  That is good to know.  
0
 
LVL 1

Author Comment

by:wsturdev
ID: 16487604
In all fairness, the following works:

            For I = 1 To 750
                DoEvents
            Next I


but it slows processing down a lot.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 16488261
You may want to also consider a timer event, but I have had limited success with them.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question