How to make visibility one item dependent on choice of other

[jdfergus]

Hello, I have what seems like it should be a fairly simple question, but am having problems for whatever reason. In an Access form, I have two list boxes, [Status] and [PendTo]. In the [Status] box, I have the options "Open", "Pending", and "Closed"; with "PersonA" and "PersonB" in [PendTo]. What I would like to know is how to make it where the [PendTo] list box is only visible if the [Status] is set to "Pending". What I have is something like this in the BeforeUpdate field of the PendTo list box ('StatusLB' is the name of the [Status] list box itself):

=IIf("StatusLB='Pending'","Me!Visible=True","Me!Vi sible=False")

But alas, it doesn't work. Am I making a simple syntax error or is it even possible to do this, or am I just completely off? Please help.

[missinglinq]

To answer your question, you're just completely off! Sorry, but you asked!

Me.Visible controls the visibility of the entire form, not the PendTo listbox.

Also, any code in the PendTo_BeforeUpdate event won't fire until after something is selected from the PendTo listbox, so that isn't going to work for you, either.

Assuming that the listboxes are bound to fields in the underlying table/query, StatusLB has Multi-Select Property set to No and this is a Single View form, as opposed to a Datasheet or Continuous View form, this should do the job:

Private Sub StatusLB_AfterUpdate()
 If Me.StatusLB = "Pending" Then
  PendTo.Visible = True
 Else
  PendTo.Visible = False
 End If
End Sub
 
Private Sub Form_Current()
 If Me.StatusLB = "Pending" Then
  PendTo.Visible = True
 Else
  PendTo.Visible = False
 End If
End Sub

Welcome to Bytes!

Linq ;0)>

[jdfergus]

It works! Oh thank you so much. I actually tried something close to each of those two subs at one point, but never thought of using them both together. It makes sense why you have to do it that way though. Thanks again.

[missinglinq]

Glad we could help!

Linq ;0)>

[NeoPa]

Just as an alternative (as I know Linq's code should work) I'll post :

Private Sub StatusLB_AfterUpdate()
  Call PendToVis()
End Sub
 
Private Sub Form_Current()
  Call PendToVis()
End Sub
 
Private PendToVis()
  Me.PendTo.Visible = (Me.StatusLB = "Pending")
End Sub

I always recommend taking common code out into its own procedure, as an aide to avoiding potential problems later (if anything needs to be changed).

This is purely for the benefit of the OP as I know you wouldn't need to be told this Linq ;)