update one List Box row source from value selected in other list box 1

[THWatson]

Using Access 2007

This should be relatively easy but, for whatever reason, I am having trouble getting things to work properly.

On a form there are 2 list boxes.

The first list box, called lstYearLastVisit, has the following row source

SELECT qryPatients.YearLastVisit
FROM qryPatients
GROUP BY qryPatients.YearLastVisit;

The second, called lstPatients, has the following row source

SELECT tblPatients.PatientID, [PatLast] & ": " & [PatFirst]+(" " & [PatMiddle]) AS FullName, tblPatients.PatFirst, tblPatients.PatMiddle, tblPatients.PatLast, tblPatients.DateBirth, tblPatients.DateLastVisit, Year([DateLastVisit]) AS YearLastVisit
FROM tblPatients;

I want to be able to change the row source in lstPatients, to reflect the Year selected in lstYearLastVisit.

Tom

 

[dhookom]

You can either use code to change the Row Source property of the second or you can add a criteria to the second list box.

CODE
SELECT tblPatients.PatientID, [PatLast] & ": " & [PatFirst]+(" " & [PatMiddle]) AS FullName, tblPatients.PatFirst, tblPatients.PatMiddle, tblPatients.PatLast, tblPatients.DateBirth, tblPatients.DateLastVisit, Year([DateLastVisit]) AS YearLastVisit
FROM tblPatients
WHERE Year([DateLastVisit]) = Forms!NoFormNameGiven!lstYearLastVisit;

You may need code to requery the second list box after the update of the first.

Duane
Hook'D on Access
MS Access MVP

 

[THWatson]

Duane
Thanks. That works.

I had been tinkering with that but used Me instead of spelling out the full Forms!frmName!lstYearLastVisit.

Tom