"Prochot" <pr*****@gmail.comwrote in
news:11*********************@j72g2000cwa.googlegro ups.com:
I'm trying to design a database to track projects and the
associates assigned to them. I have almost no experience with
this. I have three tables:
-Projects
-Associates
-Assignments
where assignments contains projectID and associateID and is
related to the projects.projectID primary key and the
associates.associateID primary key. I have a form based on
the projects table that contains a list box that I would like
to use to view/add/remove associates from each project. So I
think the rowsource for the listbox names namesList would have
to be populated with a query something like the following...
Me.NamesList.RowSource = _
"SELECT Associates.LastName" & ", " &
"Associates.FirstName
" & _
"AS Name " & _
"FROM Associates" & _
"WHERE Me.ProjectID.Value = Assignments.ProjectID
" & _ "AND Assignments.AssociateID =
Associates.AssociateID;"
Me.NamesList.Requery
...but this doesn't work. Could someone please help me with
what this query SHOULD look like?
Thanks!
the reference to me.projectID needs to be outside the quotes if
used as you try to, in visual basic.
if project id is a numeric type"
"WHERE Assignments.ProjectID = " & Me.ProjectID.Value & ...
if it's a text type field, you need embedded quotes
"WHERE Assignments.ProjectID = """ & Me.ProjectID.Value & """...
(note that to embed a quote you put two side by side. The third
actually delimits the string.
But since you want to add/remove you are going to have problems,
since you are filtering on the selected project and associates.
I usually set up two (2) listboxes, one for the people already
assigned and a second listbox for the candidates to be added.
I'd set up the first, using an IN clause with a subquery.
SELECT name
FROM associates
WHERE associateID
IN (
SELECT AssociateID
FROM Assignments
WHERE projectID = forms!formname!ProjectID
);
I'm using the form's long name because me.project ID won't work
directly pasted into the rowsource of the listbox. It's a lot
simpler than mucking about generating the SQL in VB and
programatically stuffing it to the rowsource. You have the
choice.
for the second listbox, use the same SQL, but add the word NOT
before the IN.
Now you need two buttons on the form, one to add and one to
remove an associate. I would use a SQL INSERT INTO query and a
SQL DELETE FROM query with the proper filters,
Then after the SQL simply requery the two listboxes.
You also need to issue the requery statements in the CURRENT
event of the form.
Good luck.
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account from
http://www.teranews.com 
