Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2012
    Posts
    28

    Question Unanswered: Hidding inactive items from combobox list

    I have a combobox that is linked to a query as the control source.
    The query holds a list of members. I created an Active flag.

    In my form, I have the combobox and I only want to show active members.
    However, I don't simply want to filter out the query on active members because then the old entries end up displaying blank if the member was inactive.

    I attempted to create an BeforeUpdate event to check for the active flag and hide those that are inactive in the form when you enter a new entry but no luck.

    Was hoping someone has a suggestion as how people solve this issue?

    Cheers

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If you have a flag then use that as part of the combo rowsource. EG:-
    SELECT my,column, list FROM mytable WHERE activeflag = True

    If you want to be a smartypants put other controls alongside (radio or checkbox, probably 3vradio buttons would be best) and use those settings to control whether you display active, lapsed or both
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2012
    Posts
    28
    Quote Originally Posted by healdem View Post
    If you have a flag then use that as part of the combo rowsource. EG:-
    SELECT my,column, list FROM mytable WHERE activeflag = True
    But that's exactly the point I was trying to make above. When I do that, the records that have inactive members end up being blank.
    But when another record that has an active member listed, it shows up just fine.

    Let me clarify a bit more.
    I have a form and within that form I have 10 fields. One of those fields is a member field. Say I have 100 records. I still want to see all 100 records (regardless of if they are inactive or active). When I start a new record, 101, I want to be able to pick only active members for the member field.
    This works just fine with the where statement activeflag = true. But again, now the member field for records with inactive members, shows blank.


    Quote Originally Posted by healdem View Post
    If you want to be a smartypants put other controls alongside (radio or checkbox, probably 3vradio buttons would be best) and use those settings to control whether you display active, lapsed or both
    Can you explain this little more please? I'm not following how this works.

    Thanks again

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ok so youhave two spearate issues
    1) you have exisitng records where you need to show the members details (irresepctive of whether they are current/active or not)
    2) you need to only have current members for new records

    presumably on historic records you are not going to change the member...

    what I'd suggest you do is have whatevber controls you need for displaying the member (or ex member) as required
    make these control bound tot hemembershiup data however you've done that)
    add the combo whgich contaisn only current / active memebrs. have that also bound tot he membership number

    that way round you can see the historic details
    you can only change the members details for current active members
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    If this is a Single View Form, you could do what I did for a client, once, and change the RowSource of the Combobox, depending on whether it is a New Record, or an existing Record; show only active members for new Records and all members for existing Records.

    The Status Field is Boolean; Active = Null/False, Inactive = True.

    Code:
    Private Sub Form_Current()
    
     Dim strComboRowSource As String
        
     If Me.NewRecord Then
       
        strComboRowSource = "SELECT tblMembers.MemberID, [MemLast] + ', ' + [MemFirst] AS MemberName, tblMembers.Status " & "FROM tblMembers " & "WHERE (((tblMembers.Status)<>True));"
    
      Else
       
         strComboRowSource = "SELECT tblMembers.MemberID, [MemLast] + ', ' + [MemFirst] AS MemberName, tblMembers.Status " & "FROM tblMembers; "
      
      End If
    
        Me.cboMemberName.RowSource = strComboRowSource
        Me.cboMemberName.Requery
    
    End Sub
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •