Listbox row source is query with a control as criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want a listbox to list the results of a query; the query has a criteria of
a value in a "person_index" control on my form. The idea is that, when the
form moves to a different record, the listbox would show the results of the
query with the new "person_index" value from the new record.

When I navigate to a new record in my form, the listbox doesn't change
values; it is as if the query doesn't note the change in the value of the
"person_index" control.

The query is in a SQL statement in the listbox's Row Source propery. The SQL
statement references the "person_index" control on the form. It reads the
control fine for the first record but doesn't note the change in value when
viewing another record using the form. The statement works fine if I change
the criteria to a constant (as a test).

I tried using a subform, but I can't select the items in the subform
(selecting the items is the whole point).

What am I doing wrong?

Thanks in advance for your help!

Don
 
Don Starnes said:
I want a listbox to list the results of a query; the query has a
criteria of a value in a "person_index" control on my form. The idea
is that, when the form moves to a different record, the listbox would
show the results of the query with the new "person_index" value from
the new record.

When I navigate to a new record in my form, the listbox doesn't change
values; it is as if the query doesn't note the change in the value of
the "person_index" control.

The query is in a SQL statement in the listbox's Row Source propery.
The SQL statement references the "person_index" control on the form.
It reads the control fine for the first record but doesn't note the
change in value when viewing another record using the form. The
statement works fine if I change the criteria to a constant (as a
test).

I tried using a subform, but I can't select the items in the subform
(selecting the items is the whole point).

What am I doing wrong?

Thanks in advance for your help!

Don
Click to expand...

You just need to tell the list box to requery itself each time you come
to a new record or change the value of person_index. That's two events
you need to write code for:

'----- start of code -----
Private Sub Form_Current()

Me.lstMyList.Requery

End Sub

Private Sub person_index_AfterUpdate()

Me.lstMyList.Requery

End Sub
'----- end of code -----

If person_index is an autonumber or otherwise uneditable field, you
don't need the proc for its AfterUpdate event.
 
Back
Top