RecordSet Object Returns Value

[ewong1]

I am having some trouble with the code below:

Const strSQLWhere As String = "SELECT [LIV_HR_AppraisalLeadershipDetail].[idRating] " & _
"FROM [LIV_HR_AppraisalLeadershipDetail] " & _
"WHERE ((([LIV_HR_AppraisalLeadershipDetail].[idAppraisalSkillSetHeader])=forms!frmAppraisalHeader!frmLIV_HR_AppraisalSkillSetHeader!idAppraisalSkillSetHeader));"

Public Function fLeadershipRating()

Dim DB As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As String
Dim intResult As Double
Dim intCount As Integer
Dim intRating As Double

intResult = 0
intCount = 0

strSQL = strSQLWhere
Set DB = CurrentDb
    
    Set rs = DB.OpenRecordset(strSQL, dbOpenForwardOnly)
    Do Until rs.EOF
        intResult = intResult + rs!idRating
        intCount = intCount + 1
    Loop
    
    intRating = intResult / intCount
        
    rs.Close
    
    Set rs = Nothing
    Set DB = Nothing
    
Let fLeadershipRating = intRating
End Function

I am trying to return the average rating of a particular skillset based on the query set by strSQLWhere. When I run the code, I get the error message: "Expected: 1" and it takes me to the line where I actually open the recordset - "Set rs = DB.OpenRecordset(strSQL, dbOpenForwardOnly)" It wants me to change dbOpenForwardOnly to Table, but when I do that the system cannot find the information because there is no table called strSQLWhere. Any ideas on how I might be able to accomplish this? Thanks!

 

[MarkK]

My guess is that there's trouble evaluating the reference to the form object as it resides inside the CONST string.
Try...

Public Function fLeadershipRating() As Double
Dim DB As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As String
Dim intResult As Double
Dim intCount As Integer
Dim dblRating As Double

intResult = 0
intCount = 0

[COLOR="Green"]'given that there's only one table, no need to qualify table name
'and evaluates the "Forms!xxx" reference outside the string[/COLOR]
strSQL = "SELECT idRating " & _
"FROM LIV_HR_AppraisalLeadershipDetail " & _
"WHERE idAppraisalSkillSetHeader = " & _
Forms!frmAppraisalHeader!frmLIV_HR_AppraisalSkillSetHeader!idAppraisalSkillSetHeader

Set DB = CurrentDb
    
    Set rs = DB.OpenRecordset(strSQL, dbOpenForwardOnly)
    Do Until rs.EOF
        intResult = intResult + rs!idRating
        intCount = intCount + 1
    Loop
    
    dblRating = intResult / intCount
        
    rs.Close
    
    Set rs = Nothing
    Set DB = Nothing
    
    fLeadershipRating = dblRating

End Function

But you might also consider...

Public Function fLeadershipRating() As Double
  fLeadershipRating = DAvg("idRating", "LIV_HR_AppraisalLeadershipDetail", _
    "idAppraisalSkillSetHeader = " & Forms!frmAppraisalHeader!frmLIV_HR_AppraisalSkillSetHeader!idAppraisalSkillSetHeader
End Function

Hope this helps,
Mark

 

[ewong1]

Thanks! The second option works great! I guess simply is always better.