By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
439,977 Members | 1,369 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 439,977 IT Pros & Developers. It's quick & easy.

Count records in a dao.querydef

100+
P: 121
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


Expand|Select|Wrap|Line Numbers
  1. Dim RstQueryCheckCal As DAO.QueryDef
  2.  
  3.  
  4.  
  5.     Set dbsEquipBookLinked = DBEngine.OpenDatabase("\\bert\sessiondata$\equipdata\EquipBooking2007v15Data")
  6.  
  7.     Set frm1 = [Forms]![FrmCalBooking]
  8.  
  9.     strSql1 = "PARAMETERS DateStart DateTime, DateEnd DateTime; " & vbCrLf & _
  10.     "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 & _
  11.     "FROM TblCalendar " & vbCrLf & _
  12.     "WHERE (((TimeValue([TblCalendar].[startDate]))>=[dateStart]) AND ((TimeValue([TblCalendar].[endDate]))<=[dateEnd]) AND ((TblCalendar.Location)=[Forms]![FrmCalBooking]![TxtLocation]) AND ((DateValue([TblCalendar].[startDate]))=[Forms]![FrmCalBooking]![TxtStart]));"
  13.  
  14.  
  15.  
  16.      Set RstQueryCheckCal = dbsEquipBookLinked.CreateQueryDef("", strSql1)
  17.  
  18.  
  19.     RstQueryCheckCal.Parameters("dateStart").Value = frm1![TxtStartTime]
  20.     RstQueryCheckCal.Parameters("dateEnd").Value = frm1![TxtEndTime]
  21.     RstQueryCheckCal.Parameters(2) = frm1![TxtLocation]
  22.     RstQueryCheckCal.Parameters(3) = frm1![TxtStart]
  23.  
  24.     If RstQueryCheckCal.RecordCount > 1 Then
  25.  
  26.         'end sub here
  27.  
  28.     End If
Many thanks Phill
Jun 9 '10 #1

✓ answered by 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?

Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,560
It doesn't appear that you have opened your QueryDef anywhere.
Jun 9 '10 #2

100+
P: 121
Hi Neopa

Sorry what do you mean?

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

Many thanks Phill
Jun 9 '10 #3

NeoPa
Expert Mod 15k+
P: 31,560
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?
Jun 9 '10 #4

100+
P: 121
Hi Neopa

Thanks for the reply

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

thanks for your help
Jun 10 '10 #5

NeoPa
Expert Mod 15k+
P: 31,560
Always a pleasure Phill :)
Jun 10 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.