public update function

Topic

New Reply

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?

Reply | Quote

Replies

Instead of “Me!TOrderID”, you might try “Screen.ActiveControl”.

Reply | Quote

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

Reply | Quote

Try writing it like this:

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

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

Reply | Quote

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.

Reply | Quote

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:

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

You would call it like this:

DeleteRecords “Order Details”, “orders.orderid = ” & Screen.ActiveControl.value

Reply | Quote