Macro Error

[enquiries]

Hi,



I've created a macro to open a query from a button on a form, the thing is that the query prompts the user for the parameters, so if I select "cancel" when prompted with the query parameters it will come up with an error Action Failed 2950. How can I prevent this please?

Any help/reference material appreciated
Thanks.

[June7]

Why opening query? Is this just a SELECT query? Could try LIKE operator and wildcard parameters. Review http://www.datapigtechnologies.com/f...artparams.html

[enquiries]

Yes, it's just a select query that prompts the user for the parameters and then searches and yub I'm using the LIKE operator. The reason why I'm opening the query from a form, is because I plan on having a list of queries that will be read only but are opened from buttons on the single form, otherwise I might use switchboard instead. It's a problem with the macro as the query works on it's own but below is my SQL:


SELECT [Applicant Information].[First Name], [Applicant Information].[Last Name], JobsLkUpTble.[Position Title], JobsLkUpTble.Organisation, JobsApplicant.[Year Started], JobsApplicant.[Year Ended], [Applicant Information].Village, [Applicant Information].District, [Applicant Information].Email, [Applicant Information].Gender, [Applicant Information].Nationality, [Applicant Information].Facsimile, [Applicant Information].[Date of Birth], [Applicant Information].[Phone One], [Applicant Information].[Phone Two], [Applicant Information].[Mobile One], [Applicant Information].[Mobile Two], [Applicant Information].[Mailing Address], [Applicant Information].[Date Received], [Applicant Information].[Residential Address], [Applicant Information].Location, [Applicant Information].[Community Status]
FROM JobsLkUpTble INNER JOIN ([Applicant Information] INNER JOIN JobsApplicant ON [Applicant Information].ApplicantID = JobsApplicant.AppllicantID) ON JobsLkUpTble.JobsID = JobsApplicant.JobID
WHERE (((JobsLkUpTble.[Position Title]) Like "*" & [Enter in Position Title] & "*") AND ((JobsLkUpTble.Organisation) Like "*" & [Enter in Organisation] & "*"));


Thanks for your help

[June7]

I have only ever had one occasion to allow users to open query object and that was for export to Excel.

I don't use dynamic parameterized queries. I apply dynamic filters to forms and reports.

I just did a test of this in VBA and Cancel generates run-time error 2001. Might be able to manage this with error handler code in VBA. Error handler is branching code and no idea if macro can replicate.

Are you sure these queries will all be non-editable?

[enquiries]

Yub, in the arguments of the macro that I'm using to open the query I set it to "read-only". I was wondering whether, there was a way to prevent the error from happening instead of handling it - so if someone clicks "cancel" on the parameter prompt it will cancel the event or not open the query at all.

Do you have suggestions on good readings on dynamic filters to forms and reports please. I think that will be a good solution.

Thanks

[enquiries]

Thanks for your time & help, I've tested it using the switchboard and running the macro on the switchboard - it works perfectly. Cancels when I select cancel and it doesn't run an error.

Thanks again, always helps to write things out/talk it over with someone - you sometimes end up finding the solution by just doing that

[enquiries]

Oh and just to clarify the queries are editable but it's the records that are produced from the query that are read-only