Auto respond to Modal dialog boxes in Macro

[puzzles7]

Hi, I designed a report to show all permanent people who DO NOT have a current evaluation based on a start date parameter. Because of our data structure I have to run a Make-Table query to create a complete list and then use the parameter in a Delete query to delete all records from the table who HAVE had an evaluation as of the date entered and then populate a report from the remaining records in the table. This series of steps creates 5 modal dialog boxes where you just press Yes/OK to. Is there any way I can automatically answer these dialogs without the user seeing them?

 

[pbaldy]

See if the SetWarnings action does what you want. Make sure to turn them back on.

 

[puzzles7]

Unfortunately no. Is there a way using VBA that I can auto answer those modals that pop up related to Make-Table and Delete Queries?

 

[pbaldy]

What exactly do those modals say?

 

[puzzles7]

They are all related to "You are about to run a Make-Table Query", "The existing table will be deleted", "You are about to write ### records to the table", "Youn are about to tun a detele query that will alter your existing table", "You are about to delete ### records from the table".

I tried running the SQL in a select query to avoid the pop-ups but I kept ending up with an all or nothing result set. I couldn't just produce a list of ONLY those employees WITHOUT an evaluation by using a select query so I had to go to a Make-Table query followed by a Delete query and then produce the report off of the remaining table records.

I'd love a suggestion to do this entire process just through code or any way I can turn off these modal dialog boxes to the user.

 

[pbaldy]

Did you look at SetWarnings? Those are the types of message boxes it will stop. In VBA, you can use the Execute method, which doesn't throw them.

 

[puzzles7]

I did use SetWarnings in my macro after each call to OpenQuery. I tried setting them both to Yes and then to No but neither worked. I am working on this report in 2003 version. I have never written in VBA so I'm not sure what I need to do to make that happen. Can you show me?

Here's the SQL from the Make-Table query
SELECT DISTINCT dbo_CREW.FLEET_TRADE AS FLEET,
(dbo_CREW!LAST_NAME & ", " & dbo_CREW!FIRST_NAME) AS NAME, dbo_CREW.PAYROLL_NUMBER AS [EMP ID], dbo_CMC_PJC_TRANSLATOR.CMC_POS_TITLE_TRANSLATION AS [POSITION], dbo_CREW.PERSONNEL_ID INTO tblNoEval1
FROM (dbo_CREW LEFT JOIN dbo_CREW_PERFORMANCE ON dbo_CREW.PERSONNEL_ID = dbo_CREW_PERFORMANCE.PERSONNEL_ID)
INNER JOIN dbo_CMC_PJC_TRANSLATOR ON dbo_CREW.PERM_RATING_ID = dbo_CMC_PJC_TRANSLATOR.PAYROLL_JOB_ID
WHERE (((dbo_CREW.STATUS_TYPE)="PERMANENT") AND ((dbo_CREW.HIDDEN)=0) AND ((([dbo_CREW]![LAST_NAME] & ", " & [dbo_CREW]![FIRST_NAME])) Not ALike "%do not hide%"))
ORDER BY dbo_CREW.FLEET_TRADE, (dbo_CREW!LAST_NAME & ", " & dbo_CREW!FIRST_NAME);

 

[pbaldy]

You would have to set them to No before the call to OpenQuery, not after. In VBA:

CurrentDb.Execute "NameOfThatQuery", dbFailOnError

 

[puzzles7]

Thank you, setting the SetWarnings to No Before each query took care of it. Thanks again.

 

[pbaldy]

No problem. Make sure to set them back to Yes at the end of your process. You'll likely see some odd behavior if you don't.

 

[puzzles7]

Ok, will do.