Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    public update function

    I have an update function in the OnClick event of a control called Torderid.I want to make
    a global funnction to be executed from every form which has a control TOrderid.In
    other words i want to use this update function in many forms which have a control
    called TOrderID.

    The following code is executed in the OnClick event of the control TOrderID :

    CurrentDb.Execute "UPDATE orders INNER JOIN (products INNER JOIN [order details] ON

    (products.Productid = [order details].ProductID) AND (products.Productid = [order

    details].ProductID)) ON orders.orderid = [order details].OrderID SET products.stock =

    products.stock+[order details].cartons WHERE orders.orderid = " & Me!TOrderid & ";"
    End Function


    I want to make a public function called fnctUpdate and i want to put the control TOrderID
    in the brackets, but how cani replace the Me in the global function?

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: public update function

    Instead of "Me!TOrderID", you might try "Screen.ActiveControl".
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: public update function

    CurrentDb.Execute "UPDATE orders INNER JOIN (products INNER JOIN [order details] ON (products.Productid = [order details].ProductID) AND (products.Productid = [order details].ProductID)) ON orders.orderid = [order details].OrderID SET products.stock = products.stock+[order details].cartons WHERE orders.orderid = " & Screen.ActiveControl & ""

    Dear Sir,
    Thank you so much for your advice.It works excellent and saves a lot of time!!!!
    No that i solved the problem, may i ask you an additionalk question,applying your solution. .I think i can further simplyfy my coding.I have two functions,they are similar ,only the Where clause is dufferent.In the first function, like the above , the diferring part of the where clause is
    WHERE orders.orderid
    in the other function the differing part of the where clause is :
    WHERE orders.paymentid

    The full second function is the following
    CurrentDb.Execute "UPDATE orders INNER JOIN (products INNER JOIN [order details] ON (products.Productid = [order details].ProductID) AND (products.Productid = [order details].ProductID)) ON orders.orderid = [order details].OrderID SET products.stock = products.stock+[order details].cartons WHERE orders.paymentid = " & Screen.ActiveControl & ""

    So you see, the two functions are different only in that part of the where clause.Is it possbile to create a constant and then replace this constant,or something like that?
    I am very grateful to you anyway

    Please accept my best regards

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: public update function

    Try writing it like this:

    <pre>Public Function UpdateOrders(ByVal strWhere As String)
    Dim strSQL As String

    strSQL = "UPDATE orders INNER JOIN (products INNER JOIN " _
    & " [order details] ON (products.Productid = [order details].ProductID) " _
    & "AND (products.Productid = [order details].ProductID)) " _
    & "ON orders.orderid = [order details].OrderID SET " _
    & "products.stock = products.stock+[order details].cartons " _
    & "WHERE " & strWhere

    CurrentDb.Execute strSQL
    End Function</pre>


    Then create the Where string in the routine that calls the function and simply pass it the literal string to use as the WHERE expression. So in the first instance, you would call it like this:

    UpdateOrders "orders.orderid = " & Screen.ActiveControl.value

    and in the second instance, you would call it like this:

    UpdateOrders "orders.paymentid = " & Screen.ActiveControl.value
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: public update function

    Dear Charlotte,

    I want to thank you to you and all other contributors to this forum for the excellent advice
    and wonderful solutions i have received.
    The update code suggested by you works wonderful and without errors. I had 4 functions before and

    now i will have one!!!
    May i ask you for some additional help? I have simplified my code because i wanted to be more

    clear and also i thought i could manage it myself.But i cant.
    Actually after the update process,which you have solved sucessfully, i have to carry our a

    deletion process as follows

    CurrentDb.Execute "DELETE * FROM [Order Details] WHERE orderid = " & orderid & ";"
    CurrentDb.Execute "DELETE * FROM [Orders] WHERE orderid = " & orderid & ";"

    and another function for the paymentid,if i have to delete the payments:

    CurrentDb.Execute "DELETE * FROM [Order Details] WHERE paymentid = " & paymentid & ";"
    CurrentDb.Execute "DELETE * FROM [Orders] WHERE paymentid = " & paymentid & ";"


    Since it may be the same Where clause i tried to put it inside but i received an
    error too few parameters.
    If i can manage to do this i will have a miracle code.Can you help?I want to do it all in one

    function.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: public update function

    OK, this one is a little different because you're trying to delete from different tables. That means you have to pass both the WHERE string and the table name into the function. You could do it like this:

    <pre>Public Function DeleteRecords(ByVal strTableName As String, _
    ByVal strWhere As String)
    Dim strSQL As String

    strSQL = ""DELETE * FROM [" & strTableName & "] " _
    & "WHERE " & strWhere & ";"

    CurrentDB.Execute strSQL
    End Function</pre>

    You would call it like this:

    DeleteRecords "Order Details", "orders.orderid = " & Screen.ActiveControl.value
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •