Empty Listbox shows listcount = 1

[TheSearcher]

--Column Heads property set to "No".
lst_Goals.Rowsource = ""
lst_Goals.Requery
msgbox lst_Goals.listcount - This returns 1
Any ideas?

 

[theDBguy]

Just a guess, but maybe that's the default or minimum number the ListCount property will return. I bet even if you set Column Heads to Yes, you would still get a 1.

 

[moke123]

Listcount returns the number of rows in a list whether there is visible data or not.
A listbox with a rowsource of a Zls still has one row.
You can still select the first row of an empty list and the .ItemsSelected.Count property will return 1, as will .listcount

 

[TheSearcher]

moke123 - I didn't know a listbox with a zls would return 1 for a listcount. Interesting. What would I need to do to have it return 0?
This is an issue because sometimes the listbox does have 1 legitimate record in it. I need my code to let me know when it is truly empty.

 

[theDBguy]

moke123 - I didn't know a listbox with a zls would return 1 for a listcount. Interesting. What would I need to do to have it return 0?
This is an issue because sometimes the listbox does have 1 legitimate record in it. I need my code to let me know when it is truly empty.

Could you check for its Value? Just thinking out loud...

 

[TheSearcher]

It's value is null since nothing has been selected.
I tried using:

For i = 0 To Me.List1.ListCount - 1
    Me.lst_Goals.RemoveItem (i)
Next

but I got a "can only use this method with a Value List Rowsource Type" error. I don't believe I've ever run across this problem before. I'd be pulling my hair out if I had any!

 

[CJ_London]

the 1 is the the header row, visible or not.

use listcount=1 or listcount-1=0

create a listbox with a value list of say 3 rows. listcount will be 3. If columnheads=no, you can select any of the 3 rows, if it is yes, the first row becomes the header and is not selectable

all listbox values are text, regardless of the source datatype - which is why numbers a left aligned

 

[moke123]

lst_Goals.Rowsource = ""

Set the rowsource to an empty table and listcount returns 0 even with column heads showing.
You could also use something like

Me.lstBox.RowSource = "Select * from Table1 where ID = 0"

here's a demo to see effects on listcount

 

[Auntiejack56]

If you set the ColumnHeads property to Yes, the row of column headings is included in the number of rows returned by the ListCount property. For combo boxes and list boxes based on a table or query, adding column headings adds an additional row. For combo boxes and list boxes based on a value list, adding column headings leaves the number of rows unchanged (the first row of values becomes the column headings).

Looks to me (but what would I know) that if you set the rowsource to an empty string, Access cannot process that as though it was a SQL statement which returns no records (because it isn't).
Instead, it 'returns' a null and puts that in the listbox, which actually makes more sense, but paradoxically counts as 1 row.
If, as others have suggested, you deliberately use a SQL statement that returns no records, the listbox will have no records. If you add or remove headers, as Microsoft says above, the listcount increments or decrements appropriately.
So if you are Zls-ing the rowsource hither and yon, you need to check that, instead of (or as well as) checking the listcount, and/or the content of the first row.
Jack

 

[moke123]

If, as others have suggested, you deliberately use a SQL statement that returns no records, the listbox will have no records. If you add or remove headers, as Microsoft says above, the listcount increments or decrements appropriately.

That does not seem to be the case. If you use an empty table or an empty recordset as the rowsource, the existance of column headers has no effect on listcount. The column heads only register if there are records in the listbox or a zls.

Edit: I haven't checked value lists but will shortly.

Edit:Edit: It appears using a value list and ZLS returns 0, headers or not.
updated demo with value lists.

@TheSearcher - now that we know the various quirks of the listcount, perhaps you can clue us in as to why you need to test for a 0 listcount.

 

[TheSearcher]

moke123 - Your demo works just as you described. Unfortunately, when I set the rowsource to an empty table in my app the listcount is still 1.
To answer your question:
I have two listboxes (lst_Tasks, lst_Goals). lst_Goals is not yet visible. The user enters a task into a text box and then clicks an "Add Task" command button and the task gets added to lst_Tasks. The user then must enter a corresponding goal for that task. If he tries to add another task before entering a goal for the first task then I check to see if lst_Goals is visible. Since it is not he gets a prompt stating that he must enter a corresponding goal before he can enter another task. This all works fine because here I am testing to see if lst_Goals is visible or not.
If the user does enter a goal (in a text box) for the first task then lst_Goals becomes visible and the goal appears in it. Now he is allowed to enter another task. When he enters another task I wipe out lst_Goals (lst_Goals.rowsource = "") so that only the goals corresponding to the new task will show. Now, if the user tries to enter another task before a corresponding goal was entered I need to check if lst_Goals is empty. If it is then the user would receive a prompt stating that he must enter a corresponding goal. The problem is that lst_Goals is never empty. Listcount is always 1. When I set the rowsource to an empty table, as you suggested, the listcount, for me, is still 1. The worst, and most confusing aspect of this, is listcount will equal 1 when there is a record in it and when there isn't.

 

[moke123]

Could you post an example?

What rowsourcetype are you using? Table, quey, or value list?

 

[TheSearcher]

I'm using Table/Query. I'll try to post an example as soon as I can get to it.

 

[MajP]

Why do you keep setting it to an empty string?? Makes no sense. If you keep do the same thing and expecting different results then...
As already stated
NOT combo.rowsource = ""
combo.rowsource = "Select x from sometable where 1 = 2"

 

[MajP]

But if you insist on doing the same thing over again then check
If isnull(me.somecombo.itemdata(0))
Will return null when there is no record.

 

[TheSearcher]

MajP - Reread my prior posts.
moke123 - I've attached a sample. Please follow these instructions to observe the issue:

Open frm_NewNote
Choose a location
Choose a client
Save
Enter "Task 1" in the Task box
Click Add Task
Try to add another task - you should get a prompt stating that you must enter a goal that correspond to the first task. This is good.
Enter "Goal 1 - Task 1" in the Goal box
Click Add Goal
Enter "Task 2" in the Task box
Click Add Task
Now - try to enter another task before entering another goal. I don't want to allow this so I check to see if the listbox is empty - but the listcount = 1.
Since listcount equals 1 the user can now enter as many tasks as he likes without entering corresponding goals. Not good.

 

[MajP]

If lst_Tasks.Visible = True And (Me.lst_Goals.Visible = False Or IsNull(Me.lst_Goals.ItemData(0))) Then

 

[TheSearcher]

That worked. Thanks MajP! And thank you to all who responded. I really appreciate your help!

 

[MajP]

FYI, I think you could do this a lot easier if you did it with bound subforms and not listboxes. Everything could look the same. It seams real confusing because after you add a task if you go back to it no goals are linked. In fact if you reopen the form you do not see the existing tasks or goals.

I would do this with two subforms.
Tasks linked to the combo boxes. You change a client and location you see all tasks.
Goals are synched to the tasks. If you click on any task you see all related goals.

Now the check is simply if there are any tasks for that user, location where the goal count is 0.

 

[TheSearcher]

All of the goals are now linked to the tasks. When a user clicks on a task lst_Goals presents only the goals associated with that task. If they doubleclick on a goal then they will have the option to delete it. If they doubleclick on a task they will have the option to delete it and all of its corresponding goals. This is a work in progress. I appreciate your input.