Determine if listbox has values

[Gittyup]

Help please,

We have a form, based on a query, that contains a listbox. The contents of
the listbox are based on the results of the query.

When the form is opened, the user selects an item from the listbox, and,
using the AfterUpdate and Visible properties, a subform appears with
information relative to the selected item.

It works perfectly.

But we wish to create a simple MsgBox the says something like "No Records"
if the listbox is not populated, so that when the user clicks the command to
open the form, he/she gets the MsgBox and not the form.

Is this possible?

Thank you for any help or suggested links.

[Beacher]

IF listbox.rowsource = "" Then
msgbox("No Records")
Else
~ code here
End IF

[LeighP]

"Beacher" <be*****@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

IF listbox.rowsource = "" Then
msgbox("No Records")
Else
~ code here
End IF

Well - if there's nothing changing the rowsource of the listbox then perhaps
you might be better off with something like

Dim strSQL As String
Dim db As Database
Dim rst As Recordset

strSQL = List0.RowSource

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

With rst
If .EOF And .BOF Then
'There is nothing to show - get out
MsgBox "No records to select", vbInformation
'DoCmd.Close acForm, Me.Name
Else
'Do whatever happns as your form opens
End If
.Close
End With

Set rst = Nothing
Set db = Nothing

Or if you can predict the SQL or query that the listbox is based upon then
don't even bother with the listbox reference and just use that instead.

hth

[Trevor Best]

LeighP wrote:

If .EOF And .BOF Then

Beware: I've recently had instances where both .BOF and .EOF were false
yet there were no records, I use .RecordCount to check this now.

--
[OO=00=OO]

[LeighP]

Really? And so we come full circle - amazing.
I remember the discussions years back when the tide was in the other
direction...

Under what circumstances did you find this?
Bog standard or exceptional?
"Trevor Best" <no****@besty.org.uk> wrote in message
news:42***********************@news.zen.co.uk...

LeighP wrote:

If .EOF And .BOF Then

Beware: I've recently had instances where both .BOF and .EOF were false
yet there were no records, I use .RecordCount to check this now.

--
[OO=00=OO]

[Trevor Best]

LeighP wrote:

Really? And so we come full circle - amazing.
I remember the discussions years back when the tide was in the other
direction...
Had the same argument vis CurrentDb vs DbEngine(0)(0). DbEngine(0)(0)
was the preferred method for speed but nowdays cannot guarantee pointing
to the current database. In fact you can almost guarantee it won't if
you use a wizard, close your db and open another.
Under what circumstances did you find this?
Bog standard or exceptional?

Must be exceptional as it's happened to me only 2 or 3 times.

--
[OO=00=OO]

[lylefair]

Have you ever seen this happen with ADO?

[lylefair]

Does ADO's CurrentProject.Connection have this identity confusion?

[Tim Marshall]

Trevor Best wrote:

LeighP wrote:

If .EOF And .BOF Then

Beware: I've recently had instances where both .BOF and .EOF were false
yet there were no records, I use .RecordCount to check this now.

Hmmmm, actually, for a list or combo box, I'd use neither method.

My preference is to use the listcount property for the list box:

If lstMyListBox.listcount = 0 then '1 if headers are used

msgbox "hi there"

end if

This works whether your rowsource is a "Table/Query" or "Value List".

Do not use the method Beacher suggested.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Trevor Best]

ly******@yahoo.ca wrote:

Does ADO's CurrentProject.Connection have this identity confusion?

<shrug>

I wouldn't have thought so since it looks like a function akin to
CurrentDb whereas DbEngine(0)(0) is referencing the first database in a
collection of n databases.

--
[OO=00=OO]

[Trevor Best]

ly******@yahoo.ca wrote:

Have you ever seen this happen with ADO?

No.
--
[OO=00=OO]

[David W. Fenton]

Trevor Best <no****@besty.org.uk> wrote in
news:42***********************@news.zen.co.uk:

ly******@yahoo.ca wrote:

Does ADO's CurrentProject.Connection have this identity
confusion?

<shrug>

I wouldn't have thought so since it looks like a function akin to
CurrentDb whereas DbEngine(0)(0) is referencing the first database
in a collection of n databases.

What does CurrentProject.Connection do? Is it like DBEngine(0)(0) or
is it like CurrentDB()?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"LeighP" <Le***@NotThis.DatabaseDevelopment.co.uk> wrote in
news:d8**********@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com:

"Beacher" <be*****@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

IF listbox.rowsource = "" Then
msgbox("No Records")
Else
~ code here
End IF

Well - if there's nothing changing the rowsource of the listbox
then perhaps you might be better off with something like

Dim strSQL As String
Dim db As Database
Dim rst As Recordset

strSQL = List0.RowSource

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

With rst
If .EOF And .BOF Then
'There is nothing to show - get out
MsgBox "No records to select", vbInformation
'DoCmd.Close acForm, Me.Name
Else
'Do whatever happns as your form opens
End If
.Close
End With

Set rst = Nothing
Set db = Nothing

Or if you can predict the SQL or query that the listbox is based
upon then don't even bother with the listbox reference and just
use that instead.

This is a ridiculously silly solution. You don't need to open a
recordset using the rowsource of the listbox to find out the number
of records -- all you need do is check the listbox's .ListCount
property.

Indeed, opening an additional recordset has two major disadvantages:

1. it's another hit on the back end to collect information about
data that's already been loaded into the listbox.

2. it may not be accurate if records have been added to or deleted
from the dataset defined by the criteria used in populating the
listbox.

3. it won't work at all if the rowsource is not a SQL string.

REALLY REALLY BAD ANSWER.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Gittyup]

Hey,

Thanks to Tim for his short, yet logical answer.

We've put this in a condition in the macro (please don't laugh, we use what
we know). And it does work. If you click the command button on the Main form
(where the cmdButton is located), the message box appears if there are no
records. The form never displays. All is well.

But we have to open the called form in Hidden mode so that the program can
actually "see" the control - the ListBox that it's checking.
If the condition - If lstMyListBox.listcount = 0 - evaluates to true, the
Msgbox is displayed, the macro ends. The form is never displayed.

But the next time we run this we still get the Msgbox (even if the query now
returns records). We've determined that the form does not unload itself, and
the close command (or DoCmdClose) doesn't seem to work. In other words, the
"hidden form" (and its variables?) are still lingering. We've tested this.
We've reset the query so it returns results. We open the form. Nothing in
the listbox. We close the form and open it again. Now it's back to normal
(the returned query records appear in the Listbox).

It appears we need to find some way to unload a hidden from, and/or Set it
to Nothing, so the variables are cleared.

The macro tools are limiting, but when we've tried coding we can't seem to
get the right combination or sequence.

Thanks to all for the great discussion here, and your patience.
"Tim Marshall" <TI****@PurplePandaChasers.Moertherium> wrote in message
news:d8**********@coranto.ucs.mun.ca...

Trevor Best wrote:

LeighP wrote:

If .EOF And .BOF Then

Beware: I've recently had instances where both .BOF and .EOF were false
yet there were no records, I use .RecordCount to check this now.

Hmmmm, actually, for a list or combo box, I'd use neither method.

My preference is to use the listcount property for the list box:

If lstMyListBox.listcount = 0 then '1 if headers are used

msgbox "hi there"

end if

This works whether your rowsource is a "Table/Query" or "Value List".

Do not use the method Beacher suggested.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me