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

Run Query from Form

P: 99
Hey all,

My goal is to get to 100 new threads in the next month, and if I keep this up I may make it. But anyways, here the issue.

I have a form (frmData) which is used to enter information (numeric) for a specific client. So about 80 fill-ins for 1 client. This information is then calculated (sum) in a query (qryCalc). So the query shows the sum of the numbers entered per client. Now, I have inserted this query into the form to allow for a “sum” field. This is viewable only. Now, everything works and shows. I can see the pre-entry answer. I currently have to run the query, close the form, and reopen to see the sum. SO:

Now I am trying to add a “run query” command on the form which will allow the user to click the command button after entering the information and thus, the “Sum” total (query) will show, without closing the form, or manually running the query. This will allow people to check to make sure the correct amount has been entered.

So I have a command button which should run the query. Instead it opens the query. So you click on the button and the query opens. I want to change this to not have the query open and to also make it so it runs automatically. Thus updating the “sum” field. Here’s the code I used:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Update_Click()
  2. On Error GoTo Err_Update_Click
  3.  
  4.     Dim stDocName As String
  5.  
  6.     stDocName = "qryCalc"
  7.     DoCmd.OpenQuery stDocName, acNormal, acEdit
  8.  
  9. Exit_Update_Click:
  10.     Exit Sub
Any suggestions and/or alternative approaches.
Dec 12 '06 #1
Share this Question
Share on Google+
19 Replies


NeoPa
Expert Mod 15k+
P: 31,661
The SQL of qryCalc would help here...
Dec 12 '06 #2

NeoPa
Expert Mod 15k+
P: 31,661
Bear in mind, if you have an action query (you do - or certainly should have) which is in design view when you execute DoCmd.OpenQuery in the code, it will Display and not execute.
Maybe this was your problem?
Dec 12 '06 #3

P: 99
The SQL of qryCalc would help here...
SELECT tblAnswers.ClientAutoID, Sum(tblAnswers.Answer) AS SumOfAnswer
FROM tblAnswers
WHERE (((tblAnswers.QuestionNumber) Between 201 And 232))
GROUP BY tblAnswers.ClientAutoID
ORDER BY tblAnswers.ClientAutoID;
Dec 12 '06 #4

P: 99
Bear in mind, if you have an action query (you do - or certainly should have) which is in design view when you execute DoCmd.OpenQuery in the code, it will Display and not execute.
Maybe this was your problem?
I think I just have a Select Query.
Dec 12 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Robert,

Firstly qryCalc is an UPDATE Query. All you need to do is add the line Me.Requery to requery the forms record source and update the data on the form.

Mary

Expand|Select|Wrap|Line Numbers
  1. Private Sub Update_Click()
  2. On Error GoTo Err_Update_Click
  3.  
  4.     Dim stDocName As String
  5.  
  6.    stDocName = "qryCalc"
  7.    DoCmd.OpenQuery stDocName, acNormal, acEdit
  8.    Me.Requery
  9.  
  10. Exit_Update_Click:
  11.    Exit Sub
  12. End Sub
  13.  
Dec 12 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Actually Robert

I just checked back and what I gave you was an insert query.

This is probably another query. What are you trying to do. A SELECT query doesn't actually change the data in any way.

Mary
Dec 13 '06 #7

P: 99
Actually Robert

I just checked back and what I gave you was an insert query.

This is probably another query. What are you trying to do. A SELECT query doesn't actually change the data in any way.

Mary
I don't need to change the data for this query. I am trying to show a sum of what was entered. So if someone entered 1, 1, 1, 1, 1. The query will sum these to 5 and this would be displayed on the form at the bottom of the page.
Dec 13 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
I don't need to change the data for this query. I am trying to show a sum of what was entered. So if someone entered 1, 1, 1, 1, 1. The query will sum these to 5 and this would be displayed on the form at the bottom of the page.
You don't need the query. Behind the command button put:

Expand|Select|Wrap|Line Numbers
  1. Me.textboxName=Me.RecordsetClone.RecordCount
Mary
Dec 13 '06 #9

P: 99
Hey Mary,

I'm running into some trouble here. I inserted the code and it does work, but it only counts the answers once. This form is for entry of answers to the prepopulated questions. So the design view only has 1 field for answers. When I ran it originally, it entered a 1 into the textbox even though I had numbers equaling 5. When I entered 1 more number (sum to 6) it showed 8 in the text box. So I’m not sure what’s going on (I do have 8 clientID’s though?).

Also, if it is possible to use the query I would like to go ahead and do that for another form I am working on. In that form, I do the same thing (sum the entries) but then I also multiply that sum by 25% and subtract it out. I would like to display these on the form as well so people can see the effect of what is occurring. Thanks.
Dec 13 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
You need the command button on the subform not the main form.

Mary

Hey Mary,

I'm running into some trouble here. I inserted the code and it does work, but it only counts the answers once. This form is for entry of answers to the prepopulated questions. So the design view only has 1 field for answers. When I ran it originally, it entered a 1 into the textbox even though I had numbers equaling 5. When I entered 1 more number (sum to 6) it showed 8 in the text box. So I’m not sure what’s going on (I do have 8 clientID’s though?).

Also, if it is possible to use the query I would like to go ahead and do that for another form I am working on. In that form, I do the same thing (sum the entries) but then I also multiply that sum by 25% and subtract it out. I would like to display these on the form as well so people can see the effect of what is occurring. Thanks.
Dec 13 '06 #11

NeoPa
Expert Mod 15k+
P: 31,661
Hey Mary,

I'm running into some trouble here. I inserted the code and it does work, but it only counts the answers once. This form is for entry of answers to the prepopulated questions. So the design view only has 1 field for answers. When I ran it originally, it entered a 1 into the textbox even though I had numbers equaling 5. When I entered 1 more number (sum to 6) it showed 8 in the text box. So I’m not sure what’s going on (I do have 8 clientID’s though?).

Also, if it is possible to use the query I would like to go ahead and do that for another form I am working on. In that form, I do the same thing (sum the entries) but then I also multiply that sum by 25% and subtract it out. I would like to display these on the form as well so people can see the effect of what is occurring. Thanks.
Robert,

Are you SUMming or COUNTing here?
RecordCount gives you the total number of records not the sum of any values in those records (unless each value is 1). Your question specifies all 1s hence the response. Is the question right?
Dec 14 '06 #12

P: 99
I am trying to do a "sum" of the values entered. Sorry for the confusion. That is probably why the code Mary sent did what it did. I also tried inserting the query into the sbf, but ran into a problem. So here’s more background:

I am using a query based off of tblAnswers (prepopulated from an append query – thanks Mary!!) to list all questions of a certain range. (QuestionNumber 401-499). I then created a sbf (sbf1) and based the record source off this qry. The sbf has information as follows: ClientAutoID, QuestionNumber, answer. Answer is the only field enabled (not locked) and thus the only 1 that can have information entered. This sbf is in datasheet view.

I then created a form, based off tblClients. I inserted the sbf1 into this form and set ClientAutoID to equal. This form’s only information displayed is the sbf’s data entry for answers and the corresponding question. No other information is entered here.

On this mainform I also inserted a qry (qry2). Based off tblAnswers for the answers to the questions displayed on sbf1. So the query works right now on summing the answers entered through this form. The qry2 sums the answers for each client and this seems to work fine in the query itself.

I wanted to insert the query on the bottom of the form so that people could see the “total or sum” value of the data they entered. (This will allow for people to double check that what they entered above equals what it should).

Now the qry2 I inserted only shows the amount for this sum. In order to display the updated information, I am forced to close the form, then reopen and it then shows the correct amount. Since the way it works right now, people would have to close the form every time they wanted to see the updated total, I tried inserting a button to runt he query.

This button is currently on the form. Instead of running the query and updating the display box, it opens the query and does not run. That’s were I’m at now.

I tried inserting the qry2 into the sbf based off the 1st qry, but instead of showing the field, it added the “expand” (+) button to the left of each question. So inserting it there causes a problem.

I’d like to say thanks again for all this. I know I ask a lot of questions, but I’m starting to feel like I understand Access now. Granted I may still reach that 100 thread goal, but maybe it will take me 2 months now instead of just the 1.
Dec 14 '06 #13

P: 99
On the plus side. When the qry is inserted into the sbf, and the + is shown, clicking on it does display the updated information as entered. I'm just not sure how to display it properly on the form.
Dec 14 '06 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
On the plus side. When the qry is inserted into the sbf, and the + is shown, clicking on it does display the updated information as entered. I'm just not sure how to display it properly on the form.
Go back to how you had it originally and instead of running the query off the command button just put the following

Expand|Select|Wrap|Line Numbers
  1. Me.sub2Name.Requery
  2.  
Mary
Dec 15 '06 #15

P: 99
Go back to how you had it originally and instead of running the query off the command button just put the following

Expand|Select|Wrap|Line Numbers
  1. Me.sub2Name.Requery
  2.  
Mary
Hi.

I tried placing the code behind the command button but no luck. Here’s what I used:

Me.Form1.Requery

Form1 is the current name of the sbf based off the calculation query (sum). An error message on the code pops up and says: Method or data member not found.
Dec 18 '06 #16

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi.

I tried placing the code behind the command button but no luck. Here’s what I used:

Me.Form1.Requery

Form1 is the current name of the sbf based off the calculation query (sum). An error message on the code pops up and says: Method or data member not found.
Form1 may be the name of the subform but not the subform object.

On the main form in design view check the properties of the subform object under the other tab. What is entered for the Name property?

Mary
Dec 18 '06 #17

P: 99
Form1 may be the name of the subform but not the subform object.

On the main form in design view check the properties of the subform object under the other tab. What is entered for the Name property?

Mary

Very cool. I didn't realize it was named differently. Thanks, the update button is now working great.
Dec 18 '06 #18

P: 99
This thread is now closed. Thanks ya'll.
Dec 18 '06 #19

MMcCarthy
Expert Mod 10K+
P: 14,534
This thread is now closed. Thanks ya'll.
Another one down Robrert

Congratulations

Mary
Dec 19 '06 #20

Post your reply

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