473,387 Members | 1,391 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,387 software developers and data experts.

Determine if listbox has values

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.
Nov 13 '05 #1
13 7603
IF listbox.rowsource = "" Then
msgbox("No Records")
Else
~ code here
End IF

Nov 13 '05 #2
"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


Nov 13 '05 #3
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]
Nov 13 '05 #4
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]

Nov 13 '05 #5
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]
Nov 13 '05 #6
Have you ever seen this happen with ADO?

Nov 13 '05 #7
Does ADO's CurrentProject.Connection have this identity confusion?

Nov 13 '05 #8
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
Nov 13 '05 #9
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]
Nov 13 '05 #10
ly******@yahoo.ca wrote:
Have you ever seen this happen with ADO?


No.
--
[OO=00=OO]
Nov 13 '05 #11
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
Nov 13 '05 #12
"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
Nov 13 '05 #13
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

Nov 13 '05 #14

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

17
by: amber | last post by:
Hello. Can someone tell me what I may be doing wrong here? I'm using the code (lboxRP is a listbox): Dim newRPindex As Integer newRPindex = Me.lboxRP.FindString(RP)...
6
by: Valerian John | last post by:
I have a ListBox webcontrol on an aspx page. Items are added to the ListBox using client-side code. However, when the page is posted back the items are missing/not available. (It is like the...
2
by: Rob | last post by:
I have a listbox which is populated by a dataset: 'initiate the DataSet and all the rest here my sql statement is this: SELECT company_id, name, description FROM companies ds =...
6
by: Janaka | last post by:
Help! I have two ListBox controls on my web form. The first one gets populated on entry with values from the DB. I then use JavaScript to copy options from this ListBox to my second one. (I...
6
by: SStory | last post by:
Can anyone tell me what algorithm I should use to determine how many tabs to add to a string. I have two strings. I want to add them to a listbox concatenated but with a tab between them. ...
0
by: Dave | last post by:
Hi all, I have a listbox that is complex bound by an arraylist. The problem is that when I delete an object from the arraylist, the listbox does not reflect those changes. I tried refreshing...
2
by: tks423 | last post by:
How can I determine the values of a listbox, both unselected or selected after submitting the form: Code: <select name="sel1" size="10" multiple="multiple"> <? PHP code populates listbox ?>...
3
by: tks423 | last post by:
Hi, I was wondering if this is correct code for determine the contents of a listbox. I'm particularly interested to know if the sel1.options.valueOf() call will tell me the value of the item as...
0
by: =?Utf-8?B?UGF1bA==?= | last post by:
I have a ListBox server control named "lb_dates" with a SelectionMode of "Multiple". The user can select multiple dates from the listbox. I have ObjectDataSource named "ods_rfq" with a...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.