Comb Box values

[Snowflake68]

Hi have a combo box on a datasheet that is bound to a table. The list is created via a query to a look up table. The user selects a size from the combo box for record 1 then moves down to record 2 to select the size for the next record.
The issue is that I am trying to limit the list to exclude the size that has already been selected on record one (or any of the other records in the dataset)

I did find a way to do this by creating a query to exclude the values that have already been selected for a previous record which works until I close the form and reopen it. When I close the form and then try to reselect from any of the combos (not just the one with the exclude from list query) I am then unable to move out of the record. I cannot even change back to the design of the form, the only thing I can do is close the form, this produces the error, "You cant save the record at this time.

The SQL for the query to the combo box is

SELECT qry_Conv_SizeOutgoing.OutgoingSize, qry_Conv_SizeOutgoing.Description, qry_Conv_SizeOutgoing.IncomingSize, qry_Conv_SizeOutgoing.SizeCategory, qry_Conv_SizeOutgoing.SizeRange, qry_Conv_SizeOutgoing.UpDownSize
FROM qry_Conv_SizeOutgoing
WHERE (((Exists (SELECT NULL   FROM qry_Conv_OutgoingSizesSelected   WHERE qry_Conv_OutgoingSizesSelected.SelectedSize = qry_Conv_SizeOutgoing.OutGoingSize))=False));

This is the query that the above query uses

SELECT tbl_ConvSizesOutIn.OutgoingSize, tbl_ConvSizesOutIn.IncomingSize, tbl_ConvSizesOutIn.SizeCategory, tbl_ConvSizesOutIn.SizeRange, tbl_ConvSizesOutIn.UpDownSize
FROM tbl_ConvSizesOutIn
GROUP BY tbl_ConvSizesOutIn.OutgoingSize, tbl_ConvSizesOutIn.IncomingSize, tbl_ConvSizesOutIn.SizeCategory, tbl_ConvSizesOutIn.SizeRange, tbl_ConvSizesOutIn.UpDownSize;

Is there any other way of excluding values from the list where the value has already been selected in the same dataset?

 

[Snowflake68]

Just to add to my post above. If i click into the combo box and DONT select anything and then move to the next record in the datasheet (just by clicking on the down arrow on the keyboard), I can then select from the combo box on any record. It only causes an issue if I select from the combo immediately. Basically clicking into the combo box on one record and moving to another is sorting the issue. So I need to include VBA on the after update event of the record that will mimic this. How do I do this?

 

[The_Doc_Man]

Your problem is not entirely clear, but here is a thought for you.

If you are trying to exclude something and remember the exclusion, you must remember to make a place to remember it.

Let's say for example that your excluded choices can be uniquely identified by a choice based on a code number (simplest case). So you have, say, ten or twelve codes. The way I might do this is to have a table behind the scenes that you use to remember the selections. Might even be a one-field table. You start life with this table empty and the one field is the same size/type as the code that goes along with the bound value of the combo box.

Say you make a selection. When you do that, you store the selected code in this table. And the trick is that your combo box RowSource looks something like this:

SELECT CodeNum, CodeName FROM CodeListTable WHERE CodeNum NOT IN ( SELECT Codes FROM UsedCodes ) ;

Every time you select a code number, you would have to enter it into the UsedCodes table. When that code becomes usable again, you would remove it. Either way, if you change the contents of the UsedCodes table, you would have to Requery to assure that the changes get picked up.

 

[Mark_]

An alternate approach.
You are showing items based on X. Your drop down is based on table Y.
Create a query based on table Y.
Create a query based on table X that ONLY return the values you are trying to exclude.
Change your query based on Y to exclude values that are in the query on table X.

Oddly this is the same answer I gave to a student posting about his assignment on here a couple weeks ago. Creating exclusion queries sometimes are the easiest solutions for these kinds of issues. I may have to make a demo to show exactly how to do this soon.

 

[Deleted member 147267]

AFAIK, any modification of a combo list when the control is on a datasheet or continuous form means that if you go to a previous record, that value cannot be chosen from the list again - because in reality there is really only one of each control in such a form, and that list value is now gone.

When I close the form and then try to reselect from any of the combos (not just the one with the exclude from list query) I am then unable to move out of the record.

 

[Snowflake68]

The way I got around this was to include this code

Forms!sfrm_ConvMaster!sfrm_DS_ConvDetailSub.Form!frm_DS_ConvDetail!cboOutgoingSize.SetFocus
DoCmd.GoToRecord , "", acFirst

I now no longer get the error when I use the 'Exists' part in my query