List box

[I don''t know Excel]

I have a form where user can select group name from a names included in a
list box. I created 2 fields, 1st for phone number and 2nd for email address
and placed then next to the list box on my form.
I would like to:
Populate both fields with phone number when user selects group name.
Should I use a query to do this or use SQL statement but I am not sure what
I am selecting
Select phone number from Table where group name = ?
Also since I don't have phone numbers and email addresses for some groups I
would like to have both field be visible only when user selects group name
which have phone number. If group ABC no phone number I want both filed to
be invisible, but if user select group CDF that have a phone number stored in
a table I want both fields to be visible and populated with respected phone
and email address.
Thanks

 

[Frank H]

There are two parts to this:
1)Rewrite the query that populates your list box.
2)Event procedures to fill values into and hide the text boxes when null

Let's say the list box name is lstGroup, and the text boxes are txtPhone and
txtEmail.

1) You revise the lstGroup to include the phone and email in its own list,
and then reference those values from the text boxes...
Set up the properties of your lstGroup to have two more columns than it
currently has. (You may want to set the column width properties to 0" for
these two columns). Then use the RowSource property of lstGroup, builder
button, to revise the query: include the phone number (let's say in column 3
of the query) and the email (let's say in column 4 of the query).

The following would then be the code in the AfterUpdate event procedure of
the lstGroup:
'{note that the list box columns start numbering at zero so your reference
is one less that where the column is in the query}
I may not have the syntax exactly right from memory, but this gives you the
idea of what should be happening:
dim HasPhone as variant
HasPhone = me.lstgroup.columns(2)
if isnull(HasPhone) then
me.txtphone.visible = false
me.txtemail.visible = false
me.txtPhone=""
me.txtemail=""
else
me.txtphone.visible = true
me.txtemail.visible = true
me.txtPhone=HasPhone
me.txtemail=me.lstGroup.columns(3)
endif

Post back to let me know how this turned out.

 

[Frank H]

I forgot to mention: I'm assuming that you are referreing to Access, and not
Excel.

 

[I don''t know Excel]

Thanks a lot Frank, everything is working however for some reason when i
select group without phone number. phone number field is still showing up.

Here is my code:

Private Sub lstGroup_AfterUpdate()
Dim HasPhone As Variant

HasPhone = Me.lstGroup.Column(2)
If IsNull(HasPhone) Then
Me.txtPhoneday.Visible = False
Me.txtPhoneNight.Visible = False
Me.txtPhoneday = ""
Me.txtPhoneNight = ""
Else
Me.txtPhoneday.Visible = True
Me.txtPhoneNight.Visible = True
Me.txtPhoneday = HasPhone
Me.txtPhoneNight = Me.lstGroup.Column(3)
End If
End Sub

I want those 2 fields be invisible when there is no phone number. List box
is based on a query and phone number is in column 3 in this query

Thanks

 

[John Spencer]

Try checking for a zero-length string

If Len(HasPhone & "") = 0 Then
...
Else
...
End If

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County