Updating list box content on a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
Thanks Gerald.
However, it does not seem to work. The ProjectID is a numeric value so this
should not be the problem.
Niels

Gerald Stanley said:
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


NielsE said:
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
Click to expand...
Click to expand...
 
Back
Top