Using Listbox to find records

[John Wirt]

I have a simple form configuration but I cannot make it work. I have
Access 2003.

A Form is bound to a Master List table (IDCode, List item name). Both
fields are included on the form. The list has 3,000 items. IDCode is unique.

A second table contains subitems for each item. A one-to-many
Relationship is defined in the database.

The New Subform wizard was used to create a linked subform on the main
Form. The subform works. It is bound to a table called Transcripts
(IDCode, Subitem name). Changing the main form record number with the
arrows at bottom of screen changes the subitems displayed in the form.
The correct subitems are displayed.

Then I used the ListBox wizard to create a listbox for finding the
record to display. I followed the instruction in Help: "Find a record by
selecting a value from a list." The wizard added this code to the
AfterUpdate event:

Private Sub ListBox_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID_Code] = " & Str(Nz(Me![ListBox], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

The Listbox does not work. When I click (or doubleclick) on a ListBox
item, nothing happens. The first item has a faint, dotted box around it,
but this does not change. Hence the Subform still shows the subitems for
the first record in the Listbox.

What am I doing wrong?

Thanks.

John Wirt

 

[Guest]

Hi John

I can't see anything wrong with the AftterUpdate event.

What is the Row Source Type and the Row Source of the list box ?

 

[John]

The row source is,
SELECT [ELS2002 Master Course List].CSSC_Code, [ELS2002 Master Course
List].[COURSE TITLE] FROM [ELS2002 Master Course List];

These are the correct form and variable names.

The row source type is Table/Query.

Thanks for responding. I cannot figure out why listbox selector for
this simple form/subform does not work.

The link between the Master and Child tables is detemined by a
relationship. Should I base the forms on a query.

John