Solved

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

Posted on 2009-04-11
5
354 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 5

Expert Comment

by:brandonvmoore
ID: 24124828
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
ID: 24124829
Sunday evening, that is
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 24124853
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
ID: 24127948
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
ID: 31569278
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

628 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