Query ith expression in a continuous form (1 Viewer)

bobunknown

Registered User.
Local time
Today, 03:01
Joined
May 25, 2018
Messages
77
Hi all,

I have been trying to use a query with an expression as the control source for a continuous form but all I get back is the dreaded #Name?.

The query itself is, Collection Costs (individual) and im trying to use it in my Bins subform to show the total cost of a bin collection. I use the expression:

=[Collection Costs (individual)]![Cost]

I understand that this error sometimes appears when there are duplicate field names or something but I have tried a variation of changes to no avail. even deleting the text boxes on my form and re-entering them from scratch.

To save time im attaching a copy of my database so you can see what im on about. I have removed everything that isn't relevant to the problem from the DB as otherwise it would have been to large for the 2MB upload so just click OK through the error messages and hit enable content.

The section that is relevant is the lower left hand tab ladled bins that should open when it loads up.

I feel like this is a simple thing that im missing so sorry if its something stupid.

Thanks
 

Attachments

  • Test Database - Empty.accdb
    1.1 MB · Views: 82

theDBguy

I’m here to help
Staff member
Local time
Today, 03:01
Joined
Oct 29, 2018
Messages
21,358
Hi. Since you're trying to look up a value in a separate query, have you tried using the DLookup() function?
 

Minty

AWF VIP
Local time
Today, 10:01
Joined
Jul 26, 2013
Messages
10,355
That's a dreadful name for a query. Anything with weird characters }{()~# or spaces will give you problems somewhere down the line.

That query isn't in your subforms datasource, so it's not part of the underlying data.

You can't just pull in an arbitrary table / query onto a form, which record should it display ?

Join the aforementioned dreadfully name query into your rowsource and then the cost field can be part of the rowsource.

If you can't then you'll have to use a dlookup() in the form which will get clunky and slow.
 

bobunknown

Registered User.
Local time
Today, 03:01
Joined
May 25, 2018
Messages
77
Just tried the DLookup idea:

=DLookUp([Cost],[Collection Costs (individual)],"[BinID] =" & [Bins]![Bin ID])

same result as before.

Join the aforementioned dreadfully name query into your rowsource and then the cost field can be part of the rowsource.

Is there a preferred way to do this?

Thanks for the suggestions
 

Minty

AWF VIP
Local time
Today, 10:01
Joined
Jul 26, 2013
Messages
10,355
Try putting the following into your rowsource for the sub form;

Code:
SELECT Bins.*, [Bin Pricing].[Bin Cost], [Bin Pricing].[Emptying Cost], [Collection Costs (individual)].Cost
FROM ([Bin Pricing] INNER JOIN Bins ON [Bin Pricing].[Bin Size ID] = Bins.[Bin Size ID]) INNER JOIN [Collection Costs (individual)] ON Bins.[Bin ID] = [Collection Costs (individual)].[Bin ID];

I can't easily test it as too much is missing / broken in the sample you uploaded.

Your DLookup isn't formatted correctly. This might work instead

Code:
=DLookUp("Cost","[Collection Costs (individual)]","[BinID] =" & [Bin ID])

But the query is the way forwards.
 

bobunknown

Registered User.
Local time
Today, 03:01
Joined
May 25, 2018
Messages
77
Roger that.

I’ll give it a go next time I’m at my PC

I’ve also changed the query name to:

IndivCost

As per your recommendation.

Thanks
 

Users who are viewing this thread

Top Bottom