Combo question

[avagodro]

I know that using a listbox for multiple selections, being stored in a table, is not the best choice. That it violated standardization.
What I am working on instead is a continuous form with combo boxes. What I would like to do, though, is that if someone selects an option in the combo box, that option would no be available in the next combo. For example...in the combo you have Option1, Option2, Option3. In the first record they select Option2. When they look in the combo for the next record in the continuous form, they will only see Option1 and Option3. Option2 will not be listed since it was already selected.
Any suggestions?

 

[HandsOnAccess]

Use a dynamic recordsource for the combobox.
Get an Idea from the generic code below

Private sub Combobox_GotFocus()
     me.combobox.rowsource = "SELECT <<option>> from <<table name>> where <<option>> not in (SELECT <<option>> from " & me.recordsource & ")"
     me.combobox.requery
End Sub

Hope this helps you...

Regards,

 

[avagodro]

OK. I tried this but I must be missing something. The data in the Combobox is taken from tblResponsesList QstnID and Rspns where QstnID equals a field on a subform. This works. It is when I add the NOT IN statement that I get the error. The error that I have been getting is: "Characters found after end of SQL statement"
As a note, the choices selected are stored in the table tblResponses and are linked to the forms by QstnID
My SQL is:
Me.ListRspns.RowSourceType = "Table/Query"
Me.ListRspns.RowSource = "SELECT tblResponsesList.QstnID, tblResponsesList.Rspns from tblResponsesList where (((tblResponsesList.QstnID)=[Forms]![frmQuestionaire]![tblQuestions]![tblResponses Subform].[Form]![QstnID])); not in (SELECT tblResponses.Rspns from " & tblResponses & ")""

 

[HandsOnAccess]

Remove the ";" mark from this line

[Forms]![frmQuestionaire]![tblQuestions]![tblResponses Subform].[Form]![QstnID])); not in (SELECT

";" is present just before the " NOT IN (SELECT" phrase.

Let me know how it goes...

Regards

 

[avagodro]

I had tried that previously. The message that I get is:
Syntax error. in query expression '(((tblResponsesList.QstnID)=[Forms]![frmQuestionaire]![tblQuestions]![tblResponses Subform].[Form]![QstnID])) not in (SELECT tblResponses.Rspns from )".

Like I said previously, Me.ListRspns.RowSource = "SELECT tblResponsesList.QstnID, tblResponsesList.Rspns from tblResponsesList where (((tblResponsesList.QstnID)=[Forms]![frmQuestionaire]![tblQuestions]![tblResponses Subform].[Form]![QstnID]));

 

[HandsOnAccess]

Here is the full and correct SQL statement

Me.ListRspns.RowSource = "SELECT tblResponsesList.QstnID, tblResponsesList.Rspns from tblResponsesList where tblResponsesList.QstnID=[Forms]![frmQuestionaire]![tblQuestions]![tblResponses Subform].[Form]![QstnID] AND tblResponsesList.Rspns NOT IN (SELECT tblResponses.Rspns from tblResponses)"

Firstly there was problem with bracketing.

Secondly the where condition was wrong.
The above sql statment is fully readable and should make you understand the concept clearly.

Thirdly I am not satisfied with this expression
tblResponsesList.QstnID=[Forms]![frmQuestionaire]![tblQuestions]![tblResponses Subform].[Form]![QstnID]
However If it work fine then no problem, or else you have have to cross-check this expression again.
Others seem to be okay

Let me know how it goes.

Regards,

Regards