Combo Box list In Access

[fliu2265]

Hello Everyone,

I am new and hope you all are doing well.
I have a question regarding a Combo Box.
I created a Combo Box with lists as:
Math
Chemistry
English
History
How can I select item from the lists only once? For example , if I select 'Math', then next time If I select 'Math' again, I would
like to get a warning message such as 'You already select Math'.

Thanks

Frank

 

[Mike Krailo]

Maybe use checkboxes instead of combo box. Can you explain more of the big picture of what you are doing? The reason why I ask is that if this was for students to classes, usually there is many to many relationship and this changes everything. Also you did not say if the combo box is bound to a table or query or not.

 

[Uncle Gizmo]

I agree with Mike, more info required....

 

[theDBguy]

Hi. Welcome to AWF!

Is this combobox bound to a field in your table? If so, you might be able to use a unique index. Just a thought...

 

[Gasman]

You would have to record the fact that you selected it previously.

 

[Moosak]

Hello @fliu2265
I have done some thing similar before, Any item selected Before disappears from the list.
Check this :

The demo is attached below

 

[Pat Hartman]

Welcome aboard Frank.

We don't have enough information to provide a complete solution. You may want to use the technique suggested by @Moosak but that is insufficient and too narrow a scope for what you are probably looking for.

I think you are asking to limit the usage in "context", not in a blanket fashion. For example, you may be assigning students to classes. So you don't want Johnny to have Math listed twice but surely Suzy can also take Math.

The solution starts with using a unique index in your junction table.

Assuming you have a Student table, a Classes table and a StudentClasses table (the junction table for the many-many relationship) The StudentClasses table needs:
tblStudentClasses:
StudentClassesID (autonumber PK)
StudentID (FK to tblStudent and fld1 of a unique index)
ClassID (FK to tblClasses and fld2 of a unique index)

To create a unique, multi-field index, open the table in design view and click on the indexes button. See the example below. to create the multi-field index, to to the first empty line. Type in a name for the index (must be unique within this table). Choose the first field, mark the index as unique. Move to the next line and skipping the index name field, enter the second field. Save and close.

 

[Moosak]

Okay Mr. Pat , lets use the same technique but in a sub form this time.
See what we got :

 

[arnelgp]

can you show that when the form is Datasheet or Continuous?

 

[Moosak]

can you show that when the form is Datasheet or Continuous?

It works in both ways.
This is the row source of the combo box :

SELECT DISTINCTROW ComboList_T.ComboList FROM ComboList_T LEFT JOIN Subjects_T ON ComboList_T.ComboList = Subjects_T.Subject WHERE (((ComboList_T.ComboList) Not In (select [Subjects_T].[Subject] from [Subjects_T] where [Subjects_T].[StudentID] = forms![DataForm]![ID] )));

 

[Mike Krailo]

That is something I have never seen before. Got any more tricks up your keyboard?

 

[Pat Hartman]

Yes Moosak, you can limit the RowSource in the combo, but in the world of RDBMS', you STILL need to create the schema correctly and that means indexes and referential integrity. Your suggested technique is fine for limiting the selections in the combo (and I use it myself for similar situations) but that is insufficient to ensure only valid data is saved.