VBA Code to run a query

[skahmed]

I have a query which prompts the user to enter a date.

Using the following VBA code, which is assigned to a button:

Sub GetMErateData()
'Displays all ME rate data
DoCmd.OpenQuery "qry Divisional Analysis Month End Rate"
End Sub

When the prompt appears and I click cancel rather then enter a date I get an error message:

Runtime Error '2001'
You cancelled the previous operation

Is there anyway I can press cancel and not receive this message?

 

[vbaInet]

What kind of query is this? A SELECT query for example?

 

[skahmed]

Yes, its a select query based on a date (which it prompts the user to enter).

Thanks

 

[vbaInet]

What users should be seeing is a form. Have you thought about using a form and setting its Default View property to Datasheet (which looks exactly like a query)?

Also, does the query open up normally if you double click it from the main window?

 

[wjburke2]

Try this

Sub GetMErateData()
'Displays all ME rate data
Turn off massages
DoCmd.SetWarnings = False
DoCmd.OpenQuery "qry Divisional Analysis Month End Rate"
'Turn on massages
DoCmd.SetWarnings = True
End Sub

Be sure to to set your messages back on after

 

[vbaInet]

It's only a SELECT query and plus it's an error, not a message. Turning off those type of messages won't have any effect.

 

[skahmed]

Hi, the query works fine if opened from the normal query window. When prompted for a date, I click cancel and I don't get an error message.

 

[vbaInet]

Where are you calling the sub GetMErateData()? Let me see the block of code.

And -->

What users should be seeing is a form. Have you thought about using a form and setting its Default View property to Datasheet (which looks exactly like a query)?

 

[skahmed]

Here is the VBA code to run a stored query called 'qry Divisional Analysis Month End Rate'

Sub GetMErateData()
'Displays all ME rate data
DoCmd.OpenQuery "qry Divisional Analysis Month End Rate"
End Sub

I don't understand your suggestion regarding creating a form and setting a datasheet view. Can you elaborate please.

Thanks

 

[vbaInet]

My question was, where is the code called? On the Click event of a button perhaps? That's what I want to see.

Create a form based on your query, set the Default Value property to Datasheet. You will find that it looks exactly like a query. You can then open the form instead.

 

[skahmed]

Yes, the code is called on the click event of the button on the form.

I'll try your suggestion.

Thanks

 

[vbaInet]

Yes, the code is called on the click event of the button on the form.

Can I see that sub?

 

[skahmed]

Not sure what Sub you mean.

I've shown the code that is run when you click the button.

 

[vbaInet]

I'm having to repeat myself over again. I want to see the CLICK event sub.