Run Query from Form

[robertmeyer1]

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:

Private Sub Update_Click()
On Error GoTo Err_Update_Click
 
    Dim stDocName As String
 
    stDocName = "qryCalc"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
 
Exit_Update_Click:
    Exit Sub

Any suggestions and/or alternative approaches.

[NeoPa]

The SQL of qryCalc would help here...

[NeoPa]

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?

[robertmeyer1]

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;

[robertmeyer1]

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.

[MMcCarthy]

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

Private Sub Update_Click()
On Error GoTo Err_Update_Click
 
    Dim stDocName As String
 
   stDocName = "qryCalc"
   DoCmd.OpenQuery stDocName, acNormal, acEdit
   Me.Requery
 
Exit_Update_Click:
   Exit Sub
End Sub
 

[MMcCarthy]

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

[robertmeyer1]

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.

[MMcCarthy]

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:

Me.textboxName=Me.RecordsetClone.RecordCount

Mary

[robertmeyer1]

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.

[MMcCarthy]

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.

[NeoPa]

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?

[robertmeyer1]

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.

[robertmeyer1]

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.

[MMcCarthy]

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

Me.sub2Name.Requery
 

Mary

[robertmeyer1]

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

Me.sub2Name.Requery
 

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.

[MMcCarthy]

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

[robertmeyer1]

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.

[robertmeyer1]

This thread is now closed. Thanks ya'll.

[MMcCarthy]

This thread is now closed. Thanks ya'll.

Another one down Robrert

Congratulations

Mary