Running a Query and displaying the results in a Message Box 2

[BajanPOET]

How would I create a query in access that adds all the Total Costs of a set of products (Total Cost being a calculated field in the query of Qty * Unit Cost) so that the Total Cost is displayed in a msgbox()?

I'm trying to allow the staff to see the total cost of the requisitions. If I create the query using the Query Design wizard so that it runs when I click the saved name in the database window, how can I get the result to show up when I press a button in a form?

What? Who? ME????

 

[AlexCuse]

Have you tried commenting out the ReqNo.SetFocus command?

That is all that comes to mind, unless it is some code that you have running on the form itself.

HTH,
Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[BajanPOET]

I can't comment it out, coz without it the program says that I can't access a control's properties unless the control has the focus.

I just thought of something. The ReqQuery is a parameterised query. I need to be able to feed the query the current requisition number from the form that I will use this query with so that it can calculate the total cost for the correct requisition. Then I need to display that amount. I don't see anywhere in this code where I am supplying the requisition number (stored in this form in a textbox) to the query. Maybe this is what it means when it gives me that wierd error message.

Any thoughts?

What? Who? ME????

 

[AlexCuse]

How are you referencing the form control in your query? As long as the form is open and the control is populated it should work.

HTH,

Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[BajanPOET]

I'm running this code from a command button on the form so at the moment of execution the command button has the focus. I would expect that I should not have to expressly set the focus to the ReqNo textbox, but that's what's happening.

What? Who? ME????

 

[BajanPOET]

I'm running this code from a command button on the form so at the moment of execution the command button has the focus. I would expect that I should not have to expressly set the focus to the ReqNo textbox, but that's what's happening.

What? Who? ME????

 

[AlexCuse]

But how are you referencing the control for ReqNo from your query (where the DLookup goes)?

Here's an easier idea though, how about since the DLookup is using the ReqNo on the form to go out and look, remove the parameter from your "GrandTotal" query so that it returns ALL ReqNo's and total costs?

Let me know if this works for you.

Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[BajanPOET]

I'm not referencing the control from ReqNo to send the current req no to the query... that's what I was going to ask about, coz I'm actually not referencing the value of the control at all... I was trying to figure out how to do that so that the query works without me having to know each ReqNo and type it in manually.

I'll try removing the parameter and see if that works.

What? Who? ME????

 

[AlexCuse]

If the query returns ALL req numbers, then your DLookup will only look for the total that corresponds to ReqNo on your form. This would save you from having to type it in manually.

FYI- the manual entry of the parameter is what prevents the DLookup function from being able to grab data from your query.

Let me know how it works,

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[PHV]

The SQL code of ReqQuery:
SELECT SUM(Qty * UnitPrice) AS GrandTotal
FROM Items
WHERE ReqNo = [Forms]![Name of mainform]![Name of ReqNo TextBox]

The VBA code:
TotalReqCost = DLookup("GrandTotal", "ReqQuery")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AlexCuse]

That would work as well. You don't need to specify parameter twice is what it comes down to.


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[BajanPOET]

Still getting the error: You canceled the previous operation.

But I had a thought - instead of using the queries in the Database window, could I use the DoCmd.RunSQL to run the SQL string? Could I use this to show the result in a message box?



What? Who? ME????

 

[AlexCuse]

RunSQL is only for action queries. There is something going on here that you haven't specified. I still think it's a problem with your setfocus, it should not need to be set. I frequently use DLookups referencing a certain form control as on lost focus events.

Let us know which methods you tried, etc...

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[PHV]

And what about this ?

Private Sub cmdCalculate_Click()
  Dim TotalReqCost As Double
  TotalReqCost = Nz(DSum("Qty*UnitPrice", "Items", "ReqNo='" & Me!ReqNo & "'"), 0)
  MsgBox "Total Requisition Cost:  " & TotalReqCost, _
  vbInformation + vbOKOnly, "Calculation"
End Sub

If ReqNo is defined as numeric then get rid of single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BajanPOET]

THANK U guys!!!!! It worked - finally!!!!!!! <breathes a sigh of relief>

What? Who? ME????