Question Limiting a list in a continuous form (1 Viewer)

[adenjones]

I am currently trying to limit a list on a continuous form so that it is impossible to enter values that have already been entered. The list is the query value of a combo box placed on the continuous form itself. The combo box is bound to a form and I am trying to query to see if a value has already been selected and eliminating it so long as it is not the value of the current instance of the combo box.

The following is the Row Source for the Combo Box:

PARAMETERS [Combo0].[Value] Long; SELECT testFields.FieldID, testFields.Name FROM testFields WHERE (((Exists (SELECT * FROM Test_Duplicates WHERE testFields.FieldID = EnterValue))=False)) OR (((testFields.FieldID)=[Combo0].[Value]));

I am then requerying the combo box with:

Private Sub Combo0_AfterUpdate()
Combo0.Requery
End Sub

Safe to say I am getting pretty chaotic results and I think it is obvious that my understanding of how continuous forms work is lacking here.

Any input would be greatly appreciated.

Cheers

 

[pbaldy]

This is similar to trying to use cascading combos in a continuous form. It just doesn't work very well, because as you see in design view, there is really only one instance of the combo. I would probably not try to restrict the list, but rather test in either the before update event of the combo or the before update event of the form to make sure the selected item hasn't already been chosen. Basics of using the before update event here:

http://www.baldyweb.com/BeforeUpdate.htm

You could either open a recordset or use DCount() to test the selected item against existing data.