asked on

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

mbizup

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

wsturdev

ASKER

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.

mbizup

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

mbizup

I beleive that would work, provided the focus is in the subform.

wsturdev

ASKER

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.

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wsturdev

ASKER

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.

mbizup

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?

mbizup

>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)

wsturdev

ASKER

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?

mbizup

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

wsturdev

ASKER

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.

mbizup

A couple of other things to try...

Me.Refresh

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

wsturdev

ASKER

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.

wsturdev

ASKER

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.

wsturdev

ASKER

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.

https://www.experts-exchange.com/questions/20686350/We-are-trying-to-automate-reports-with-graphs-we-may-get-one-graph-then-the-rest-are-a-generic-template-graph.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.

wsturdev

ASKER

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. "

mbizup

Thanks for the credit, and for posting the URL. That is good to know.

wsturdev

ASKER

In all fairness, the following works:

For I = 1 To 750
DoEvents
Next I

but it slows processing down a lot.

mbizup

You may want to also consider a timer event, but I have had limited success with them.