Field1 = "this" then field2 must = "this, this,this"

[tjones]

I have two fields 1 & 2

field 1 is a simple combo list of user defined values ie A, B, or C

Field 2 relates to a attribute of the data in field 1 and is not always the same for A, B, and C i.e

A could have a,d or e
B could have b,d or e
C could have c,d or e

I would like field 2 to have a combo box which only displays (or throws an error if other than) a,d, or e when A is chosen in field 1; b,d or e when B is chosen; etc

I though about using cascading combo boxes, but field 1 is already the first selection in another cascading combo box group and I did not know if it could control 2.

 

[boblarson]

1. Your example is confusing. I know you are trying to simplify it but it is actually better to use real terms that are being used instead.

2 Yes, the first one can be referred to by another one as a limit to the second. In other words the first combo selection can control multiple other combos - all you have to do is to set that control as its criteria:
[Forms]![FormNameHere]![ControlNameHere]

 

[tjones]

Below is the code I used. The first section is the first time the field is used as a "trigger field" and the form was stable at this point, and the second the one I am attempting to set up. I managed to get this working the way I wanted, in that i limited the selection the way I wanted, but it made my split form unstable and made my queries using:

=Nz(DLookUp("[TotalCreditsReq]","qryUnitTotalRequired"),"")

no longer work probably due to the fact that the field now is set up as a second combo box

Private Sub cboCourseTypeID_AfterUpdate()
' Set the Department combo box to be limited by the selected CourseType
Me.cboDepartmentID.RowSource = "SELECT tblCourseDept.DepartmentID, tblCourseDept.DepartmentName FROM tblCourseDept " & _
" WHERE CourseTypeID = " & Nz(Me.cboCourseTypeID) & _
" ORDER BY DepartmentName"
Me.cboDepartmentID = Null

' Set the Course Status combo box to be limited by the selected CourseType
Me.cboCourseStatus.RowSource = "SELECT refCourseStatus.CourseStatusID, refCourseStatus.CourseStatus FROM refCourseStatus " & _
" WHERE CourseTypeID = " & Nz(Me.cboCourseTypeID) & _
" ORDER BY CourseStatus"
Me.cboCourseStatus = Null


Private Sub FilterDescriptionList()

Dim strRS As String

' Filter the list box appropriately based on the combo box selection(s)
strRS = "SELECT qryCourseDescList.UnitsAssigned, qryCourseDescList.Offered, qryCourseDescList.Prereqs FROM qryCourseDescList"
If Not IsNull(Me.cboCourseNameID) Then
strRS = strRS & " WHERE CourseNameID = " & Me.cboCourseNameID
ElseIf Not IsNull(Me.cboCourseNoID) Then
strRS = strRS & " WHERE CourseNoID = " & Me.cboCourseNoID
ElseIf Not IsNull(Me.cboDepartmentID) Then
strRS = strRS & " WHERE DepartmentID = " & Me.cboDepartmentID
ElseIf Not IsNull(Me.cboCourseTypeID) Then
strRS = strRS & " WHERE CourseTypeID = " & Me.cboCourseTypeID
End If

strRS = strRS & " ORDER BY qryCourseDescList.Offered;"

Me.lstDescriptionID.RowSource = strRS

Me.lstDescriptionID.Requery

'filter the Cstatus list box approriatly based on selection
If Not IsNull(Me.cboCourseStatus) Then
strRS = strRS & " WHERE CourseStatusID = " & Me.cboCourseStatus
ElseIf Not IsNull(Me.cboCourseTypeID) Then
strRS = strRS & " WHERE CourseTypeID = " & Me.cboCourseTypeID
End If

End Sub