Dlookup query value in continuous form by ID

B

bruceblack

Registered User.
Local time
Today, 04:24
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!
 
Have to ask, why are you looking up something you already know.?
 
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
 
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:
 
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
 
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])
 
Aren't you the user who uses ; instead of , ??
 
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.
 
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.
 
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.?
 
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
 
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? :(
 
Surround it with a single quote '
Code: 
=DCount("ID","major_count","[reportnumber] = [COLOR="Red"]'[/COLOR]" & [reportnumber] & "[COLOR="red"]'[/COLOR]")
bruceblack said:
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? :(
Click to expand...
 
You must log in or register to reply here.

Similar threads

E
Solved DLookup value display with a minus sign
Replies
16
Views
784
Mark_
M
H
Solved Dlookup Or SetValue not working
Replies
17
Views
685
mike60smart
mike60smart
I
Get a field ID
Replies
3
Views
616
Isaac
Isaac
L
Continuous form with different rowsource in same combobox
Replies
4
Views
167
linux_s2002
L
S
Solved Create new child record with modal dialog?
Replies
16
Views
769
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom