Combo Box and preventing duplicate entry in listbox

[carlarae]

I have a form that has a combo box where a user name is selected and added to
a listbox and an assignment table with an assignment ID. I want to prevent
a user name from being assigned under the same assignment ID more than once.
How can I do this?

 

[KARL DEWEY]

This is only good for picking --- no good for display as once picked they
disappear from list.
Use this as source for the combo box.
SELECT User.[user name]
FROM User LEFT JOIN Assignment ON User.[user name] = Assignment.AssignmentID
WHERE (((Assignment.AssignmentID) Is Null));

 

[carlarae via AccessMonster.com]

That doesn't pull what I need or I'm doing something wrong still. My problem
is that I have a table that list the username and I have another table that
tracks the assignmentID, taskID, and username that is assigned to that task.
The combo box allows selection of a username to add with a onclick button to
the listbox which pulls from the Assignment table in relation to the taskid.
I somehow need to make it only allow a username to be added to the
AssignmentID and/or TaskID onetime. Multiple users can be assigned but I do
not want one user assigned more than once. I'm sure this is easy but I'm
going in circles trying to make it work. Is my design perhaps in need of
change for this?

This is only good for picking --- no good for display as once picked they
disappear from list.
Use this as source for the combo box.
SELECT User.[user name]
FROM User LEFT JOIN Assignment ON User.[user name] = Assignment.AssignmentID
WHERE (((Assignment.AssignmentID) Is Null));

I have a form that has a combo box where a user name is selected and added to
a listbox and an assignment table with an assignment ID. I want to prevent
a user name from being assigned under the same assignment ID more than once.
How can I do this?

 

[KARL DEWEY]

That doesn't pull what I need or I'm doing something wrong still.
I can not help if you do not say what is wrong. Did it pull too many or not
enough records? Was there an error message?

Post your SQL for the combo source.

--
KARL DEWEY
Build a little - Test a little

That doesn't pull what I need or I'm doing something wrong still. My problem
is that I have a table that list the username and I have another table that
tracks the assignmentID, taskID, and username that is assigned to that task.
The combo box allows selection of a username to add with a onclick button to
the listbox which pulls from the Assignment table in relation to the taskid.
I somehow need to make it only allow a username to be added to the
AssignmentID and/or TaskID onetime. Multiple users can be assigned but I do
not want one user assigned more than once. I'm sure this is easy but I'm
going in circles trying to make it work. Is my design perhaps in need of
change for this?

This is only good for picking --- no good for display as once picked they
disappear from list.
Use this as source for the combo box.
SELECT User.[user name]
FROM User LEFT JOIN Assignment ON User.[user name] = Assignment.AssignmentID
WHERE (((Assignment.AssignmentID) Is Null));

I have a form that has a combo box where a user name is selected and added to
a listbox and an assignment table with an assignment ID. I want to prevent
a user name from being assigned under the same assignment ID more than once.
How can I do this?

 

[carlarae via AccessMonster.com]

Well it only pulls a few user names. and it looks like they are only the
ones that have not been assigned to any tasks at all "overall", what I need
are the ones that haven't been assigned to "a" task rather than "any" task....
.., when I remove the Is null from Assignment ID, I get duplicated names......

I can not help if you do not say what is wrong. Did it pull too many or not
enough records? Was there an error message?

Post your SQL for the combo source.

That doesn't pull what I need or I'm doing something wrong still. My problem
is that I have a table that list the username and I have another table that

[quoted text clipped - 18 lines]

 

[KARL DEWEY]

Post the actual SQL you are using for the combo and that for the form.
--
KARL DEWEY
Build a little - Test a little

Well it only pulls a few user names. and it looks like they are only the
ones that have not been assigned to any tasks at all "overall", what I need
are the ones that haven't been assigned to "a" task rather than "any" task....
.., when I remove the Is null from Assignment ID, I get duplicated names......

That doesn't pull what I need or I'm doing something wrong still.

I can not help if you do not say what is wrong. Did it pull too many or not
enough records? Was there an error message?

Post your SQL for the combo source.

That doesn't pull what I need or I'm doing something wrong still. My problem
is that I have a table that list the username and I have another table that

[quoted text clipped - 18 lines]

a user name from being assigned under the same assignment ID more than once.
How can I do this?

 

[carlarae via AccessMonster.com]

Well I found a way to make it work now! I'm so happy for that! Your help
led me down the right path on the SQL changes that needed to be made though,
here is how I fixed it, I put the below SQL on the combo box, which includes
a query built that looks at the forms TASKID/SOSID and omits the usernames
from the combo list that are already assigned to the TASK:

The SQL for the first built query is:
SELECT T_Assignment.AssignID, T_Assignment.UserID, T_Assignment.SOSID
FROM T_Assignment
WHERE (((T_Assignment.SOSID)=[forms]![SOSForm1]![SOSID]));

And the SQL on the combo is:
SELECT T_Personnel.UserID
FROM T_Personnel LEFT JOIN qryAssign.UserID
WHERE (((qryAssign.UserID)Is Null));

I also triggered the combo box to .requery on the onclick event button that
actually adds the name to the list (Assignment Table).

works like a charm. Thank you for leading me down the right path there.

Post the actual SQL you are using for the combo and that for the form.

Well it only pulls a few user names. and it looks like they are only the
ones that have not been assigned to any tasks at all "overall", what I need

[quoted text clipped - 12 lines]

 

[KARL DEWEY]

Remember what I said in my first post -- This is only good for picking --- no
good for display as once picked they disappear from list.

--
KARL DEWEY
Build a little - Test a little

Well I found a way to make it work now! I'm so happy for that! Your help
led me down the right path on the SQL changes that needed to be made though,
here is how I fixed it, I put the below SQL on the combo box, which includes
a query built that looks at the forms TASKID/SOSID and omits the usernames
from the combo list that are already assigned to the TASK:

The SQL for the first built query is:
SELECT T_Assignment.AssignID, T_Assignment.UserID, T_Assignment.SOSID
FROM T_Assignment
WHERE (((T_Assignment.SOSID)=[forms]![SOSForm1]![SOSID]));

And the SQL on the combo is:
SELECT T_Personnel.UserID
FROM T_Personnel LEFT JOIN qryAssign.UserID
WHERE (((qryAssign.UserID)Is Null));

I also triggered the combo box to .requery on the onclick event button that
actually adds the name to the list (Assignment Table).

works like a charm. Thank you for leading me down the right path there.

Post the actual SQL you are using for the combo and that for the form.

Well it only pulls a few user names. and it looks like they are only the
ones that have not been assigned to any tasks at all "overall", what I need

[quoted text clipped - 12 lines]

a user name from being assigned under the same assignment ID more than once.
How can I do this?

 

[carlarae via AccessMonster.com]

Yes I do, however the display part is the listbox that I have, once they are
picked then the listbox refreshes and they are there. I just had the
headache with the picking part, and thanks to you I was able to fix that. So
glad too!

Remember what I said in my first post -- This is only good for picking --- no
good for display as once picked they disappear from list.

Well I found a way to make it work now! I'm so happy for that! Your help
led me down the right path on the SQL changes that needed to be made though,

[quoted text clipped - 23 lines]