Requerying a combo box to EXCLUDE previous selections

[Atomic Shrimp]

I have a datasheet form where the user is able to create entries in a criteria list by selecting them from a combo box, What I would like would be for the combo box to exclude any items already selected (in previous rows.

I thought I could do this by basing the combo box row source on a find unmatched query, then requerying the combo in the afterupdate event, but this causes all records to go blank.

Any thoughts? - I'm sure this is quite simple, I'm just attacking it from the wrong angle.

 

[MikeAngelastro]

Mike,

You need to create a Cartesian query of all the possible combos of selectors and selected items. Feed this into another query where you do a left join on these two fields and include a field that will be null when a record in the Cartesian query is not also in the query that shows the actual combos (already selected). Put a criterion there that says "Is Null". The result set of this query will include only the combos not yet used. Use the second query as the RowSource of the combo box. I have done this at least 5 or more times and it works quite well. Let me know if you would like any clarification.

Mike

 

[Atomic Shrimp]

Thanks Mike, It's working now.

 

[Rich]

Did you find the answer to your columns problem?

 

[Atomic Shrimp]

you mean this one:
Select whole record ('neutral' focus) in a continuous form ?

No, I'm still struggling with that one.

 

[Rich]

No I meant the report footer problem not spanning the entire width when using columns

 

[Atomic Shrimp]

oh, that one.

No, since the report is being printed to Adobe Distiller (to produce a .pdf file) with a view to sending it to a litho print shop for production as an order form, we just decided to do the footer separately and let them marry it up.

I think it might be possible by using a subreport and keeping the columns within that, but I haven't got around to trying yet.