put SQL code in VB code

[Cantor]

Hello,I know how to do SQL request. However, I need to do this on a form in access. I have to put the result of my request in a field of a form and I have trouble with that. See the beginning of the code:<b>Dim strSQL As stringstrSQL = &quot;SELECT Sum(FORMCOD.SUPER_COMP)AS SumOfSUPER_COMP FROM FORMCOD WHERE formcod.parcelle = &quot;8097.0&quot; GROUP BY FORMCOD.PARCELLE; &quot;</b>but after this? I don't know how to execute my SQL string.The field I want to fill in is<b>Forms.from1_0.Superficie</b>I hope someone will understand what I tried to explain here. Don't be shy to send me your comments and suggestions, I take all of them.Thanks,Cantor

 

[cmmrfrds]

Cantor, here is a function that I have in one of my applications. You can follow the syntax to get the field names. Just reference the field name by the recordset identifier, such as, RSMT!fieldname. Notice you need the dot notation for constants and method within the recordset. Like RSMT.EOF where field names use ! <br>Hope you find this of value. <br>Jerry<br><br><br>Function GetMeasureColumnName1() As String<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Dim mKey As Integer, RSMT As New Recordset, cnn As ADODB.Connection<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim sql1 As String, sql2 As String, sql3 As String, sql4 As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim SqlString As String, CNT As Integer, recCnt As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;Set cnn = CurrentProject.Connection<br><br>&nbsp;&nbsp;&nbsp;&nbsp;sql1 = &quot;SELECT MeasureName.measureName, MeasureIncrementName.sequenceNumber, &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;sql1 = sql1 + &quot;MeasureIncrementName.incrementKey &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;sql1 = sql1 + &quot;FROM MeasureName RIGHT JOIN MeasureIncrementName ON &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;sql1 = sql1 + &quot;MeasureName.measureNameKey = MeasureIncrementName.measureNameKey &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;sql1 = sql1 + &quot;WHERE (((MeasureIncrementName.measureKey)= &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;sql2 = &quot; ) AND ((MeasureIncrementName.incrementKey)= &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;sql3 = &quot; AND MeasureIncrementName.sequenceNumber = 1));&quot;<br>&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;SqlString = sql1 & glbMeasureTypeKey & sql2 & glbIncrementKey & sql3<br>&nbsp;&nbsp;&nbsp;&nbsp;''Debug.Print &quot; GetMeasureColumnName1 SQL = &quot;; SqlString<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;RSMT.Open SqlString, cnn, adOpenKeyset, adLockReadOnly<br>&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;If (RSMT.BOF And RSMT.EOF) Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;GetMeasureColumnName1 = &quot;No Column 1 record found &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;RSMT.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Exit Function<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;RSMT.MoveFirst<br>&nbsp;&nbsp;&nbsp;&nbsp;'&nbsp;&nbsp;Get the Column name<br>&nbsp;&nbsp;&nbsp;&nbsp;GetMeasureColumnName1 = RSMT!measureName<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;RSMT.Close<br><br>End Function<br>