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

Admittedly I am a bit of a newbie, so please be patient :)

I'm trying to develop a form on Access 365 where the user selects values from a series of combo boxes on the form, the combo boxes are based on look up tables that I've created in the DB.

What I am trying to do is if I set the one field value to say carpet, enter image description here

I want to have 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, however 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 assigned in the VB script doesn't carry over to the SQL build function. So I am assuming that the VB script and SQL Designer do not talk to each other.

Is there another way to do this?

CodePudding user response：

Assuming VBA, consider having your query point to a user-defined function saved in a standard module (i.e., not behind form or report) of an Access project:

VBA

Public Global ProductType As String: ProductType = "some value"

Public Function GetProductType() As String
   GetProductType = ProductType
End Function

SQL

SELECT DISTINCT Flooring.[Product Name], Flooring.[Flooring Type]
FROM Flooring
WHERE Flooring.[Flooring Type] = GetProductType();

However, you can simply run conditional combobox recordsources.

SELECT DISTINCT Flooring.[Product Name], Flooring.[Flooring Type]
FROM Flooring
WHERE (((Flooring.[Flooring Type])=[Forms]![Selections Flooring Type]![Product Type]));

But be sure to run Requery on the AfterUpdate event of the preceding combobox:

Private Sub Product_Type_Combo_AfterUpdate() 
   Me.Product_Name_Combo.Requery
End Sub

NOTE: Because you are using continuous forms, the .Requery even will run on all displayed, repeating comboboxes which will cause some displayed data to wipe out if condition on combobox is not met for that specific row. Rest assured the data will remain in underlying table. This may cause an undesired user experience.

One workaround includes resetting combo box back to all possible product types (i.e., no WHERE clause) when user loses focus of the Product Name combo box (LostFocus event) and when user navigates across rows (CurrentRow event). This in turn may cause you to .Requery to the conditional subset on the GotFocus event in addition to above AfterUpdate of preceding combobox. Basically, you will toggle between the full set and conditional recordsource queries.