Put value from query in form field, no result and flickering field (1 Viewer)

bruceblack

Registered User.
Local time
Today, 20:44
Joined
Jun 30, 2017
Messages
119
Hi folks! Please can someone help me out?

I have a form that i use to display query data.

In the control source of a form field, i do a Dlookup to get the value from the query. However, no result shows up, instead the form field is flickering like crazy.

The code i used on the form field control source:

=DLookUp("[CountOfitem_ID]","[ASV_itemcount TOTAL]")


Note: the ASV_itemcount TOTAL query is result of another query. It only has 1 value.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:44
Joined
May 7, 2009
Messages
19,169
On what form did you put it, continuous, single? Control field means unbound textbox?
 

bruceblack

Registered User.
Local time
Today, 20:44
Joined
Jun 30, 2017
Messages
119
Thanks for the reply again :) very kind and a lifesaver.

This case its a single form
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:44
Joined
May 7, 2009
Messages
19,169
For test, Remove first the expression on the textbox. On the load event of the form show it in msgbox. Did you get any result.
 

bruceblack

Registered User.
Local time
Today, 20:44
Joined
Jun 30, 2017
Messages
119
The result i get is an error.

runtime 2471
the expression you entered as a query parameter produced this error:
'[CountOfitem_ID]'




my code looks like this:

Private Sub Form_Load()
MsgBox DLookup("[CountOfitem_ID]", "[ASV_itemcount TOTAL]")
End Sub
 

missinglinq

AWF VIP
Local time
Today, 16:44
Joined
Jun 20, 2003
Messages
6,423
To be clear, are you really trying to return CountOfitem_ID from the [ASV_itemcount TOTAL] Query? That's what your syntax, as written, is going to do:

DLookup("[CountOfitem_ID]", "[ASV_itemcount TOTAL]")

Linq ;0)>
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:44
Joined
May 7, 2009
Messages
19,169
Try using dcount against the base table.
 

bruceblack

Registered User.
Local time
Today, 20:44
Joined
Jun 30, 2017
Messages
119
Hey guys! Thanks again for the help and effort.

Still having problems.
Yes im trying to do just that. I even opened a blank new form with only a text field and ran the code in the control source property. When i open the form i get #error in the field. Also when i use Dcount on the the base query. :banghead:

When i just run the query, i get the correct result. So the query seems to function just right.
 

missinglinq

AWF VIP
Local time
Today, 16:44
Joined
Jun 20, 2003
Messages
6,423
What is the SQL for the Query that runs correctly?

Linq ;0)>
 

bruceblack

Registered User.
Local time
Today, 20:44
Joined
Jun 30, 2017
Messages
119
SELECT Sum(ASV_ENDofDAY_itemcount.CountOfitem_ID) AS SumOfCountOfitem_ID
FROM ASV_ENDofDAY_itemcount;

Doesn't seem wrong. And the query works. I just dont get the value in my form.
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:44
Joined
Sep 21, 2011
Messages
14,047
Haven't you (or really Access) called the field SumOfCountOfitem_ID ?, not CountOfitem_ID
 

bruceblack

Registered User.
Local time
Today, 20:44
Joined
Jun 30, 2017
Messages
119
Nope. Check that also.

I have multiple threads on this forum. They all have to do with what im trying to do.

I want a form where i select a date range and spawns my results in unbound fields. This because it concerns multiple different queries. Apparently my approach for all this is wrong. I run into too many problems.

What would be the best approach to have a form filter my queries where i select a date range and click a button and retrieves my results from the queries?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:44
Joined
Feb 19, 2002
Messages
42,976
Having multiple threads on the same topic confuses you and wastes our time.

Based on #10, the name of the field you want is
=DLookUp("[SumCountOfitem_ID]","[ASV_itemcount TOTAL]")

When you aggregate data in a query, it always ends up with a different name. Either you assign an alias as you did or Access does it for you. Access usually uses Expr followed by some number.
 

bruceblack

Registered User.
Local time
Today, 20:44
Joined
Jun 30, 2017
Messages
119
Thanks Pat!!!! Thanks alot.

That was exactly the issue. Its kinda deceiving how access puts a different name in there. I went through the query wizard all over again and then i stumbled upon the real naming. Thanks!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:44
Joined
Feb 19, 2002
Messages
42,976
You're welcome but it is important to understand what happened.

Access didn't do this, you did!!!

AS SumOfCountOfitem_ID

Is an alias. However, If you hadn't given the calculated value a specific name, Access would have given it a default name. EVERY column MUST have a unique name even a calculated one. Otherwise, how would you refer to it?
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:44
Joined
Sep 21, 2011
Messages
14,047
Sorry Pat, I'd have to disagree here, despite being a novice.

You probably write your SQL yourself, but if you use the design grid and then use totals, Access creates that alias. I can tell as I have had that in the past and recognised the format?. That is why I mentioned in post 11 that (really Access) had named the field as that.?

So that was the default name that Access gave to the calcualted field.?

Code:
SELECT tblTransferFee.TWMPaid, Sum(tblTransferFee.AppliedAmount) AS SumOfAppliedAmount
FROM tblTransferFee
GROUP BY tblTransferFee.TWMPaid;

You're welcome but it is important to understand what happened.

Access didn't do this, you did!!!

AS SumOfCountOfitem_ID

Is an alias. However, If you hadn't given the calculated value a specific name, Access would have given it a default name. EVERY column MUST have a unique name even a calculated one. Otherwise, how would you refer to it?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:44
Joined
Feb 19, 2002
Messages
42,976
Access creates that alias.
I did say that:
If you hadn't given the calculated value a specific name, Access would have given it a default name.
You only read the first part of my comment. In this case, the poster provided the alias. You can see it in the SQL he posted:

SELECT Sum(ASV_ENDofDAY_itemcount.CountOfitem_ID) AS SumOfCountOfitem_ID
FROM ASV_ENDofDAY_itemcount;

You might have me on a technicality though. I was only looking at the string and saw an alias so it didn't register that Access would have assigned the exact same name.
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:44
Joined
Sep 21, 2011
Messages
14,047
Sorry Pat,

I was reading that as if you were saying the o/p created the alias and not Access.

I was under the impression that Access did it and that is why the O/P did not notice the difference in name.

Although in a previous post to my query, it was stated that was checked.

I did say that:

You only read the first part of my comment. In this case, the poster provided the alias. You can see it in the SQL he posted:

SELECT Sum(ASV_ENDofDAY_itemcount.CountOfitem_ID) AS SumOfCountOfitem_ID
FROM ASV_ENDofDAY_itemcount;

You might have me on a technicality though. I was only looking at the string and saw an alias so it didn't register that Access would have assigned the exact same name.
 

Users who are viewing this thread

Top Bottom