How to prevent a user from seeing the "Stop All Macros" when the enter the incorrect date format for a Parameter Query

[Galapagos15]

I have a macro which includes a parameter query. Currently, when the macro is initiated and the incorrect date format is entered for the parameter query prompt I get "Stop All Macros" error box.

I tried adding an "OnError" "Fail" step into the macro but it's not working correctly. What can I add to the macro commands so the user won't see the "Stop All Macros" box but will be reprompted to enter the correct date format?

Thanks in advance!

 

[Joe4]

I don't think you can do that with a Macro. You would have to probably use VBA.
Instead of entering the parameters with a parameter query (which really has no controls on the input), use a Form to enter in the criteria, where you can set Data Types and add VBA code to verify valid entries before applying it to your query.

 

[Galapagos15]

Okay thanks. I'm not really familiar with forms so I had this set up in a macro which has multiple queries in it and one was a parameter query which then in turn generates a report for that particular timeframe. I was hoping that when that macro went through the series of queries that I could insert an action prior to the parameter query which would prompt the individual to enter the dates correctly. Thanks anyway.

 

[Joe4]

Here are a few articles that you may find helpful in querying by form:
Microsoft Access tips: Search criteria
https://www.599cd.com/tips/access/forms-formname-field-notation/

Personally, I use a Form for my criteria, but then I build the SQL code for my query using VBA (instead of having my query reference the forms field directly like in the articles above). But that is probably a bit more complex than what you are looking for.

 

[Galapagos15]

Thank you I will take a look at those articles.

 

[Micron]

You could use the macro to present a message box that informs the input format but you will still have the problem if they don't comply. If you don't have time to learn the form method (which is best) check out the input box function. At least you could use a mask with that to enforce the date format. I wonder where you are located such that users are using what must be a non-native date format for your region.