Results 1 to 8 of 8
  1. #1
    drhansenjr is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    14

    Query Criterion Based on Field Value of Current Form Record

    I have a form that contains a number of comboboxes based on a combination of values from the same table that populates the form. One of the fields is gender. I am a bit stumped as to how to specify the criteria for each query to say "if the gender field value of the current record is "F", show only results in the combobox where gender="M", else show only results in the combobox where gender="F". I can create a query (see attached) that filters on the SAME gender value of the record, but not the opposite.



    Thanks!
    Click image for larger version. 

Name:	query.jpg 
Views:	14 
Size:	83.7 KB 
ID:	44724

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    How about

    <>”M”

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I don't understand your IF statement. If field value is "F", why would you want combobox to show names classed as "M"?

    If you want a cascading (dependent) combobox, that is a very common topic.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    drhansenjr is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    14

    re: Query Criterion Based on Field Value of Current Form Record

    Quote Originally Posted by June7 View Post
    I don't understand your IF statement. If field value is "F", why would you want combobox to show names classed as "M"?

    If you want a cascading (dependent) combobox, that is a very common topic.
    Because I want the combobox to show a list of potential SPOUSES of the opposite gender of the person shown in the currently displayed record. Each person is coded "M" or "F". If the person in the current record is coded "F", for example, I would want the spouse combobox to show only IDs a name of members coded "M:.

  5. #5
    drhansenjr is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    14
    Quote Originally Posted by Ajax View Post
    How about

    <>”M”
    The issue is that whether I use <>"M" or <>"F" depends on the gender of the person in the current record. Consider this bit of clumsy pseudocode for the criteria:
    IF (The GENDER of the member in the current record is "F"] THEN [Set the GENDER criterion for the combobox query to "M"] ELSE [Set the GENDER criterion for the combobox query to "F"]

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    drhansenjr is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    14

    Re: Query Criterion Based on Field Value of Current Form Record

    Quote Originally Posted by June7 View Post
    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????

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 11-09-2018, 08:47 AM
  2. Replies: 7
    Last Post: 01-04-2018, 06:35 PM
  3. Replies: 3
    Last Post: 11-22-2015, 11:01 AM
  4. Replies: 2
    Last Post: 04-02-2013, 02:24 AM
  5. Query based on current record in form
    By bhsvendbo in forum Queries
    Replies: 1
    Last Post: 06-01-2010, 11:20 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums