Filtering based on result of previous column

[jackjsmtih88]

Hi Guys,

I'm creating a stock monitoring database.

in this database you have 3 relevant tables:

OutboundStock
Products
Customers

the one bit im struggling with?

In Products:

[ID](PK) [ProductRange] [ProductName]

theres only 3 ranges, but 50+ products in each range.

So, if i create a lookup field through the wizard, the result = dropdown field in table, when clicked, shows 50x range.

What im trying to achieve is, the lookup box shows the 3 available ranges.

once [ProductRange] is selected, the [ProductName] Field, then limits the list down to products available in the range selected.



I created a lookup box through wizard, and just entered the 3 ranges myself, and then i tried to build an SQL code, for the [ProductName] Column:

SELECT [Products].[ID], [Products].[SlabName] FROM [Products] WHERE [Products].[ProductRange] = [OutboundStock].[SlabRange] ORDER BY [SlabName];


Apparently, the Syntax is incorrect. (i have no idea about SQL) is anyone able to advise what is wrong here, or suggest an alternative please?

Please note: i am trying to accomplish this in a TABLE not a Query

[pbaldy]

Is this what you're trying to do?

http://www.baldyweb.com/CascadingCombo.htm

your SQL includes a table not in the FROM clause.

[jackjsmtih88]

Hi Paul,

Yeah that seems right, the second option anyway!

is there anyway to do that in SQL though and not VBA? you seemed to identify something within my SQL statement. is it fixable or is what im trying just not possbile?

Tahnks for your help by the way

[pbaldy]

The first option uses SQL, other than the requery. Regarding your SQL, you can't refer to a table (OutboundStock in this case) that isn't included in the FROM clause. Typically the two tables would be related somehow. In any case I would think you'd need a criteria from the form.

[jackjsmtih88]

I'm s complete noob when it comes to SQL and VBA. still learning mate.

What i'm thinking is, if i have the data manipulated within the table, the form will do the same once i re-add the fields will they not?

please help me to understand? :-)

[pbaldy]

I don't think it can be done at the table level. It has no events you can work with. Most of us never let users into tables or queries directly.

[jackjsmtih88]

Fair enough mate, well even on a form, how woild i accomplish this? That code looks a bit high tech for me.

So, ok, in a form:

I'd have:

Dropdown box for ProductRange (i might populate this myself with the 3 Ranges, "Silestone", "Dekton" and "Sensa"

Then, depending on what was in that box, it'd then tell SlabName what Data to look for.

In the Table [Products] there are 3 columns, 1, ID(Primary Key) 2, Product Range, and 3, SlabName

So id be looking at something along the lines of:

Private_Sub SlabName() OnClick
If Me.ProductRange.value = "Dekton" Then
Me.Slabname only shows the records that specify Dekton as a product Range.

but because i don't know VBA i don't know how to code it properly :-(

And then some code

[jackjsmtih88]

i'm looking at your cascade code given and i don't even know where to start to make that fit into my code mate.

Sorry for being thick

[June7]

Suggest you complete a tutorial, something like http://webpage.pace.edu/ig53120n/Access%20VBA%20Programming%20for%20Dummies.pdf

The tutorial is written for pre-Access2007 so the Access GUI is different but the VBA editor is same. And the PropertySheet is same.

and review https://support.office.com/en-us/art...7-70649e33be4f

[pbaldy]

That is the code, you just need to replace with your object names. Try the first method, where the second combo's source refers to the first. Then all you need is a requery of the second, either with code or a macro.