acting on null recordset

[crisox]

hiya,
Found just about everything I need on this group apart from the
following:
I want to produce some code that allows me to enter a string in a
combo which will then be checked against a field in a table. I then
want a message box to appear telling me whether there is a match or
not. Simple! I dont know whether I'm going round the houses but the
following very nearly works:

Dim db As Database
Dim rs As DAO.Recordset
Dim strsql As String
Dim Ref As Integer

strsql = "select * from [first] where [codey] = ( ' " &
Forms![Form4]!Combo10.Column(1) & " ') "

Set db = CurrentDb()
Set rs = db.OpenRecordset(strsql)

If Not (IsNull(rs.Fields("codey"))) Then

MsgBox "no"

Else

MsgBox "yes"

End If

I dont get any errors but I do get the same answer everytime
regardless of whether I have a match or not

Thanks in Advance
Chriso

[Bruce M. Thompson]

> If Not (IsNull(rs.Fields("codey"))) Then

**Replace this line with:

If Not rs.Recordcount Then

--
Bruce M. Thompson, Microsoft Access MVP
bt******@mvps.org (See the Access FAQ at http://www.mvps.org/access)

NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<

[Pieter Linden]

c.********@blueyonder.co.uk (crisox) wrote in message news:<31**************************@posting.google. com>...

hiya,
Found just about everything I need on this group apart from the
following:
I want to produce some code that allows me to enter a string in a
combo which will then be checked against a field in a table. I then
want a message box to appear telling me whether there is a match or
not. Simple! I dont know whether I'm going round the houses but the
following very nearly works:

Dim db As Database
Dim rs As DAO.Recordset
Dim strsql As String
Dim Ref As Integer

strsql = "select * from [first] where [codey] = ( ' " &
Forms![Form4]!Combo10.Column(1) & " ') "

Set db = CurrentDb()
Set rs = db.OpenRecordset(strsql)

If Not (IsNull(rs.Fields("codey"))) Then

MsgBox "no"

Else

MsgBox "yes"

End If

I dont get any errors but I do get the same answer everytime
regardless of whether I have a match or not

Thanks in Advance
Chriso

Instead of:

If Not (IsNull...)

Try

if not rs.BOF=rs.EOF

[Trevor Best]

On Wed, 11 Feb 2004 19:11:19 -0500 in comp.databases.ms-access, "Bruce
M. Thompson" <bthmpson@big_NOSPAM_foot.com> wrote:

If Not (IsNull(rs.Fields("codey"))) Then

**Replace this line with:

If Not rs.Recordcount Then

Be careful using bitwise operators on non boolean values, the result
may not be as expected.

--
A)bort, R)etry, I)nfluence with large hammer.

[crisox]

Thanks for your input but I am still getting the same results
everytime - is there something in the preceeding code that is causing
me grief?

Cheers
Chriso

[Lyle Fairfield]

c.********@blueyonder.co.uk (crisox) wrote in
news:31**************************@posting.google.c om:

hiya,
Found just about everything I need on this group apart from the
following:
I want to produce some code that allows me to enter a string in a
combo which will then be checked against a field in a table. I then
want a message box to appear telling me whether there is a match or
not. Simple! I dont know whether I'm going round the houses but the
following very nearly works:

Dim db As Database
Dim rs As DAO.Recordset
Dim strsql As String
Dim Ref As Integer

strsql = "select * from [first] where [codey] = ( ' " &
Forms![Form4]!Combo10.Column(1) & " ') "

Set db = CurrentDb()
Set rs = db.OpenRecordset(strsql)

If Not (IsNull(rs.Fields("codey"))) Then

MsgBox "no"

Else

MsgBox "yes"

End If

I dont get any errors but I do get the same answer everytime
regardless of whether I have a match or not

Thanks in Advance
Chriso

If rs.BOF Then
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Lyle Fairfield]

pi********@hotmail.com (Pieter Linden) wrote in
news:bf**************************@posting.google.c om:

c.********@blueyonder.co.uk (crisox) wrote in message
news:<31**************************@posting.google. com>...

hiya,
Found just about everything I need on this group apart from the
following:
I want to produce some code that allows me to enter a string in a
combo which will then be checked against a field in a table. I then
want a message box to appear telling me whether there is a match or
not. Simple! I dont know whether I'm going round the houses but the
following very nearly works:

Dim db As Database
Dim rs As DAO.Recordset
Dim strsql As String
Dim Ref As Integer

strsql = "select * from [first] where [codey] = ( ' " &
Forms![Form4]!Combo10.Column(1) & " ') "

Set db = CurrentDb()
Set rs = db.OpenRecordset(strsql)

If Not (IsNull(rs.Fields("codey"))) Then

MsgBox "no"

Else

MsgBox "yes"

End If

I dont get any errors but I do get the same answer everytime
regardless of whether I have a match or not

Thanks in Advance
Chriso

Instead of:

If Not (IsNull...)

Try

if not rs.BOF=rs.EOF

Can you think of a situation where this statement:
rs.BOF=rs.EOF
is False for a newly opened RecordSet?

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[crisox]

Thanks for the responses - I have tried them but am still getting the
same response every time - any suggestions?

Cheers
Chriso

[Bas Cost Budde]

>> If Not rs.Recordcount Then

Trevor Best wrote:

Be careful using bitwise operators on non boolean values, the result
may not be as expected.

Nor does it document itself optimally.

I'd do
if rs.recordcount=0 then
--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

[Bas Cost Budde]

crisox wrote:

hiya,
Found just about everything I need on this group apart from the
following:
I want to produce some code that allows me to enter a string in a
combo which will then be checked against a field in a table. I then
want a message box to appear telling me whether there is a match or
not. Simple! I dont know whether I'm going round the houses but the
following very nearly works:

I think you *are* going around the houses.

if dcount("*","[first]","codey='"& combo10.column(1) &"'")>0 then
msgbox "Yes"
else
msgbox "No"
end if

will do. If the code is in form4, that is, otherwise have the complete
reference.

Do you really mean to get the value in the *second* column of the
combobox, by the way?
--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

[Brendan Reynolds]

This is how I do it ...

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim boolFound As Boolean

strSQL = "SELECT Count(*) AS TheCount FROM SomeTable WHERE SomeField = '" &
Me!SomeControl & "'"
Set db = CurrentDb
Set rst = dbOpenRecordset(strSQL)
boolFound = (rst.Fields("TheCount")<>0)
rst.Close
set rst = Nothing
set db = Nothing
If boolFound Then
'we found a match
Else
'we didn't
End If

--
Brendan Reynolds

"crisox" <c.********@blueyonder.co.uk> wrote in message
news:31**************************@posting.google.c om...

Thanks for the responses - I have tried them but am still getting the
same response every time - any suggestions?

Cheers
Chriso

[David W. Fenton]

Bas Cost Budde <ba*@heuveltop.org> wrote in
news:c0**********@news2.solcon.nl:

If Not rs.Recordcount Then

Trevor Best wrote:

Be careful using bitwise operators on non boolean values, the
result may not be as expected.

Nor does it document itself optimally.

I'd do
if rs.recordcount=0 then

Paging Steve Jorgensen! :)

This is a case where I'd do the FALSE case:

If rs.RecordCount <> 0 Then

unless there was actually something I wanted to do when the
recordset was empty. In most of my programming, I only want to do
something when records are returned, so I use only the <>0 case.

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

[Bruce M. Thompson]

If Not rs.Recordcount Then

Be careful using bitwise operators on non boolean values, the result
may not be as expected.

Of course, you're quite right. :-)

--
Bruce M. Thompson, Microsoft Access MVP
bt******@mvps.org (See the Access FAQ at http://www.mvps.org/access)

NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<

[Trevor Best]

On Thu, 12 Feb 2004 16:59:35 GMT in comp.databases.ms-access, "David
W. Fenton" <dX********@bway.net.invalid> wrote:

Bas Cost Budde <ba*@heuveltop.org> wrote in
news:c0**********@news2.solcon.nl:

If Not rs.Recordcount Then

Trevor Best wrote:

Be careful using bitwise operators on non boolean values, the
result may not be as expected.

Nor does it document itself optimally.

I'd do
if rs.recordcount=0 then

Paging Steve Jorgensen! :)

This is a case where I'd do the FALSE case:

If rs.RecordCount <> 0 Then

unless there was actually something I wanted to do when the
recordset was empty. In most of my programming, I only want to do
something when records are returned, so I use only the <>0 case.

I do something similar but it would be:

If not (rs.BOF And rs.EOF) Then
...do something
end if

Unless I wanted to draw attention to the fact that no records were
returned then:

if rs.BOF and rs.EOF then
msgbox "There be no records here"
else
... do something
end if
--
A)bort, R)etry, I)nfluence with large hammer.

[Trevor Best]

On 12 Feb 2004 09:28:26 GMT in comp.databases.ms-access, Lyle
Fairfield <Mi************@Invalid.Com> wrote:

Can you think of a situation where this statement:
rs.BOF=rs.EOF
is False for a newly opened RecordSet?

When there are no records in between, you are at both ends of the
recordset at the same time. If there are records, you cannot be at
both ends.

--
A)bort, R)etry, I)nfluence with large hammer.

[Lyle Fairfield]

c.********@blueyonder.co.uk (crisox) wrote in
news:31**************************@posting.google.c om:

Thanks for the responses - I have tried them but am still getting the
same response every time - any suggestions?

You are NOT getting the same result for rs.BOF when there are records and
when there are not records.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[David W. Fenton]

Trevor Best <bouncer@localhost> wrote in
news:0s********************************@4ax.com:

On Thu, 12 Feb 2004 16:59:35 GMT in comp.databases.ms-access,
"David W. Fenton" <dX********@bway.net.invalid> wrote:

Bas Cost Budde <ba*@heuveltop.org> wrote in
news:c0**********@news2.solcon.nl:

> If Not rs.Recordcount Then

Trevor Best wrote:

Be careful using bitwise operators on non boolean values, the
result may not be as expected.

Nor does it document itself optimally.

I'd do
if rs.recordcount=0 then
Paging Steve Jorgensen! :)

This is a case where I'd do the FALSE case:

If rs.RecordCount <> 0 Then

unless there was actually something I wanted to do when the
recordset was empty. In most of my programming, I only want to do
something when records are returned, so I use only the <>0 case.

I do something similar but it would be:

If not (rs.BOF And rs.EOF) Then
...do something
end if

Why do two tests when one will do?
Unless I wanted to draw attention to the fact that no records were
returned then:

if rs.BOF and rs.EOF then
msgbox "There be no records here"
else
... do something
end if

Same answer as mine, just testing something different.

Is there a reason why you prefer BOF/EOF over .RecordCount?

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

[Bruce M. Thompson]

> If not (rs.BOF And rs.EOF) Then

...do something
end if

That's what I used to do. Guess I should stick with it, eh?

--
Bruce M. Thompson, Microsoft Access MVP
bt******@mvps.org (See the Access FAQ at http://www.mvps.org/access)

NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<

[Trevor Best]

On Thu, 12 Feb 2004 21:58:04 GMT in comp.databases.ms-access, "David
W. Fenton" <dX********@bway.net.invalid> wrote:

If not (rs.BOF And rs.EOF) Then
...do something
end if
Why do two tests when one will do?

Belt and braces :-)
Is there a reason why you prefer BOF/EOF over .RecordCount?

Just the first thing I stumbled across all those years ago. It aint
broke so....

--
A)bort, R)etry, I)nfluence with large hammer.

[David W. Fenton]

Trevor Best <bouncer@localhost> wrote in
news:16********************************@4ax.com:

On Thu, 12 Feb 2004 21:58:04 GMT in comp.databases.ms-access,
"David W. Fenton" <dX********@bway.net.invalid> wrote:

If not (rs.BOF And rs.EOF) Then
...do something
end if

Why do two tests when one will do?

Belt and braces :-)

No, a belt will hold up your pants (.RecordCount <> 0) but one brace
won't. Well, not reliably.

My point is that EOF and BOF must both be tested (right and left
suspendors) to do any good. That's two properties you must test,
whereas the alternative tests one.

Is there a reason why you prefer BOF/EOF over .RecordCount?

Just the first thing I stumbled across all those years ago. It
aint broke so....

In other words, force of habit.

I can't think of a reason why .RecordCount would be superior other
than that it's one test instead of two.

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

[CDB]

Perhaps because Microsoft has written that Recordcount is the most
reliable - where recordcount
= 0. Any other test is unreliable.

Clive
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.90...

I can't think of a reason why .RecordCount would be superior other
than that it's one test instead of two.

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

[David W. Fenton]

"CDB" <al***@delete.wave.co.nz> wrote in
news:c0**********@news.wave.co.nz:

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.90...

I can't think of a reason why .RecordCount would be superior
other than that it's one test instead of two.

Perhaps because Microsoft has written that Recordcount is the most
reliable - where recordcount= 0. Any other test is unreliable.

Glad to hear it. Do you know a Web citation for that?

And does that cite have an explanation of why .EOF/BOF would be less
reliable?

And is this DAO we're talking about, or is ADO mixed in?

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

[Trevor Best]

On Fri, 13 Feb 2004 16:29:51 GMT in comp.databases.ms-access, "David
W. Fenton" <dX********@bway.net.invalid> wrote:

I can't think of a reason why .RecordCount would be superior other
than that it's one test instead of two.

Guess I'm gonna need > 1KG copper heatsink and a bucket of liquid
nitrogen to keep my CPU from overheating and more "please wait"
messages with those extra extra tests.

--
A)bort, R)etry, I)nfluence with large hammer.

[David W. Fenton]

Trevor Best <bouncer@localhost> wrote in
news:gd********************************@4ax.com:

On Fri, 13 Feb 2004 16:29:51 GMT in comp.databases.ms-access,
"David W. Fenton" <dX********@bway.net.invalid> wrote:

I can't think of a reason why .RecordCount would be superior other
than that it's one test instead of two.

Guess I'm gonna need > 1KG copper heatsink and a bucket of liquid
nitrogen to keep my CPU from overheating and more "please wait"
messages with those extra extra tests.

That's not the issue, obviously (i.e., performance).

It's really the same kind of issue as Steve was bringing up about
the question of whether you always make the TRUE side of an
IF/THEN/ELSE first.

But maybe Clive has a citation from MS that explains why RecordCount
is better.

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

[Trevor Best]

On Fri, 13 Feb 2004 21:40:29 GMT in comp.databases.ms-access, "David
W. Fenton" <dX********@bway.net.invalid> wrote:

Trevor Best <bouncer@localhost> wrote in
news:gd********************************@4ax.com :

On Fri, 13 Feb 2004 16:29:51 GMT in comp.databases.ms-access,
"David W. Fenton" <dX********@bway.net.invalid> wrote:

I can't think of a reason why .RecordCount would be superior other
than that it's one test instead of two.
Guess I'm gonna need > 1KG copper heatsink and a bucket of liquid
nitrogen to keep my CPU from overheating and more "please wait"
messages with those extra extra tests.

That's not the issue, obviously (i.e., performance).

It's really the same kind of issue as Steve was bringing up about
the question of whether you always make the TRUE side of an
IF/THEN/ELSE first.

I don't see any issue, I will "not" or the false in an if/then/else if
I'm coding the false part first or there is only a false part to it.
Some people may not understand but the same people should not be
involved in boolean logic, algebra, etc.
But maybe Clive has a citation from MS that explains why RecordCount
is better.

If there is one from MS, I'd like to see it.

--
A)bort, R)etry, I)nfluence with large hammer.

[Trevor Best]

On Fri, 13 Feb 2004 22:07:43 +0000 in comp.databases.ms-access, Trevor
Best <bouncer@localhost> wrote:

I don't see any issue, I will "not" or the false in an if/then/else if

Typo:
I don't see any issue, I will use "not" or the false in an
if/then/else if

--
A)bort, R)etry, I)nfluence with large hammer.

[Pink Panther]

> It's really the same kind of issue as Steve was bringing up about

the question of whether you always make the TRUE side of an
IF/THEN/ELSE first.

Hi David

Won't the true 'side' always be first? It more what logical test do
you use. I'd say the most common code path should be the first block.
If it's 'not A' most of the time use

If Not A then
msgbox "Its Not A"
else
msgbox "Its A"
End If

If its A most of the time use

If A then
msgbox "Its A"
else
msgbox "Its Not A"
End If

Think I read this in Code Complete (Steve McConnell).

Also on a recordset opened by currentdb.openrecordset (and no other
methods performed on the object) in what instances would you need to
test for both BOF and EOF? I thought either would suffice under the
above circumstance.

Regards,

Peter

[Trevor Best]

On 14 Feb 2004 01:15:36 -0800 in comp.databases.ms-access,
Pi*************@mail.com (Pink Panther) wrote:

Also on a recordset opened by currentdb.openrecordset (and no other
methods performed on the object) in what instances would you need to
test for both BOF and EOF? I thought either would suffice under the
above circumstance.

Say programmer A writes:

Set rst = db.openrecordset(...)
if rst.eof then

Then programmer B comes along and inserts something:

Set rst = db.openrecordset(...)
' --- new code start
do until rst.eof
rst.movnext
loop
' --- new code end
if rst.eof then

The original code would assume no records whereas I always test both
and do a movefirst before starting a loop.

I cannot be sure myself, despite assurances that a recordset would
always open on the first record if there are any and not be at .bof or
..eof, by assuming such things you are leaving the reliability of your
program in the hands of others. No matter what may change in the
future, a recordset pointer cannot be at both ends if there's
something in the niddle.

--
A)bort, R)etry, I)nfluence with large hammer.

[David W. Fenton]

Pi*************@mail.com (Pink Panther) wrote in
news:ec**************************@posting.google.c om:

Won't the true 'side' always be first? It more what logical test
do you use. I'd say the most common code path should be the first
block.

This was discussed at some length a few weeks ago in this thread
started by Steve Jorgensen with this post:

http://groups.google.com/groups?selm...cjm1vsc20hpf46
sa%404ax.com
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Bas Cost Budde]

David W. Fenton wrote:

Pi*************@mail.com (Pink Panther) wrote in

Won't the true 'side' always be first? It more what logical test
do you use. I'd say the most common code path should be the first
block.

This was discussed at some length a few weeks ago in this thread
started by Steve Jorgensen with this post:

http://groups.google.com/groups?selm...cjm1vsc20hpf46
sa%404ax.com

I can't find the thread to reply to it, but I do have an idea.

We know Null means "don't know", do we? So if you want a function that
tells you something that is Not Null, it is a known value. Would the
function be named IsKnown?

--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

[David W. Fenton]

Bas Cost Budde <ba*@heuveltop.org> wrote in
news:c0**********@news2.solcon.nl:

David W. Fenton wrote:

Pi*************@mail.com (Pink Panther) wrote in

Won't the true 'side' always be first? It more what logical test
do you use. I'd say the most common code path should be the first
block.

This was discussed at some length a few weeks ago in this thread
started by Steve Jorgensen with this post:

http://groups.google.com/groups?selm...q70cjm1vsc20hp
f46 sa%404ax.com

I can't find the thread to reply to it, but I do have an idea.

We know Null means "don't know", do we? So if you want a function
that tells you something that is Not Null, it is a known value.
Would the function be named IsKnown?

IsKnown()=False

That is, I don't know. :)

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

[Pink Panther]

Well I can't argue with defensive programming...

Find and Replace coming up...

Trevor Best <bouncer@localhost> wrote in message news:<ei********************************@4ax.com>. ..
<SNIP>

I cannot be sure myself, despite assurances that a recordset would
always open on the first record if there are any and not be at .bof or
.eof, by assuming such things you are leaving the reliability of your
program in the hands of others. No matter what may change in the
future, a recordset pointer cannot be at both ends if there's
something in the niddle.