Originally Posted by
June7
That's still a cascading/dependent combobox. Instead of = you need to use <> and reference textbox that has Gender value.
SELECT ID, [Name] FROM Members WHERE Gender <> [tbxGender];
And in some event, such as form OnCurrent and/or textbox AfterUpdate, Requery the combobox.
Be aware that cascading combobox does not work nice on Continuous or Datasheet form.
I have been trying several things to try to get this to work. I thought that one reasonable option would be to change the RowSource of the combobox control based on a query referencing the gender field of the current record, so I defined three queries:
qryGetMaleNamesAndIDsByCurrentMemberGender
Code:
SELECT Members2.ID, [LONG COMPLEX CONCATENATED STRING] AS GetMaleNamesAndIDsByCurrentMemberGender, Members2.Gender
FROM Members2
WHERE (((Members2.Gender)<>'F'))
ORDER BY [LONG COMPLEX CONCATENATED STRING]
qryGetFemaleNamesAndIDsByCurrentMemberGender
Code:
SELECT Members2.ID, [LONG COMPLEX CONCATENATED STRING] AS GetFemaleNamesAndIDsByCurrentMemberGender, Members2.Gender
FROM Members2
WHERE (((Members2.Gender)<>'M'))
ORDER BY [LONG COMPLEX CONCATENATED STRING]
qryGetAllNamesByID
Code:
SELECT Members2.ID,[LONG COMPLEX CONCATENATED STRING] AS GetAllNamesByID, Members2.Gender
FROM Members2
ORDER BY [LONG COMPLEX CONCATENATED STRING]
...and based on other things I read, tried changing the RowSource of the combobox based on various combinations of events:
Code:
Option Compare Database
Private Sub cbFirstSpouseName_Change()
Forms!Members2!cbFirstSpouseName.RowSourceType = "Table/Query"
If Me.Controls("txtGender").Value <> "F" Then
Forms!Members2!cbFirstSpouseName.RowSource = "qryGetMaleNamesAndIDsByCurrentMemberGender"
ElseIf Me.Controls("txtGender").Value <> "M" Then
Forms!Members2!cbFirstSpouseName.RowSource = "qryGetFemaleNamesAndIDsByCurrentMemberGender"
Else
Forms!Members2!cbFirstSpouseName.RowSource = "qryGetAllNamesByID"
End If
cbFirstSpouseName.Requery
End Sub
Private Sub Form_AfterUpdate()
Forms!Members2!cbFirstSpouseName.RowSourceType = "Table/Query"
If Me.Controls("txtGender").Value <> "F" Then
Forms!Members2!cbFirstSpouseName.RowSource = "qryGetMaleNamesAndIDsByCurrentMemberGender"
ElseIf Me.Controls("txtGender").Value <> "M" Then
Forms!Members2!cbFirstSpouseName.RowSource = "qryGetFemaleNamesAndIDsByCurrentMemberGender"
Else
Forms!Members2!cbFirstSpouseName.RowSource = "qryGetAllNamesByID"
End If
cbFirstSpouseName.Requery
End Sub
Private Sub Form_Current()
Forms!Members2!cbFirstSpouseName.RowSourceType = "Table/Query"
If Me.Controls("txtGender").Value <> "F" Then
Forms!Members2!cbFirstSpouseName.RowSource = "qryGetMaleNamesAndIDsByCurrentMemberGender"
ElseIf Me.Controls("txtGender").Value <> "M" Then
Forms!Members2!cbFirstSpouseName.RowSource = "qryGetFemaleNamesAndIDsByCurrentMemberGender"
Else
Forms!Members2!cbFirstSpouseName.RowSource = "qryGetAllNamesByID"
End If
cbFirstSpouseName.Requery
End Sub
Private Sub Form_Load()
Forms!Members2!cbFirstSpouseName.RowSourceType = "Table/Query"
If Me.Controls("txtGender").Value <> "F" Then
Forms!Members2!cbFirstSpouseName.RowSource = "qryGetMaleNamesAndIDsByCurrentMemberGender"
ElseIf Me.Controls("txtGender").Value <> "M" Then
Forms!Members2!cbFirstSpouseName.RowSource = "qryGetFemaleNamesAndIDsByCurrentMemberGender"
Else
Forms!Members2!cbFirstSpouseName.RowSource = "qryGetAllNamesByID"
End If
cbFirstSpouseName.Requery
End Sub
None of these change the RowSource of the combobox. If I enter a specific query name into the combobox, it remains there unchanged.
Ideas????