QueryDef Problem

[devGarfield]

Hi All,

I have successfully created a query using CreateQueryDef which is used by my report. The problem is that sometimes there are no records to display. I would like a way to check if there is anything to display within my VBA code, so that I can display a message to the screen.

a snipette of the code is below.

    Set qdf = db.CreateQueryDef("qryTempLearners", strSql)
    Set rs = db.OpenRecordset(strSql)
    If (rs.NoMatch) Or rs.RecordCount = 0 Then
        MsgBox "No Data"
    End If

I get an error 'To few paramenters. Expected 2' The strSql is dynamically built based and using controls selected by the user.

Any ideas would be appreciated.

 

[nabiS2K]

Do this instead...

    Set qdf = db.CreateQueryDef("qryTempLearners", strSql)
    Set rs = qdf.OpenRecordset

    If (rs.NoMatch) Or rs.RecordCount = 0 Then
        MsgBox "No Data"
    End If

I left your rs.NoMatch, but I am not sure it is necessary.

 

[devGarfield]

hello nabiS2K

Thanks for that it worked.

 

[devGarfield]

Hello Again,

I had this function working fine, but for some reason it keeps on giving me the error 'Too few Parameters". Does anybody know why this is? is it a bug within access?

Public Function NoDataCheck(inQry As String, inSql As String) As Boolean
'Check if the query actually produces anything.  Returns True or False
'Stops the reports from having #Error's displayed on it.

'inQry = name of the temp query definition
'inSql = SQL string

    NoDataCheck = True
    Set qdf = db.CreateQueryDef(inQry, inSql)
    Set rs = qdf.OpenRecordset
    
    If rs.RecordCount = 0 Then  'if the record count is 0 nothing found
        NoDataCheck = False
    End If

End Function

 

[nabiS2K]

You will receive the 'Too Few Parameters' error if you have a query that either has parameters that you are not assigning values in code, or if you are referencing an object on a form directly as a query's criteria when using a querydef in VBA.

Can you post an example of the SQL Statement?