remove from drowdown when selected

[Guest]

I have a form with a sub form. Main form link to sub is based on the
project number id.

The sub form has a dropdown of project risks.
The user adds as many risks to the sub form as applies to the project.

When the user chooses a risk from the drop down, I do not want it to no
longer show in the list.

When a different project is chosen in the main form, the sub form shows the
risk that were selected pertaining to that project, and the dropdown only
displays the risks that have not been chosen for that project.

When a new project is entered, all risks are in the dropdown.

By showing only the risks not previously chosen it will eliminate the chance
of duplicate selections and clearly show the options that are remaining as
not selected in the dropdown list.

Thanks...

 

[ruralguy via AccessMonster.com]

It sounds like your "dropdown" needs to have a RowSource that includes a NOT
IN clause.

 

[Guest]

Thank you for your response.

Can you give me an example of what you mean. I am not familiar with this.

Thanks

 

[ruralguy via AccessMonster.com]

Hi Deb,
How about posting the SQL for the RowSource of your ComboBox ("dropdown") and
the SQL for the RecordSource of your SubForm.

Thank you for your response.

Can you give me an example of what you mean. I am not familiar with this.

Thanks

It sounds like your "dropdown" needs to have a RowSource that includes a NOT
IN clause.

[quoted text clipped - 19 lines]

 

[Guest]

I found this...
http://support.microsoft.com/kb/132026

I used the Northwind db.
I tried it exactly as described but it does not update the customer table
when choosing from the listbox.

I then made the form a bound form to Customer table. Still did not update
the table with the country. It updated everything but country.

I then changed the querys to include the CustomerID. Still didn't work.

Please help.

It does delete the item from the list but it does not update the Customer
table with the Country selected.


--
deb

Hi Deb,
How about posting the SQL for the RowSource of your ComboBox ("dropdown") and
the SQL for the RecordSource of your SubForm.

Thank you for your response.

Can you give me an example of what you mean. I am not familiar with this.

Thanks

It sounds like your "dropdown" needs to have a RowSource that includes a NOT
IN clause.

[quoted text clipped - 19 lines]

Thanks...

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[ruralguy via AccessMonster.com]

<repeat>Hi Deb,
How about posting the SQL for the RowSource of your ComboBox ("dropdown") and
the SQL for the RecordSource of your SubForm.</repeat>

I found this...
http://support.microsoft.com/kb/132026

I used the Northwind db.
I tried it exactly as described but it does not update the customer table
when choosing from the listbox.

I then made the form a bound form to Customer table. Still did not update
the table with the country. It updated everything but country.

I then changed the querys to include the CustomerID. Still didn't work.

Please help.

It does delete the item from the list but it does not update the Customer
table with the Country selected.

Hi Deb,
How about posting the SQL for the RowSource of your ComboBox ("dropdown") and

[quoted text clipped - 10 lines]

 

[Guest]

Per instructions..

the Rowsource of the listbox is the table ListTemp
the Source of the form is the table Customers
--
deb

<repeat>Hi Deb,
How about posting the SQL for the RowSource of your ComboBox ("dropdown") and
the SQL for the RecordSource of your SubForm.</repeat>

I found this...
http://support.microsoft.com/kb/132026

I used the Northwind db.
I tried it exactly as described but it does not update the customer table
when choosing from the listbox.

I then made the form a bound form to Customer table. Still did not update
the table with the country. It updated everything but country.

I then changed the querys to include the CustomerID. Still didn't work.

Please help.

It does delete the item from the list but it does not update the Customer
table with the Country selected.

Hi Deb,
How about posting the SQL for the RowSource of your ComboBox ("dropdown") and

[quoted text clipped - 10 lines]

Thanks...

 

[ruralguy via AccessMonster.com]

Sorry Deb but I'm a bit confused. In the 1st post you have:
"The sub form has a dropdown of project risks."
and not you are referring to the control as a ListBox. What is it really?
Are you really adding Risk Factor records to a table named "Customers"?

Per instructions..

the Rowsource of the listbox is the table ListTemp
the Source of the form is the table Customers

<repeat>Hi Deb,
How about posting the SQL for the RowSource of your ComboBox ("dropdown") and

[quoted text clipped - 22 lines]

 

[Guest]

When I first posted, I was relating to my db that AI am trying to build.
I found an example that used the Northwind db. I followed the example in
this example using the nw db and it is exactly what I need. However when
selecting the item in the dropdown Listbox or combo box it deletes it from
the list as is should but it does not update the table with the selection.
When you choose the itme in the dropdown i need it to update the table with
the choice and delete that option from the dropdown list.

I really appreciate your help.
Please take a look at ...
http://support.microsoft.com/kb/132026
It will show you what I need.
--
deb

Sorry Deb but I'm a bit confused. In the 1st post you have:
"The sub form has a dropdown of project risks."
and not you are referring to the control as a ListBox. What is it really?
Are you really adding Risk Factor records to a table named "Customers"?

Per instructions..

the Rowsource of the listbox is the table ListTemp
the Source of the form is the table Customers

<repeat>Hi Deb,
How about posting the SQL for the RowSource of your ComboBox ("dropdown") and

[quoted text clipped - 22 lines]

Thanks...

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[ruralguy via AccessMonster.com]

Deb,
Here's a simple example of using a NOT IN clause:
SELECT OptionID, Option
FROM tbl_Reference_Option
WHERE OptionID NOT IN (SELECT OptionID FROM qry_LookUp_SelectedOptions
The idea in this case is that you do not want any Options returned if they
are alread in the qry_LookUp_SelectedOptions query.

Per instructions..

the Rowsource of the listbox is the table ListTemp
the Source of the form is the table Customers

<repeat>Hi Deb,
How about posting the SQL for the RowSource of your ComboBox ("dropdown") and

[quoted text clipped - 22 lines]

 

[ruralguy via AccessMonster.com]

As I've been trying to explain (poorly I admit), all you need to do is use a
NOT In clause in the RowSource of the List box and requery the ListBox when
you add somthing to the table referenced by the NOT IN clause. The row will
just vanish from your ListBox as if by magic.

When I first posted, I was relating to my db that AI am trying to build.
I found an example that used the Northwind db. I followed the example in
this example using the nw db and it is exactly what I need. However when
selecting the item in the dropdown Listbox or combo box it deletes it from
the list as is should but it does not update the table with the selection.
When you choose the itme in the dropdown i need it to update the table with
the choice and delete that option from the dropdown list.

I really appreciate your help.
Please take a look at ...
http://support.microsoft.com/kb/132026
It will show you what I need.

Sorry Deb but I'm a bit confused. In the 1st post you have:
"The sub form has a dropdown of project risks."

[quoted text clipped - 10 lines]

 

[Guest]

I am so sorry, I am just not getting it to work.

Can you use your example in the Northwind db so I can follow along and
actually use the same tables and fields that you would use.

Your choice of tables and forms.

I am fairly new to access and I need this very badly.

Thank you again for all of your help.

--
deb

Deb,
Here's a simple example of using a NOT IN clause:
SELECT OptionID, Option
FROM tbl_Reference_Option
WHERE OptionID NOT IN (SELECT OptionID FROM qry_LookUp_SelectedOptions
The idea in this case is that you do not want any Options returned if they
are alread in the qry_LookUp_SelectedOptions query.

Per instructions..

the Rowsource of the listbox is the table ListTemp
the Source of the form is the table Customers

<repeat>Hi Deb,
How about posting the SQL for the RowSource of your ComboBox ("dropdown") and

[quoted text clipped - 22 lines]

Thanks...

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[ruralguy via AccessMonster.com]

I'm sorry Deb but I don't see anything in the Northwind example that event
comes close to what I think you are looking form. Are you really adding Risk
Factor records to a table named "Customers"?

I am so sorry, I am just not getting it to work.

Can you use your example in the Northwind db so I can follow along and
actually use the same tables and fields that you would use.

Your choice of tables and forms.

I am fairly new to access and I need this very badly.

Thank you again for all of your help.

Deb,
Here's a simple example of using a NOT IN clause:

[quoted text clipped - 13 lines]

 

[Guest]

I was at home and did not have my database in front of me so I was trying to
work it out using Northwind.

Now I Have mine...

Main form is fRisk and subform is fIssue. The fIssue sub-form is a
Continuous form
The dropdown is in the subform and is called IssueDescID. Rowsource for
dropdown is ...
SELECT t013IssueDesc.IssueDescID, t013IssueDesc.IssueDesc FROM t013IssueDesc;

Subform recordsource is tIssue
Fields are
IssueID
RiskId
IssueDescID
Note

Each risk can have many Issues, however I do not want duplicate issues per
risk.
This is why I want the issue to disappear from the dropdown option upon
choosing.

Please help, I really need this to work!!

--
deb

I'm sorry Deb but I don't see anything in the Northwind example that event
comes close to what I think you are looking form. Are you really adding Risk
Factor records to a table named "Customers"?

I am so sorry, I am just not getting it to work.

Can you use your example in the Northwind db so I can follow along and
actually use the same tables and fields that you would use.

Your choice of tables and forms.

I am fairly new to access and I need this very badly.

Thank you again for all of your help.

Deb,
Here's a simple example of using a NOT IN clause:

[quoted text clipped - 13 lines]

Thanks...

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[ruralguy via AccessMonster.com]

Hi Deb,
Let's try replacing the RowSource of the IssueDescID with:

SELECT IssueDescID, IssueDesc
FROM t013IssueDesc
WHERE IssueDescID NOT IN
(SELECT IssueDescID FROM Forms!fRisk!fIssue.FORM.RecordfSource

I have never tried this but I believe it should work.

I was at home and did not have my database in front of me so I was trying to
work it out using Northwind.

Now I Have mine...

Main form is fRisk and subform is fIssue. The fIssue sub-form is a
Continuous form
The dropdown is in the subform and is called IssueDescID. Rowsource for
dropdown is ...
SELECT t013IssueDesc.IssueDescID, t013IssueDesc.IssueDesc FROM t013IssueDesc;

Subform recordsource is tIssue
Fields are
IssueID
RiskId
IssueDescID
Note

Each risk can have many Issues, however I do not want duplicate issues per
risk.
This is why I want the issue to disappear from the dropdown option upon
choosing.

Please help, I really need this to work!!

I'm sorry Deb but I don't see anything in the Northwind example that event
comes close to what I think you are looking form. Are you really adding Risk

[quoted text clipped - 16 lines]

 

[Guest]

I want to thank you for stick with me on this. It is very important to me to
have this function!!

I am now getting syntax error on the last part...(SELECT IssueDescID FROM
Forms!fRisk!fIssue.FORM.RecordfSource

SELECT IssueDescID, IssueDesc
FROM t013IssueDesc
WHERE IssueDescID NOT IN
(SELECT IssueDescID FROM Forms!fRisk!fIssue.FORM.RecordfSource


I also tried after removing the f in RecordSource.
--
deb

Hi Deb,
Let's try replacing the RowSource of the IssueDescID with:

SELECT IssueDescID, IssueDesc
FROM t013IssueDesc
WHERE IssueDescID NOT IN
(SELECT IssueDescID FROM Forms!fRisk!fIssue.FORM.RecordfSource

I have never tried this but I believe it should work.

I was at home and did not have my database in front of me so I was trying to
work it out using Northwind.

Now I Have mine...

Main form is fRisk and subform is fIssue. The fIssue sub-form is a
Continuous form
The dropdown is in the subform and is called IssueDescID. Rowsource for
dropdown is ...
SELECT t013IssueDesc.IssueDescID, t013IssueDesc.IssueDesc FROM t013IssueDesc;

Subform recordsource is tIssue
Fields are
IssueID
RiskId
IssueDescID
Note

Each risk can have many Issues, however I do not want duplicate issues per
risk.
This is why I want the issue to disappear from the dropdown option upon
choosing.

Please help, I really need this to work!!

I'm sorry Deb but I don't see anything in the Northwind example that event
comes close to what I think you are looking form. Are you really adding Risk

[quoted text clipped - 16 lines]

Thanks...

 

[John W. Vinson]

Hi Deb,
Let's try replacing the RowSource of the IssueDescID with:

SELECT IssueDescID, IssueDesc
FROM t013IssueDesc
WHERE IssueDescID NOT IN
(SELECT IssueDescID FROM Forms!fRisk!fIssue.FORM.RecordfSource

I have never tried this but I believe it should work.

Ruralguy, that's close - but you can only SELECT FROM a Table or Query, not
from a Form's recordsource (which is a string).

Try

SELECT t013IssueDesc.IssueDescID, t013IssueDesc.IssueDesc FROM t013IssueDesc
WHERE IssueDeskID NOT IN
(SELECT IssueDescID FROM tIssue WHERE tIssue.RiskID = Forms!fRisk!RiskID);


John W. Vinson [MVP]

 

[ruralguy via AccessMonster.com]

Thanks for jumping in John. Give it a go Deb.

Hi Deb,
Let's try replacing the RowSource of the IssueDescID with:

[quoted text clipped - 5 lines]

I have never tried this but I believe it should work.

Ruralguy, that's close - but you can only SELECT FROM a Table or Query, not
from a Form's recordsource (which is a string).

Try

SELECT t013IssueDesc.IssueDescID, t013IssueDesc.IssueDesc FROM t013IssueDesc
WHERE IssueDeskID NOT IN
(SELECT IssueDescID FROM tIssue WHERE tIssue.RiskID = Forms!fRisk!RiskID);

John W. Vinson [MVP]

 

[Guest]

It removed the item from the dropdown list perfectly!!!

What a great thing!!!!

Now the field in the dropdown shows the ID and not the riskDesc. I tried
adjusting the column counts and column widths but it still only shows the ID
number and not the text RiskDesc.

Any suggestions?
--
deb

Thanks for jumping in John. Give it a go Deb.

Hi Deb,
Let's try replacing the RowSource of the IssueDescID with:

[quoted text clipped - 5 lines]

I have never tried this but I believe it should work.

Ruralguy, that's close - but you can only SELECT FROM a Table or Query, not
from a Form's recordsource (which is a string).

Try

SELECT t013IssueDesc.IssueDescID, t013IssueDesc.IssueDesc FROM t013IssueDesc
WHERE IssueDeskID NOT IN
(SELECT IssueDescID FROM tIssue WHERE tIssue.RiskID = Forms!fRisk!RiskID);

John W. Vinson [MVP]

 

[ruralguy via AccessMonster.com]

Do you have your ColumnWidths set to 0.0";1.5"?? ...which should hide the 1st
column.

It removed the item from the dropdown list perfectly!!!

What a great thing!!!!

Now the field in the dropdown shows the ID and not the riskDesc. I tried
adjusting the column counts and column widths but it still only shows the ID
number and not the text RiskDesc.

Any suggestions?

Thanks for jumping in John. Give it a go Deb.

[quoted text clipped - 14 lines]