Customizing MS Access Form field Look-up based on results from another field value on form

Question

I'm trying to develop a form on Access 365 to selects values from a series of combo boxes on the form. The combo boxes are based on look up tables in the DB.

If I set the one field value to say carpet,

I want the Product Name field to only pop up the product names for carpet.
By default the look up list of the Product Name field will show all the possible values for all the Flooring types.

I tried to code event VB scripts to capture the Flooring Type value as a Global variable, and I am able to show a message box that the variable is available when accessing the Product Name combo box.
I would like to adjust the row source query for the Product Name query to only return the values that match the Flooring type that I selected

SELECT DISTINCT Flooring.[Product Name], Flooring.[Flooring Type]
FROM Flooring
WHERE Flooring.[Flooring Type] = ProdType
ORDER BY Flooring.[Product Name];

WHERE Flooring.[Flooring Type] = WHERE (((Flooring.[Flooring Type])=[Forms]![Selections Flooring Type]![Product Type])) doesnt' seem to work either

Issue is that the ProdType global variable that I assign in the VB script doesn't carry over to the SQL build function. I assume the VB script and SQL Designer do not talk to each other.