putting sql into a text box?

[shutzy]

i am wanting to put some sql in a text box. im not even sure if this is possible but i want to stay away from DSum as the criteria will get very complex and i think i wont be able to manage it.

so i have built a query and got the sql of what i want to do. i thought about vba

Me.TextBox= ("SQL")

but this doesnt work.

im looking to do it this way as i dont want to clogg up my form source just for 2 text boxes..

=("SELECT Sum(tblOrdersItems.ActualCost) AS SumOfActualCost
FROM tblOrders INNER JOIN (tblEmployeeList INNER JOIN (tblOrderItemsType INNER JOIN (tblItems INNER JOIN tblOrdersItems ON tblItems.ItemsID = tblOrdersItems.ItemsID) ON tblOrderItemsType.OrderItemsTypeID = tblItems.OrdersItemsTypeID) ON tblEmployeeList.EmployeeListID = tblOrdersItems.Employee) ON tblOrders.OrderID = tblOrdersItems.OrderID
WHERE (((tblEmployeeList.EmployeeListID)=[Forms]![frmPasswordScreen]![EmployeeListID]) AND ((tblOrders.Status)=3))
GROUP BY tblOrderItemsType.OrderItemsTypeID
HAVING (((tblOrderItemsType.OrderItemsTypeID)=1));")

any suggestions?

 

[pr2-eugin]

Try this simple hack.. Save the Query, by giving it a name (example complexSumQry).. Then all you have to do is just..

Me.TextBoxName = DLookUp("SumOfActualCost", [B]complexSumQry[/B])

If you do wish to use SQL here, which is quiet efficient, you need to know "How to Create and Use Recordset"..

Hope this helps..

 

[shutzy]

you have helped me before with a record set

Dim rs As DAO.Recordset
On Error GoTo Err_Proc
Set rs = CurrentDb.OpenRecordset("ComplexQuerySQL")
    If rs.RecordCount <> 0 Then
        Do While Not rs.EOF
            Me.txtTreatmentTakings= rs.Fields("TreatmentTakingsSum")
            rs.MoveNext
            End If
     Exit_Proc:
    rs.Close
    Set rs = Nothing
    Exit Sub
Err_Proc:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_Proc
    End If
End Sub

is this correct?

 

[pr2-eugin]

Yes.. That's the way you need to go about.. However the Code does not need a Do While bit, as the result set will only have one Record.. So simply checking if the Recordset <> 0 is more than enough..

Also, you can use Saved Queries in Recordsets, but you also have the choice of directly using them in Run time..

This is only my suggestion.. but you seem to have the idea..

Dim rs As DAO.Recordset
On Error GoTo Err_Proc
Set rs = CurrentDb.OpenRecordset("SELECT Sum(tblOrdersItems.ActualCost) AS SumOfActualCost FROM tblOrders INNER JOIN (tblEmployeeList INNER JOIN " & _
                                 "(tblOrderItemsType INNER JOIN (tblItems INNER JOIN tblOrdersItems ON tblItems.ItemsID = tblOrdersItems.ItemsID) ON " & _
                                 "tblOrderItemsType.OrderItemsTypeID = tblItems.OrdersItemsTypeID) ON tblEmployeeList.EmployeeListID = tblOrdersItems.Employee) " & _
                                 "ON tblOrders.OrderID = tblOrdersItems.OrderID WHERE (((tblEmployeeList.EmployeeListID)= " & _
                                 [Forms]![frmPasswordScreen]![EmployeeListID] & ") AND ((tblOrders.Status)=3)) GROUP BY tblOrderItemsType.OrderItemsTypeID " & _
                                 "HAVING (((tblOrderItemsType.OrderItemsTypeID)=1));")
    If rs.RecordCount <> 0 Then
        Me.txtTreatmentTakings= rs.Fields("TreatmentTakingsSum")
    End If
Exit_Proc:
    rs.Close
    Set rs = Nothing
    Exit Sub
Err_Proc:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_Proc
    End If
End Sub

 

[shutzy]

thats a great suggestion. i used to have stack loads of queries saved before i learnt how to use vba. i was very scared of it for sometime and racked up a lot of queries, it got pretty messy. i do now prefer to put it straight into vba as the query is right behind what it represents.

thanks again

 

[shutzy]

im begining to like this vba lark. i can see that by the end the code window will be a million miles long.

 

[shutzy]

for some reason the sql you put in works. the only thing is that i want to add a date range to it so i used the sql i posted and added a date range. pasted it in and it doesnt like it.

whats different?

Dim rs As DAO.Recordset
On Error GoTo Err_Proc
Set rs = CurrentDb.OpenRecordset("SELECT Sum(tblOrdersItems.ActualCost) AS SumOfActualCost FROM tblOrderItemsType INNER JOIN (tblItems INNER JOIN (tblEmployeeList INNER JOIN (tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID) ON tblEmployeeList.EmployeeListID = tblOrdersItems.Employee) ON tblItems.ItemsID = tblOrdersItems.ItemsID) ON tblOrderItemsType.OrderItemsTypeID = tblItems.OrdersItemsTypeID WHERE (((tblEmployeeList.EmployeeListID)=[Forms]![frmPasswordScreen]![EmployeeListID]) AND ((tblOrders.Status)=3) AND ((tblOrdersItems.StartDate) Between Date()-Weekday(Date(),0)+0 And Date()-Weekday(Date(),0)+6)) GROUP BY tblOrderItemsType.OrderItemsTypeID HAVING (((tblOrderItemsType.OrderItemsTypeID)=1));")
    If rs.RecordCount <> 0 Then
        Me.txtTreatmentTakings = rs.Fields("SumOfActualCost")
    End If
    
Set rs = CurrentDb.OpenRecordset("SELECT Sum(tblOrdersItems.ActualCost) AS SumOfActualCost FROM tblOrderItemsType INNER JOIN (tblItems INNER JOIN (tblEmployeeList INNER JOIN (tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID) ON tblEmployeeList.EmployeeListID = tblOrdersItems.Employee) ON tblItems.ItemsID = tblOrdersItems.ItemsID) ON tblOrderItemsType.OrderItemsTypeID = tblItems.OrdersItemsTypeID WHERE (((tblEmployeeList.EmployeeListID)=[Forms]![frmPasswordScreen]![EmployeeListID]) AND ((tblOrders.Status)=3) AND ((tblOrdersItems.StartDate) Between Date()-Weekday(Date(),0)+0 And Date()-Weekday(Date(),0)+6)) GROUP BY tblOrderItemsType.OrderItemsTypeID HAVING (((tblOrderItemsType.OrderItemsTypeID)=2));")
    If rs.RecordCount <> 0 Then
        Me.txtRetailTakings = rs.Fields("SumOfActualCost")
    End If
    
Exit_Proc:
    rs.Close
    Set rs = Nothing
    Exit Sub
Err_Proc:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_Proc

 

[pr2-eugin]

Concatenate the Values shutzy.. Strings need to be enclosed between single quotes ('" & stringVariable &"'), dates between hash symbols (#" & dateVariable & "#).. refer to the code in Post#4.. I am off from my system, so I am afraid I cannot be of much help now..

 

[shutzy]

i am seriously struggling with this

the errors are that

too many )

and the 91 object variable or with blaock variable set

i have tried to put in the # & " with the dates but every combination i try doesnt work. also i cant seem to find the extra ) anywhere. i spent about 2 hours on this yesterday and i am still no closer.

((tblOrderItemsType.OrderItemsTypeID)=1) AND ((tblOrdersItems.StartDate) Between Date()-Weekday(Date(),0)+0 And Date()-Weekday(Date(),0)+7));

 

[pr2-eugin]

Did you Debug the error? Can you post the entire Query?

 

[shutzy]

debug, i need to do alot of debugging. bit late to say that i suppose. my database is riddled with little bugs. its a task i will be tackling soon.

the full sql

SELECT Sum(tblOrdersItems.ActualCost) AS SumOfActualCost FROM tblOrders INNER JOIN (tblEmployeeList INNER JOIN " & _
                                 "(tblOrderItemsType INNER JOIN (tblItems INNER JOIN tblOrdersItems ON tblItems.ItemsID = tblOrdersItems.ItemsID) ON " & _
                                 "tblOrderItemsType.OrderItemsTypeID = tblItems.OrdersItemsTypeID) ON tblEmployeeList.EmployeeListID = tblOrdersItems.Employee) " & _
                                 "ON tblOrders.OrderID = tblOrdersItems.OrderID WHERE (((tblEmployeeList.EmployeeListID)= " & _
                                 [Forms]![frmPasswordScreen]![EmployeeListID] & ") AND ((tblOrders.Status)=3)) GROUP BY tblOrderItemsType.OrderItemsTypeID " & _
                                 "HAVING ((tblOrderItemsType.OrderItemsTypeID)=1) AND ((tblOrdersItems.StartDate) Between Date()-Weekday(Date(),0)+0 And Date()-Weekday(Date(),0)+7));

i copied the sql you put in an earlier post and added the date part at the end. i did the query design again and added the date part. i saw that the date criteria was at the end after tblOrderItemsType.

i hope that this is correct apart from # & "

 

[shutzy]

should there be only 2(( here

(((tblEmployeeList.EmployeeListID)

 

[pr2-eugin]

Okay try this..

Dim stDt As Date, endDt As Date
Dim strSQL As String
stDt = Date() - Weekday(Date(), 0) + 0
endDt = Date() - Weekday(Date(), 0) + 7
strSQL = "SELECT Sum(tblOrdersItems.ActualCost) AS SumOfActualCost FROM tblOrders INNER JOIN (tblEmployeeList INNER JOIN " & _
         "(tblOrderItemsType INNER JOIN (tblItems INNER JOIN tblOrdersItems ON tblItems.ItemsID = tblOrdersItems.ItemsID) ON " & _
         "tblOrderItemsType.OrderItemsTypeID = tblItems.OrdersItemsTypeID) ON tblEmployeeList.EmployeeListID = tblOrdersItems.Employee) " & _
         "ON tblOrders.OrderID = tblOrdersItems.OrderID WHERE (((tblEmployeeList.EmployeeListID)= " & [Forms]![frmPasswordScreen]![EmployeeListID] & ") " & _
         "AND ((tblOrders.Status)=3)) GROUP BY tblOrderItemsType.OrderItemsTypeID " & _
         "HAVING ((tblOrderItemsType.OrderItemsTypeID) = 1) AND ((tblOrdersItems.StartDate) Between #" & stDt & "# And #" & endDt & "#);"

 

[shutzy]

just use the sql you provided, i got an error

3122 you tried to execute a query that does not include the specified expression
'tblOrderItemsType.OrderItemsTypeID = 1 AND tblOrdersItems.StartDate Between #06/16/2013# And #06/23/2013#'

i also get the error

91 object variable or with blaock variable set

here is the complete code i have

Dim rs As DAO.Recordset
On Error GoTo Err_Proc
Dim stDt As Date, endDt As Date
Dim strSQL As String
stDt = Date - Weekday(Date, 0) + 0
endDt = Date - Weekday(Date, 0) + 7
strSQL = "SELECT Sum(tblOrdersItems.ActualCost) AS SumOfActualCost FROM tblOrders INNER JOIN (tblEmployeeList INNER JOIN " & _
         "(tblOrderItemsType INNER JOIN (tblItems INNER JOIN tblOrdersItems ON tblItems.ItemsID = tblOrdersItems.ItemsID) ON " & _
         "tblOrderItemsType.OrderItemsTypeID = tblItems.OrdersItemsTypeID) ON tblEmployeeList.EmployeeListID = tblOrdersItems.Employee) " & _
         "ON tblOrders.OrderID = tblOrdersItems.OrderID WHERE (((tblEmployeeList.EmployeeListID)= " & [Forms]![frmPasswordScreen]![EmployeeListID] & ") " & _
         "AND ((tblOrders.Status)=3)) GROUP BY tblOrderItemsType.OrderItemsTypeID " & _
         "HAVING ((tblOrderItemsType.OrderItemsTypeID) = 1) AND ((tblOrdersItems.StartDate) Between #" & stDt & "# And #" & endDt & "#);"
Set rs = CurrentDb.OpenRecordset(strSQL)
    If rs.RecordCount <> 0 Then
        Me.txtTreatmentTakings = rs.Fields("SumOfActualCost")
    End If
    
Exit_Proc:
    rs.Close
    Set rs = Nothing
    Exit Sub
Err_Proc:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_Proc

have i missed something?

 

[shutzy]

also i have put in the Debug.Print strSQL. dont really know how to use it. if the way to use it is to run the code then i get the same error as reported in my last post

 

[pr2-eugin]

Okay you need to add the StartDate to the GROUP BY clause.. Try the following..

Dim rs As DAO.Recordset
On Error GoTo Err_Proc
Dim stDt As Date, endDt As Date
Dim strSQL As String
stDt = Date - Weekday(Date, 0) + 0
endDt = Date - Weekday(Date, 0) + 7
strSQL = "SELECT Sum(tblOrdersItems.ActualCost) AS SumOfActualCost FROM tblOrders INNER JOIN (tblEmployeeList INNER JOIN " & _
         "(tblOrderItemsType INNER JOIN (tblItems INNER JOIN tblOrdersItems ON tblItems.ItemsID = tblOrdersItems.ItemsID) ON " & _
         "tblOrderItemsType.OrderItemsTypeID = tblItems.OrdersItemsTypeID) ON tblEmployeeList.EmployeeListID = tblOrdersItems.Employee) " & _
         "ON tblOrders.OrderID = tblOrdersItems.OrderID WHERE (((tblEmployeeList.EmployeeListID)= " & [Forms]![frmPasswordScreen]![EmployeeListID] & ") " & _
         "AND ((tblOrders.Status)=3)) GROUP BY tblOrderItemsType.OrderItemsTypeID, [B]tblOrdersItems.StartDate[/B] " & _
         "HAVING ((tblOrderItemsType.OrderItemsTypeID) = 1) AND ((tblOrdersItems.StartDate) Between #" & stDt & "# And #" & endDt & "#);"
Set rs = CurrentDb.OpenRecordset(strSQL)
    If rs.RecordCount <> 0 Then
        Me.txtTreatmentTakings = rs.Fields("SumOfActualCost")
    End If
    
Exit_Proc:
    rs.Close
    Set rs = Nothing
    Exit Sub
Err_Proc:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_Proc

Debug.Print is a debugging tool, that allows you to see the Generated SQL.. To access the Immediate window, hit CTRL+G in the VBA window.. It till show you the generated VBA, if there is any Syntax error or something, you will simply see it.. Debug.Print should be placed before the .Execute statement.. It will generate the code then throw error (i.e. if any)..

The additional Run Time error 91, is happening because you have instructed the compiler to go to the error handling procedure where you are trying to close the Recordset, without even setting/opening it..

 

[shutzy]

thanks. it worked like a charm. ill have to remember that debug thing. can i just ask what the .Execute statement is. is it

Set rs = CurrentDb.OpenRecordset(strSQL)

 

[pr2-eugin]

Set rs = CurrentDb.OpenRecordset(strSQL)

Yes, yes.. that's the one..

Glad you have it sorted.. Good Luck..

 

[shutzy]

sorry to bring up an old thread but i was thinking of making more use of the record set function. does

If rs.RecordCount <> 0 Then

mean that if i set the 0 to 3 then if there is 3 or more records it will trigger the next line.
ie.
If rs.RecordCount > 2 Then
msgbox this client has done this far too many times would you like to place a charge?

i know the vba msg box is incorrect but you get my meaning

can it be used in this way?

 

[pr2-eugin]

You need to MoveNext until the second record.. Then you can do it.. something along the lines of..

If rstObj.RecordCount >0 Then
    If rstObj.RecordCount > = 2 Then
        'do the suff you want to..
        'Make sure you have rstObj.MoveNext here 
    Else
        rstObj.MoveNext
    End IF
End If