Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    436

    Check listbox value on current record (form)

    Experts:

    I need some assistance with throwing a message box depending on whether or not a listbox has a value. My listbox resides on a tab control (Page Index = 3).

    Background:
    - Form name: F124_N1S_Billet
    - Page/tab control name: tabBillet
    - Page/tab index number: 3
    - Listbox name: lstCFT_Summary

    Process:


    - Upon opening form "F124_N1S_Billet", I want to throw a message indicating either "Listbox is empty!" or "Assigned to WQSB!".
    - The "Assigned to WQSB" message box shown be thrown when there's at least one (1) value shown in the listbox (for current record).
    - Naturally, it needs to check the listbox value based on the current record. So, once I navigate from one record to the next, I want the IF to check again and throw the message box.
    - Please do keep in mind that throwing the message box is for testing my code only. Once it is properly activated, I will replace the message box with a different action (i.e., enabling/disabling another control).

    Current VBA:
    - As of right now, I use the VBA below.
    - Unfortunately, at this time I always get the message box from the Else statement ("Assigned to WQSB") even if the listbox is empty.

    Code:
    Private Sub Form_Current()
    
        'If Forms![F124_N1S_Billet]![tabBillet]![lstCFT_Summary].ItemData(0) = "" Then
        If Forms![tabBillet]![lstCFT_Summary].ItemData(0) = "" Then
        
             MsgBox "Listbox is empty!"
        Else
            MsgBox "Assigned to WQSB!"
        End If
    End Sub
    My question: How do I need to modify the IF statement (i.e., using a correct form/tab/page/listbox reference) so that the If/Else is properly checked?

    Thank you,
    EEH

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,240
    Don't know what the rowsource for the listbox is, but if it's a query, you could simply check the recordcount = 0 or DCount = 0 instead of looking at the listbox itself.

  3. #3
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    436
    Davegri:

    Yes, the listbox's row source is a query:

    Code:
    SELECT [T103_N1S_Billets].BilletIDpk, [T202_CFT_CrossFunctionalTeams].CFT, [T203_CFT_Roles].RoleName FROM T203_CFT_Roles INNER JOIN (T103_N1S_Billets INNER JOIN (T202_CFT_CrossFunctionalTeams INNER JOIN (T101_N1S_JunctionOBS INNER JOIN T201_CFT_JunctionCFT ON [T101_N1S_JunctionOBS].RecordIDpk=[T201_CFT_JunctionCFT].RecordIDfk) ON [T202_CFT_CrossFunctionalTeams].CFTIDpk=[T201_CFT_JunctionCFT].CFTIDfk) ON [T103_N1S_Billets].BilletIDpk=[T101_N1S_JunctionOBS].BilletIDfk) ON [T203_CFT_Roles].RoleIDpk=[T201_CFT_JunctionCFT].RoleIDfk WHERE ((([T103_N1S_Billets].BilletIDpk)=Forms!F124_N1S_Billet!BilletIDpk)) ORDER BY [T202_CFT_CrossFunctionalTeams].CFT;
    Could you please further elaborate on the IF statement... i.e., how do I check the DCount in the code provided in the original post?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,893
    Why would listbox have more than 1 selected item?

    If listbox is not multi-select and is bound to field and you want a message if field is empty, record count is not needed. Just check field value.

    If listbox is multi-select and bound to multi-value field, this does complicate.

    Location on tab control is irrelevant.

    What form is code behind?
    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.

  5. #5
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    436
    Multi-select property is set on "None"

    Code:
    Private Sub Form_Current()
    
        If Forms![F124_N1S_Billet]![lstCFT_Summary].ItemData(0) = "" Then
    
             MsgBox "Listbox is empty!"
        Else
            'MsgBox "Assigned to WQSB!"
            MsgBox [Ra_BIN]
            [lstCFT_Summary].SetFocus
              MsgBox [lstCFT_Summary].
    [ListCount]
           
        End If
    End Sub

    Right now, I'm always hitting the "Else"... even if the msgbox indicates "0". How should I rewrite the above IF/Else statement so that -- when navigating from one record to another -- the current message is displayed. I'm hopeful you have some other recommendations.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,893
    Did you try what was already suggested?

    If IsNull(Me.lstCFT_Summary) Then

    or

    If IsNull(Me!fieldname) Then
    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
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    436
    Additional background:

    - A staff member may be assigned to one or many cross-functional teams.
    - Whatever the number of their assignments, the names of the CFTs would be listed for the particular staffmember.

    Now, in another step (after I solved the last one), I then want to enable a combo box where a value could be changed to "training completed" or "training not completed".
    - However, I do NOT want to be able to change the combo box value for a staff member in the event the staff member is not assigned to any CFT.

    So, as part of phase 1, I want to merely test (via the msgbox) whether the staff member is assigned to the CFT.
    - That info is not part of my staff member table so I'm using a lookup in the listbox.

    Hope this helps clarifying the underlying concept.

  8. #8
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    436
    June:

    Now, it's the other way around... that is, I only get the MsgBox "Listbox is empty!" ... even if the listbox has values. I'm baffled... any additional thoughts?


    Code:
        If IsNull(Me.lstCFT_Summary) Then
    
             MsgBox "Listbox is empty!"
        
        Else
            'MsgBox "Assigned to WQSB!"
            MsgBox [Ra_BIN]
            [lstCFT_Summary].SetFocus
            MsgBox [lstCFT_Summary].
    [ListCount]
         
        End If

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,893
    Listboxes act differently from comboboxes and I seldom use them for data entry and I did not test that code option.

    Not clear what your setup is and what you want to accomplish.

    Did not really answer question: Why would listbox have more than 1 item selected (you said "at least one")? If it is not multi-select enabled then it cannot have multiple items selected.

    If listbox is bound to field then check the field for content.
    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.

  10. #10
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    436
    Let's press the reset button...

    - Depending on the record, the listbox will either a) be blank... i.e., zero value; b) have one value or c) have more than one values
    - When I navigate from one record to the next, I simply want each time check a) if listbox has 0 values or b) has one or more values
    - Depending on that check, I want to throw the appropriate message box. Data entry is not part of this thread/question.

    My question remains: How do I write the IF/Else statement so that each time I move to another record, the count of the listbox is checked and then throws the msgbox?

  11. #11
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,887
    It says
    at least one (1) value shown in the listbox (for current record)
    it doesn't say at least one selected value.
    Try If Me.lstCFT_Summary.RecordSource.RecordCount > 0 - which is sort of what was already suggested but maybe not tried.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,893
    So the list box will have no items listed, 1 item listed, or several items listed.

    Use of word 'value' implies data in control value property to me, not listed items to choose from. Sorry for misunderstanding.

    I tried Micron's suggestion and it fails. The following did work.

    Me.lstCFT_Summary.Recordset.RecordCount
    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.

  13. #13
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    436
    Excellent!!! Thank you both Micron and June7.

    Now... I just need one additional tweak. Instead of checking one listbox, I need to actually check three listboxes. So, in the event
    Listbox #1 (lstCFT_Summary) has zero "items" OR
    Listbox #2 (lstOPT_Summary) has zero "items" OR
    Listbox #3 (lstWS_Summary) has at least one or more "items", I then want the IF to throw MsgBox "Assigned to WQSB!"

    So, ultimately, as long as there is at least one in either of the three listboxes, I need to go into the IF. Otherwise, if all three listboxes have no items, I then want to throw "MsgBox "Not assigned to any WQSB!"

    Right now, the code below works great but is only based on the first listbox:
    Code:
    Private Sub Form_Current()
    
        If Me.lstCFT_Summary.Recordset.RecordCount > 0 Then
    
            MsgBox "Assigned to WQSB!"
            MsgBox [lstCFT_Summary].
    [ListCount]
        
        Else
            
            MsgBox "Not assigned to any WQSB!"
            MsgBox [lstCFT_Summary].
    [ListCount]
         
        End If
     
    End Sub

  14. #14
    skydivetom is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    436
    Actually... I figured it out... code is shown below and works fine:

    Code:
    Private Sub Form_Current()
    
        If Me.lstCFT_Summary.Recordset.RecordCount > 0 Or Me.lstOPT_Summary.Recordset.RecordCount > 0 Or Me.lstWS_Summary.Recordset.RecordCount > 0 Then
    
            MsgBox "Assigned to WQSB!"
            MsgBox "CFT: " & [lstCFT_Summary].
    [ListCount]
            MsgBox "OPT: " & [lstOPT_Summary].
    [ListCount]
            MsgBox "WS: " & [lstWS_Summary].
    [ListCount]
            
        Else
            
            MsgBox "Not assigned to any WQSB!"
            MsgBox "CFT: " & [lstCFT_Summary].
    [ListCount]
            MsgBox "OPT: " & [lstOPT_Summary].
    [ListCount]
            MsgBox "WS: " & [lstWS_Summary].
    [ListCount]
         
        End If
     
    End Sub
    I will work on "phase 2" tomorrow. That is, if any of the listboxes has at least one item, I want a combo to be enabled on another form. I'm not sure how I cross reference the two forms (billets and staff members yet). I will provide more details in another thread.

    In the meanwhile, thank you for your help... you guys rock!!!

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,893
    Could just add the counts. If total is greater than 0 then "Assigned" otherwise "Not Assigned"

    MsgBox IIf(Me.lstCFT_Summary.Recordset.RecordCount + Me.lstOpt_Summary.Recordset.RecordCount + Me.lstWS_Summary.Recordset.RecordCount = 0, "Not Assigned to any WQSB", "Assigned to WQSB")
    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.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 08-18-2018, 10:23 AM
  2. Make last record in Form's RecordSource the current record
    By GraeagleBill in forum Programming
    Replies: 6
    Last Post: 06-27-2018, 10:43 AM
  3. Listbox in form is current upon form open
    By LonghronJ in forum Modules
    Replies: 3
    Last Post: 03-18-2018, 10:01 AM
  4. Replies: 1
    Last Post: 05-18-2015, 01:27 AM
  5. Replies: 3
    Last Post: 02-06-2015, 01:18 PM

Posting Permissions

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