Using strSQL

Question

I have an unbound text box in a subform that I would like to reference information from a query via VBA.  When I go from design view to form view my VBA pops up telling me that the line db.Execute strSQL, dbFailOnError is the culprit.  When I hover over this line I do not see a value on strSQL but instead the lengthy SQL code (it should be "1.75"). Any thoughts as to what I am missing on this?

Private Sub Form_Current()

Dim strSQL As String

Dim db As DAO.Database

Set db = CurrentDb

strSQL = "SELECT 0.1666*([tblRLS]![1Opt]+[tblRLS]![2Opt]" _

    + "[tblRLS]![3Opt]+[tblRLS]![4Opt]" _

    + "[tblRLS]![5Opt]+[tblRLS]![6Opt])*0.5" _

    + "0.25*([tblRDS]![CPCDOpt]+[tblRDS]![PDOpt]" _

    + "[tblRDS]![SQSOpt]+[tblRDS]![UOOpt])*0.5 AS PerfEvalCalc " _

    & "FROM tblRLS INNER JOIN tblRDS ON tblRLS.EmplID = tblRDS.EmplID;"

db.Execute strSQL, dbFailOnError

Set db = Nothing

Me.txtPerfEvalCalc = strSQL

End Sub

Answer 1

You might want to try

strSQL = "SELECT tblRLS.EmplID, 0.1666*(tblRLS![1Opt]+tblRLS![2Opt]+tblRLS![3Opt]+tblRLS![4Opt]+tblRLS![5Opt]+tblRLS![6Opt])*0.5+0.25*(tblRDS!CPCDOpt+tblRDS!PDOpt+tblRDS!SQSOpt+tblRDS!UOOpt)*0.5 AS PerfEvalCalc " & vbCrLf & _

"FROM tblRLS INNER JOIN tblRDS ON tblRLS.EmplID = tblRDS.EmplID " & vbCrLf & _

"WHERE (((tblRLS.EmplID)=" & [Forms]![frmPerfEval].Form.[EmplID] & "));"

Daniel Pineault

http://www.cardaconsultants.com

Answer 2

Here is the debug.print output:

SELECT tblRatingsLevelScore.EmplID, 0.1666*(tblRatingsLevelScore![1Opt]+tblRatingsLevelScore![2Opt]+tblRatingsLevelScore![3Opt]+tblRatingsLevelScore![4Opt]+tblRatingsLevelScore![5Opt]+tblRatingsLevelScore![6Opt])*0.5+0.25*(tblSec2RoleDescrScore!CPCDOpt+tblSec2RoleDescrScore!PDOpt+tblSec2RoleDescrScore!SQSOpt+tblSec2RoleDescrScore!UOOpt)*0.5 AS PerfEvalCalc

FROM tblRatingsLevelScore INNER JOIN tblSec2RoleDescrScore ON tblRatingsLevelScore.EmplID = tblSec2RoleDescrScore.EmplID

WHERE (((tblRatingsLevelScore.EmplID)=[Forms]![frmPerfEval]![EmplID]));

I entered this same SQL into a new query and the output is pulling a favorable result.  Your thoughts?

Answer 3

There is something obviously still wrong with the SQL statement.

Add a debug.print after the strSQL to return the SQL statement being generated (Post it back here so we can take a look).  Then copy that result into a new Query designer and try to run it and see what happens.

Daniel Pineault

http://www.cardaconsultants.com

Answer 4

Daniel,

Actually yes, that is exactly what I am trying to accomplish here.  I need to return a SQL value into a text box located within my subform.  Nothing fancy but it is appearing alot more complicated than I originally envisioned.

I have copied your example into my VBA but still have a error.  This time, the line: "Set rs =... " is highlighted.  Outside of placing break points to pinpoint the problem I am unsure of the best way of troubleshooting this.  Your thoughts?

Private Sub Form_Current()

Dim db As DAO.Database

Dim rs As DAO.Recordset

Dim iCount As Integer

Dim strSQL As String

strSQL = <code entered here>

Set db = CurrentDb()

    Set rs = db.OpenRecordset(strSQL) 'open the recordset for use (table, Query, SQL Statement)

    With rs

        If .RecordCount <> 0 Then 'Ensure that there are actually records to work with

            'The next 2 line will determine the number of returned records

            rs.MoveLast 'This is required otherwise you may not get the right count

            iCount = rs.RecordCount 'Determine the number of returned records

            Do While Not .BOF

                'Do something with the recordset/Your Code Goes Here

                PerfEvalCalc = ![PerfEvalCalc]

                .MovePrevious

            Loop

        End If

    End With

    rs.Close 'Close the recordset

    Set rs = Nothing

    Set db = Nothing

    Me.txtPerfEvalCalc = PerfEvalCalc

End Sub