Display message box if query returns no results

[elmonko]

Below is an example of multiple SQL queries that are run from a button on an excel sheet and are retrieved from an Access Database.

The WHERE clause in all the queries is the same and relates to a single cell reference.

I just have 2 small issues.....

If the query is to return no information than i would like a message box stating that's the case so the end user will need to enter the information manually. (once this is completed the user send back via email , the information is then collated into the DB and then when the next unit enters the value into the WHERE clause cell it will return data)

Secondly, if the query returns nothing then, the query that returns values into B18:F18, locks out these cells so the user can only enter from B19 downwards.

Any guidance would be appreciated

Thanks

sConn = "ODBC;CollatingSequence=ASCII;DBQ=" & Mailbox & DataB & ";DefaultDir=c:\temp;Deleted=1;Driver={Microsoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;"
sSql = "SELECT FORMAT(Total.FormReviewed, 'dd/mm/yy' ) AS DateRev FROM " & Mailbox & Totals & " WHERE(Total.UnitCode=" & Sheets("Pipe Cleaning").Range("C4") & " )"
Set oQt = ActiveSheet.QueryTables.Add(Connection:=sConn, Destination:=Range("C6"), Sql:=sSql)
oQt.AdjustColumnWidth = False
oQt.PreserveFormatting = True
oQt.RefreshStyle = xlOverwriteCells
oQt.FieldNames = False
oQt.Refresh
 
sConn = "ODBC;CollatingSequence=ASCII;DBQ=" & Mailbox & DataB & ";DefaultDir=c:\temp;Deleted=1;Driver={Microsoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;"
sSql = "SELECT FORMAT(Total.MeasuredClean, 'dd/mm/yy' ) AS DateRev FROM " & Mailbox & Totals & " WHERE(Total.UnitCode=" & Sheets("Pipe Cleaning").Range("C4") & " )"
Set oQt = ActiveSheet.QueryTables.Add(Connection:=sConn, Destination:=Range("F7"), Sql:=sSql)
oQt.AdjustColumnWidth = False
oQt.PreserveFormatting = True
oQt.RefreshStyle = xlOverwriteCells
oQt.FieldNames = False
oQt.Refresh
 
[B]'This query locks cells'[/B]
sConn = "ODBC;CollatingSequence=ASCII;DBQ=" & Mailbox & DataB & ";DefaultDir=c:\temp;Deleted=1;Driver={Microsoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;"
sSql = "SELECT Detail.BarName, Detail.ProdCode, Detail.ProdDesc, Detail.Price, Detail.Qty FROM " & Mailbox & Detail & " WHERE(Detail.UnitCode=" & Sheets("Pipe Cleaning").Range("C4") & " )"
Set oQt = ActiveSheet.QueryTables.Add(Connection:=sConn, Destination:=Range("B18"), Sql:=sSql)
oQt.AdjustColumnWidth = False
oQt.PreserveFormatting = True
oQt.RefreshStyle = xlOverwriteCells
oQt.FieldNames = False
oQt.Refresh
 
sConn = "ODBC;CollatingSequence=ASCII;DBQ=C:\Dropbox\capcon\queries\Clients.mdb;DefaultDir=c:\temp;Deleted=1;Driver={Microsoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;"
sSql = "SELECT Units.Name FROM C:\Dropbox\capcon\queries\Clients.Units Units WHERE(Units.Code=" & Sheets("Pipe Cleaning").Range("C4") & " )"
Set oQt = ActiveSheet.QueryTables.Add(Connection:=sConn, Destination:=Range("C5"), Sql:=sSql)
oQt.AdjustColumnWidth = False
oQt.PreserveFormatting = True
oQt.RefreshStyle = xlOverwriteCells
oQt.BackgroundQuery = False
oQt.FieldNames = False
oQt.Refresh

 

[John_w]

Try oQt.ResultRange.Rows.Count