Retrieving Records based on Record Selection from another table

10 pts.
Tags:
Access Database
Microsoft Access
SQL Database
I have an Access database (which eventually is going to SQL)that contains several tables. One table is contains categories of items that are available for purchase. The other tables contain those items that belong to the appropriate category. In other words I have categories of flowers, vegetables, equipment, etc that are in one table. For each of those categories there is another table that contains the items that fall into that category. I want to create a form for taking an order over the phone that allows the operator to select a category and based upon the category selected, only those items that apply to that category show up on the drop down list for selection. How can I accomplish this? I am using MS Access 2003 at this time and eventully will be putting this database into SQL. Thanks for any help or advice you can give.

Answer Wiki

Thanks. We'll let you know when a new response is added.

In the combo box for the item, put in the Row Source propery a query like this:

Select item, description from items where category = forms![orderentry]![category]

Also, in the Got Focus event of the item, do:

me![item].requery

This will cause the combo box to requery if the category has changed

If you move the database to SQL, this should still workout changing anything assuming you link the tables and they have the same names for columns, etc.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Randym
    I meant to say: If you move the database to SQL, this should still work without changing anything assuming you link the tables and they have the same names for columns, etc.
    1,740 pointsBadges:
    report
  • carlosdl
    For the solution suggested by Randym to work, you would need to change your database design (and I think you should, unless there is a reason for not to do it). If I understood well, you have one different table for the items of each category, but it would probably be better to have one single table for all items, and a field to identify which category the item belongs to.
    69,475 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following