Solved

MS Access - Check Box to show updated records in List Box

Posted on 2009-04-11
5
349 Views
Last Modified: 2013-11-28
Hi -
I have three tables with the column Updated_Date

Company.Updated_Date
Contacts.Updated_Date
Activity.Updated_Date

What I would like to happen is:
If the Updated_Date in any of the above tables is greater than 1/1/2009,
then I would like the Companies to show in my ListBox (Control Name: List16)

My form name is CompanyMain

Currently the rowsource for List16 is:
    Me.List16.RowSource = "SELECT Company.Company_Name, Company.DUNS_Number FROM Company GROUP BY Company_Name, DUNS_Number;"

Note: Company.DUNS_Number is Unique and shown in all tables.

If the Box is UnChecked the ListBox would revert to it's orig list.
Or I have a Reset Command Button I could add a line to to clear the criteria...
Thanks for your help.
Stacy

0
Comment
Question by:stacydr
  • 2
  • 2
5 Comments
 
LVL 5

Expert Comment

by:brandonvmoore
Comment Utility
Are you saying that these 3 tables are related by the DUNS_Number field?
And are you just needing help with the construction of the SELECT statement, or do you need help with more than that?

I'll see if I can post an answer tomorrow evening if someone else doesn't get to it first.
0
 
LVL 5

Expert Comment

by:brandonvmoore
Comment Utility
Sunday evening, that is
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
Comment Utility
To get the rowsource for your listbox you will have to find all the DUNS numbers from all the tables that have an updated date >1/1/2009.

You do this in a Union query.

Select Duns_Number from Company where Updated_Date >=#2009/01/01#
Union
Select Duns_Number from Contacts where Updated_Date >=#2009/01/01#
Union
Select Duns_Number from Activity where Updated_Date >=#2009/01/01#

Save this as qry1

Change the rowsource to:
"SELECT Company.Company_Name, Company.DUNS_Number FROM Company
Inner Join qry1 on company.DUNS_Number = qry1.DUNS_Number
 GROUP BY Company_Name, Company.DUNS_Number;"



0
 
LVL 2

Author Comment

by:stacydr
Comment Utility
Hi Brandon.. thank you for your post, I wasn't quite sure what I needed, but Peter..'s ideas worked well.
Thanks again.
Stacy
0
 
LVL 2

Author Closing Comment

by:stacydr
Comment Utility
Hi peter... thank you for your solution, I wasn't sure what kind of formula or function I needed and this worked well.  Thanks again..
Stacy
0

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

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

763 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

8 Experts available now in Live!

Get 1:1 Help Now