Using SQL Query Code in Form Module to Return Record Count

Z

Zikar

Hi Team,

I am trying to call the following SQL Query from within the Code to return
the count of records that meet a criteria where a field value in the Table
would be equal to the Value of a Control that is within a Subform:

Dim rst As Recordset
Dim SQL As String

SQL = "SELECT
Count(Tbl_Risk_LocalProfiles_ResponsiblePersons.RiskResponsiblePersonRecordID) AS COUNTER " & _
"FROM Tbl_Risk_LocalProfiles_Subform INNER JOIN
Tbl_Risk_LocalProfiles_ResponsiblePersons ON
Tbl_Risk_LocalProfiles_Subform.ProfileUpdate_ID =
Tbl_Risk_LocalProfiles_ResponsiblePersons.ProfileUpdate_ID " & _
"WHERE (((Tbl_Risk_LocalProfiles_ResponsiblePersons.ProfileUpdate_ID)>0) AND
((Tbl_Risk_LocalProfiles_ResponsiblePersons.ProfileUpdate_ID) =
Forms![Risk_ProfileUpdate_Subform].Form![txtProfileUpdate_ID]));"

Set rst = CurrentDb.OpenRecordset(SQL)
Me![NumOfRiskOwners] = rst![COUNTER]
Set rst = Nothing

When I run the code I get the Run-time error '3061':
Too few parameters, Expected 1.

However, when I substitute in the query code a field value which is equal to
the value of the Control in the Subform, in this case is written above as
Forms![Risk_ProfileUpdate_Subform].Form![txtProfileUpdate_ID], then the code
runs fine and returns the correct count number of records. I know my problem
lies in the method I am referencing the Control named txtProfileUpdate_ID in
the Subform which is a main feature for this procedure to work.

I would greatly appreciate if someone can provide me with help to resolve
this issue of correctly referencing the Control in the Subform.

Cheers!
 
S

Steve Sanford

Hi Zikar,

You need to concatenate the criteria to the SQL string. VBA can't evaluate
the reference to the subform.

Try this:

SQL = "SELECT
Count(Tbl_Risk_LocalProfiles_ResponsiblePersons.RiskResponsiblePersonRecordID) AS COUNTER"
SQL = SQL & " FROM Tbl_Risk_LocalProfiles_Subform INNER JOIN"
SQL = SQL & " Tbl_Risk_LocalProfiles_ResponsiblePersons ON"
SQL = SQL & " Tbl_Risk_LocalProfiles_Subform.ProfileUpdate_ID =
Tbl_Risk_LocalProfiles_ResponsiblePersons.ProfileUpdate_ID"
SQL = SQL & " WHERE
Tbl_Risk_LocalProfiles_ResponsiblePersons.ProfileUpdate_ID > 0 AND"
SQL = SQL & " Tbl_Risk_LocalProfiles_ResponsiblePersons.ProfileUpdate_ID = "
& Forms![Risk_ProfileUpdate_Subform].Form![txtProfileUpdate_ID] & ";"

Debug.Print SQL

Note: Each line above should start with SQL =
--watch for line wrap--


HTH
 
Z

Zikar

Steve,

I wish to thank you very much for the given advice. It worked!!. Well
apreciated for the quick help and I beleive you are great.

Cheers

Steve Sanford said:
Hi Zikar,

You need to concatenate the criteria to the SQL string. VBA can't evaluate
the reference to the subform.

Try this:

SQL = "SELECT
Count(Tbl_Risk_LocalProfiles_ResponsiblePersons.RiskResponsiblePersonRecordID) AS COUNTER"
SQL = SQL & " FROM Tbl_Risk_LocalProfiles_Subform INNER JOIN"
SQL = SQL & " Tbl_Risk_LocalProfiles_ResponsiblePersons ON"
SQL = SQL & " Tbl_Risk_LocalProfiles_Subform.ProfileUpdate_ID =
Tbl_Risk_LocalProfiles_ResponsiblePersons.ProfileUpdate_ID"
SQL = SQL & " WHERE
Tbl_Risk_LocalProfiles_ResponsiblePersons.ProfileUpdate_ID > 0 AND"
SQL = SQL & " Tbl_Risk_LocalProfiles_ResponsiblePersons.ProfileUpdate_ID = "
& Forms![Risk_ProfileUpdate_Subform].Form![txtProfileUpdate_ID] & ";"

Debug.Print SQL

Note: Each line above should start with SQL =
--watch for line wrap--


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Zikar said:
Hi Team,

I am trying to call the following SQL Query from within the Code to return
the count of records that meet a criteria where a field value in the Table
would be equal to the Value of a Control that is within a Subform:

Dim rst As Recordset
Dim SQL As String

SQL = "SELECT
Count(Tbl_Risk_LocalProfiles_ResponsiblePersons.RiskResponsiblePersonRecordID) AS COUNTER " & _
"FROM Tbl_Risk_LocalProfiles_Subform INNER JOIN
Tbl_Risk_LocalProfiles_ResponsiblePersons ON
Tbl_Risk_LocalProfiles_Subform.ProfileUpdate_ID =
Tbl_Risk_LocalProfiles_ResponsiblePersons.ProfileUpdate_ID " & _
"WHERE (((Tbl_Risk_LocalProfiles_ResponsiblePersons.ProfileUpdate_ID)>0) AND
((Tbl_Risk_LocalProfiles_ResponsiblePersons.ProfileUpdate_ID) =
Forms![Risk_ProfileUpdate_Subform].Form![txtProfileUpdate_ID]));"

Set rst = CurrentDb.OpenRecordset(SQL)
Me![NumOfRiskOwners] = rst![COUNTER]
Set rst = Nothing

When I run the code I get the Run-time error '3061':
Too few parameters, Expected 1.

However, when I substitute in the query code a field value which is equal to
the value of the Control in the Subform, in this case is written above as
Forms![Risk_ProfileUpdate_Subform].Form![txtProfileUpdate_ID], then the code
runs fine and returns the correct count number of records. I know my problem
lies in the method I am referencing the Control named txtProfileUpdate_ID in
the Subform which is a main feature for this procedure to work.

I would greatly appreciate if someone can provide me with help to resolve
this issue of correctly referencing the Control in the Subform.

Cheers!
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Number of records from Query 5
Update Table Field Value with SQL Query 2
append query and update field problem 13
Record count in form view subforms 5
Duplicate record in Form and Subform 0
Getting Query Record Count into Text Box 5
SQL Query with subForm?? 1
coding a report from a form. 2

Top