Updating list box content on a form

[Guest]

I have three list boxes on a form. The list boxes are populated from queries
based upon tables that have a many-to-one relationship to the main table. The
queries are the following:

SELECT tblProjectSubject.ProjectID, tblProjectSubject.SubjectName
FROM tblProjectSubject
WHERE (((tblProjectSubject.ProjectID)=[Forms]![frmViewPro]![ProjectID]))
ORDER BY tblProjectSubject.SubjectName;

SELECT tblProjectTargetGr.ProjectID, tblProjectTargetGr.TargetgrName
FROM tblProjectTargetGr
WHERE (((tblProjectTargetGr.ProjectID)=[Forms]![frmViewPro]![ProjectID]))
ORDER BY tblProjectTargetGr.TargetgrName;

SELECT tblProjectCountry.ProjectID, tblProjectCountry.CountryName
FROM tblProjectCountry
WHERE (((tblProjectCountry.ProjectID)=[Forms]![frmViewPro]![ProjectID]))
ORDER BY tblProjectCountry.CountryName;

The form is based on the main table (tblProjects).
I want to re-populate/update the list boxes when the ProjectID on the form
changes. I have tried to use a macro (ProjectID_After_Update) for requery of
the list boxes, but can't get it to work.
Any other solutions? Thanks.
Niels








The form is based upon the main table.

 

[Guest]

My recommendation would be to build the SELECT statements 'on the fly' in the
ProjectId_AfterUpdate event handler

e.g.

listBox1.RowSource = "SELECT tblProjectSubject.ProjectID,
tblProjectSubject.SubjectName
FROM tblProjectSubject
WHERE (((tblProjectSubject.ProjectID)= " & Me.ProjectID & "))
ORDER BY tblProjectSubject.SubjectName;"

If ProjectId is a alpha then it would have to be enclosed in single quotes.

Hope This Helps

 

[Guest]

Thanks Gerald.
However, it does not seem to work. The ProjectID is a numeric value so this
should not be the problem.
Niels

My recommendation would be to build the SELECT statements 'on the fly' in the
ProjectId_AfterUpdate event handler

e.g.

listBox1.RowSource = "SELECT tblProjectSubject.ProjectID,
tblProjectSubject.SubjectName
FROM tblProjectSubject
WHERE (((tblProjectSubject.ProjectID)= " & Me.ProjectID & "))
ORDER BY tblProjectSubject.SubjectName;"

If ProjectId is a alpha then it would have to be enclosed in single quotes.

Hope This Helps
--
Gerald Stanley MCSD

I have three list boxes on a form. The list boxes are populated from queries
based upon tables that have a many-to-one relationship to the main table. The
queries are the following:

SELECT tblProjectSubject.ProjectID, tblProjectSubject.SubjectName
FROM tblProjectSubject
WHERE (((tblProjectSubject.ProjectID)=[Forms]![frmViewPro]![ProjectID]))
ORDER BY tblProjectSubject.SubjectName;

SELECT tblProjectTargetGr.ProjectID, tblProjectTargetGr.TargetgrName
FROM tblProjectTargetGr
WHERE (((tblProjectTargetGr.ProjectID)=[Forms]![frmViewPro]![ProjectID]))
ORDER BY tblProjectTargetGr.TargetgrName;

SELECT tblProjectCountry.ProjectID, tblProjectCountry.CountryName
FROM tblProjectCountry
WHERE (((tblProjectCountry.ProjectID)=[Forms]![frmViewPro]![ProjectID]))
ORDER BY tblProjectCountry.CountryName;

The form is based on the main table (tblProjects).
I want to re-populate/update the list boxes when the ProjectID on the form
changes. I have tried to use a macro (ProjectID_After_Update) for requery of
the list boxes, but can't get it to work.
Any other solutions? Thanks.
Niels