Chapter 8, Problem 7TD

Explanation of Solution

a.

Deleting query in the table “ORDERS”:

Public Function Order_Delete(I_ORDER_NUM)

    Dim strSQL As String

    strSQL = "DELETE FROM ORDERS WHERE ORDER_NUM = '"

    strSQL = strSQL & I_ORDER_NUM

    strSQL = strSQL & "'"

    DoCmd.RunSQL strSQL

End Function

 Explanation:

• Create a function named as “Order_Delete” and pass an argument “I_ORDER_NUM”.
• Set the strSQL string variable to “DELETE FROM ORDERS WHERE ORDER_NUM = '” and makes everything necessary in the command up to and including the single quotation mark preceding the order number.
• The next command will concatenated previous value with the value in “I_ORDER_NUM”.
• The final command is used to set strSQL to the output of the value already created, concatenated with a single quotation mark...

Explanation of Solution

b.

Updating query:

Public Function Order_Update(I_ORDER_NUM, I_ORDER_DATE)

    Dim strSQL As String

    strSQL = "UPDATE ORDERS SET ORDER_DATE = '"

    strSQL = strSQL & I_ORDER_DATE

    strSQL = strSQL & "' WHERE ORDER_NUM = '"

    strSQL = strSQL & I_ORDER_NUM

    strSQL = strSQL & "'"

    DoCmd.RunSQL strSQL

End Function

 Explanation:

• Create a function named as “Order_Update” and pass the arguments “I_ORDER_NUM” and “I_ORDER_DATE”.
• Set the strSQL string variable to “UPDATE ORDERS SET ORDER_DATE = '” and makes everything necessary in the command up to and including the single quotation mark preceding the order number.
• Here, the user pass two arguments, so there are two portions of the construction of the SQL command that involve variables...

Explanation of Solution

c.

Retrieving the list in the table “ITEM”:

Public Function Find_Items(I_CATEGORY)

    Dim rs As New ADODB.Recordset

    Dim cnn As ADODB.Connection

    Dim strSQL As String

    Set cnn = CurrentProject.Connection

    strSQL = "SELECT ITEM_NUM, DESCRIPTION, STOREHOUSE, PRICE FROM ITEM WHERE CATEGORY= '"

    strSQL = strSQL & I_CATEGORY

    strSQL = strSQL & "'"

    rs.Open strSQL, cnn, adOpenStatic, , adCmdText

    Do Until rs.EOF

        Debug.Print (rs!ITEM_NUM)

        Debug.Print (rs!Description)

        Debug.Print (rs!STOREHOUSE)

        Debug.Print (rs!PRICE)

        rs.MoveNext

    Loop

End Function

 Explanation:

• Create a function named as “Find_Items” and pass an argument “I_CATEGORY”.
• The “rs” and “cnn” is used to processing the “Recordset” and “ADODB.Connection”...