Making Listbox Visable 1

[TommyF]

I have a combo box on my form which is a list of job numbers from 2 tables. I also have two list boxes on my form that fill up with Data when a Job number is selected from the combo box. What I want to happen is, if one of the list boxes is empty after you have selected from the combo box then I don't want it to be visable.

At the moment I have the follwonig code in the click event on the combo box, but it does not seem to work.


If me.list8 = "" Then
me.list14.visible = True
me.list8.visible = False
Else
me.list14.visible = False
me.list8.visible = True
End If

I can get it to sort of work and one of the list boxes will be visible but when I select another job Number which should put data in the other list box nothing happens.

Hope you can help.

 

[tunsarod]

The click event of the combo-box is the wrong place for this action since it occurs before the combo-box value has been set and therefore before the data rows have been loaded into the list-boxes.

Use the combo-box After_Update event and code it like this:


Sub MyCombo_After_Update()

Me.List8.Visible = IIf(Me.List8.ListCount > 0, True,False)

Me.List14.Visible = IIf(Me.List14.ListCount > 0, True,False)

End Sub


Note: I assumed your intent is that each ListBox should be hidden if it has nothing to list - independently of the other.

Rod

 

[TommyF]

Thanks for that it does work but I have a problem. If I select a Job Number nothing happens. If I select the same job Number again then it changes.

I have tried to put a refresh in but that has not worked.

I am using Access 97.

Thanks for your help

 

[tunsarod]

Sounds like the After_update code is processing befopre the ListBoxes are reloaded, the click. After the second click go the List boxs are already loaded based on the combo selection.

Try placing the code in a separate procedure and calling that procedure from the combo's After_Update event.

I have to go out now but I'll check back later this evening.

Rod

 

[TommyF]

Thanks for your help Rod, but it still does the same.

 

[tunsarod]

Tommy,

I just built a form with one Listbox and One Combo box where the List box is based on an SQL Select query in which the bound column is based on the current value of the combo box.

In order to force the Listbox to refresh its data after a new value is selected in the combo I used List2.Requery. Immediately after I interrogate the ListCount property and set the List box visible condition appropriately.

Private Sub Combo0_AfterUpdate()
List2.Requery

List2.Visible = IIf(List2.ListCount > 0, True, False)

End Sub

This works exactly as you want. The key point is that the list box's data source must be requeried BEFORE the ListCount value is examined.

If this doesn't work for you then I am assuming that the ListBox's are being refreshed some other way. Can you say what method is used to requery the list box data source after the combo is updated.

Rod

 

[TommyF]

Thanks for all your help Rod, that worked.