If query returns no records display message box

[Datatonic]

I have a form when a button is clicked it performs some calculations,
then runs a query.

DoCmd.OpenQuery "qrySettlementBill"

I would like a message box to appear to say that no records were
returned and then return the user back to the form when the message box
is closed.

If the query does return records then I would like the following to
continue to happen.

Dim oApp As Object
Set oApp = CreateObject("Word.Application")
oApp.Visible = True
oApp.documents.Open "c:\Settlement.doc"

What do I need to do?

 

[Dirk Goldgar]

I have a form when a button is clicked it performs some calculations,
then runs a query.

DoCmd.OpenQuery "qrySettlementBill"

I would like a message box to appear to say that no records were
returned and then return the user back to the form when the message
box
is closed.

If the query does return records then I would like the following to
continue to happen.

Dim oApp As Object
Set oApp = CreateObject("Word.Application")
oApp.Visible = True
oApp.documents.Open "c:\Settlement.doc"

What do I need to do?

Do you actually have to open the query in datasheet view, as you are
doing with DoCmd.OpenQuery? To check for any records you have to
extract data from the query, which you can't readily do unless you open
a recordset on the query or use one of the domain aggregate functions
such as DCount or DLookup, specifying the query as the domain. For
example,

If DCount("*", "qrySettlementBill") = 0 Then
MsgBox "No records in query results!"
Else
Set oApp = CreateObject("Word.Application")
' ... and so on ...
End If

But doing the above essentially processes the query all over again, if
you have also opened it using OpenQuery. But sometimes people think
they have to open queries using OpenQuery in order to process them
programmatically, which is not the case.

 

[PC Datasheet]

If DCount("*","qrySettlementBill") = 0 Then
Msgbox <Your Message>
Exit Sub
End If
Dim oApp As Object
Set oApp = CreateObject("Word.Application")
oApp.Visible = True
oApp.documents.Open "c:\Settlement.doc"

 

[TC]

I prefer DLookup() here. You only need to search for one record, not to
count all records

TC

If DCount("*","qrySettlementBill") = 0 Then
Msgbox <Your Message>
Exit Sub
End If
Dim oApp As Object
Set oApp = CreateObject("Word.Application")
oApp.Visible = True
oApp.documents.Open "c:\Settlement.doc"

 

[TC]

I agree, though I used DCount as an example in my own post so as to
avoid having to come up with an imaginary field name for a DLookup
expression. Maybe Steve's motives were the same.

Don't need one

if dlookup (1, "mytable", "x=99") = 1 then ...

If there is no record with x=99, the call returns Null, and the test fails.

Cheers,
TC

 

[TC]

That's a nice trick, but you *did* invent an imaginary field name: x.
You might as easily have written

If Not IsNull(DLookup("x", "mytable", "x=99")) Then

But in the case we were presented with, all we know is that there's a
query "qrySettlementBill", and we want to know if it contains any
records. Short of opening a recordset on it, I can't see how to do that
without either specifying a field name or using DCount("*",
"qrySettlementBill"). No ... wait a minute ... Cool! How about this:

If DLookup(1, "qrySettlementBill", "True") Then

I do believe that will return 1 if the query returns any records, and
Null if it doesn't.

Thanks for an eye-opener, TC.

Dirk, the only time I get an "eye opener" around here is when someone
punches me in it!!

I lost track of things a bit. If he only wants to know if the query has

any< records:

if dlookup (1, "qrywhatever") = 1

The third param is optional, no?

TC
(off for the day)

 

[Dirk Goldgar]

I prefer DLookup() here. You only need to search for one record, not
to count all records

I agree, though I used DCount as an example in my own post so as to
avoid having to come up with an imaginary field name for a DLookup
expression. Maybe Steve's motives were the same.

 

[Dirk Goldgar]

Don't need one

if dlookup (1, "mytable", "x=99") = 1 then ...

If there is no record with x=99, the call returns Null, and the test
fails.

That's a nice trick, but you *did* invent an imaginary field name: x.
You might as easily have written

If Not IsNull(DLookup("x", "mytable", "x=99")) Then

But in the case we were presented with, all we know is that there's a
query "qrySettlementBill", and we want to know if it contains any
records. Short of opening a recordset on it, I can't see how to do that
without either specifying a field name or using DCount("*",
"qrySettlementBill"). No ... wait a minute ... Cool! How about this:

If DLookup(1, "qrySettlementBill", "True") Then

I do believe that will return 1 if the query returns any records, and
Null if it doesn't.

Thanks for an eye-opener, TC.

 

[Dirk Goldgar]

Dirk, the only time I get an "eye opener" around here is when someone
punches me in it!!

I lost track of things a bit. If he only wants to know if the query has

if dlookup (1, "qrywhatever") = 1

The third param is optional, no?

Duh. Yes.

 

[TC]

(snip)

Do you actually have to open the query in datasheet view, as you are
doing with DoCmd.OpenQuery? To check for any records you have to
extract data from the query, which you can't readily do unless you open
a recordset on the query or use one of the domain aggregate functions
such as DCount or DLookup, specifying the query as the domain.

Dirk, I have the same need as the OP. In my case, there are good reasons why
the data must be opened as a datasheet, & can't be opened as a form or
report. I need to pop-up a message when the datasheet is empty. Empty
datasheets look, to the user, as if something has "gone wrong".

I looked into this last night, & it is actually quite do-able.

OpenQuery seems to select the datasheet (empty or otherwise) as the current
object, although this is not doc'd anywhere (AFAICS). Thus,
screen.actuvedatasheet will refer to the opened datasheet. The methods &
props of that object, look very much like those of a form. In fact, in the
object browser, the datasheet object is defined "As Form" (if I remember
correctly - I don't have Access here to check). In particular,
screen.activedatasheet has a recordsetclone property, which works exactly as
expected. So you can use that propr to determine whether the opened
datasheet, does or does not have any records

I then had problems closing the datasheet from code. There is no
acDatasheet, as one would expect. acDefault & acForm did not work either.
The only one that worked for me, was DoCmd.Close with no arguments.

Cheers,
TC

 

[TC]

Holy cow! I sure didn't know that. So, the query datasheet is really a
form in disguise ... That makes a lot of nice things possible -- if you
want to use datasheets, of course. For example, you can manipulate the
datasheet's RecordsetClone (or Recordset, in A2K+) property to navigate
between records, or to find a particular record. Yes, I've tested this.

And there's all those other juicy things! (recordselectors & so on)

That will certainly save running the query twice; once in advance just
to find out if it has any records. It would be nice to be able to do it
without popping the datasheet view up on the screen at all, but the only
way I can think of to do that is Application.Echo False, and I don't
like to use that.

My experiements suggested that if you close the datasheet >before< you issue
the msgbox, the datasheet just does not appear, at all.

You could use
DoCmd.Close acQuery, "TheQueryName"

Duh! (Yes. Clearly safer, as it does not assume the current object.)

 

[TC]

No, I get a brief "screen pop" of the datasheet -- just a blink. It's
annoying, though, and I think I'd rather see the empty datasheet behind
the message box than get that flash. If you get different results, it's
probably a timing issue and may depend on the Access version.

Ok, that's interesting. I did see a blink, but it was so quick that I do not
think a user would notice. Mine is A97 on a 1.6MHz PC, so it's nothing
special in the grunt department.

Cheers,
TC
(off for the day)

 

[Dirk Goldgar]

Dirk, I have the same need as the OP. In my case, there are good
reasons why the data must be opened as a datasheet, & can't be opened
as a form or report. I need to pop-up a message when the datasheet is
empty. Empty datasheets look, to the user, as if something has "gone
wrong".

I looked into this last night, & it is actually quite do-able.

OpenQuery seems to select the datasheet (empty or otherwise) as the
current object, although this is not doc'd anywhere (AFAICS). Thus,
screen.actuvedatasheet will refer to the opened datasheet. The
methods & props of that object, look very much like those of a form.
In fact, in the object browser, the datasheet object is defined "As
Form" (if I remember correctly - I don't have Access here to check).
In particular, screen.activedatasheet has a recordsetclone property,
which works exactly as expected. So you can use that propr to
determine whether the opened datasheet, does or does not have any
records

Holy cow! I sure didn't know that. So, the query datasheet is really a
form in disguise ... That makes a lot of nice things possible -- if you
want to use datasheets, of course. For example, you can manipulate the
datasheet's RecordsetClone (or Recordset, in A2K+) property to navigate
between records, or to find a particular record. Yes, I've tested this.

That will certainly save running the query twice; once in advance just
to find out if it has any records. It would be nice to be able to do it
without popping the datasheet view up on the screen at all, but the only
way I can think of to do that is Application.Echo False, and I don't
like to use that.

I then had problems closing the datasheet from code. There is no
acDatasheet, as one would expect. acDefault & acForm did not work
either. The only one that worked for me, was DoCmd.Close with no
arguments.

You could use

DoCmd.Close acQuery, "TheQueryName"

I think you can rely on the query's being the active datasheet
immediately after DoCmd.OpenQuery, but if you didn't want to I suppose
you could use DoCmd.SelectObject to make it so.

 

[Dirk Goldgar]

And there's all those other juicy things! (recordselectors & so on)



My experiements suggested that if you close the datasheet >before<
you issue the msgbox, the datasheet just does not appear, at all.

Hmm, I'll have to go back and check that again. I may have
inadvertently sidestepped that particular test.

 

[Dirk Goldgar]

Hmm, I'll have to go back and check that again. I may have
inadvertently sidestepped that particular test.

No, I get a brief "screen pop" of the datasheet -- just a blink. It's
annoying, though, and I think I'd rather see the empty datasheet behind
the message box than get that flash. If you get different results, it's
probably a timing issue and may depend on the Access version.

 

[Dirk Goldgar]

Ok, that's interesting. I did see a blink, but it was so quick that I
do not think a user would notice. Mine is A97 on a 1.6MHz PC, so it's
nothing special in the grunt department.

(probably 1.6GHz, not MHz )

My test was A2K2 on a 1.2GHz Athlon processor; I imagine both the
version and the processor are responsible for making it slower.