Limit combobox for displaying values that is already selected in a different record.

[scopes456]

Hi all, I was doing some research but could not find an answer. I have a database that i use to assign projects. One the form there is two combo box. One combobox has a list of all the projects [cmbProj] and the other is Priority [cmbPri]. What i would like to do if i select a project and a priority, the next time i select the same project type, i would not be able to select the same priority until the project is completed. this way there is no same projects with the same priority.

 

[Minty]

Query your current list of projects that aren't completed, group by priority. This will give you a list of the priorities you can't use. Use that as a sub query for your priorities - NOT IN (your list of used priorities).

If you haven't used subqueries before http://allenbrowne.com/subquery-01.html

 

[arnelgp]

if you have completion date there in your form,
you can test if this field is null (no date). eg:

Private Sub PriorityCombo_BeforeUpdate(Cancel As Integer)
If Not Me.NewRecord Then
If IsNull(Me.CompletionDate) Then
Cancel = True
MsgBox "Unable to change priority until this priority has been completed."
Me.Undo
End If
End If
End Sub