Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-22-2018, 01:25 AM   #1
bruceblack
Newly Registered User
 
Join Date: Jun 2017
Posts: 115
Thanks: 41
Thanked 0 Times in 0 Posts
bruceblack is on a distinguished road
Dlookup query value in continuous form by ID

Hey guys! Hope you can correct some syntax here. Im a bit lost.

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!

bruceblack is offline   Reply With Quote
Old 08-22-2018, 01:35 AM   #2
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,116
Thanks: 423
Thanked 753 Times in 731 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Dlookup query value in continuous form by ID

Have to ask, why are you looking up something you already know.?
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 08-22-2018, 01:38 AM   #3
bruceblack
Newly Registered User
 
Join Date: Jun 2017
Posts: 115
Thanks: 41
Thanked 0 Times in 0 Posts
bruceblack is on a distinguished road
Re: Dlookup query value in continuous form by ID

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 is offline   Reply With Quote
Old 08-22-2018, 01:51 AM   #4
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,116
Thanks: 423
Thanked 753 Times in 731 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Dlookup query value in continuous form by ID

Used Dcount? Dlookup is to obtain a single item that matches certain criteria, not a total/count.
https://support.office.com/en-us/art...a-11a64acbf3d3
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 08-22-2018, 03:49 AM   #5
bruceblack
Newly Registered User
 
Join Date: Jun 2017
Posts: 115
Thanks: 41
Thanked 0 Times in 0 Posts
bruceblack is on a distinguished road
Re: Dlookup query value in continuous form by ID

Code:
=DCount("[minorormajor]","nonconforms_major_count","[ID] = Me![ID]")

Still no luck. I tried many things but it dont know what is going wrong...
bruceblack is offline   Reply With Quote
Old 08-22-2018, 03:53 AM   #6
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,421
Thanks: 162
Thanked 1,735 Times in 1,705 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Dlookup query value in continuous form by ID

You need to concatenate the current form records ID into the Dcount / Dlookup

Code:
=DCount("[minorormajor]","nonconforms_major_count","[ID] = " & 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
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 08-22-2018, 04:29 AM   #7
bruceblack
Newly Registered User
 
Join Date: Jun 2017
Posts: 115
Thanks: 41
Thanked 0 Times in 0 Posts
bruceblack is on a distinguished road
Re: Dlookup query value in continuous form by ID

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])

bruceblack is offline   Reply With Quote
Old 08-22-2018, 05:06 AM   #8
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,116
Thanks: 423
Thanked 753 Times in 731 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Dlookup query value in continuous form by ID

Aren't you the user who uses ; instead of , ??
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 08-22-2018, 07:31 AM   #9
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,421
Thanks: 162
Thanked 1,735 Times in 1,705 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Dlookup query value in continuous form by ID

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.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 08-22-2018, 10:31 PM   #10
bruceblack
Newly Registered User
 
Join Date: Jun 2017
Posts: 115
Thanks: 41
Thanked 0 Times in 0 Posts
bruceblack is on a distinguished road
Re: Dlookup query value in continuous form by ID

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.
bruceblack is offline   Reply With Quote
Old 08-23-2018, 01:05 AM   #11
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,116
Thanks: 423
Thanked 753 Times in 731 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Dlookup query value in continuous form by ID

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.?
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 08-23-2018, 02:55 AM   #12
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,421
Thanks: 162
Thanked 1,735 Times in 1,705 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Dlookup query value in continuous form by ID

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
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 08-27-2018, 01:46 AM   #13
bruceblack
Newly Registered User
 
Join Date: Jun 2017
Posts: 115
Thanks: 41
Thanked 0 Times in 0 Posts
bruceblack is on a distinguished road
Re: Dlookup query value in continuous form by ID

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?
bruceblack is offline   Reply With Quote
Old 08-27-2018, 02:08 AM   #14
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,116
Thanks: 423
Thanked 753 Times in 731 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Dlookup query value in continuous form by ID

Surround it with a single quote '
Code:
=DCount("ID","major_count","[reportnumber] = '" & [reportnumber] & "'")
Quote:
Originally Posted by bruceblack View Post
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?

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Reply

Tags
by id , dlookup , form field , query

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
DLookUp on a Continuous Form shawnacker Forms 5 10-15-2014 01:34 AM
continuous form with query exaccess Forms 5 04-07-2014 12:25 AM
Continuous Form Query louisa Forms 3 07-20-2010 04:50 AM
Continuous Form Query gary.newport Forms 1 10-27-2009 01:38 AM




All times are GMT -8. The time now is 07:46 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World