Populate a List Box using code

J

Jeremy Ellison

I am trying to use a list box in a subform that is populated with some data
from the subform. The subform is linked to the main form by PersonID, I have
this code in the Row Source:

SELECT tblPerson.PersonID, tblEvent.Date, tblEvent.Time, tblEvent.InputID,
tblEvent.personID FROM tblPerson INNER JOIN tblEvent ON
tblPerson.PersonID=tblEvent.personID;

It works but when I switch people on the main form, the list doesn't
change...I think I am just missing something to tell it to look at what
person is displayed????

I'm sure it's simple.. can someone help?
 
J

Jeff Boyce

Jeremy

If you use the main form/subform construction, it isn't even necessary to
restrict the Row Source of the subform ... by adding the subform control to
the main form, you get to set which fields in parent and child "forms" are
related. A change in the parent's ID results in the subform "following".

The listbox, however, needs some way to know ... and I didn't see any
'source' info for the listbox. What are you using for that?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
J

Jeremy Ellison

Control source is blank - I am using the listbox as a selector to switch
between events being displayed on the subform.... so the user can select
wich case to view by using the list box and then the data switches... using
this code in the afterupdate of the list box:

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

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

End Sub


IS this what you were asking about? I'm a bit new..sorry!
 
J

Jeremy Ellison

OK -

I got the list to give me the right values, but it won't update the list
when I change people... I manully had to create a "refresh" button.... is
there a way to do this in code?
 
J

Jeremy Ellison

oK - i FIGURED IT OUT --

I added a refresh to "ON CURRENT" of the subform. This thena llowed the
change in the main form to affect the cahnges in the subform...

Thnx for getting me started!
 
Top