If query returns nothing

L

LtFass

Well lets start; I have a popup form that has its module set to true, this
form is derived from a query very simple so far (He He). The query criteria
is a function that derives its info from a written Public function in a
Module, (the plot thickens), The public function is derived from a bound
column in a list box (OhBoy).
Now the result I want is when I dbl clck the list box I want the form to
popup with the info from the from. This works great as long as the query
returns data.... the problem is when there is no data for the query to return
the form never finishes loading and therefore my close button is not
availble and the form is stuck. (Whew) I would like to have a msgbox popup to
say "No data available" and the ok button would close the msgbox and the
popup form. I ahve tried a few things with no luck any help or ideas would be
great the codes are as follows:

syntax of the Public Funtion:
PumpData = Forms!frmRecordMain!lstPastSer
This seems to work great and, in the criteria of the query I have
PumpData()
 
J

John Spencer (MVP)

You might try testing to see if the query returns data before opening the form.

Since you didn't post the SQL of the query, it is difficult to tell you exactly
how to do that. Also, since you didn't say I just guessed that LstPastSer was a
number field. If it is text or a date field you will have to add the
appropriate field delimiters into the string (quote marks or # marks).

Perhaps something as simple as using the DCount Function.

If DCount("*", "YourTable", "SomeField = " & Forms!frmRecordMain!LstPastSer) > 0 then
'open your form here
Else
MSGBox "NO Data exists for " & Forms!frmRecordMain!LstPastSer
End If
 
L

LtFass

Opps i am sorry here is the sql statement:
SELECT tblPump.strDPmpInst, tblPump.strTypPmp, tblPump.strPmpManu,
tblPump.strMdl, tblPump.strHp, tblPump.strPpeSze, tblPump.strCbleSze,
tblPump.strPmpStng, tblPump.strDteCde, tblPump.strMtrManu,
tblPump.strJobNumber
FROM tblDesc RIGHT JOIN tblPump ON tblDesc.JobNum = tblPump.strJobNumber
WHERE (((tblPump.strJobNumber)=PumpInfo()));

As long as I have a record in the tblPump the operation runs great its only
when the table doenot contain a entry that it fails. In a nut shell the data
base has four tables
1. customer conact info
2. job location info
3. description of service preformed.
4. information about the pumps installed ( however not evey customer has a
pump record.)
the table relationship runs customer > joblocation( where one customer may
have several different job locations {each with unique ID}) > description of
work {again each with a seperate individual job number} this table is joined
by locationId to the location table on location ID. and finaly the pump table
is joined on job number to the description table. (Whew I hope this makes
sense)
Thanks again for your help
 
L

LtFass

WHEW!!!! after much work with your suggestion I fiqured it out mostly with
your help....
here is what I did on the dblclk event of the lstPastSer I placed the
following code -

If DCount("*", "qryPumpData") = 0 Then
MsgBox "This Job didn't have pump data "
Else
DoCmd.OpenForm "frmPumpInfo", acNormal, , , acFormReadOnly, acDialog
End If

and left the function PumpInfo() in the criteria of the column strtJobNumber
in the querey

so again many thanks to you guys who answer our sometime silly questions
 
J

John Spencer (MVP)

Is this a NAMED Query? If so, I think you might be able to do

IF DCount("*","[TheNameOfTheQuery]") > 0 THEN
'Open the form
End If
 
Top