Binding a ListBox to a query in MS Access 2003

Question

I have a very simple application with two tables, ParentTable and ChildrenTable. Each child in the table has a foreign key to a parent (parent-child relationship is one-to-many).

When I display a form with parent info, I want to display a ListBox with all the parents children.

I already got a ListBox that displays all the children, but I'm not sure how to change the query so that I see only the active parents children (the parent which I am looking at its form right now). It goes like this:

SELECT ChildrenTable.IdNumber, ChildrenTable.FirstName, ChildrenTable.LastName FROM ChildrenTable ORDER BY [FirstName];

I guess I'm looking for something like:

WHERE ChildrenTable.ParentIdNumber == <active parent>.IdNumber

Answer 1

A form named frmParent includes a text box control named txtIdNumber which displays the IdNumber from ParentTable.

Then the query for your list box rowsource can reference the value in txtIdNumber:

SELECT c.IdNumber, c.FirstName, c.LastName
FROM ChildrenTable AS c
WHERE c.ParentIdNumber = Forms!frmParent!txtIdNumber
ORDER BY c.FirstName;

You can update the list box as you move through ParentTable records in frmParent by using this code for the form's "On Current" event:

Private Sub Form_Current()
    Me.YourListBoxName.Requery
End Sub