List only unselected items, please help

[newboy18]

I am having trouble trying to design an Access2000 query to use with a dropdown ComboBox.
I have created ComboBox that uses a Query to list parts and put them in order but what I wanted was for the dropdown list to NOT show any parts that had already been selected.
I guess that I need another, more complicated query.
Has anyone got an answer or better still an example that I can look at?

 

[jbehrne]

Hi,

The quickest thing to do would be to add a field in your recordset that is a "Yes/No" field. Once an item from your combobox is selected then set the field to "Yes". Your query that controls the would then look something like this:

"SELECT FieldName FROM TableName WHERE (((TableName.FieldName) Is Null));"

After every click you would then requery your combox and refresh the form to not show the updated values...

jbehrne

If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[AmritPaulSingh]

Hey newboy, You mentioned that you don't want the selected parts to show up in the combo. Do you have a check box field in the table to check for selected parts. If yes, then the query should be:

"Select tblTableName.Parts from tblTableName where tblTableName.Selected=False;"

If this is not whatyou were looking for, let me know.

 

[newboy18]

Thanks for the answers but I didn't want to use SQL yet, I am only a novice, if possible I wanted to just use the functions in Access like Create Query.

 

[jbehrne]

Hi newboy,

You can create the query using the Access wizard. But in order to keep this easy you will have to do this:

1. Add a "Yes/No" field to your table.
2. Create a query that is based on your table, using the Query wizard. In the design view add the "Yes/No" field to the displayed boxes (below the section showing what tables are in the query). In the field's Criteria put this in without quotes: "Is Null"
3. Then set your form's control to the query you just created.
4. In your combobox's "After Update" event put this code: (Change Yes/NoFieldName to match the name of your field)
(Change ComboboxName to the name of your combobox)

Me!Yes/NoFieldName = 0
Me!ComboboxName.Requery

If you have any problems, or can't figure it out then send me a zipped copy of your db w/o sensitive info in it and I'll gladly make the changes for you and you can study the code! E-mail me at jbehrne@hotmail.com

jbehrne


If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations