Using a listbox on a subform to select subform record.

[caddcop]

I have used the Controls Wizard to create both listboxes and combo boxes that can be used to select a record on the form and to jump to that record.



Now, I have some subforms which return a limited number of records. What I'd like to do on the subform is to list a particular field's values in a listbox that is limited to the matching values on the subform records and to allow users to click on the list to jump to that matching record.

Currently, I am getting very mixed results on my efforts.
For example, my subform contains the following fields from a table:

• ID - Unique identifier for a project
• Indx - alphabetical (A, B, C, etc.) based identifier - unique for each ID
• Name - company name
• Location - company location
• Role - company role on project ID

On any matching ID records, there are one or more Names within that set of matching records.

So my listbox should only list the Name where the ID is the same as the ID on my main form current record. The subform already is linked via the ID, but the listbox is not filtering the results in the same way that the subform does.

[caddcop]

OK, I fingered it out.
I set the listbox Row Source Type to Table/Query and left the Row Source blank. You also need to set the Control Source to the field/control that the listbox will be populated with and eventually interact with.
Then in the subforms Current() procedure you build an SQL string and then assign the listbox RowSource property to the SQL string.
For my needs, to get a listbox populated with Descriptions, my sql string needed to be assigned as:
strSQL = "Select Description, ID from Projects_Descriptions where ID = '" & Me.ID.Value & "' Order by Description"
I also made the subform order the records by the Description field. The result is that when a new record is displayed in the main form the listbox is populated by all records matching the ID field. And the current record in the subform is automatically selected in the listbox.
If you fail to set the Control Source to the field or control, it will list the matches, but none are selected.
All I need to do now is to add was some code using a recordset and bookmarks. But, those had the unfortunate effect of changing the field value instead of jumping to the proper record.
Looks like a new post will be in order.