SELECT DISTINCT or equivalent

[kpo]

Hello,



I have created a table that houses our inventory. I also have created a split form to be able to query the table. The form currently has list boxes where the recordsource property is based on what is chosen from the manufacturers list box. The query is set to use the list box values as it's criteria.

I would like to have the list boxes populate its choices from the records already entered in our inventory. The "SELECT DISTINCT" function would be perfect but I remember reading somewhere that it only works with "active queries"? (If I remember correctly)

is there some other code or expression that would work?

I am a little scatter brained today so if you need more info or this doesn't make sense let me know. THANKS IN ADVANCE!

[June7]

I use the SELECT DISTINCT just as you describe without issue. Don't know what 'active queries' means.

[kpo]

June7,

I have tried SELECT DISTINCT and can't seem to get it to work. I am fairly new to writing code in VBA. Maybe I am just not writing it correctly. Is that anyway you could paste a copy of your code for me to see? It would be greatly appreciated! Thanks!

[June7]

It is not VBA code. It is an SQL statement in the combo or list box RowSource property. Examples:

SELECT DISTINCT SampledFrom FROM SUBMIT WHERE SampledFrom Is Not Null;

SELECT DISTINCT StateNum FROM Projects ORDER BY StateNum;

[kpo]

Thanks. That works great for my first combo box.

Our inventory comes from 4 different manufacturers that make different types and different sizes for each type. Example, the manufacturer Limitorque makes a type L120 that come in a size 10, 20, 40, 85, etc. Limitorques type called LY comes in 1001, 2001 & 3001. While EIM makes an EB that comes in a 000, 00, 0, 1, 2, etc. Among many other types with their own sizes.

When we search our inventory we need to know the manufacturer, type and size. So the query is set to use these three combo boxes as it's search criteria.

Is there a way to tell my "type" combo box, using the SELECT DISTINCT statement, to read what is chosen in the manufacturers combo box and only show the appropriate types? And the same for Sizes?

Again, THANKS SO MUCH FOR YOUR HELP!!! I really appreciate it!!!

[June7]

You want cascading (dependent) combo/list boxes.

Review tutorials at http://datapigtechnologies.com/AccessMain.htm especially the 3 on comboboxes in the Access Forms: Control Basics section.

[kpo]

thank you.

[kpo]

June7,

I watched the videos on combo boxes in the link you gave me. I was particularly excited about the multiple column combo box but quickly found out we just have too many different options. That drop down menu was a little overwhelming. So I am back to square one. (Technically step two thanks to your redirection of where to actually put the sql statement, haha no wonder it wasn't working for me!)

I originally had combo boxes that looked up records in tables I created with complete lists of the different types and sizes using VBA and the select case statement. However, we do not have all the different types and sizes in inventory. So, instead of searching and the query possibly returning no results, I would rather the user only be able to choose from what is already in inventory.

Here is the row source statement I am using for the Manufacturers combo box (cmboManu),

SELECT DISTINCT [Manufacturer] FROM [Whole Unit Inventory];

which works perfectly but I am trying to get the Type combo box to do the same.

SELECT DISTINCT [Type] FROM [Whole Unit Inventory] WHERE [Manufacturer] = [cmboManu];

I am having trouble with the where statement. Is there anyway I can set the where statement to equal the value from the Manufacturer combo box? I have unsuccessfully tried several different ways. :/

Thanks again!

[June7]

Is cmboManu a multi-column? Is its RowSource including a ManufacturerID and Manufacturer name? Is the Manufacturer field in [Whole Unit Inventory] the actual name or an ID value? Did you try:

SELECT DISTINCT [Type] FROM [Whole Unit Inventory] WHERE [Manufacturer] = Forms!yourformname.[cmboManu];

Do you want to provide project for analysis? Follow instructions at bottom of post?

[kpo]

June7,

No, cmboManu is not a multi-column and we don't have use an ID system for our manufacturers. The Manufacturer field in [Whole Unit Inventory] is the actual name.

When I changed the statement to reflect what you posted above and flip back to form view a pop up box asks me to enter a parameter value.

Will I need to assign a number to the manufacturers to make this work?

I know I say it in every post but I really do appreciate all your help with this! Thank you!

[June7]

Assigning number is not necessary. Access can't find the cmboManu control. Did you put your form name in place of yourformname?

I haven't actually used dependent comboboxes much so just did a test. Reference with the form name should not be necessary. Your original SQL should work.

Again, want to provide project for analysis?

[kpo]

LSA Inventory Database - Copy.zip

Here is a smaller version of the pertinant objects in my database. Thanks.

[June7]

The QueryUnitInventory refers to a field [Status] which is not in the table.

The form and subform container have the same data source. Recommend instead of form/subform, just a single form. Move the unbound search controls and button into form Header section. Set form to Single or Continuous view. Set form RecordSource to the query (if you want all data, add fields to the query). Create bound textboxes in the Detail section. Arrange controls and resize sections.

The Manufacturer field in table has a Lookup set to a table Manufacturers which is not in the db. When I created control in Detail section, it adopted the Lookup from table. I never set Lookup in table.

I removed the form qualifier from the cmboType RowSource statement and it worked:
SELECT DISTINCT [Type] FROM [Whole Unit Inventory] WHERE [Manufacturer]=cmboManu;

Should RowSource for cmboSize be: SELECT DISTINCT [Size] FROM [Whole Unit Inventory] WHERE [Manufacturer]=cmboManu AND [Type]=cmboType;

I removed the Embedded Macro for the Search button and used VBA. VBA sub behind the form should be replaced with:

Private Sub cmboManu_AfterUpdate()
Me.cmboType.Requery
Me.cmboSize.Requery
End Sub

Private Sub cmboType_AfterUpdate()
Me.cmboSize.Requery
End Sub

Private Sub Command19_Click()
Me.Requery
End Sub

If you want the search to allow for any combination of the three criteria, meaning any could be left blank, use LIKE and wildcard in the query parameters:
Like [Forms]![SearchUnitInventory]![cmboManu] & "*"
Like [Forms]![SearchUnitInventory]![cmboType] & "*"
Like [Forms]![SearchUnitInventory]![cmboSize] & "*"

Then might want a button to clear the criteria combos:
Me.cmboManu = Null
Me.cmboType = Null
Me.cmboSize = Null

[kpo]

June7,

Thank you so much for all your help!!