wsturdev
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.Recordset Clone
myTable.MoveFirst
With myTable
If Not .EOF Then
.MoveFirst
End If
Do Until myTable.EOF
Set GraphObj = Me.SubformB.Form.Vhart1.Ob ject.Appli cation.Cha rt
GraphObj.ChartArea.Copy
Set rng = wdApp.Selection.Range
wdApp.Selection.PasteSpeci al Link:=False, DataType:=wdPasteOLEObject , Placement _
:=wdInLine, DisplayAsIcon:=False
Set rng = wdApp.Selection.Range
sText = " "
wdApp.Selection.TypeText (sText)
Set GraphObj = Me.SubformB.Chart2.Object. Applicatio n.Chart
GraphObj.ChartArea.Copy
Set rng = wdApp.Selection.Range
wdApp.Selection.PasteSpeci al Link:=False, DataType:=wdPasteOLEObject , Placement _
:=wdInLine, DisplayAsIcon:=False
Set rng = wdApp.Selection.Range
sText = " "
wdApp.Selection.TypeText (sText)
Set GraphObj = Me.SubformB.Form.Chart3.Ob ject.Appli cation.Cha rt
GraphObj.ChartArea.Copy
Set rng = wdApp.Selection.Range
wdApp.Selection.PasteSpeci al 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
VVVVVVVVVVVVVVVVVVVVVVVVVV VVHere 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
^^^^^^^^^^^^^^^^^^^^^^^^^H ere 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
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.Recordset
myTable.MoveFirst
With myTable
If Not .EOF Then
.MoveFirst
End If
Do Until myTable.EOF
Set GraphObj = Me.SubformB.Form.Vhart1.Ob
GraphObj.ChartArea.Copy
Set rng = wdApp.Selection.Range
wdApp.Selection.PasteSpeci
:=wdInLine, DisplayAsIcon:=False
Set rng = wdApp.Selection.Range
sText = " "
wdApp.Selection.TypeText (sText)
Set GraphObj = Me.SubformB.Chart2.Object.
GraphObj.ChartArea.Copy
Set rng = wdApp.Selection.Range
wdApp.Selection.PasteSpeci
:=wdInLine, DisplayAsIcon:=False
Set rng = wdApp.Selection.Range
sText = " "
wdApp.Selection.TypeText (sText)
Set GraphObj = Me.SubformB.Form.Chart3.Ob
GraphObj.ChartArea.Copy
Set rng = wdApp.Selection.Range
wdApp.Selection.PasteSpeci
:=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
VVVVVVVVVVVVVVVVVVVVVVVVVV
Set Me.SubformB.Form.Bookmark = myTable.Bookmark
'The above statement gets an error about Object required
Me.SubformB.Form.Repaint
^^^^^^^^^^^^^^^^^^^^^^^^^H
DoEvents 'So the computer can do other things while this loop is processing
Loop
End With
myTable.Close
Set myTable = Nothing
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.
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.
Have you tried moving directly to the next record, rather than using a bookmark and repaint?
VVVVVVVVVVVVVVVVVVVVVVVVVV VVHere 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
VVVVVVVVVVVVVVVVVVVVVVVVVV
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
I beleive that would work, provided the focus is in the subform.
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.
I also tried it with me.SubformB.SetFocus first. Same result.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.RecordsetC lone
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?
Dim rst As DAO.Recordset
Set rst = Me.subfrm1.Form.RecordsetC
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?
>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)
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)
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?
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
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?
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
DoEvents
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.
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.
A couple of other things to try...
Me.Refresh
and also... What happens if you place the movenext afer the Bookmark and repaint?
Me.Refresh
and also... What happens if you place the movenext afer the Bookmark and repaint?
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.
As for your question, the result is the same.
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.
I have tried doing a repaint on them, but to no avail.
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.
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.
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. "
Thanks for the credit, and for posting the URL. That is good to know.
ASKER
In all fairness, the following works:
For I = 1 To 750
DoEvents
Next I
but it slows processing down a lot.
For I = 1 To 750
DoEvents
Next I
but it slows processing down a lot.
You may want to also consider a timer event, but I have had limited success with them.
Dim myTable as DAO.Recordset