clear list box

[raviyah]

I have a list box with a query data source which works fine, unless the query
yields no records. Currently, the list box keeps the previous data until a
selection with data is made. I would like to clear out the list box in that
case.

Any ideas?

 

[Dirk Goldgar]

I have a list box with a query data source which works fine, unless the
query
yields no records. Currently, the list box keeps the previous data until
a
selection with data is made. I would like to clear out the list box in
that
case.

Any ideas?

How do you have the list box set up, and what triggers its requery?
Normally, if a list box's rowsource query returns no records, then the list
box will be blank. So if that's not what's happening for you, there must be
something unusual about your setup.

 

[fredg]

I have a list box with a query data source which works fine, unless the query
yields no records. Currently, the list box keeps the previous data until a
selection with data is made. I would like to clear out the list box in that
case.

Any ideas?

"Clear out" as in unselect the selected items?

Dim varItem As Variant
For Each varItem In ListBoxName.ItemsSelected
ListBoxName.Selected(varItem) = False
Next varItem

 

[raviyah]

Good question.
The entire setup is that I have a combo box that selects an item.
This triggers the first list box and will generate 0-10 items in this first
list box
When I click on one of the members of the first list box, the second listbox
is populated with a query that uses the selected item as a criteria.

When I reset the combo box, yet have not selected one of the items, the
second list box remains as it was. This is a problem when there are no items
in the first listbox as I cannot reset the second list box.

Am I clear in this explanation?

 

[Dirk Goldgar]

Good question.
The entire setup is that I have a combo box that selects an item.
This triggers the first list box and will generate 0-10 items in this
first
list box
When I click on one of the members of the first list box, the second
listbox
is populated with a query that uses the selected item as a criteria.

When I reset the combo box, yet have not selected one of the items, the
second list box remains as it was. This is a problem when there are no
items
in the first listbox as I cannot reset the second list box.

Am I clear in this explanation?

I think so. You could still be cascading your combo and list boxes in
either of two different ways: either by referring to the previous control as
a query parameter in the next control's rowsource, or by totally rewriting
the rowsource of the next control in the AfterUpdate event of the previous
one.

If you're doing it the first way, then you probably have code along these
lines in the combo box's AfterUpdate event:

Me.Listbox1.Requery

In that case, all you should need to do is requery the second one at the
same time:

Me.Listbox1.Requery
Me.Listbox2.Requery

On the other hand, you may be rewriting the rowsource queries of the
control's on the fly. So you may have code like this:

Me.Listbox1.RowSource = "SELECT ...."

If that's the case, you can just clear the rowsource of the next list box
completely:

Me.Listbox1.RowSource = "SELECT ...."
me.Listbox2.RowSource = ""

Does that give you enough to go on?

 

[raviyah]

I have the multiple requeries in the code. What is happening is when the
query has no records, there is no change to the listbox. I tried Fred's
select idea and it unselected the records in the first list box, but again no
change to the second. Is there no way to force the list box to delete the
current records when there are no records in the query?

 

[raviyah]

Follow up,

What I have done is to reset the rowselect in the combobox code to
Me.listbox2,rowselect=""

and in listbox1_afterUpdate code to
Me.listbox2.rowselect="Select ..."

This is working so far. It just seems that I am asking for trouble down the
road.

Thank you.

 

[Dirk Goldgar]

I have the multiple requeries in the code. What is happening is when the
query has no records, there is no change to the listbox. I tried Fred's
select idea and it unselected the records in the first list box, but again
no
change to the second. Is there no way to force the list box to delete the
current records when there are no records in the query?

It occurs to me that you may need to also set each of the list boxes' values
to Null, as well as requerying them. So instead of just:

Me.Listbox1.Requery
Me.Listbox2.Requery

.... do this:

Me.Listbox1.Requery
Me.Listbox1 = Null
Me.Listbox2.Requery
Me.Listbox2 = Null

(or something along those lines).

 

[Dirk Goldgar]

Follow up,

What I have done is to reset the rowselect in the combobox code to
Me.listbox2,rowselect=""

and in listbox1_afterUpdate code to
Me.listbox2.rowselect="Select ..."

This is working so far. It just seems that I am asking for trouble down
the
road.

By "rowselect", I take it you mean "RowSource". This approach is fine, if I
understand you, but see my reply to your other message. That may solve the
problem.

 

[raviyah]

Setting the listbox to null had no effect. I will use the rowsource option.

Thank you again

 

[Dirk Goldgar]

Setting the listbox to null had no effect. I will use the rowsource
option.

Then there must be something going on that is still obscure. If you were to
post the complete code from the form, I'm sure we could figure out what it
is. However, if you have found a good workaround, that's fine.

 

[raviyah]

Dirk,

If there is another way to send the code, let me know.
There are a number of combo boxes to choose from and fill in lboModPartMod
That query is working fine.
lboModPartModNum is listbox1 and lboModPartLoc is listbox2. lboModEff is
also a listbox2

cmd are command buttons, cbo is a combo box and lbo are listboxes.
There is just the one form

here is the code from the VBE:

Option Compare Database

Private Sub cboModPartLoc_AfterUpdate()
Me.lboModPartMod.Requery
End Sub

Private Sub cboModPartNum_AfterUpdate()
Me.lboModPartMod.Requery
End Sub

Private Sub cmdModPartLocDelete_Click()
Me.cboModPartLoc = ""
End Sub

Private Sub cmdModPartNumDelete_Click()
Me.cboModPartNum.Value = ""
End Sub

Private Sub lboModPartMod_AfterUpdate()
Me.lboModPartLoc.RowSource = "SELECT qryModPartLoc.ModPartNum,
qryModPartLoc.Loc1, qryModPartLoc.Loc2, qryModPartLoc.Loc3,
qryModPartLoc.Loc4 FROM qryModPartLoc WHERE
(((qryModPartLoc.ModPartModNum)=forms!frmModPartsSearch!lboModPartMod.value)); "
Me.lboModPartLoc.Requery
Me.lboModEff.RowSource = "qryModNumEff"
Me.lboModEff.Requery
End Sub

Private Sub CmdSearch_Click()
Dim varItem As Variant
For Each varItem In lboModPartMod.ItemsSelected
lboModPartMod.Selected(varItem) = False
Next varItem
Me.lboModPartMod.Requery
'Me.lboModPartLoc = Null
Me.lboModPartLoc.RowSource = ""
Me.lboModPartLoc.Requery
'Me.lboModEff = Null
Me.lboModEff.RowSource = ""
Me.lboModEff.Requery
End Sub

 

[Dirk Goldgar]

Dirk,

If there is another way to send the code, let me know.
There are a number of combo boxes to choose from and fill in lboModPartMod
That query is working fine.
lboModPartModNum is listbox1 and lboModPartLoc is listbox2. lboModEff is
also a listbox2

cmd are command buttons, cbo is a combo box and lbo are listboxes.
There is just the one form

here is the code from the VBE:

Option Compare Database

Private Sub cboModPartLoc_AfterUpdate()
Me.lboModPartMod.Requery
End Sub

Private Sub cboModPartNum_AfterUpdate()
Me.lboModPartMod.Requery
End Sub

Private Sub cmdModPartLocDelete_Click()
Me.cboModPartLoc = ""
End Sub

Private Sub cmdModPartNumDelete_Click()
Me.cboModPartNum.Value = ""
End Sub

Private Sub lboModPartMod_AfterUpdate()
Me.lboModPartLoc.RowSource = "SELECT qryModPartLoc.ModPartNum,
qryModPartLoc.Loc1, qryModPartLoc.Loc2, qryModPartLoc.Loc3,
qryModPartLoc.Loc4 FROM qryModPartLoc WHERE
(((qryModPartLoc.ModPartModNum)=forms!frmModPartsSearch!lboModPartMod.value));
"
Me.lboModPartLoc.Requery
Me.lboModEff.RowSource = "qryModNumEff"
Me.lboModEff.Requery
End Sub

Private Sub CmdSearch_Click()
Dim varItem As Variant
For Each varItem In lboModPartMod.ItemsSelected
lboModPartMod.Selected(varItem) = False
Next varItem
Me.lboModPartMod.Requery
'Me.lboModPartLoc = Null
Me.lboModPartLoc.RowSource = ""
Me.lboModPartLoc.Requery
'Me.lboModEff = Null
Me.lboModEff.RowSource = ""
Me.lboModEff.Requery
End Sub

Hmm, that code does not conform to what I suggested that you do. Let me
state my understanding of the situation, for you to check and correct or
confirm:

+ cboModPartNum and cboModPartLoc are combo boxes.

+ lboModPartMod is a list box whose rowsource is dependent on the values
selected in cboModPartNum and cboModPartLoc.

+ lboModPartLoc and lboModEff are list boxes whose rowsources are, or should
be, dependend on the value selected in lboModPartMod.

+ There is code currently in the AfterUpdate events of the two combo boxes
to requery lboModPartLoc so that its list correctly reflects the the
selected values in the combo boxes. This works.

+ However, the other list boxes, lboModPartLoc and lboModEff, don't clear
when you do this. Instead, they keep the same lists they had when you last
selected an item in lboModPartMod. What you want is for these list boxes to
clear when you change the selected values in the combo boxes, until you next
select an item in lboModPartMod.

Assuming that's correct, change your code to the following:

'------ start of code (modified procedures only) -----
Private Sub cboModPartLoc_AfterUpdate()
Me.lboModPartMod.Requery
Me.lboModPartMod = Null
Me.lboModPartLoc.Requery
Me.lboModEff.Requery
End Sub

Private Sub cboModPartNum_AfterUpdate()
Me.lboModPartMod.Requery
Me.lboModPartMod = Null
Me.lboModPartLoc.Requery
Me.lboModEff.Requery
End Sub

Private Sub lboModPartMod_AfterUpdate()
Me.lboModPartLoc.Requery
Me.lboModEff.Requery
End Sub

'------ end of code -----

That assumes that you have set up the list boxes at design time with
rowsources that refer to the appropriate controls on the form for criteria.

I don't know what your CmdSearch button is for -- there was no mention of it
in your previous posts, but it looks like you tried some of the code I
suggested there, though that was not what I intended.

By the way, there is never any need to explicitly requery a combo or list
box after you set its RowSource property. Setting the RowSource property
automatically forces a requery.

 

[raviyah]

Dirk,

That works great thank you

Hmm, that code does not conform to what I suggested that you do. Let me
state my understanding of the situation, for you to check and correct or
confirm:

+ cboModPartNum and cboModPartLoc are combo boxes.

+ lboModPartMod is a list box whose rowsource is dependent on the values
selected in cboModPartNum and cboModPartLoc.

+ lboModPartLoc and lboModEff are list boxes whose rowsources are, or should
be, dependend on the value selected in lboModPartMod.

+ There is code currently in the AfterUpdate events of the two combo boxes
to requery lboModPartLoc so that its list correctly reflects the the
selected values in the combo boxes. This works.

+ However, the other list boxes, lboModPartLoc and lboModEff, don't clear
when you do this. Instead, they keep the same lists they had when you last
selected an item in lboModPartMod. What you want is for these list boxes to
clear when you change the selected values in the combo boxes, until you next
select an item in lboModPartMod.

Assuming that's correct, change your code to the following:

'------ start of code (modified procedures only) -----
Private Sub cboModPartLoc_AfterUpdate()
Me.lboModPartMod.Requery
Me.lboModPartMod = Null
Me.lboModPartLoc.Requery
Me.lboModEff.Requery
End Sub

Private Sub cboModPartNum_AfterUpdate()
Me.lboModPartMod.Requery
Me.lboModPartMod = Null
Me.lboModPartLoc.Requery
Me.lboModEff.Requery
End Sub

Private Sub lboModPartMod_AfterUpdate()
Me.lboModPartLoc.Requery
Me.lboModEff.Requery
End Sub

'------ end of code -----

That assumes that you have set up the list boxes at design time with
rowsources that refer to the appropriate controls on the form for criteria.

I don't know what your CmdSearch button is for -- there was no mention of it
in your previous posts, but it looks like you tried some of the code I
suggested there, though that was not what I intended.

By the way, there is never any need to explicitly requery a combo or list
box after you set its RowSource property. Setting the RowSource property
automatically forces a requery.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)