468,533 Members | 1,903 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,533 developers. It's quick & easy.

listbox rowsource query problem

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!

Dec 5 '06 #1
3 7849

Prochot wrote:
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!
You can't make a WHERE clause out of elements from different tables
without joining them. Take a look in the Access help under "Inner Join".

Dec 5 '06 #2
"Jamey Shuemaker" <ca*********@yahoo.comwrote in
news:11**********************@j44g2000cwa.googlegr oups.com:
>
Prochot wrote:
>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!

You can't make a WHERE clause out of elements from different
tables without joining them. Take a look in the Access help
under "Inner Join".
Jamey, what you say here is wrong. It is perfectly legal to use
a where clause instead of a join. However, in the code given,
the FROM reference is missing the second table.

& " AS Name " _
& " FROM Associates, Assignments " _
& " WHERE ..."
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Dec 6 '06 #3
"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

Dec 6 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Colleyville Alan | last post: by
3 posts views Thread by Paul T. Rong | last post: by
1 post views Thread by (PeteCresswell) | last post: by
3 posts views Thread by deejayquai | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.