MS Access - Run Query with button

[chimpang]

Hello all, I hope this is a simple one.

I'm looking to run a query that I have stored with the click of a button.

I know that there is an 'Open Query' action available in the macro setup for the button, but I do not want to open it. I'd just like to rerun it in the background each time I hit the button.

Is there a way to do this without displaying the query for the user to see?

Thank you for any help you can give me,

Sam
Access Newbie

Additional Notes: the query is a SELECT query with criteria based on a selection within my form. I have already created it, and it's working correctly. I just don't want to view it when I hit the button, that's all :)

[MikeTheBike]

Hi

I don't understand why you would want to open a select query without viewing it. When (how) dose it get closed before you hit the button again?

[chimpang]

Hello Mike, thanks for your response.

The query itself is basically used to calculate quote revisions. It's bit of a faff but I'll do my best to explain it.

I have two tables. One for Projects, another for Quotes. They are both linked so that I can attach quotes to a project number. I am not using the Quote Table ID as a reference because it is an autonumber field and I could end up with something like:

Project 100 - Q1
Project 100 - Q32

So instead, I'm using a normal number field as the reference, and I use the query to return the quotes that exist for the specified project, then use a basic "count + 1" calculation to give each new quote the proper increment.

I have that part all working fine, it's just that I am having to manually re-run the query each time (and close it) when I select a different project, and I'd like to be able to do that automatically and in the background preferably.

I hope that offers some clarity. I'm still fairly new to Access and learning as I go, I apologise if it's not entirely clear.

Cheers!
Sam

[MikeTheBike]

Well I think I know what you are trying to do, but it is also possible that you DB design may need tweeking.

On the basis that doese not, then the way I would do it (have have in similar circumstances) is to either write s function to return the next sequential number or use DMax() +1 something like this

Dim NextIndex As Long
    NextIndex = DMax("CountFieldName", "QuotesTableBleName", "QuoteID = " & FormControlWithProjectID) + 1

This code would be behind the button, next index can then be used in an append query or to populated a form control.

This could also be done automatically as soon as the 'PrijectID' is available (on Current perhaps!?).

HTH

MTB

ps Having troubl getting the code tags to work !!??

[chimpang]

Ah that's a good idea! I'll give that function a try tomorrow and we'll see if it does the trick, if not, I may have to take a closer look at the DB design as you suggested. I'll update this tomorrow.

Thank you for your help Mike, have a good evening.

Sam