I have built a form with tab control. The listbox is on tab2 not bound to any control source. I simply want a listbox that populates from a rowsource query. The form is saved with an empty Control Source value, and in code, I update the property with the query SQL. Row Source Type is table/query.
When my code updates the Row Source property, nothing happens, list box is empty. I've also tried a variety of methods like Requery, Refresh, Repaint. and nothing.
In the code below, the various things I have tried are commented out. I am using a After Update from a combo box on tab 1. The debug.print query is copy and pasted into the MySQL client and runs perfectly.
Can anyone suggest why the list box will not update. A couple hours searching has resulted is some answers (hence my commented out attempts) but nothing works.
It "may" be that the query is based on linked tables. Maybe I need to build it as a pass-thru?
Code:
Else
'Display the found record in the form tab#1.
Me.Bookmark = rs.Bookmark
' Update the ingredients list box on tab#2
squery = "SELECT ingredients.ingredient_id, ingredients.ingredient_name, " & _
"recipe_items_links.recipe_ingredient_qty, uom.uom_description " & _
"FROM recipe_items_links " & _
"LEFT JOIN ingredients ON ingredients.ingredient_id = recipe_items_links.recipe_ingredient_id " & _
"LEFT JOIN uom ON ingredients.ingredient_uom = uom.uom_id " & _
"WHERE recipe_items_links.recipe_id = " & Me.recipe_id & ";"
Debug.Print squery
'Set rs2 = db.OpenRecordset(squery)
Me.ingredient_list.RowSource = squery
'Me.ingredient_list.Recordset = rs2
Debug.Print ingredient_list.RowSource
'Me.ingredient_list.Requery
'Me.Repaint
'DoCmd.save acForm, "Recipes"
'Me.Refresh
End If
Set rs = Nothing
Thanks in advance for any help.
Cheers.