Msg Box for Showing Command is runnig

[ootkhopdi]

Hello everyone

i am running an query name "Qery1"

which is taken so long time to complete query

i want to show a msgbox in meanwhile for showing "Process in running, Please Wait"

or "Process in running ,,,,,% Complete,, Please Wait"

or "Query is ----% Complete, Please wait untill 100% Done")
how can i do it

i can't solve it.

thanks in advance

please rply soon

 

[Minty]

Do any of these google searches help?
https://christopherjmcclellan.wordpress.com/2014/03/08/progress-bar-for-ms-access/comment-page-1/
http://www.access-programmers.co.uk/forums/showthread.php?t=130802

 

[sxschech]

A low tech trick if your query is kicked off with a button, you could in vba turn the color of the text red and then when the query completes turn it black again. Won't give you percentage, but will let you know that it is running. Something like:

Me.MyButtonName.ForeColor = vbRed
Query code goes here
Me.MyButtonName.ForeColor = vbBlack

Only downside, if code breaks/aborts in middle, text will not revert to black until you reset code and reopen form.

 

[static]

You need a certain level of granularity in your data. You identify a non unique field (or fields) that allows splitting the data up into smaller chunks.
Then you execute the query against each group. e.g.

'get list of data 'groups'
with currentdb.openrecordset("select distinct MyGroup from MyTable")
	if .beof and .eof then exit sub
	.movelast
	recs = .RecordCount
	.movefirst
	
	'execute query for each group and display status
	for i = 1 To recs
	
		text1 = "processing MyGroup (" i & " of " & recs & ") '" & .fields(0) & "'"
	
		doevents 'allow screen to update
		
		currentdb.execute("update MyTable [...] where MyGroup='" & .fields(0) & "'")
		.movenext
		
	next
end with

This method also allows you to add a cancel update if you want.

The trick is to try and find a group that's small enough for doevents to fire often enough that the screen isn't frozen for long periods, but big enough that constant screen refreshes don't slow the code down too much.
Doevents also unlocks the screen so users can work with forms while the code is still running so it's a good idea to lock the button that starts the code to prevent it running twice on a doubleclick.

 

[ootkhopdi]

not clear,,


please give solutions with steps or examples

 

[jdraw]

See this free video from DataPig

 

[The_Doc_Man]

There can be such a thing as a progress bar, but the problem is that if you use a single SQL statement to implement some action, to the Access program, that query is indivisible. To be able to show incremental progress implies divisibility, which is contrary to the SET theory basis on which SQL was founded.

Having said that, there are ways to ask active back-end servers like ORACLE or SQL Server or some of the other DB active back ends to give you progress reports. But The only way to do that with Access is to somehow design your queries to be multi-segmented or otherwise divisible.

 

[Frothingslosh]

Pretty much what Doc said - if you're running an action query, the only progress bar available is the tiny one on the bottom of the screen.

If you're doing a process with multiple discrete steps, then I'd recommend either one of the ones linked by Minty, or else my own personal favorite: http://www.access-programmers.co.uk/forums/showthread.php?t=265537

Which reminds me, I really should post a more current version, since color override doesn't work right in the posted copy.

 

[static]

not clear,,


please give solutions with steps or examples

That's not really how it works. Nobody can give you a solution unless you give some proper details and I've got better things to do with my time than guess what you want.

Reading my post back it seems perfectly clear.

In a properly normalised database, records should be related to other records.

Sales data might be related to a region, for example. So if you had 10 regions you could filter your query by each region and run it 10 times.

1 region then = 10% of records processed.