Populate a List Box using code

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeremy Ellison - 20 Mar 2007 16:46 GMT

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?

Reply to this Message

Jeff Boyce - 20 Mar 2007 17:05 GMT

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

>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
[quoted text clipped - 10 lines]
>
> I'm sure it's simple.. can someone help?

Reply to this Message

Jeremy Ellison - 20 Mar 2007 17:33 GMT

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!

> Jeremy
>
[quoted text clipped - 25 lines]
> >
> > I'm sure it's simple.. can someone help?

Reply to this Message

Jeremy Ellison - 20 Mar 2007 18:15 GMT

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?

> Jeremy
>
[quoted text clipped - 25 lines]
> >
> > I'm sure it's simple.. can someone help?

Reply to this Message

Jeremy Ellison - 20 Mar 2007 18:28 GMT

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!

> Jeremy
>
[quoted text clipped - 25 lines]
> >
> > I'm sure it's simple.. can someone help?

Reply to this Message