CODEWITHSUNDEEP
ms-accessvbams-access-2013
jstyler
jstyler

Reputation: 3

Populating listbox from another listbox selection

I am pretty much a newbie to using VBA in Access and I'm having trouble with something that seems like it should be quite simple.

I have two listboxes (called LB1_ID and LB2_ID) on my form (MainForm) that I want to list related IDs from their respective Row Sources. I need LB2 to be populated based on the selection in LB1. They both have Row Sources from the same Table (Table1) and it is a many to many relationship of Requirement IDs ("Req ID1" and "Req ID2"). My current form, which is not working, has the Row Source of LB1 as:

SELECT Table1.ID, Table1.[Req ID1] FROM Table1 ORDER BY Table1.ID;

and the Row Source of LB2 as:

SELECT Table1.ID, Table1.[Req ID2] FROM Table1 WHERE ([Forms]![MainForm]![LB1_ID]=Table1.[Req ID1]);

When I make a selection in LB1, nothing happens in LB2. The column widths are formatted correctly and I can get it to work if I use Me.[Forms]![MainForm]![LB1_ID] but I have to type out the LB1 selection manually in a popup box if I use that.

What am I missing?

Upvotes: 0

Views: 3764

Answers (2)

MoondogsMaDawg
MoondogsMaDawg

Reputation: 1714

If your listbox is multi-select, you cannot use a simple form reference as query criteria. If it is not multi-select, keep in mind that its value may be a hidden column (usually an ID field), so there are two possible issues and solutions:

Possible Issues:

  1. Single-Select listbox has an ID field that is hidden (column width = 0") and you are matching it to the wrong field in your table. To check the output of the listbox, open the VBE and type ?[Forms]![MainForm]![LB1_ID] into the immediate window and press enter when your form is open in form view and a row is selected in LB1_ID. If the returned line is what you expect, then the problem must be elsewhere.

  2. Multi-Select listbox property is enabled. In this case, your query will not work, because the listbox will only return Null. You will need to write some VBA to loop through the rows and figure out which ones are selected, which is a bit of a pain. Ultimately you'll build some code that will alter your query with the specific criteria for each selected row. Instead of explaining here, take a look at this article for a tutorial.

The .Requery method is still important to put in the AfterUpdate event of your first listbox to refresh the second.

Upvotes: 1

LostReality
LostReality

Reputation: 687

Your query seems to work, but you need to refresh your listbox2 whenever you make selection into listbox1, so if both listbox are in the same form add this event handler :

Private sub LB1_ID_Change()
  Me.LB2_ID.Requery
End sub

Without this, your listbox2 will only get populated once on load based on the initial value of listbox1.

Also, if you have not already done it, I would recommend to add your listbox1 control as a parameter into your listbox2 query (in query builder, right click -> parameters).

Upvotes: 1

Related Questions