By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,231 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

MS Access - Run Query with button

P: 3
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 :)
Nov 18 '13 #1
Share this Question
Share on Google+
4 Replies


Expert 100+
P: 634
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?
Nov 18 '13 #2

P: 3
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
Nov 18 '13 #3

Expert 100+
P: 634
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

Expand|Select|Wrap|Line Numbers
  1. Dim NextIndex As Long
  2.     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 !!??
Nov 18 '13 #4

P: 3
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
Nov 18 '13 #5

Post your reply

Sign in to post your reply or Sign up for a free account.