I’m getting a peculiar response when I utilize the BeforeRefresh event to run some code. Essentially, all I want to do is refresh the dataproviders within the report in a certain order (last one first, then the rest in order) and cancel the document refresh. To do this, I use the following code:
Private Sub Document_BeforeRefresh(Cancel As Boolean)
Dim i As Integer
Dim dps As DataProviders
Set dps = ActiveDocument.DataProviders
dps.Item(dps.Count).Refresh
For i = 1 To dps.Count - 1
dps.Item(i).Refresh
Next
Cancel = True
End Sub
When I click refresh though, for some reason, the first two dataproviders are refreshed, then all the dataproviders are refreshed according to the code I have written. I can’t figure out what is happening when I click Refresh because if I put this exact same code in a seperate function and run it (not within the BeforeRefresh sub or executed by the Refresh button), it works fine, but I need to have it execute seemlessly within the BeforeRefresh sub.
Has anyone else run into something similiar? Any ideas what might be occuring?
Thanks for your response. The report I’m working with is going to be a scheduled report that must be refreshed in a certain order to increase performance on the backend database. The last dataprovider added helps with this performance and must be run first.
I tried your suggestion but the report is still refreshing too many times. To test this code, I have a report with three dataproviders. When I followed your suggestion, all three dataproviders are refreshed (in order from one to three), then the first two are again refreshed. I’m not sure what’s going on here! Any suggestions?
Yes, Default refresh not cancelling when you call refresh function inside before_refresh event. Executing default refresh first.
I have another suggestion:
why you can’t re-create the data provider in correct order
I know, that is a pain, but it would avoid coding and fighting with default functionality.
There is utility available at BOB to copy the dataprovider within the report. https://bobj-board.org/t/20948
Thanks
Copy the dataprovider, in correct order
Use those variable into the report (all tabs)
Delete old data provider
Private Sub Document_BeforeRefresh(Cancel As Boolean)
Dim i As Integer
Dim dps As DataProviders
Set dps = ActiveDocument.DataProviders
i = dps.Count
While i > 0
dps.Item(i).Refresh
i = i - 1
Wend
Cancel = True
Use something like this. Replace for loop with while. In the code you can see we can exit when ever we want
Sub Edit_DP()
Dim dps As DataProviders
Dim DP As DataProvider
Dim intDp As Integer
Dim intMsg As Integer
Set dps = ActiveDocument.DataProviders
intMsg = MsgBox(“Found " & Str(dps.Count) & _
" Data Providers. Do you want to Edit”, vbYesNo)
If intMsg = vbNo Then Exit Sub
For intDp = 1 To dps.Count
Set DP = dps.Item(intDp)
intMsg = MsgBox(“Do you want to Edit (” & DP.Name & “)”, vbYesNoCancel, _
"Data Provider " & Str(intDp) & " of " & dps.Count)
If intMsg = vbCancel Then Exit Sub
If intMsg = vbYes Then dps.Item(intDp).Refresh
Next intDp
End Sub
I was finally able to get this to work. See code below…
Private Sub Document_BeforeRefresh(Cancel As Boolean)
'disable refresh on all data providers
For Each dp In ActiveDocument.DataProviders
dp.IsRefreshable = False
Next
'cancel default refresh
Cancel = True
'run custom refresh
Call CustomRefresh
End Sub
Private Function CustomRefresh()
Set dps = ActiveDocument.DataProviders
'refresh last data provider
With dps.Item(dps.Count)
.IsRefreshable = True
.Refresh
End With
'refresh all data providers except last one
For i = 1 To dps.Count - 1
With dps.Item(i)
.IsRefreshable = True
.Refresh
End With
Next
End Function
(BOB member since 2003-02-28)
