Count number of results from search query

[buee04]

Hi,

I need help implementing a record count when I run my query. I want my form, frmSearchResults, to display the number of results that were returned by the query. I have a record count at another place in my db, but it counts the number of records in a table. It is as follows:

Private Sub Form_Current()
Dim db As DAO.Database, rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblJobs&quot
rs.MoveLast: rs.MoveFirst
Me.txtCount = rs.RecordCount + 1

End Sub

But when I tried this with the query, qrySearching, it gives me an error.

Thank you in advance!

 

[BlackKnight]

Hi,

You should avoid using the recordcount property since it isn't supported by all DB providers instead you should use the COUNT() aggregate function inside of a query like:

SELECT Count(*) AS NumRecords FROM table1;

Have a good one!
BK

 

[buee04]

I'm not sure if I understand. This is my first db and I don't have any experience with vb scripting, so could you explain to me what I have to do?

THANKS!!

 

[BlackKnight]

Hi,
Try something like this although having it in the On Current event may cause problems?


Private Sub Form_Current()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("qrySearching&quot
With rs
If .BOF And .EOF Then
MsgBox "no recs"
Else
Me!txtCount = rs!NumRecords + 1
End If
.Close
End With
Set rs = Nothing: Set db = Nothing
End Sub

Have a good one!
BK

 

[buee04]

Hey, sorry to drag this on. But I got the same error:

Run-time error '3601':
Too few parameters. Expected 10.

And it's pointing to the line,
Set rs = db.OpenRecordset("qrySearching&quot

 

[BlackKnight]

Hi,

You didn't mention the query has parameters. <g>

Private Sub Form_Current()
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set qd = db.QueryDefs(&quot;qrySearching&quot

'Substitute between the quotes the name of each parameter in qrySearching.
'Be sure that the parameter value your are assigning to the parameter is of
'the correct data type.
'Change as needbe.
qd.Parameters(&quot;Parm1&quot = &quot;zzz&quot; 'if text data type
qd.Parameters(&quot;Parm2&quot = 10 'if numerical data type
qd.Parameters(&quot;Parm3&quot = &quot;&quot;
qd.Parameters(&quot;Parm4&quot = &quot;&quot;
qd.Parameters(&quot;Parm5&quot = &quot;&quot;
qd.Parameters(&quot;Parm6&quot = &quot;&quot;
qd.Parameters(&quot;Parm7&quot = &quot;&quot;
qd.Parameters(&quot;Parm8&quot = &quot;&quot;
qd.Parameters(&quot;Parm9&quot = &quot;&quot;
qd.Parameters(&quot;Parm10&quot = &quot;&quot;

Set rs = qd.OpenRecordset()
With rs
If .BOF And .EOF Then
MsgBox &quot;no recs&quot;
Else
Me!txtCount = rs!NumRecords + 1
End If
.Close
End With
Set rs = Nothing: Set db = Nothing
End Sub

Have a good one!
BK

 

[buee04]

Ahhh i still don't get it. I didn't know my query had parameters...what are parameters? Which parameter do I substitute? &quot;Param1&quot; or &quot;zzz&quot;

If &quot;zzz&quot;, then what do I sub it with?

confused.

 

[BlackKnight]

Hi,

I see you are new to Access entirely. Please provide the SQL of your query: &quot;qrySearching&quot;.

Have a good one!
BK

 

[buee04]

Hehe, yup. I just started learning Access 2 months ago. Here's the SQL of my query:

SELECT [tblJobs].[ContractNumber], [tblJobs].[Job Name], [tblJobs].[OfficeID], [tblJobs].[Type of Building], [tblJobs].[Area], [tblJobs].[Controls], [tblJobs].[Floor], [tblJobs].[Installed Date]
FROM tblJobs

WHERE ([tblJobs].[ContractNumber] Like &quot;*&quot; & [Forms]![frmSearch]![ContractNumber] & &quot;*&quot; Or IsNull([Forms]![frmSearch]![ContractNumber])) And ([tblJobs].[Job Name] Like &quot;*&quot; & [Forms]![frmSearch]![Job Name] & &quot;*&quot; Or isnull([Forms]![frmSearch]![Job Name])) And ([tblJobs].[OfficeID] Like &quot;*&quot; & [Forms]![frmSearch]!OfficeID & &quot;*&quot; Or isnull([Forms]![frmSearch]!OfficeID)) And ([tblJobs].[Type of Building] Like &quot;*&quot; & [Forms]![frmSearch]![Type of Building] & &quot;*&quot; Or IsNull([Forms]![frmSearch]![Type of Building])) And ([tblJobs].[Controls] Like &quot;*&quot; & [Forms]![frmSearch]![Controls] & &quot;*&quot; Or IsNull([Forms]![frmSearch]![Controls])) And ([tblJobs].[Floor] Like &quot;*&quot; & [Forms]![frmSearch]![Floor] & &quot;*&quot; Or IsNull([Forms]![frmSearch]![Floor])) And (([tblJobs].[Area]>=[Forms]![frmSearch]![MinArea]) Or IsNull([Forms]![frmSearch]![MinArea])) And (([tblJobs].[Area]<=[Forms]![frmSearch]![MaxArea]) Or IsNull([Forms]![frmSearch]![MaxArea])) And (([tblJobs].[Installed Date]>=[Forms]![frmSearch]![MinDate]) Or IsNull([Forms]![frmSearch]![MinDate])) And (([tblJobs].[Installed Date]<=[Forms]![frmSearch]![MaxDate]) Or IsNull([Forms]![frmSearch]![MaxDate]));

Sorry, I just pasted the whole thing cuz I wasn't sure what you wanted.

 

[BlackKnight]

Hi,

Here is how the query: qrySearching should be I think:

SELECT Count(*) AS NumRecords, tblJobs.ContractNumber, tblJobs.[Job Name], tblJobs.OfficeID, tblJobs.[Type Of Building], tblJobs.Controls, tblJobs.Floor, tblJobs.Area, tblJobs.[Installed date]
FROM tblJobs
GROUP BY tblJobs.ContractNumber, tblJobs.[Job Name], tblJobs.OfficeID, tblJobs.[Type Of Building], tblJobs.Controls, tblJobs.Floor, tblJobs.Area, tblJobs.[Installed date]
HAVING (((tblJobs.ContractNumber) Like &quot;*&quot; & Nz([Parm1],&quot;&quot & &quot;*&quot AND ((tblJobs.[Job Name]) Like &quot;*&quot; & Nz([Parm2],&quot;&quot & &quot;*&quot AND ((tblJobs.OfficeID) Like &quot;*&quot; & Nz([Parm3],&quot;&quot & &quot;*&quot AND ((tblJobs.[Type Of Building]) Like &quot;*&quot; & Nz([Parm4],&quot;&quot & &quot;*&quot AND ((tblJobs.Controls) Like &quot;*&quot; & Nz([Parm5],&quot;&quot & &quot;*&quot AND ((tblJobs.Floor) Like &quot;*&quot; & Nz([Parm6],&quot;&quot & &quot;*&quot AND ((tblJobs.Area)>=Nz([Parm7],0) And (tblJobs.Area)<=Nz([Parm8],10000)) AND ((tblJobs.[Installed date]) Between Nz([PARM9],1/1/1900) And Nz([PARM10],Date())));
==========================================================

For example purposes do the following:


1) Put a command button your form: frmSearch. Name the command button cmdSearch. In its ON Click event insert this code.

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set qd = db.QueryDefs(&quot;qrySearching&quot

'Here we are taking the data from your frmSearch
'textboxes etc and assigning it into your query's
'parameters. A parameter is a value that is passed
'to a query or function. It can then be used as
'criteria in your query so...
'The Me!Name should be the name of the control on your
'form: frmSearch
qd.Parameters(&quot;Parm1&quot = Me!ContractNumber
qd.Parameters(&quot;Parm2&quot = Me![Job Name]
qd.Parameters(&quot;Parm3&quot = Me![OfficeID]
qd.Parameters(&quot;Parm4&quot = Me![Type of Building]
qd.Parameters(&quot;Parm5&quot = Me!Controls
qd.Parameters(&quot;Parm6&quot = Me!Floor
qd.Parameters(&quot;Parm7&quot = Nz(Me!MinArea, 0)
qd.Parameters(&quot;Parm8&quot = Me!MaxArea
qd.Parameters(&quot;Parm9&quot = Me!MinDate
qd.Parameters(&quot;Parm10&quot = Me!Maxdate

Set rs = qd.OpenRecordset()
With rs
If .BOF And .EOF Then
MsgBox &quot;no recs&quot;
Else
Me!txtCount = rs!NumRecords + 1
End If
.Close
End With
Set rs = Nothing: Set db = Nothing

Have a good one!
BK

 

[buee04]

I might've messed up somewhere or missed something but when I tried to do a search for some criteria, I got

Microsoft Access can't find the field 'txtCount'
referred to in your expression.

I want to display the number of records on my frmSearchResults form and that's where txtCount is. But for most of the time when I search, it says no recs are found.

Thanks again.

 

[BlackKnight]

Hi,

Change Me!txtCount to forms!frmSearchResults!txtCount.

&quot;Me&quot; refers to the current object, in this case, it would be the form: frmSearch.

I did a test. I left all criteria blank. It should then return every record in the table. It returned a record count of 2 (1 record + 1) which I believe is what you wanted. You can remove +1 if you want.

The query criteria as I understand it is entered on your frmSearch. To my understanding the criteria is:

qd.Parameters(&quot;Parm1&quot = Me!ContractNumber
qd.Parameters(&quot;Parm2&quot = Me![Job Name]
qd.Parameters(&quot;Parm3&quot = Me![OfficeID]
qd.Parameters(&quot;Parm4&quot = Me![Type of Building]
qd.Parameters(&quot;Parm5&quot = Me!Controls
qd.Parameters(&quot;Parm6&quot = Me!Floor

The query will return anything with a partial match on these fields. If the textbox is left empty then all values are returned for that criteria.
=======================================

qd.Parameters(&quot;Parm7&quot = Nz(Me!MinArea, 0)
qd.Parameters(&quot;Parm8&quot = Me!MaxArea

The query will return anything where the field Area is >= MinArea and <= MaxArea. If the MinArea textbox is empty 0 is used. If the MaxArea textbox is empty 10000 is used.
============================

qd.Parameters(&quot;Parm9&quot = Me!MinDate
qd.Parameters(&quot;Parm10&quot = Me!Maxdate

The query will return anything where the field Installed Date is between MinDate and Maxdate. If the MinDate textbox is empty 01/01/1900 is used. If the MaxDate textbox is empty the current date is used.

If I am offbase then please provide a record or 2 of sample data for me to test my solution. As far as i can tell it works correctly.

Have a good one!
BK

 

[BlackKnight]

Hi,

I goofed in my query so copy/paste this query for qrySearching. The previous message from me still applies. <g>

SELECT Count(*) AS RecordCount
FROM [SELECT tblJobs.ContractNumber, tblJobs.[Job Name], tblJobs.OfficeID, tblJobs.[Type Of Building], tblJobs.Controls, tblJobs.Floor, tblJobs.Area, tblJobs.[Installed date]
FROM tblJobs
WHERE (((tblJobs.ContractNumber) Like &quot;*&quot; & Nz([Parm1],&quot;&quot & &quot;*&quot AND ((tblJobs.[Job Name]) Like &quot;*&quot; & Nz([Parm2],&quot;&quot & &quot;*&quot AND ((tblJobs.OfficeID) Like &quot;*&quot; & Nz([Parm3],&quot;&quot & &quot;*&quot AND ((tblJobs.[Type Of Building]) Like &quot;*&quot; & Nz([Parm4],&quot;&quot & &quot;*&quot AND ((tblJobs.Controls) Like &quot;*&quot; & Nz([Parm5],&quot;&quot & &quot;*&quot AND ((tblJobs.Floor) Like &quot;*&quot; & Nz([Parm6],&quot;&quot & &quot;*&quot AND ((tblJobs.Area)>=Nz([Parm7],0) And (tblJobs.Area)<=Nz([Parm8],10000)) AND ((tblJobs.[Installed date]) Between Nz([PARM9],1/1/1900) And Nz([PARM10],Date())))]. AS qryGetRecs;

Have a good one!
BK

 

[buee04]

Hey BK!

Mmm, the query you sent me still does not work. I guess there's a syntax error with FROM [SELECT tblJobs.ContractNumber but I don't know what it should be.

 

[buee04]

The &quot;no recs&quot; message box keeps on popping up after I seasrch, but frmSearchResults do return the correct results when I open it. Can I just take out that if then else statement?

So it is constantly returning true for .bof and .eof so &quot;no record&quot; always pops up, even though a record is found. But when i took out the if statement and only had Forms!frmSearchResults!txtCount = rs!NumRecords + 1, then it gives me an error. I guess it still has incorrect syntax. The error says No current Record.