Row Source query to filter Combo Box Values

[bubai]

In my database I have a lookup table CatagoryDescription where I got 3 columns – PrimaryKey, Category, Description. Category column has 4 options “Cig”; “Liq”; “Cos”; “Con”. Each of these categories have subcategory. For example, “Cig” has Benson, Devidoff, Marlboro. “Liq” has Beer, Whisky, Rum, Wine, Vodka. “Cos” has Perfume, Shampoo, Lotion, Powder. “Con” has Chocolate, Biscuit, Juice, Soup. Again, I have another table named Consignment where I record all imports. This table also has Category, Description columns. Category column is also limited to 4 options “Cig”; “Liq”; “Cos”; “Con” by Combo Box. Description column’s Display Control is set to Combo Box and Row Source Type is set to Table/Query. Now, when I click on the Description column’s combo box, I want it to generate only those subcategory items that falls under the Category that I have chosen on this row. Which means, if I have selected “Cig” on the Category column of Consignment table, the Description column’s combo box should generate only Benson, Devidoff, Marlboro and nothing else, because these 3 options are given on the CatagoryDescription table. Same way on the next row of the Consignment table if I have selected “Liq” on the Category column, the Description column’s combo box should generate only Beer, Whisky, Rum, Wine, Vodka and nothing else. I tried to achieve this using the following query on Row Source, but does not work accordingly.

SELECT DISTINCT CatagoryDescription.Description FROM CatagoryDescription INNER JOIN Consignment ON CatagoryDescription.Catagory = Consignment.Catagory;

Any help please?

[ranman256]

make the base query for the form show all data,
then in the AFTERUPDATE event of the combo box run:

Public Sub cboCatagory_AFTERUPDATE()
Dim sWhere
If IsNull(cboCatagory) Then
   Me.FilterOn = False
Else
  sWhere = "[field] ='" & cboCatagory & "'"
 
  Me.filter = sWhere
  Me.FilterOn = True
End If
End Sub

[ssanfu]

In my database I have a lookup table <snip>

From your description and looking at the image, you DO NOT have a Look up table! You have a Look-up FIELD.
NOT a good idea. Most (if not all) experienced developers avoid Look-up FIlELDs.
As to why, see The Evils of Lookup Fields in Tables

Also, I'm not sure you can look up data from one table to another table. But I NEVER use Look-up FIELDS, so what do I know?
If you could, I would think both tables would need to be open at the same time....

Unless this dB is for you only and it is a quick and dirty dB, you should be using forms to enter and display data.

If you were using forms, this would be easy....

[Micron]

I would offer that the consignment table also holds redundant data. Methinks all you need in that table with respect to category is the category ID.

[bubai]

From your description and looking at the image, you DO NOT have a Look up table! You have a Look-up FIELD.
NOT a good idea.

Dear ssanfu,
I don't have a look-up field either as field values are not coming from another table as ID. But in this case Category, Description columns of both table has same data type - Short text. And Combo box of both Category columns values are Limited to List - “Cig”; “Liq”; “Cos”; “Con”. That's why I was thinking to build a query for Description column's Row Source which would enable me narrow down the values according to what I choose in Category column. My query returns all the values from the Description column of CatagoryDescription without any distinction, which is not what I am looking for. Should I use a sub-query or perhaps a correlated sub-query?

[Micron]

but does not work accordingly.

Doesn't work doesn't help.
My guess is that means you get repeated options even though you've used DISTINCT. If that's not it, then I'm about to waste your time and mine.
You can try DISTINCT ROW, but whether or not that helps depends on what fields are involved. I'm thinking it might because there is only one involved.
As I said, your description reads like the data is redundant. By repeating the same values in more than one table, you will get duplicates because of the join. There are, after all, pairs of values. You could also try Left or Right joins but I don't see that working as both sides contain the same values (I'm getting repetitive here)
When consigning Beer, your consignment table should only hold the PK value from category - 4 and any other values related to the Consignment of which Description is not one of those values.

[ssanfu]

Rather than play 20 questions , maybe you could/would post your dB for analysis. Then we could see what is actually happening/not happening.

[bubai]

maybe you could/would post your dB for analysis.

HI ssanfu,
I'm attaching the database named Import. In it I've created a parameter query which asks for Category and if I put in “Cig”/ “Liq”/ “Cos”/ “Con”, it returns the result accordingly. The SQL is -

SELECT [_CatagoryDescriptionLookup].DescriptionFROM _CatagoryDescriptionLookup
WHERE ((([_CatagoryDescriptionLookup].[Catagory<])=[]));

Distinct keyword was not required actually, as in the _CatagoryDescriptionLookup table combination of Category, Description columns are unique. Now in this code if I could replace the [] (ie the parameter) with something which fetches the record on the Category column of Consignment table and put that code on Description column's Row Source on Consignment table then the job is done.

[bubai]

Hi Micron,
Please check the screenshot which shows the result if I click the Description ComboBox of Consignment table. In the previous reply I have added the query that almost does the job. What I need is to replace [] (ie the parameter) with something which fetches the record on the Category column of Consignment table.

[apr pillai]

The second Combo Box RowSource SQL method example: https://www.msaccesstips.com/2008/03...-contents.html

[Micron]

Hi Micron,Please check the screenshot

Regardless of what you mentioned, it sure looks like a lookup field to me. Are you sure you know the difference?
Two others have downloaded your db already so I will leave this in their capable hands.

[ssanfu]

@bubai, this is not meant to be mean or burn you.

This design is NOT the way I would design this dB. Granted, I know nothing about what you are trying to accomplish, but there are some things that really need to be corrected (IMHO).

After looking at your dB this is what I found:
~ 1 calculated FIELD
~7 Look-up FIELDs (different than Look-up TABLES)
~ 9 Value List FIELDs
~ Special characters in field names
~ special character in table name
~ 4 PK fields named "ID"
~ one table with a 4 field compound PK field
~ one table without a PK field.
~ 4 Text type fields used as a PK field. Should use a compound index instead of making them a compound PK.

Tables store data. Should not be trying to fill other table/fields with data from a different table.
In Post #3I posted the link to The Evils of Lookup Fields in Tables - for me, this includes Value List FIELDs in tables.

You should read this site (at least 4 times) Microsoft Access Tables: Primary Key Tips and Techniques
I ALWAYS use an Autonumber type field as the PK field.

Names:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not begin an object name with a number.
Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.




Did you sit down and design the dB structures using pencil/paper, whiteboard, etc BEFORE jumping into Access??