cancel openquery if query contains no data

[mazza]

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

 

[john471]

maybe a report ?

...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

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.

Private Sub Report_NoData(Cancel As Integer)
   'put your code here
End Sub

Just a suggestion.

Regards

John

 

[Bat17]

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

HTH

Peter

 

[mazza]

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));

 

[Bat17]

You may need a reference set to DAO to run this 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

 

[mazza]

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

 

[Bat17]

punctuation is incorrect

You have an extra comma.

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

Peter

 

[mazza]

thanks got it

 

[mazza]

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

 

[Bat17]

The underlying query must have parameters set, Try this

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

 

[mazza]

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