Query Combo box based on values in a form...

[imtheodore]

OK, I have a list box on a form.
The values inside the list box are "clicked" into other fields on a form.
Each time a selection is clicked and moved into the text box, I would like to re-query the table for the list box omitting any values on the current form.

Can this be done?

Basically, I have a list box and 40 text boxes on a form, as the text boxes fill up, the selections in the list box should shrink, so duplicates cannot be selected.

Thanks!

 

[imtheodore]

Anyone know?

 

[pbaldy]

Are the textboxes bound to a table? If not, you'll have to build dynamic SQL for the source of the listbox. Basically:

strSQL = "SELECT Blah FROM TableName WHERE FieldName Not IN(1,3,5,7)

then set the source of the listbox to that string. The values in the IN clause would be built by getting the values of the textboxes.

 

[imtheodore]

Are the textboxes bound to a table? If not, you'll have to build dynamic SQL for the source of the listbox. Basically:

strSQL = "SELECT Blah FROM TableName WHERE FieldName Not IN(1,3,5,7)

then set the source of the listbox to that string. The values in the IN clause would be built by getting the values of the textboxes.

The textboxes are bound to a table. The source of list box is a separate table. here is what I tried, but it does not work:

SELECT List_CRNA1.CRNA_CC, not Exists (select unscheduled_pto_1, unscheduled_pto_2, unscheduled_pto_3, unscheduled_pto_4, unscheduled_pto_5, unscheduled_pto_6
from master where (id = 1097)) AS Expr1
FROM List_CRNA1;

The Not Exists part of the querie brings back the correct results, but the entire query never finds any of the values

 

[pbaldy]

I don't use Exists, but I don't think that's valid SQL. Does it run without error? I'd think you need WHERE in there instead of a field, as in:

WHERE Not Exists...

Also, your SQL hints at a design that may be difficult to work with in the long run. The repeating fields (unscheduled_pto_1, 2...) do not sound properly normalized.

 

[imtheodore]

I don't use Exists, but I don't think that's valid SQL. Does it run without error? I'd think you need WHERE in there instead of a field, as in:

WHERE Not Exists...

Also, your SQL hints at a design that may be difficult to work with in the long run. The repeating fields (unscheduled_pto_1, 2...) do not sound properly normalized.

It does run without error but returns "0" for all of the values, it does not recognize any of the fields chosen the the nested select

CRNA_CC Expr1
Smith-9725 0
Adams-6270 0
Wilson-2234 0
Bender-2154 0

etc....

Is there a better way, or a way that works? It sounds simple, but I've been working on it for a long time now.

Dave

 

[pbaldy]

I would suspect something like this, unless I misunderstand your structure:

SELECT List_CRNA1.CRNA_CC
FROM List_CRNA1
WHERE not Exists (select unscheduled_pto_1, unscheduled_pto_2, unscheduled_pto_3, unscheduled_pto_4, unscheduled_pto_5, unscheduled_pto_6
from master where (id = 1097))
FROM List_CRNA1

However, since I don't use Exists normally, I don't know if the repeating fields will affect the result. My suspicion is that they will. I typically use In, and that would require one field in the subquery.

 

[imtheodore]

Yep, your query is getting the same result as I mine. Either all or none.
There has to be a way to do this, I'll keep thinking.....

 

[pbaldy]

Can you post a sample db?

 

[imtheodore]

Can you post a sample db?

I can, I'll post it tonight.

Thanks for your help

 

[pbaldy]

I'm not sure how you can do what you want. As I suspected, that design is nowhere near normalized. That's spreadsheet thinking and you need to apply relational database thinking. I'd recommend reading up on that (normalization) and correcting the design first. Then this type of thing will be fairly easy. I don't know enough about what the overall goal of the database is to even suggest a direction to go in.

 

[imtheodore]

Anyone else have an idea on how to do this?