Unanswered: Problem using the results of a parameter query as a listbox rowsource
Dear DB forum users,
I have been a user of Access 2007 for a few years now, and I have made increasing use of forms to display and navigate through my data. In my current situation, I am making use of a single table, called People. I have based a form on this table, also called People. In the table, I have columns which serve as a unique identifier for a single individual, which I called "PersonID". I also have a column for each individual called "MotherID" which the unique identifier of that individual's mother.
I wish to use this information on a form that I have built. On the form, I wish to display the record of a single individual. In addition, I hope to build a listbox which shows a list of all the PersonID values of all the individuals in the People table who are children of the individual in the current record. The current individual displayed in the form is referenced in a textbox called PersonIDinForm. To select all the records from the People table of those individuals who claim the currently displayed individual (PersonIDinForm) as a mother, I have built a parameter query which in SQL is as follows:
This select query works perfectly when I execute it alone, and it produces its results in datasheet view. However, if I instead wish to use the results of this query as the rowsource for a listbox (which would be a better way to display these results in the form), and I insert this same SQL as the rowsource for the listbox, then the listbox remains completely blank as I scroll through records. I have tried various other solutions which I thought could circumvent this problem, such as, instead of inserting the raw SQL into the rowsource of the listbox, I have also attempted to use the select query file itself as the rowsource to the listbox. These attempts and others have failed entirely. Is there any person out there who can tell me why this is not working, or what I might do to work around this issue? Is there a problem with using a parameterized query as the rowsource to a listbox? Do I need to build a macro or add code which forces the listbox to requery? Any help is greatly appreciated, this has been tearing me up.