Run a query when the database has been opened by the user?

[jo]

Hi I when I open my database I would like a query to run that tells the user
that there are overdue items. I have the query ready but I dont know how to
or where to put this so it starts up when the database is opened? I would
also like message to say that a query is checking for overdue items.
Please help if you can
Jo

 

[Access::Student]

Do you have a homepage? You can always put the query results in a subform on
your homepage. In AC 2007 go to Office Button->Access Options->Current
Database-> Display Form , and choose which form to show.

 

[jo]

Hi ken, I have put a macro to open the query when they click on a button that
open the form. This works good including a msg to say "checking for
overdue....". But my problem now is solving the problem when there are no
overdue items. I have tried putting a macro in the On No data event but not
sure what to put there? Can you please help?
Jo

 

[jo]

Hi ken I have put the macro in as you said and it works as such!! If there
are overdue items the macro says it's "checking for items" then shows the
report with this data on, BUT when I test it for no overdue items the
"checking for items" msgbox does not come up it just goes straight to "No
gauges found". Is it possible for the "checking for items " msgbox to come up
first so the user knows the check is being done???
Thanks
jo

Jo:

You can make the macro actions conditional, so you can call the DCount
function to check if the query returns any rows and only pop up the message
box and open the query if the function returns a value greater than zero.
You could also pop up a different message if it returns zero rows if you
wished. If the Condition column isn't showing in the macro designer select
Conditions from the View menu. You'll need a condition for each action so it
would look something like this in design view:

Condition Action

DCount("*","YourQueryName")>0 MsgBox
DCount("*","YourQueryName")>0 OpenQuery
DCount("*","YourQueryName")=0 MsgBox

The first message box would be your "Checking for overdue items." message,
the second one, if you use it, something like "No overdue items found.".

Alternatively you could do the same in code either in the Click event
procedure of the button which opens the form, or in the form's Open event
procedure:

If DCount("*","YourQueryName")>0 Then
MsgBox "Checking for overdue items.", vbInformation, "Overdue Items
Check"
DoCmd.OpenQuery "YourQueryName"
Else
MsgBox ""No overdue items found.", vbInformation, "Overdue Items Check"
End If

Ken Sheridan
Stafford, England

Hi ken, I have put a macro to open the query when they click on a button that
open the form. This works good including a msg to say "checking for
overdue....". But my problem now is solving the problem when there are no
overdue items. I have tried putting a macro in the On No data event but not
sure what to put there? Can you please help?
Jo

Jo:

[quoted text clipped - 15 lines]

Please help if you can
Jo

 

[jo]

Great Ken that is perfect!! thanks for your help!!
Jo

Jo:

Just remove the condition from the first message box action. It will then
show regardless of whether there are any items found or not.

Ken Sheridan
Stafford, England

Hi ken I have put the macro in as you said and it works as such!! If there
are overdue items the macro says it's "checking for items" then shows the
report with this data on, BUT when I test it for no overdue items the
"checking for items" msgbox does not come up it just goes straight to "No
gauges found". Is it possible for the "checking for items " msgbox to come up
first so the user knows the check is being done???
Thanks
jo

Jo:

[quoted text clipped - 42 lines]

Please help if you can
Jo