Display query results in textbox on form that launches query? (1 Viewer)

[Ingeneeus]

I'm sure the answer to the question above is yes. I just can't seem to make it work.

I've got a form titled Line-Item PO Search. On this form are a couple of fields in which the user can input the following information: CustomerID, PO#, Start Date, and End Date.
These fields serve as the parameters of a query called Line-Item PO Query.

Line-Item PO Query looks at my Order Details table and returns results for any order from CustomerID x that has PO# y, that was made between Start Date and End Date. It gives me the SKU of the product ordered, the ProductName, and the PricePerUnit (among a few other fields)

Line-Item PO Query doesn't give me the data I need, however. I need the total expenditure made under the PO# in question in my time window.

To get that, I created a 2nd query, Line-Item PO Query Totals, which uses Line-Item PO Query as its source. I take the two fields I need, PricePerUnit and QuantityShipped and use the SUM function. Viola, the result I need, under the headers SumOfPricePerUnit and SumOfQuantityShipped.

The idea is that my user can input his or her criteria on the Line-Item PO Search form, hit a button which has DoCmd.OpenQuery "Line-Item PO Query Totals" as its OnClick event. So far so good. All works as intended.

What I want is for two textboxes on the Line-Item PO Search form to display the results of the Line-Item PO Query Totals query. And I'm failing miserably. I've set the record source of the form to be the Line-Item PO Query Totals query, which I think is right. I initially set the control source of my results textbox to PricePerUnit, but that got me a ?Name error, so I set it to SumOfPricePerUnit. That cleared the ?Name error, but the textbox isn't picking up the query result when I hit the Search button to run the query; it's just blank.

I read somewhere that I can't just use the name of the query field in this kind of situation, and that the recommended way to go about it was to use a DLookUp as the control source for my textbox, so I tried
=DLookup("[SumOfPricePerUnit]", "Line-Item PO Query Totals") as the control source. It workedthe very first time, but not any subsequent tries. Obviously, I'm missing something, but I don't know what it is. Do I need to reset the value of the result textbox before I can query again? I'm not sure how I go about that.

~Gene

 

[sneuberg]

I think the easier way to do this is just create a form with the Line-Item PO Query Totals as the record source and then put it on the Line-Item PO Search form as a subform. You can change the formatting (record selector, borders, etc) so that you won't even know it a subform if you want.

 

[Ingeneeus]

Thanks, sneuberg
You're probably right. I was hoping to do it nice and neat on the same form and avoid adding yet ANOTHER (sub)form to my database.
Still open to suggestions if anyone has any ideas on how to do this without the subform

 

[sneuberg]

Another way would be to use DLookUps as you were trying. I suggest trying this

=DLookup("[SumOfPricePerUnit]", "Line-Item PO Query Totals")

with brackets like

=DLookup("[SumOfPricePerUnit]", "[Line-Item PO Query Totals]")

and you might want play it safe by enclosing it in Nz just in case

=Nz(DLookup("[SumOfPricePerUnit]", "[Line-Item PO Query Totals]"),0)

 

[Ingeneeus]

Thanks, sneuberg!
I will give that a try later this afternoon if I get a chance.

 

[Ingeneeus]

Sadly, no joy with the DLookUP, even with the added brackets.
I will try the subform route.
Thanks again!

 

[sneuberg]

I think DLookUp should work for this. You can test a DLookUp in the Immediate window. I suggest trying it there. You usually get more information about what is wrong that way and it's easier to test, change, test change; just poke it with a stick until something comes out. If you haven't used it before you just go to the visual basic editor (Alt F11 in some versions). In the View menu click on Immediate Window or press Ctrl G to open it. To evaluate an expression just type a question mark and then the expression. For example:

?DLookup("[SumOfPricePerUnit]", "[Line-Item PO Query Totals]")

When you press enter you should get an answer or an error message.

Also if it working in the Immediate Window but not in the form try pressing F9. If that shakes it loose then you may need a Me.Recalc in some place, maybe the form Current event.

 

[Ingeneeus]

Thanks, snueberg --
I will give this a try when I get back into the office. I'll leave the thread open for now and post what I find out for the benefit of future viewers. Have a good weekend!