Link to home
Avatar of wlwebb
wlwebbFlag for United States of America

asked on

Access Listbox - Empty/Null

Hello All

I have a Tabbed Form with a Subform on one of the Tab's .  On that Subform I have a Listbox to display info about a Contact.  It works fine to display existing info.  However, if the List is Null is there a way for essentially a message to appear within that Listbox of say "-{None}-" or  "-{No Info}-"
Avatar of peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

What is the rowsource for the listbox?
Avatar of mbizup
Flag of Kazakhstan image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of wlwebb


Me.listShipperLocations.RowSource = "SELECT ShipperLocID, ShipperLoc, ShipperID FROM dta_ShippersLocations" & _
    " WHERE (((ShipperID)=[Forms]![tfrm_Shippers]![sfrm_dta_ShippersLocations].[Form]![ShipperID]))" & _
    " ORDER BY ShipperLoc"

That's sort of what I thought ......

I'll see if Peter has a different approach
I think Miriam's solution is fine.
I was going to look at counting records but Miriam's approach is simpler.
Avatar of wlwebb


I tried a couple If stmts

    If Me.listShipperLocations.ListCount = 0 Then
        Me.listShipperLocations.RowSource = "{No Locations}-"
    End If

    If Me.listShipperLocations.ListCount = 0 Then
        Me.listShipperLocations.Value = "{No Locations}-"
    End If

But none worked.........
You can't set the listbox like that.  The idea is to use a textbox above the listbox to display the listcount or "no records".
Interesting request! I'll give it a try.
Not sure if you have a .Net background... but gridviews, etc in .Net DO have this option, allowing you to specify text to display if there are no records present.  It appears as non-selectable text centered in the gridview.

Access listboxes do not have this option.  You can possibly set the row source to display "No Records", but unlike .Net, in Access it would be an actual list item -- which would be selectable unless you include code to lock/disable the listbox for such cases.  Also, if your listbox has multiple colomns, the text would have to appear in one of the columns rather than centered across the listbox.

The easiest way IMO to inform the user is to use a seperate textbox (or text boxes) to display the number of items in the list as described above.

Our standard for that here is to use two textboxes with associated labels to show how many 'filtered' records the listbox is displaying and the total possible number of unfiltered records - something like this:

          Displaying X  of  Y total records

Where X is the listcount of filtered records, and Y is the total number of unfiltered records.  (We place this information/textboxes immediately under the right hand corner of the listbox)
Try this:
Replace with your table, and field names.

Table 0 (f1, f2, f3) - this is your table to display contents of listbox  if any.
Table a(....) used to display {x} - if table 0 is empty.

Select Distinct "", " {x}" ,"" 
From a Where Not Exists(SELECT [0].[f1], [0].[f2],[0].[f2] FROM 0)
SELECT [0].[f1], [0].[f2],[0].[f2] 
FROM 0 ;

Open in new window

Avatar of wlwebb


Thanks Mbiz.....
Glad to help out :-)