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

What exactly are you trying to do?  The Execute method is meant to run an action query.  Are you wishing to run your SQL to return a value?  If so, then you are probably best to use the OpenRecordset, something more like:

    Dim db              As DAO.Database

    Dim rs              As DAO.Recordset

    Dim iCount      As Integer

      Dim strSQL As String

    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

Daniel Pineault

http://www.cardaconsultants.com

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]![EmplID]));"

Answer 2

Yes I did insert the debug.print line however, all I am seeing in the Intermediate window is my SQL string.  No value (s/b 1.75) is shown. When I click Form View my VBA error window pops up (End, Debug, Help) where db.Execute is highlighted. 

Also, I just reviewed the utility from Allen Browne.  Very helpful. With this in mind, I tried my SQL again, (somewhat tweaked from the last thread) from the SQL Window.  Allen's utility shows the following:

Dim strSQL As String

Dim db As DAO.Database

Set db = CurrentDb

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]![EmplID]));"

Debug.Print strSQL

db.Execute strSQL, dbFailOnError

Set db = Nothing

Me.txtPerfEvalCalc = strSQL

Unfortuanately the results are the same. Any thoughts?

Answer 3

Did you perform the debug.print?  What does it return?

Another approach is to build your query in the query builder and then use a utility such as the one proposed by Allen Browne (http://allenbrowne.com/ser-71.html) to convert it from plain SQL to a VBA String.  It will take the work out of splitting it into multiple lines and adding any necessary bracketing...

Daniel Pineault

http://www.cardaconsultants.com

Answer 4

Thanks for the reply Daniel.  I have altered my strSQL line to reflect your advice.  However, I am still getting and error in VBA where the db.Execute strSQL is highlighted. 

Are you able to offer any suggestions?

Answer 5

To troubleshoot such issues, you are always best to add a debug.print to see what your strSQL is actually returning as a string to the db.Execute.

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;"

debug.print strSQL

'db.Execute strSQL, dbFailOnError

'Set db = Nothing

'Me.txtPerfEvalCalc = strSQL

End Sub

No doubt you will notice that the SQL string is missing + and spaces...

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;"

Daniel Pineault

http://www.cardaconsultants.com