SQL Query to TextBox

[spinalwiz]

This might be a stupid question but how do I get the output of this SQL statement to a textbox:

SELECT Sum (Equipment_Bookings.Quantity * Equipment.CostPerDay)
FROM Equipment_Bookings, Equipment
WHERE Equipment.ID = Equipment_Bookings.Equipment

Thanks

 

[Terpsfan]

You could try this...

Private Sub Form_Load()
    Dim strSQL As String
    strSQL = "SELECT Sum (Equipment_Bookings.Quantity * Equipment.CostPerDay) " & vbCrLf & _
            "FROM Equipment_Bookings, Equipment " & vbCrLf & _
            "WHERE Equipment.ID = Equipment_Bookings.Equipment "
            
            
    txtSQL = strSQL

End Sub

 

[Crowley16]

eh, I believe the guy wanted the OUTPUT of the sql statement...

so what you would do is put that sql statement in the recordsource property of the textbox...

 

[Terpsfan]

Ok, my bad, I didn't read the question properly. What you can do is use the GetString method of an ADO Recordset to put the results of the query into a string. Then you set the text box to the string.

I put a command button and a text box on a form. On the command button's click event run something like this.

Private Sub cmdSQL_Click()

Dim strSQL As String
    Dim strFill As String
    Dim objRec As ADODB.Recordset
    strSQL =  "SELECT Sum (Equipment_Bookings.Quantity * Equipment.CostPerDay) " &  _
            "FROM Equipment_Bookings, Equipment " & _
            "WHERE Equipment.ID = Equipment_Bookings.Equipment "
    
   
    Set objRec = New ADODB.Recordset
    
    objRec.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly, adCmdText
    
    strFill = objRec.GetString(adClipString, ColumnDelimeter:="  ", RowDelimeter:=vbCrLf)
   
    
    Me.Text1 = strFill

    objRec.Close
    Set objRec = nothing

End Sub

 

[spinalwiz]

Great. Thanks, Ill try it out tomorrow.