Dlookup query value in continuous form by ID (1 Viewer)

bruceblack

Registered User.
Local time
Today, 10:27
Joined
Jun 30, 2017
Messages
119
Hey guys! Hope you can correct some syntax here. Im a bit lost. :p

I have a continuous form.
I have a query with a (count) field which i want to display on the form

So i do a Dlookup to get the value. Problem is, it only should do the counting for that specific ID.

I was trying this:

Code:
=DLookUp("[ID]";"[major_count]";"[ID] = Me![ID]")

So im using ID 2 times, i know. Maybe thats the issue? I get Error in my form field :(

Hope you guys get what im trying to do. Thanks in advance!
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:27
Joined
Sep 21, 2011
Messages
14,038
Have to ask, why are you looking up something you already know.?
 

bruceblack

Registered User.
Local time
Today, 10:27
Joined
Jun 30, 2017
Messages
119
haha :) Thanks for that reply.

Well, because im not really sure how else to put it.

I use the ID field in the query to do a count. The count result i want on my form. But only for that particular record, so i use ID again.

So i have to count another field?
Or like name it different i guess now i think of it...???

something like

majorcount: [ID]

in my query?

I try

Code:
=DLookUp("[countmajor]";"[major_count]";"[ID] = Me![ID]")

But this also gives me error
 

bruceblack

Registered User.
Local time
Today, 10:27
Joined
Jun 30, 2017
Messages
119
Code:
=DCount("[minorormajor]","nonconforms_major_count","[ID] = Me![ID]")


Still no luck. I tried many things but it dont know what is going wrong...:banghead:
 

Minty

AWF VIP
Local time
Today, 10:27
Joined
Jul 26, 2013
Messages
10,353
You need to concatenate the current form records ID into the Dcount / Dlookup

Code:
=DCount("[minorormajor]","nonconforms_major_count","[ID] =[COLOR="Red"] " &[/COLOR] Me![ID])

However it would probably be more efficient to do this in a sub query in the forms record source, or to join your existing query to the recordsource.
Have a read here http://allenbrowne.com/subquery-01.html
 

bruceblack

Registered User.
Local time
Today, 10:27
Joined
Jun 30, 2017
Messages
119
As always good help Minty :)

Now it doesn't give me an error. But it gives me #Name?

Which is was happy with until i checked everthing and didnt find ANY mistypes.

What i have now is

Code:
=DCount("ID","major_count","[reportnumber] = " & [Me]![reportnumber])
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:27
Joined
Sep 21, 2011
Messages
14,038
Aren't you the user who uses ; instead of , ??
 

Minty

AWF VIP
Local time
Today, 10:27
Joined
Jul 26, 2013
Messages
10,353
Firstly try using Me. insead of Me! as that will allow intellisense to work.
Secondly is this in a control or in VBA or in a query ?

If it's in a query it won't work, you should use a join to your original recordsource.
 

bruceblack

Registered User.
Local time
Today, 10:27
Joined
Jun 30, 2017
Messages
119
Thanks Minty.

But still dont work. Name? error.

Code:
=DCount("ID","major_count","[reportnumber] = " & [Me.reportnumber])

Im starting to think its corrupt.
Its a form control source.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:27
Joined
Sep 21, 2011
Messages
14,038
You could always try it with a hard coded value in the immediate window.?
I sometime do that to cross check something.
If the form is open and you have a breakpoint set, you could even use it as it is.?
When you hover over Me.reportnumber what does it show.?
 

Minty

AWF VIP
Local time
Today, 10:27
Joined
Jul 26, 2013
Messages
10,353
If it's in the forms control source then try

=DCount("ID","major_count","[reportnumber] = " & [reportnumber])

Assuming Reportnumber is a number data type, and that your field or control is called Reportnumber
 

bruceblack

Registered User.
Local time
Today, 10:27
Joined
Jun 30, 2017
Messages
119
Hi Minty. Cheers! It does work ofcourse. But my field has text and numbers (so text it is).

Any idea how to format the code? :(
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:27
Joined
Sep 21, 2011
Messages
14,038
Surround it with a single quote '
Code:
=DCount("ID","major_count","[reportnumber] = [COLOR="Red"]'[/COLOR]" & [reportnumber] & "[COLOR="red"]'[/COLOR]")
Hi Minty. Cheers! It does work ofcourse. But my field has text and numbers (so text it is).

Any idea how to format the code? :(
 

Users who are viewing this thread

Top Bottom