Refresh command question

[Guest]

I have a form that run’s some SQL’s using the DoCmd.RunSQL And then I need
to refresh my data but the only way it seems to work is with a Command button
routine

Private Sub cmdRefresh_Click()
On Error GoTo Err_cmdRefresh_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Exit_cmdRefresh_Click:
Exit Sub

Why won’t it work with just the following code after the DoCmd.RunSQL
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
It's the same thing as the Command button with out the goto err, Right?

 

[Ken Snell \(MVP\)]

Probably is a timing issue -- the code in your second example probably runs
before Jet has completed the updates to the tables. In the first case, most
likely enough time passes before the refresh routine is called. You could
try inserting a DoEvents code line between the RunSQL line and the refresh
code line.

Or you could try inserting this line of code between the RunSQL and the
refresh lines:
DBEngine.Idle dbRefreshCache

This also should cause the updates to be written to the tables before the
refresh is done.

 

[Guest]

No luck using the code for db Refresh
DBEngine.Idle dbRefreshCache

 

[Ken Snell \(MVP\)]

Did you try the DoEvents code line?

DoCmd.RunSQL "SQLStatement"
DoEvents
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Otherwise, you'll need to "identify" a way to let enough time go past before
doing the refresh -- this may mean that you must call another sub/routine
just as you're now doing.