Remove selection from field

[pm4698]

Hello there.I am new in access
I have a database in which i have created a table. A field in this table is called: Lastname.
Now, i want to create a form with some positions. Its for example sales position, secretary postion, management position etc. So, i create these labes. At the right of each label, i want to have a combo box, which will contain all the lastnames. So, for each position i can select one of the lastnames.

All i want is, when i select a lastname for one position, the specific lastname will be removed from the list for the next selections.

How can i do this?

Thank you in advance

 

[Guus2005]

select name from tablename where name <> [form].[cboPosition1] and name <> [form].[cboPosition2] and name <> ... etc

use actual names.

HTH

 

[pm4698]

My field is called lastname and my table is called Employer. My form is called JOBS and for example i have 3 combo boxes:
combo1
combo2
combo3

So, the code will be:

SELECT EMPLOYER.lastname WHERE EMPLOYER.lastname <> [form].[cboposition1] and etc?

or i will have to write [JOBS] instead of [form] and [combo1] instead of [cboposition1]?

Thanks in advance

 

[Guus2005]

You can use the expression builder to create an accurate path to the combobox.
It would look something like this: Forms![JOBS]![combo1] where JOBS is the name of the form and combo1 is the name of the combobox.

SELECT EMPLOYER.lastname 
   WHERE EMPLOYER.lastname <> Forms![JOBS]![combo1] 
       AND ...

You can recognise the expression builder the icon has a wand and three dots or Build... if you rightclick.

Enjoy!

 

[pm4698]

I tried this out for one combo box and it worked.
But when i added the And's for lots of combo boxes it appears no values at the combo box for me to select.

Any ideas?

Cause i have lots of combo boxes and the code will be pretty large, i though of another solution. I inserted a yes/no field at my table (where the lastnames are located).
So, when i select an option from a combo box, then this yes/no field will go to yes and at all combo boxes i will have something like this:
SELECT EMPLOYEE.lastname from EMPLOYEE WHERE yes_no_field is null

Do you think that would work? If yes, can you suggest me some kind of code so when i select a choice from the combo box to set to yes that field at the table at the specific record?

And at this situation, it will be necessary to put a box that clears the form and set the yes/no field of all lastnames at the table to null.

Any ideas?

 

[Guus2005]

It is getting more complicated than needed.
Please post a sample database.
Then i'll show you what i mean.

 

[pm4698]

Here is a sample of what i mean.

 

[Guus2005]

If you don't want your choice to appear in other comboboxes this is the rowsiource for the first combobox. You may need to add the remaining comboboxes in the NOT IN part. Put Nz function around them to prevent NULLs from mocking up the result.

SELECT employee.Lastname1
FROM employee
WHERE (((employee.Lastname1) Not In (nz([Forms]![duties]![kp2]),nz([Forms]![duties]![kp3]))));

You may need this code to requery the other comboboxes after you made a choice.

Private Sub kp1_AfterUpdate()
    Me.kp2.Requery
    Me.kp3.Requery
'etc, usw, enz
End Sub

HTH

 

[pm4698]

It really worked!

Thank you very much for your time!

 

[gemma-the-husky]

I tried this out for one combo box and it worked.
But when i added the And's for lots of combo boxes it appears no values at the combo box for me to select.

Any ideas?

Cause i have lots of combo boxes and the code will be pretty large, i though of another solution. I inserted a yes/no field at my table (where the lastnames are located).
So, when i select an option from a combo box, then this yes/no field will go to yes and at all combo boxes i will have something like this:
SELECT EMPLOYEE.lastname from EMPLOYEE WHERE yes_no_field is null

Do you think that would work? If yes, can you suggest me some kind of code so when i select a choice from the combo box to set to yes that field at the table at the specific record?

And at this situation, it will be necessary to put a box that clears the form and set the yes/no field of all lastnames at the table to null.

Any ideas?

i do stuff like this in this way - ie the way you are suggesting

i have two list boxes, one showing unselected items, one showing selected items, and arrows between them to move items either way.

so to add to the list you just

a) execute an update sql statement that says this sort of thing

update table set selectedflag = true where itemnumber = whatever

b) then update the listboxes/comboboxes

listbox1.requery
listbox2.requery


and modify the sql statement in a) if you are moving the other way.


resetting everything is just

update table set selectedflag = false - wthout any record qualifier