Using SQL Query Code in Form Module to Return Record Count

  • Thread starter Thread starter Zikar
  • Start date Start date
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!
 
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,

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...
 
Back
Top