Count records in a dao.querydef

[phill86]

Hi

I want to be able to count records in a dao.querydef recordset but I keep getting the error message method or data member not found


I have also tried to find out if the recordset is at the EOF either of these metods would work for what I need

Dim RstQueryCheckCal As DAO.QueryDef
    
    
    
    Set dbsEquipBookLinked = DBEngine.OpenDatabase("\\bert\sessiondata$\equipdata\EquipBooking2007v15Data")
    
    Set frm1 = [Forms]![FrmCalBooking]

    strSql1 = "PARAMETERS DateStart DateTime, DateEnd DateTime; " & vbCrLf & _
    "SELECT TimeValue([TblCalendar].[startDate]) AS Expr2, TimeValue([TblCalendar].[endDate]) AS Expr3, TblCalendar.CalendarID, TblCalendar.StartDate, TblCalendar.EndDate, TblCalendar.Location, DateValue([TblCalendar].[startDate]) AS Expr1 " & vbCrLf & _
    "FROM TblCalendar " & vbCrLf & _
    "WHERE (((TimeValue([TblCalendar].[startDate]))>=[dateStart]) AND ((TimeValue([TblCalendar].[endDate]))<=[dateEnd]) AND ((TblCalendar.Location)=[Forms]![FrmCalBooking]![TxtLocation]) AND ((DateValue([TblCalendar].[startDate]))=[Forms]![FrmCalBooking]![TxtStart]));"
    

     
     Set RstQueryCheckCal = dbsEquipBookLinked.CreateQueryDef("", strSql1)
 
    
    RstQueryCheckCal.Parameters("dateStart").Value = frm1![TxtStartTime]
    RstQueryCheckCal.Parameters("dateEnd").Value = frm1![TxtEndTime]
    RstQueryCheckCal.Parameters(2) = frm1![TxtLocation]
    RstQueryCheckCal.Parameters(3) = frm1![TxtStart]
    
    If RstQueryCheckCal.RecordCount > 1 Then
     
        'end sub here
     
    End If

Many thanks Phill

[NeoPa]

It doesn't appear that you have opened your QueryDef anywhere.

[phill86]

Hi Neopa

Sorry what do you mean?

I know its probably really simple but could really use some help on this

Many thanks Phill

[NeoPa]

Your code creates the new QueryDef. Before you can check how many records are there in the data that currently matches the specification of the QueryDef, it needs to be opened. This could change between runs so until you run it there is no dataset to check. Does that make the situation a bit clearer?

[phill86]

Hi Neopa

Thanks for the reply

Yes it makes it much clearer I have sorted the problem now

thanks for your help

[NeoPa]

Always a pleasure Phill :)