Prevent Combo Box showing already selected items. (1 Viewer)

[HillTJ]

Hi, I hope my description makes sense. I have the following many:many relationship. Thus,
tblAccident

• IncidentID-PK
• Other fields….

tblAccident_tblAccidentSections (AccidentID & SectionID form a composite key as a way of preventing sectionID being selected more than once))

• AccidentID-FK (1 to many with IncidentID)
• SectionID-FK (1 to many with SectionID)
• strText (this is where details are recorded)

tblAccidentSections

• SectionID-PK
• strSectionDescription (could be “Initial Investigation”, “Immediate Actions”, “Witnesses” etc)
• SectionPriority (used to sort the order of the “lookup”)

This all works fine. On my form I have a subform (in single form view) derived from tblAccident_TblAccidentSections in which SectionID is populated by a combo box that gets its data from tblAccidentSections (column count of 2 with sectionID hidden to display strSectionDescription) This works good too. But what I’d like to do is to have the “lookup” reflect only the “remaining” strSectionDescriptions. That is, not repeat the full list each time as only those items that have not been selected can actually be selected.
Does this make sense? It’s probably simple to do. Appreciate any feedback.

 

[theDBguy]

Yes, you should be able to filter the rowsource of the Combobox to remove the items you don't want to show. Can you post your current rowsource?

 

[HillTJ]

theDBguy, have a look at this;
SELECT tblAccidentSections.SectionID, tblAccidentSections.strSectionDescription, tblAccidentSections.SectionPriority, tblAccidentSections.[SectionActive(Y/N)] FROM tblAccidentSections WHERE (((tblAccidentSections.[SectionActive(Y/N)])=Yes)) ORDER BY tblAccidentSections.SectionPriority;

Thanks

 

[The_Doc_Man]

In general, you have to do three things. The table from which that .RowSource originates must have some flag to indicate that you don't want to use it again. The query that is the .RowSource must exclude those items as flagged to be excluded. (Note that you don't actually have to include the flag in the visible items of the selection query, but the WHERE clause has to reference the flag.) You must do a .Requery of the combo box once you have selected something that hadn't been selected before. That will do the job.

To reset this completely, of course the simplest action would be to reset the flag to their initial state.

 

[HillTJ]

The_Doc_Man, thanks. I'll have to digest your recommendations & give it a go.

 

[arnelgp]

based on your Query on post #3,
just exclude the SectionID's that are already in same AccidentID:

SELECT tblAccidentSections.SectionID,
    tblAccidentSections.strSectionDescription,
    tblAccidentSections.SectionPriority,
    tblAccidentSections.[SectionActive(Y/N)] FROM tblAccidentSections
WHERE ((tblAccidentSections.[SectionActive(Y/N)])=Yes)
AND  SectionID
NOT IN (SELECT SectionID FROM tblAccident_TblAccidentSections
WHERE AccidentID = [Forms]![MainFormName]![SubFormName].Form!AccidentID) ORDER BY tblAccidentSections.SectionPriority;

 

[isladogs]

One word of caution. If you exclude items already selected, you may have issues viewing records which have those combo values.
Check carefully before implementing in any production database

 

[HillTJ]

Arnelgp, thanks. Rather than just copying in your sql and for my benefit, how would i go about creating such a statement using the querybuilder?. If i understand this, then i'll be a lot happier

 

[HillTJ]

Ah, yes Colin i'll have a go but i understand

 

[MajP]

This demo uses a query like @arnelgp and addresses 2 techniques for values disappearing as discussed by @isladogs when reduce the list in a continuous form.

 

[HillTJ]

MajP, appreciate it. I'll have a look.

 

[MajP]

Rather than just copying in your sql and for my benefit, how would i go about creating such a statement using the querybuilder?

Unfortunately that you cannot do without typing a sql select statement. In the Criteria you have to do a sub query which is
NOT IN (Select SomeField from SomeTable where Field = value)

SQL IN Operator

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

 

[HillTJ]

A bit to digest but I'll work my way through it. MajP, That's a great website you referred to. I'll have a good look at it.Cheers