Listbox not functioning as expected???

[jmullan99]

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?

        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.

[Micron]

first take your debug.print output, paste it into a new query in sql view (just close the dialog for choosing tables) and run it. If it's an action query that you don't want to run at this point, simply choose datasheet view. If you get an error or no records, then there's something wrong with your sql.

Also, you cannot refer to a listbox on a tab control using just Me.lstNameOfListbox. You have to include the tab control, and if it has more than one page, usually the page name/number is needed also. Verify the sql works first.

[jmullan99]

Thanks for jumping in. The query does indeed work. I've copied and pasted the code into an Access query SQL view and it runs fine. Again, that query was set up as a pass-thru. But there is still nothing in the listbox.

Current code state for simplicity sake

            ' 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

            Me!ingredient_list.RowSource = squery
            
            Debug.Print ingredient_list.RowSource

[John_G]

Me!ingredient_list.RowSource = squery

That itself does not update the listbox contents. You have to requery the listbox after changing the row source:

Me!ingredient_list.RowSource = squery
Me!ingredient_list.Requery

[Micron]

I've also tried a variety of methods like Requery, Refresh, Repaint

On what? The form? If you alter the rowsource of a control, you have to requery the control. I don't see where you do that in the code.

With Me.ingredient_list
.RowSource = squery
.Requery
End With

or
Me.ingredient_list.RowSource = squery
Me.ingredient_list.Requery

Note that if you use Me!ingredient_list instead of Me.ingredient_list, a typo on the control name won't be caught until run time. It will not be caught when compiling your code. Most often its use is explained as being needed when the preceding word denotes that what follows belongs to a collection, such as Forms!frmMain or Reports!rptMyReport, but that's not entirely accurate.

[jmullan99]

Thanks folks. I have indeed tried .Requery (its one of the commented out lines in first post).

After messing around a bit, the following is the code that is working for me. I made a QueryDef and saved it, and made it the Row Source for the listbox. I open it, change the SQL as necessary, and update the Row Source property (even though it is the same QueryDef name every time). Why the other methods I have seen, and people tell me, haven't worked is beyond me.

            ' 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 q = db.QueryDefs("list_ingredients")
            q.SQL = squery
            q.Close
            
            Me!ingredient_list.RowSource = "list_ingredients"

[Micron]

What about the proper way to reference a list box on a tab control mentioned in paragraph 2 of post 2. I don't see where you corrected that.

[jmullan99]

I actually tried a couple variations of referencing that didn't work (but I am rather novice about the exact proper ways to reference in various scenarios). But still the assignment to RowSource in my second code sample DID work. I'm still leaning toward the fact that it doesn't work to assign the SQL if it is pass-thru. But does work to use the QryDef and change the SQL as necessary, then assign.

[John_G]

Micron -

Also, you cannot refer to a listbox on a tab control using just Me.lstNameOfListbox. You have to include the tab control, and if it has more than one page, usually the page name/number is needed also.

Are you sure about that? I just tested it, and I was unable to make any syntax work using the tab control in the reference. From the main form, I was able to reference a list box on the tab control just using Me!List13, just as with any other control. I think controls on tab pages are not part of the tab control; they are still on the main form.

[Micron]

I guess I'm not. I used MSDN or other web pages on how to reference controls on a tab control as the source behind that statement. You are saying that regardless of how many pages a tab control has, you can refer to any control on any page via Me!nameOfControl? What about Me.nameOfControl?

[moke123]

both me. and me! will work. There is no need to reference a page in a tab control unless of course you are setting focus to that page.

[June7]

Tab control has no bearing on how controls positioned on it are referenced.

Recommend you use dot (.) to trigger intellisense popup tips as well as the compile validation. The bang (!) will not do either.

I do use ! when referencing fields of recordset object.

[Micron]

The only distinction I do feel confident in is that what follows Me! won't be evaluated at compile time. That I have tested.