Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-04-11
5
Medium Priority
?
359 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
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 2000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

885 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