Avatar of Fritz Paul
Fritz Paul
Flag for South Africa

asked on 

How to get ComboBox on subform to update by VBA for "Refresh All" on ribbon.

I use Access 2010.
My application is assembling ingredients in recipes to form new items. I have a long list of items in tblIngredients. Some of the items are purchased  and others are manufactured in house. Some of the manufactured items may also be used to manufacture other items in the list. My issue here will be that I may not use item X in the recipe for item X.
I have a main form which I use to assemble recipes. the form is named frmRecipe. It has a subform subfrmRecipesIn where the list of ingredients are assembled.
In the mainform (frmRecipe), I have a combobox to select a item from the tblIngredients for which the recipe is to be assembled. It is important that the user may not use the same product for which the recipe is made as an ingredient for itself. (I may not use item X in the recipe for same item X.)
See the problem below at A where I selected Pastrami with IngrID (ingredientID) = 341 (circled).
Now I move to the subform to add some ingredients to the recipe. I do not want "Pastrami" to come up in the combo box list. As you can see at B, Pastrami is on the list. (If i first click on "Refresh All" it is not there.)
Showing the problem.The row source for the combobox is a select query as below in the query builder and also as SQL.
In query builder.
SELECT tblIngredient.IngrName, tblIngredient.IngrCost, tblUnit.Unit, tblOrganization.CompanyName, tblSubType.IngrSubType, tblIngredient.IngrID, tblType.IngrTNameID
FROM ((((tblIngredient LEFT JOIN tblIngrLevel ON tblIngredient.IngrLevel = tblIngrLevel.LevelID) LEFT JOIN tblOrganization ON tblIngredient.IngrSupplier = tblOrganization.OrganizationID) INNER JOIN tblType ON tblIngredient.MainType = tblType.IngrTNameID) INNER JOIN tblSubType ON tblIngredient.StockType = tblSubType.SubID) LEFT JOIN tblUnit ON tblIngredient.IngrUnit = tblUnit.UnitID
WHERE (((tblIngredient.IngrID)<>[Forms]![frmRecipe]![IngrID]) AND ((tblType.IngrTNameID)<>5 And (tblType.IngrTNameID)<>7 And (tblType.IngrTNameID)<>10))
ORDER BY tblIngredient.IngrName, tblSubType.IngrSubType;

Open in new window

My problem is that I cannot get the combobox to exclude the ingrID in the main form. except if I first click on "Refresh All" on the ribbon.
If I use the same SQL in a seperate external named query, it does get the right results as soon as I open it, whether I use "Refresh All" or not.
I need to be able to achieve "Refresh All" immediately after selecting the item in the main form so that the combobox will update.
Please help. This is a big issue and I have tried all I can think of.
VBAMicrosoft Access

Avatar of undefined
Last Comment
Fritz Paul

8/22/2022 - Mon