Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

put SQL code in VB code

put SQL code in VB code

put SQL code in VB code

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:Dim strSQL As stringstrSQL = "SELECT Sum(FORMCOD.SUPER_COMP)AS SumOfSUPER_COMP FROM FORMCOD WHERE formcod.parcelle = "8097.0" GROUP BY FORMCOD.PARCELLE; "but after this? I don't know how to execute my SQL string.The field I want to fill in isForms.from1_0.SuperficieI 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

RE: put SQL code in VB code

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 !
Hope you find this of value.

Function GetMeasureColumnName1() As String

    Dim mKey As Integer, RSMT As New Recordset, cnn As ADODB.Connection
    Dim sql1 As String, sql2 As String, sql3 As String, sql4 As String
    Dim SqlString As String, CNT As Integer, recCnt As Integer
    Set cnn = CurrentProject.Connection

    sql1 = "SELECT MeasureName.measureName, MeasureIncrementName.sequenceNumber, "
    sql1 = sql1 + "MeasureIncrementName.incrementKey "
    sql1 = sql1 + "FROM MeasureName RIGHT JOIN MeasureIncrementName ON "
    sql1 = sql1 + "MeasureName.measureNameKey = MeasureIncrementName.measureNameKey "
    sql1 = sql1 + "WHERE (((MeasureIncrementName.measureKey)= "
    sql2 = " ) AND ((MeasureIncrementName.incrementKey)= "
    sql3 = " AND MeasureIncrementName.sequenceNumber = 1));"
    SqlString = sql1 & glbMeasureTypeKey & sql2 & glbIncrementKey & sql3
    ''Debug.Print " GetMeasureColumnName1 SQL = "; SqlString
    RSMT.Open SqlString, cnn, adOpenKeyset, adLockReadOnly
    If (RSMT.BOF And RSMT.EOF) Then
        GetMeasureColumnName1 = "No Column 1 record found "
        Exit Function
    End If
    '  Get the Column name
    GetMeasureColumnName1 = RSMT!measureName

End Function

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close