Results 1 to 12 of 12
  1. #1
    notableaudio is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2015
    Posts
    9

    Form Command Button visibility triggered by hidden column value of list box (current selection)

    Hi all,

    I am building a parts ordering database for our maintenance shop and am having trouble getting my form to function the way I'd like.

    I have a form that filters parts values in a listbox based on combo box values of parts classifications (ie. Electrical => Fuses returns all fuse parts).

    Our Plant Manager wants to have a link to our Knowledge Base (KB) near this listbox to avoid ordering the incorrect part for a given job.

    Not all parts have an entry in the KB table, so if the currently selected row of the list box has no companion in the KB I want the command button (KBLink) that runs the KB report to turn invisible.

    The list box (PartSearchResults) has 3 columns:
    1) The Part Number (Visible in listbox)
    2) The Description of the part (Visible in listbox)
    3) The KBid, if applicable (Hidden in listbox)



    I am able to create a text field (HKBid) that correctly shows the value of the selected row's third column, and will update on click() of the listbox. The Control Source for this text field is =[PartSearchResults].[Column](2)

    I have attempted to add this code to the Form's Current() event:
    Code:
    Private Sub Form_Current()
    If IsNull(PartSearchResults.Column(2)) Or PartSearchResults.Column(2) = "" Then
        KBLink.Visible = False
    Else
        KBLink.Visible = True
        End If
    End Sub
    When I change the selection of PartSearchResults, I see the appropriate change to HKBid, but KBLink never appears (its default property is visible = No)

    Does anyone have any thoughts about where I am going wrong? I'm very new to VBA and front-end development, so maybe/hopefully this is a stupid mistake that's easy to fix.

    Thanks in advance for any feedback.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Readers need to see your tables and relationships. Please post a jpg of the Relationships window, or a copy of your database. Just enough data to highlight the issue.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    "Form_Current" only fires if you move to a different record. Changing a selection in a list box does not fire the form current event. Add the code to list box after update event.

    Try
    Code:
    Private Sub PartSearchResults_AfterUpdate()
      If IsNull(Me.PartSearchResults.Column(2)) Or Me.PartSearchResults.Column(2) = "" Then
         Me.KBLink.Visible = False
      Else
         Me.KBLink.Visible = True
      End If
    End Sub
    Could also try writing it this way:
    Code:
    Private Sub PartSearchResults_AfterUpdate()
       Me.KBLink.Visible = Len(Me.PartSearchResults.Column(2) & "") > 0
    End Sub

  4. #4
    notableaudio is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2015
    Posts
    9

    Relationships

    Quote Originally Posted by orange View Post
    Readers need to see your tables and relationships. Please post a jpg of the Relationships window, or a copy of your database. Just enough data to highlight the issue.
    Orange, Here is a snapshot of relationships and the form I am working on.

    The grey field at the bottom of the form is the HKBid. It is currently showing the KBid from Tbl KnowledgeBase for the selected item in the listbox. The Command button KBLink is not shown, as it is not visible, even through the HKBid is not null (successfully received the value from the hidden listbox column).

    Click image for larger version. 

Name:	Relationships.jpg 
Views:	13 
Size:	119.8 KB 
ID:	23602
    Click image for larger version. 

Name:	Form1.jpg 
Views:	13 
Size:	112.5 KB 
ID:	23603
    Attached Thumbnails Attached Thumbnails Relationships.jpg  

  5. #5
    notableaudio is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2015
    Posts
    9
    Quote Originally Posted by ssanfu View Post
    "Form_Current" only fires if you move to a different record. Changing a selection in a list box does not fire the form current event. Add the code to list box after update event.

    Try
    Code:
    Private Sub PartSearchResults_AfterUpdate()
      If IsNull(Me.PartSearchResults.Column(2)) Or Me.PartSearchResults.Column(2) = "" Then
         Me.KBLink.Visible = False
      Else
         Me.KBLink.Visible = True
      End If
    End Sub
    Could also try writing it this way:
    Code:
    Private Sub PartSearchResults_AfterUpdate()
       Me.KBLink.Visible = Len(Me.PartSearchResults.Column(2) & "") > 0
    End Sub
    Steve,

    Thanks for the suggestions, I have tried both to no avail. Either suggestion does not make the button appear when I select any row in the listbox. Please see the screenshot below, the grey field is the HKBid. It is finding the correct KBid, but for some reason doesn't work the same way with the command button.

    On the after Update() for the listbox I did have a macro that requery's the Recent Order History subform. I moved this to VBA, but figured I would mention that in case it's a separate problem.

    Also, I have tried using the HKBid (grey field) to test whether to make the button visible, but doesn't work properly either.

    Thanks for your help!
    -Chris


    EDIT: I tried Steve's solution again and was successful getting the command button to appear/disappear appropriately. I may have been getting false results due to old code set in other events and may have interfered with the solution.

    Thanks for all of your help!
    Last edited by notableaudio; 02-08-2016 at 04:05 PM. Reason: Solved.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  7. #7
    notableaudio is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2015
    Posts
    9
    I'd love to, but I do not have approval to distribute.

    Is there any other information I could provide that may help diagnose the issue?

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    This thread is marked as Solved. Was that in error?

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You could just give us 10 records or so. We just need enough data to show the issue.
    You can make up some names Porky Pig, Daffy Duck etc... Towns like CenterTown, NewTown... to anonymize the data.


    Ooops:
    Good question Allan, I didn't notice that. I think it's an error.

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I threw together a demo. In the list box, Part 1 and Part 2 have KBid. Part 3 does not. Clicking on Part 3 hides the button. Parts 1 & 2 display the button.

    I know it is not your dB, but the code works.


    EDIT:
    @ orange & RG
    I got a message that the code works. Didn't work the first time - now it works. OP marked the problem solved.


    EDIT 2:
    There are several reserved words used: "Name", "Type", "Class", "Description" & "Order". These should not be used for object names.

    Also should not use spaces, punctuation or special characters in object names. I saw "#" and "?" in field names.
    Attached Files Attached Files

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  12. #12
    notableaudio is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2015
    Posts
    9
    Quote Originally Posted by ssanfu View Post
    I threw together a demo. In the list box, Part 1 and Part 2 have KBid. Part 3 does not. Clicking on Part 3 hides the button. Parts 1 & 2 display the button.

    I know it is not your dB, but the code works.


    EDIT:
    @ orange & RG
    I got a message that the code works. Didn't work the first time - now it works. OP marked the problem solved.


    EDIT 2:
    There are several reserved words used: "Name", "Type", "Class", "Description" & "Order". These should not be used for object names.

    Also should not use spaces, punctuation or special characters in object names. I saw "#" and "?" in field names.
    Thanks Steve, I will be sure to rename the objects without reserved words. I guess I was pretty sloppy.

    And yes, this is solved, Steve's initial solution worked great, specifically using LEN() to set the visibility property in the after update() event.

    Thanks for the help!

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

Similar Threads

  1. Replies: 22
    Last Post: 09-23-2015, 09:01 PM
  2. Replies: 3
    Last Post: 06-03-2015, 10:16 AM
  3. Which Button triggered the afterdate of a textbox
    By sneuberg in forum Programming
    Replies: 6
    Last Post: 09-10-2014, 08:25 AM
  4. Replies: 9
    Last Post: 09-17-2013, 11:08 PM
  5. Replies: 13
    Last Post: 12-17-2012, 07:52 PM

Tags for this Thread

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