Count Related Records in other Table and if >1 change control

Posted on 2012-08-24
Last Modified: 2012-09-10
I want to populate a control (not bound) on my continuous form when there are records in a different table.  

I've gotten this far but need help making the control change.  
DLookup("Count(*)", "[BoardComments]", "[BoardID] = " & Me.BoardID)

Something like this?
DLookup("Count(*)", "[BoardComments]", "[BoardID] = " & Me.BoardID)>0
End If

Doesn't like that.  Suggestions?
Question by:Scotto123
    LVL 77

    Expert Comment

    If you use an unbound control, every record will show the same value as derived from the current record.  You cannot show different values in different records.

    Author Comment

    That's why I'm using the Dlookup to lookup the BoardID from the current record.  
    Still won't work?

    Author Comment

    I see your point Peter.  So if I bound the control to the table, how would the code look?
    LVL 77

    Accepted Solution

    If you have bound controls then the code you posted should work. I assume the code would go in the form current event, so you would only get the result for a record when you moved to that record.

    If you want to see the results for all records as soon as you open the form,  then you would have to run an update query to set the 1's and 0's  on your table before opening the form, or maybe in the form open event.  Then you would not need any further code in the form for this purpose.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    Examine Pete's suggestion first.

    For strictly informational purposes, here is something similar I did for another similar question
    (If a Customer had Orders)

    Author Comment

    I will work on this when I get back to work on 9/3.  I will take both solutions into consideration.  Thank you.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now