List Box: an easy question about a simple List Box

[WebDude]

ListBoxes ahoy!

I have a table called "Volunteers", where each record keeps personal
information (name, address, tel) of each of our volunteers. This tables
primary key is named "pkVolunteerID".

Id like to keep track of everytime we contact our volunteers, and make a
detailed note of our conversation So, i created another table called "Notes".
The table "Notes" has a memo field, a date field, a primary key called
"pkNoteID" and a foreignkey called "fkVolunteerID" that is linked to the
"Volunteers" tables primary key "pkVolunteerID".

The relationship between these two table is such that, a record in the
"Volunteer" table can have multiple related records in the "Notes" table.
Conversly, each record in the "Notes" table can only have ONE related record
in the "Volunteer" table.

Everything works so far.

Then I created a LISTBOX on the VolunteerForm, a form which displays for
editing all the fields of a record from the "Volunteer" Table. In the list
box, i would like listed all the DATES of every note from the "Notes" Table
that is related to this particular volunteer record. So, in the Listbox's ROW
SOURCE, i used the Query builder to create a query;


SELECT Notes.Date
FROM Volunteers INNER JOIN Notes ON Volunteers.pkVolunteerID =
Notes.fkVolunteerID
ORDER BY Notes.Date;


Unfortunately, all this returns is a COMPLETE LIST OF DATES for ALL THE
NOTES for ALL THE VOLUNTEERS in the Notes tables. So, i tried putting
"pkVolunteerID" in the listbox's Control Source and the following in the
RowSource query;

SELECT Notes.Date
FROM Notes
ORDER BY Notes.Date;

but it returned the same COMPLETE LIST of ALL DATES in the NOTES TABLE.

:-|


I spent days doing different things,
any ideas where my error is?

Cheers,
WebDude

 

[Allen Browne]

A stable way to do this is to assign the RowSource property of the list box
in the AfterUpdate event of of the list box and in the Current event of the
form.

This kind of thing:

Private Sub Form_Current()
Dim strSql As String
If IsNull(Me.VolunteerID) Then
strSql = "SELECT Notes.Date FROM Notes WHERE (False) ORDER BY
Notes.Date;"
Else
strSql = "SELECT Notes.Date FROM Notes WHERE (VolunteerId = " &
Me.VolunteerId & ") ORDER BY Notes.Date;"
End If
Me.MyListbox.RowSource = strSql
End Sub

 

[WebDude]

G'day Allen,

I changed the listbox rowsource to the following;

SELECT Notes.Date
FROM Notes
WHERE (((Notes.fkVolunteerID)=[Volunteers].[pkVolunteerId]))
ORDER BY Notes.Date;

and added the same code to the forms on current event, as you suggested.

and need i say, everything worked like a charm


Ta,
WebDude OUT!