Getting Query Record Count into Text Box

[Jeff Freilich]

Hi All

I have a way I am dong this but I know there has to be a better/
different way:

I have the following on a form:
Textbox - txt1
ListBox - lst1

lst1 recordssource = SELECT tblProjects.ProjectID,
tblProjects.ProjectClientID FROM tblProjects WHERE
((tblProjects.ProjectClientID) Like [Forms]![frmMain].[txtClientID]));

lst1 recordsouce returns a list of records and I refresh the listcount
of lst1 to txt1 when I want to get the number of records from the
query.

What I want is a way to find the count without using the listbox - I
can write the new SQL query:

SELECT Count(tblProjects.ProjectID) AS CountOfProjectID,
tblProjects.ProjectClientID FROM tblProjects
GROUP BY tblProjects.ProjectClientID HAVING
(((tblProjects.ProjectClientID) Like [Forms]![frmMain].
[txtClientID]));


I just dont know how to actually get this SQL to run and to get this
information and extract the 1 record that this new query into txt1

I hope I explained everything well - if not please let me know

Thanks for our help

Jeff

 

[Jeff Freilich]

I tried this below and am getting an error at the
"SetRTest=TestR.OpenRecordset" line - the error reads - Run Time Error
3061 - Too few parameters. Expected 1.

Am I in the right direction and what is causing the error?

Thanks,

Jeff

Dim TestR As QueryDef
Dim TSQ As String
Dim RTest As Recordset

TSQ = "SELECT Count(tblRequirements.ReqID) AS [Count] FROM (tblClients
INNER JOIN tblProjects ON tblClients.ClientID =
tblProjects.ProjectClientID) INNER JOIN (tblDivision INNER JOIN
tblRequirements ON tblDivision.DivisionID =
tblRequirements.ReqDivisionID) ON tblProjects.ProjectID =
tblDivision.DivisionProjectID WHERE (((tblProjects.ProjectID) Like
[Forms]![frmMain].[txtPRojectID].[Value]));"
Set TestR = CurrentDb.CreateQueryDef("", TSQ)
'TestR.Close
Set RTest = TestR.OpenRecordset

'With RS
' .MoveFirst
'End With
Me.txtProjectC.Value = RTest!Count

 

[Douglas J. Steele]

Change

Dim RTest As Recordset

ti

 

[Douglas J. Steele]

Oops, hit Enter too soon...

Change

Dim RTest As Recordset

to

Dim RTest As DAO.Recordset

You've likely got a reference set to both ADO and DAO, with the ADO
reference being higher than the DAO one. Recordset is an object in both
models, and if the ADO reference is higher, an unqualified declaration will
result in an ADO recordset, not the required DAO one.

 

[Jeff Freilich]

I tried that and that didnt work - but I also tried changing the SQL
with concatenating the variable as oppoed to having the variable in
the SQL string and that seemed to work

New SQL - Working
SELECT Count(tblRequirements.ReqID) AS [Count] FROM (tblClients INNER
JOIN tblProjects ON tblClients.ClientID = tblProjects.ProjectClientID)
INNER JOIN (tblDivision INNER JOIN tblRequirements ON
tblDivision.DivisionID = tblRequirements.ReqDivisionID) ON
tblProjects.ProjectID = tblDivision.DivisionProjectID WHERE
(((tblProjects.ProjectID) Like """ & Me.txtProjectID.Value & """));


Old SQL - not working
SELECT Count(tblRequirements.ReqID) AS [Count] FROM (tblClients INNER
JOIN tblProjects ON tblClients.ClientID = tblProjects.ProjectClientID)
INNER JOIN (tblDivision INNER JOIN tblRequirements ON
tblDivision.DivisionID = tblRequirements.ReqDivisionID) ON
tblProjects.ProjectID = tblDivision.DivisionProjectID WHERE
(((tblProjects.ProjectID) Like [Forms]![frmMain].[txtProjectID].
[Value]));

Any thoughts on why the old one does not work?

Jeff

 

[Douglas J. Steele]

OpenRecordset requires that you resolve all parameters.

You could have used

Dim TestR As QueryDef
Dim parm As DAO.Parameter
Dim TSQ As String
Dim RTest As DAO.Recordset

TSQ = "SELECT Count(tblRequirements.ReqID) AS [Count] " & _
"FROM (tblClients INNER JOIN tblProjects " & _
"ON tblClients.ClientID = tblProjects.ProjectClientID) " & _
"INNER JOIN (tblDivision INNER JOIN tblRequirements " & _
"ON tblDivision.DivisionID = tblRequirements.ReqDivisionID) " & _
"ON tblProjects.ProjectID =tblDivision.DivisionProjectID " & _
"WHERE (((tblProjects.ProjectID)
Like[Forms]![frmMain].[txtPRojectID].[Value]));"
Set TestR = CurrentDb.CreateQueryDef("", TSQ)

For Each parm In TestR.Parameters
parm.Value = Eval(parm.Name)
Next parm

Set RTest = TestR.OpenRecordset

Me.txtProjectC.Value = RTest!Count

but I'd suggest that what you did is probably better.

Sorry I missed the parameter earlier.