cancel openquery if query contains no data

M

mazza

Registered User.
Local time
Today, 03:22
Joined
Feb 9, 2005
Messages
101
I use a database that calculates costs based a range of parts on list prices. Some parts that are being used are not officially priced and have a guide price. these are marked by a true false in the table. The calculation uses various queries.
When executing the calculation I want to warn the users when 'unofficial prices' have been used. Ideally using a query that picks out those parts that have official prices, and show this as a pop up form but when all the parts used have official prices the query will have no data and I want to cancel the event if the query contains no data.

Any suggestions on how to do that
 
maybe a report ?

mazza said:
...show this as a pop up form but when all the parts used have official prices the query will have no data and I want to cancel the event if the query contains no data.

Any suggestions on how to do that
Click to expand...

Could you do it using a report (displayed to screen - print preview if you like)instead? Reports have a lovely event called "NoData" with a "Cancel" parameter.

Code: 
Private Sub Report_NoData(Cancel As Integer)
   'put your code here
End Sub
Just a suggestion.

Regards

John
 
run the query as SQL in code and examine the rsults before popping up a message.

HTH

Peter
 
The SQL code would be as follows but what code should I add to decide if there is no result?

SELECT qrystartcalculateyearlyschedule1.DispRef, qrystartcalculateyearlyschedule1.SellPrice, qrystartcalculateyearlyschedule1.[official pricelist] AS Cat
FROM qrystartcalculateyearlyschedule1
GROUP BY qrystartcalculateyearlyschedule1.DispRef, qrystartcalculateyearlyschedule1.SellPrice, qrystartcalculateyearlyschedule1.[official pricelist]
HAVING (((qrystartcalculateyearlyschedule1.[official pricelist])=False));
 
You may need a reference set to DAO to run this code.

Code: 
Dim rst As DAO.Recordset
Dim strSql As String
strSql = "SELECT .........."
Set rst = CurrentDb.OpenRecordset(strSql)
If Not (rst.EOF And rst.BOF) Then
    MsgBox "found some results"
Else
    MsgBox "found no results"
End If
End Sub

HTH

Peter
 
I tried a test with a simple module/query but keep getting the following error?
"the select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect"

Where do I go wrong here?


Private Sub Command0_Click()
Dim rst As DAO.Recordset
Dim strSql As String
strSql = "SELECT TblOil.OilType, TblOil.OilDescr, " & _
"FROM TblOil;"
Set rst = CurrentDb.OpenRecordset(strSql)
If Not (rst.EOF And rst.BOF) Then

MsgBox "found some results"
Else
MsgBox "found no results"
End If
End Sub
 
punctuation is incorrect
Click to expand...
You have an extra comma.

strSql = "SELECT TblOil.OilType, TblOil.OilDescr, " & _
strSql = "SELECT TblOil.OilType, TblOil.OilDescr " & _

Peter
 
OK failed again when moving to my actual problem query

replaced the firlds now to actualy look up the query that I need to chek if it returns records or not.

now get the message:

'too few parameters: expected 2' - debug highlights the Set rst = CurrentDb.OpenRecordset(strSql) line
where does that suddenly come from?

Private Sub Command0_Click()
Dim rst As DAO.Recordset
Dim strSql As String
strSql = "SELECT QryIncorrectPrices.DispRef, QryIncorrectPrices.SellPrice, QryIncorrectPrices.cat " & _
"FROM QryIncorrectPrices;"
Set rst = CurrentDb.OpenRecordset(strSql)
If Not (rst.EOF And rst.BOF) Then
MsgBox "found some results"
Else
MsgBox "found no results"
End If
End Sub
 
The underlying query must have parameters set, Try this
Code: 
Dim qdf As dao.QueryDef
Dim prm As dao.Parameter
Dim rst As dao.Recordset
Dim strSql As String

strSql = "SELECT ........."
Set qdf = CurrentDb.CreateQueryDef("", strSql)
For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
Next prm

Set rst = qdf.OpenRecordset(dbOpenForwardOnly)
If Not (rst.EOF And rst.BOF) Then
    MsgBox "found some results"
Else
    MsgBox "found no results"
End If
 
thanks I will try that, have started to play around with dcount function as that was an easy option.
But still need to get the syntax right for future reference
 
You must log in or register to reply here.

Similar threads

C
Sub or function not defined
Replies
12
Views
645
Pat Hartman
P
A
Query Not Visible When Connecting to Excel
Replies
14
Views
449
Gasman
Gasman
C
Subform Footer Textbox wont update
Replies
11
Views
401
Pat Hartman
P
asteropi
Solved Field Error in Report
Replies
18
Views
758
Gasman
Gasman
W
Notify the user, without sound -> success <-
Replies
3
Views
458
woef22
W

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom