Enable Button based on Two list boxes

[YorkieU]

Please help with some logic.
I have two list boxes and one command button. I want the button to be
disabled until BOTH list boxes are selected. I am having some dificulty with
the logic especially when the list boxes are requeried. When the requery
causes either or both of the list boxes to not have a selection, how do I
make the button disabled until both boxes have a selection again? Thanks for
some help!

 

[Jack Leach]

In your form's module, make a private sub to check this and set the button
enabled depending on the values. At some point your code is requerying the
list boxes, so in that same event, call this sub to check the values. You
will also want to call it from the afterupdate event of the list boxes, and
probably the OnCurrent event of the form.

Ex:

Private Sub ListBox1_AfterUpdate()
Call psSetCommandButton()
End Sub

Private Sub psSetCommandButton()
If (Len(Nz(Me.ListBox1, "")) <> 0) AND _
(Len(Nz(Me.ListBox2, "")) <> 0) Then

'both have a selection
Me.CommandButton.Enabled = True

Else

'one of them doesn't have a selection
Me.CommandButton.Enabled = False

End If
End Sub



Note though, that ListBoxes have various ways to get the current selection
depending on the properties of the control. You may have to play around with
the code a little to get it to work in your situation, but this should be a
pretty good example to get what you need.

hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Klatuu]

Or:

Me.CommandButton.Enabled = Len(Nz(Me.ListBox1,"") & Nz(Me.ListBox2,""))

 

[John Smith]

Sorry but I think that you would need:

Me.CommandButton.Enabled = Len(Nz(Me.ListBox1,"")) And Len(Nz(Me.ListBox2,""))

to check that both have entries.

HTH
John
##################################
Don't Print - Save trees

 

[BruceM via AccessMonster.com]

I don't see how either would work. Adding the values returned by Len would
return 0 if both are empty, but I don't see how that helps here. I think
something such as suggested in the first response, but the If can be avoided:

Me.CommandButton.Enabled = Len(Nz(Me.ListBox1,"")) > 0 And _
Len(Nz(Me.ListBox2,"")) > 0

Or maybe:

Me.CommandButton.Enabled = Not IsNull(Me.ListBox1) And Not IsNull(Me.ListBox2)


If the list boxes are bound, I would make sure zero-length strings are not
allowed in the table if using this method.

 

[Klatuu]

John is incorrect, Bruce is correct. Actually it should be:

Me.CommandButton.Enabled = Trim(Len(Nz(Me.ListBox1,"")) > 0 And
Trim(Len(Nz(Me.ListBox1,"")) > 0

That way it will require some value in both controls.

 

[John Smith]

My version would have worked because anything other than zero is true but
unfortunately I left out the 'NOT' that should have been after the equals so it
would have done the reverse of what was intended.

John
##################################
Don't Print - Save trees

Klatuu wrote:
John is incorrect, Bruce is correct. Actually it should be:
Me.CommandButton.Enabled = Trim(Len(Nz(Me.ListBox1,"")) > 0 And
Trim(Len(Nz(Me.ListBox1,"")) > 0
That way it will require some value in both controls.

 

[BruceM via AccessMonster.com]

I did some testing in the immediate window:

0 and 0 >> 0
Not 0 and 0 >> 0
0 and 1 >> 0
Not 0 and 1 >> 1
Not 0 and Not 1 >> -2
1 And 1 >> 1
Not 1 and 1 >> 0
Not 1 and Not 1 >> -2

If anything, the way you originally suggested may work, but Not does not, as
"Not 0 and 1" and "Not 0 and Not 1" both return a value other than 0, so
Enabled would be true. VBA Help on the And operator seems to confirm this,
as do some tests I ran with the Enabled property on a form.

Maybe it's just me, but I find the logic of this approach rather indirect.
However, I will say that your original suggestion seems to work.

My version would have worked because anything other than zero is true but
unfortunately I left out the 'NOT' that should have been after the equals so it
would have done the reverse of what was intended.

John
##################################
Don't Print - Save trees

John is incorrect, Bruce is correct. Actually it should be:
Me.CommandButton.Enabled = Trim(Len(Nz(Me.ListBox1,"")) > 0 And
Trim(Len(Nz(Me.ListBox1,"")) > 0
That way it will require some value in both controls.

I don't see how either would work. Adding the values returned by Len would
return 0 if both are empty, but I don't see how that helps here. I think

[quoted text clipped - 12 lines]