Display the result of a query in a text box on a form

[D]

Please help: I'm having trouble in trying to display the result of a
query in a text box on a form.
I'll give my whole example.

My table is called Employees and has four fields: Name, Level, Salary1,
Salary2.
Looks something like:

Name - Level - Salary1 - Salary2
Minnie - Manager - 40000 - 50000
Morris - Manager - 60000 - 70000
George - Grunt - 10000 - 15000
Gary - Grunt - 20000 - 21000

I have two list boxes on my form, Form1, driven by queries (created in
design view, but I could have written SQL for it....)
One is the list of Managers and the other is the list of Grunts.
(They are List0 and List2 on the form.)

I have a query, TotalSalaryQuery, which returns the sum of the salaries
of the ONE grunt and ONE manager selected on the form (e.g. one
selection from each list box). Here it is. I've tested it and it works.

SELECT SUM (Employees.Salary1 + Employees.Salary2) AS Expr1
FROM Employees
WHERE Employees.Name = Forms!Form1!List0 OR Employees.Name =
Forms!Form1!List2

Here's where the problem comes. I've searched the web for solutions on
exactly how to display the result of the query on the web, but none
have worked for me. I create the text box and know I have to set its
Control Source. I have tried all sorts of combinations of quotation
marks with DLookup("[Expr1]","[TotalSalaryQuery]")
but I never get a result -- the text box remains blank.

Here's what I really want to do.

Make a choice from the Manager list and a choice from the Grunt list,
and have the result of TotalSalaryQuery (based on the user's
selections) displayed in the text box. If the selection(s) change, the
result in the text box should update.

I would really, REALLY appreciate anyone's help with this. If you have
a chance to test it -- please do -- as I said, I've tried all sorts of
DLookUp variations suggested by other threads with no result. Also --
please be as explicit as possible with your solution -- as I have
become really frustrated with trying to get access to do what I want.

Thanks so much.

 

[Vincent Johns]

OK, I think I have a suggestion...

Please help: I'm having trouble in trying to display the result of a
query in a text box on a form.
I'll give my whole example.

My table is called Employees and has four fields: Name, Level, Salary1,
Salary2.
Looks something like:

Name - Level - Salary1 - Salary2
Minnie - Manager - 40000 - 50000
Morris - Manager - 60000 - 70000
George - Grunt - 10000 - 15000
Gary - Grunt - 20000 - 21000

I have two list boxes on my form, Form1, driven by queries (created in
design view, but I could have written SQL for it....)
One is the list of Managers and the other is the list of Grunts.
(They are List0 and List2 on the form.)

I have a query, TotalSalaryQuery, which returns the sum of the salaries
of the ONE grunt and ONE manager selected on the form (e.g. one
selection from each list box). Here it is. I've tested it and it works.

SELECT SUM (Employees.Salary1 + Employees.Salary2) AS Expr1
FROM Employees
WHERE Employees.Name = Forms!Form1!List0 OR Employees.Name =
Forms!Form1!List2

Here's where the problem comes. I've searched the web for solutions on
exactly how to display the result of the query on the web, but none
have worked for me. I create the text box and know I have to set its
Control Source. I have tried all sorts of combinations of quotation
marks with DLookup("[Expr1]","[TotalSalaryQuery]")
but I never get a result -- the text box remains blank.

Here's what I really want to do.

Make a choice from the Manager list and a choice from the Grunt list,
and have the result of TotalSalaryQuery (based on the user's
selections) displayed in the text box. If the selection(s) change, the
result in the text box should update.

My version of your Form does this. What I did was to create a list box,
[Form1]![lbxTotalSalary], whose Row Source is [TotalSalaryQuery], on
[Form1]. I also created a Macro, [M_Requery], containing the action

Requery lbxTotalSalary

and set the After Update event of [Form1]![List0] and of [Form1]![List2]
to refer to [M_Requery]. Maybe you wouldn't want to set them both, but
at least this gets the desired value to show up.

Incidentally, if I were doing it, I'd probably choose more suggestive
names than [Form1] and [List2]. But Access isn't very fussy about
names, it'd be more for the benefit of its human co-workers.

I would really, REALLY appreciate anyone's help with this. If you have
a chance to test it -- please do -- as I said, I've tried all sorts of
DLookUp variations suggested by other threads with no result. Also --
please be as explicit as possible with your solution -- as I have
become really frustrated with trying to get access to do what I want.

Thanks so much.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[D]

Vincent --

Thanks so much for the solution.
I'll write again once I've had a chance to test it.
Certainly nothing I had been able to get on my own -- so your help was
much appreciated.