Combo Box wants to show records not in list

pademo57 · Apr 4, 2013

Hi There,
I'm sure there is an easy way to do this but I have not clue.

I have three tables:
Students
STUDID (pk)
txtFname
txtLname
etc.

Classes
CLASSID (pk)
txtClassName
txtClassRoom
etc.

Student_Class (join table)
STUD_CLASSID (pk)
fk_StudID
fk_ClassID

On my Class form when assigning students there is a combo box which shows the students names. Once a student is picked in the combo box their name shows up in the subform.
What I would like is a way to NOT show a student in the combo box after they have been selected. Is this possible? Or should I be looking at another way of doing this?

CJ_London · Apr 4, 2013

In the rowsource of the combobox I presume you have something like:

Code:

SELECT STUDID, TxtLName, TxrFName FROM Students

and when they are picked the StudentClass table is updated

If so then the SQL you need to put in the rowsource is:

Code:

SELECT STUDID, TxtLName, TxrFName FROM Students LEFT JOIN StudentClass ON Students.STUDID = StudentClass.fk_StudID
WHERE StudentClass.fk_StudID Is Null

and in the afterupdate event of the combobox put

Code:

combobox.requery

pademo57 · Apr 5, 2013

Thanks CJ_London,
That worked just fine except for one little thing and it is probably something that I have done. I've noticed that if I have one class with 2 students in it the combo box shows the students that are not connected to that class which is great.

But when I change to the next class even though I have different students in there it still shows only students which have not been put in to any class.

Is there some way to show students NOT in a particular class or is this something I have done wrong in the code?

Solo712 · Apr 5, 2013

pademo57 said:
.
What I would like is a way to NOT show a student in the combo box after they have been selected. Is this possible? Or should I be looking at another way of doing this?

You need to use the removeitem method to get the selected student out of the combo box.

Best,
Jiri

CJ_London · Apr 5, 2013

I don't think RemoveItem is appropriate in this case because it pplies to a valuelist.

With regards the code, no, nothing wrong it is doing what is intended - only include students who are not selected. If you want it to only includes students who are not selected in that class then the code needs to be modified.

Busy right now but will see if I can come up with the modified code later - it will be something like this so give it a go - you may need to build it in vba in the form oncurrent event:

Code:

SELECT STUDID, TxtLName, TxrFName FROM Students LEFT JOIN StudentClass ON Students.STUDID = StudentClass.fk_StudID
WHERE StudentClass.fk_StudID Is Null OR StudentClass.fk_ClassID<> [classid]

Solo712 · Apr 6, 2013

CJ_London said:
I don't think RemoveItem is appropriate in this case because it pplies to a valuelist.

And where did you find that handy rule, CJ ?

Code:
With regards the code, no, nothing wrong it is doing what is intended - only include students who are not selected. If you want it to only includes students who are not selected in that class then the code needs to be modified.

Busy right now but will see if I can come up with the modified code later - it will be something like this so give it a go - you may need to build it in vba in the form oncurrent event:

Code:

SELECT STUDID, TxtLName, TxrFName FROM Students LEFT JOIN StudentClass ON Students.STUDID = StudentClass.fk_StudID WHERE StudentClass.fk_StudID Is Null OR StudentClass.fk_ClassID<> [classid]

Hmmmm... did that work ?

I bet not ! Where would StudentClass table store rows with Null STUDIDs ? I wonder.

Best,
Jiri

CJ_London · Apr 6, 2013

Hi Solo,

And where did you find that handy rule, CJ ?

Please refer to this link

http://msdn.microsoft.com/en-us/library/office/aa221565(v=office.11).aspx

Where would StudentClass table store rows with Null STUDIDs ? I wonder.

You will have noticed that it is a left join, I'm sure - and we are not bringing through anything from StudentClass

Solo712 · Apr 6, 2013

CJ_London said:
Hi Solo,

Please refer to this link

http://msdn.microsoft.com/en-us/library/office/aa221565(v=office.11).aspx

Hi, CJ
oh, I see ! But in the original OP it was not made clear the combo box was to be bound to a table/query. It could be connected to the other tables via code logic, in which case the removeitem method is the simplest solution.

You will have noticed that it is a left join, I'm sure - and we are not bringing through anything from StudentClass

But by the same token you are not restricting the student ID set. This is a most puzzling comment, CJ. You have been asked to create a recordset which displays all the student ids not enrolled in a specific class. I don't know where you think you can get at that by Left-Joining the StudentClass table to the Students'. Evidently, that will not work.

Best,
Jiri

CJ_London · Apr 6, 2013

Hi Solo,

not made clear the combo box was to be bound to a table/query

Can't say I agree with you, if your background is for example HTML then that could be the case but unusual in Access for this sort of situation.

But by the same token you are not restricting the student ID set

Yes I am, the full list of students is restricted in the first code suggestion to those who are not already assigned to any class and in the second case not assigned to any class or, if assigned then not assigned to 'this' class.

Original request was

a way to NOT show a student in the combo box after they have been selected

which the first suggestion does and is evidenced by

That worked just fine except /.../ But when I change to the next class even though I have different students in there it still shows only students which have not been put in to any class

which resulted in the second suggestion since the first request was not quite complete

Evidently, that will not work.

It does! What would you suggest would work? I would like to understand your code logic.

Solo712 · Apr 7, 2013

CJ_London said:
Solo said:

Evidently, that will not work.

Click to expand...

It does! What would you suggest would work? I would like to understand your code logic.

Hi CJ,
First things first: You have offered a piece of code, saying that you were busy and that you would supply 'modified code later'. My point to you is the code you have supplied, i.e.

Code:

SELECT STUDID, TxtLName, TxrFName FROM Students LEFT JOIN StudentClass ON Students.STUDID = StudentClass.fk_StudID
WHERE StudentClass.fk_StudID Is Null OR StudentClass.fk_ClassID<> [classid]

does not work and in fact does not make any sense at all. First you are restricting the right-joined field to Null values which sure as hell will produce an empty set (if anything) and then complementing by IDs that are enrolled in courses other than the present one. This does not address the requirement. In fact I could not get it working at all, with Access complaining about the "ambiguous join".

So you will just have prove to us that your idea does work. I have a solution for the problem (SQL based) that took about 20 minutes to build and I will post it if you do not provide a working prototype in a day or two.

Take care.

Jiri

will not work

CJ_London · Apr 7, 2013

I did say it would be 'something like this'

I have a solution for the problem (SQL based) that took about 20 minutes to build and I will post it if you do not provide a working prototype in a day or two.

Please post now - will save me the time

spikepl · Apr 7, 2013

@CJ: Your solution does work as advertised

@Solo712: Having a bad coding day?

Solo712 · Apr 7, 2013

CJ_London said:
I did say it would be 'something like this'

Please post now - will save me the time

I finally got your statement to execute. Evidently, I was wrong about the effect of NULL in the join (a major discovery for me

; spikepl is going to go wild !). But you will see that I was right about the class complement. The code you posted will not do the job. The problem is when a student is enrolled in more than one class it displays the student in the box multiple times. I added DISTINCT but that does not prevent the student name from appearing in the box when he/she is already in a class and is enrolled in another course.

This problem does not exist if you use a subquery to eliminate from the box students enrolled in the class presently displayed. So not a LEFT JOIN But a NOT IN subquery seems to be the solution. At any rate, it was an useful exercise for me. Hope pademo57 is around to profit.

Best,
Jiri

pademo57 · May 23, 2013

Thanks to all for providing answers to my question.

Combo Box wants to show records not in list

pademo57

Registered User.

CJ_London

Super Moderator

pademo57

Registered User.

Solo712

Registered User.

CJ_London

Super Moderator

Solo712

Registered User.

CJ_London

Super Moderator

Solo712

Registered User.

CJ_London

Super Moderator

Solo712

Registered User.

CJ_London

Super Moderator

spikepl

Eledittingent Beliped

Solo712

Registered User.

Attachments

pademo57

Registered User.

Similar threads

Users who are viewing this thread