Search code examples
sqlvbams-access

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

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,
enter image description here

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.

Solution

  • 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.