SEEKING HELP - importing queries to form

[quinncooper1988]

HI,

I desperatly need some help. I am simply trying to import a query, into a form.

I have tried linking them in relationships and in the query, however the field in the form does not update.

I have tried to import the query into the form, no joy.

It really is one field from a query, that needs to be on a form, generally so the information is right there in front of you rather than having to flick through the queries, there's quite a lot you see.


I am really struggling, can someone help!?!

 

[khawar]

Set the control source of the field on form to the field on query

 

[quinncooper1988]

Hi,

I have just tried that, I clicked on properties, then control source ... expression builder.

I then clicked the links, which built the expression as

Spend «Expr» [stationary jan]![SumOfPrice]

And still showing as zero.

 

[khawar]

It cannot be displayed like this field must be in record source of form or in query or table which set as recordsource

 

[quinncooper1988]

Is using a "SELECT" code in SQL view a possibility?

 

[khawar]

Can you explain in detail what value you are trying to retrieve

 

[quinncooper1988]

Sorry, I need...

Query:

I have queried a table so I can gather cost information on what items have been approved or not approved. The end result, is a single file, that has sum'd up the costs. So this one field is something like £25.

Form:

I have a form, which is going to display various budget data, yearly and monthly budgets, I need the field from the query as the "Spent" field.

There will be as follows: Monthly budget ... spend ... carry over,

In the carry over, I have used a formula, =[Monthly budget]-[spend]

This works if I am typing in an amount into the spend, however, I need the [spend] to come from the query. That way the system will add up the multiple costs, and not me.

So spend needs to be, somehow linked, so when I open the form, it is updated automatically from the query. As these tables will be updated weekly, so if i open the form in 2 weeks, then it would already have calculated the previous costs.


Does the above make sense? As I say, in the query the end result is a a single field that has added up multiple costs, from a given month, I used January in the query.

Please see the SQL View below of the query:

SELECT Sum([LWC BUDGET TRACKING].Price) AS SumOfPrice
FROM [LWC BUDGET TRACKING] LEFT JOIN [front sheet jan] ON [LWC BUDGET TRACKING].Price=[front sheet jan].Price
GROUP BY [LWC BUDGET TRACKING].[Cost Code], [LWC BUDGET TRACKING].Date
HAVING ((([LWC BUDGET TRACKING].[Cost Code])="6422 Stationary") AND (([LWC BUDGET TRACKING].Date) Like "**/01/****"));

 

[khawar]

use this as control source of field
=dlookup("[SumOfPrice]","[stationary jan]")

 

[quinncooper1988]

THANKS SO MUCH. it is now working!!!

Thank you