help with a function (Access 2000)

Topic

New Reply

Help with a function

We have a very complicated function and as you might guess it is complicated because we are not much versed in Access.
Therefore i want to ask for help.

I want to have a common function called Garuda that contains different other fucntions as GarudaLondon,GarudaBerlin,GarudaRome etc.
Depending on the city we are receiving the correspondent module as for example the module GarudaLondon from London.
Each separate module contains the function Garuda where Garuda is equal to the corresponding function.
To be clear i am showing 2 modules:

The module MdlgarudaLondon consists of the following functions:

Public Function Garuda()
Garuda = GarudaLondon
End Function
Public Function GarudaLondon)
Dim strProducts As String
strProducts = ” UPDATE products INNER JOIN productsTemp ON products.Productid = productsTemp.ProductID SET ” & _
” products.branch1 =

• 

  Donate

  Recommended subscription price: $12.00
  Set Price
  

• 

  Extend Your Membership

  Set Price
  

• 

  Free Month of the AskWoody Plus Subscription

  $0.00 for 1 month
  Sign Up Now
  

• 

  JOIN TODAY!

  Set Price
  

• 

  JOIN TODAY!

  Set Price
  

.[branch1]+[productsTemp].[cartons],” & _
” products.items1 =

• 

  Donate

  Recommended subscription price: $12.00
  Set Price
  

• 

  Extend Your Membership

  Set Price
  

• 

  Free Month of the AskWoody Plus Subscription

  $0.00 for 1 month
  Sign Up Now
  

• 

  JOIN TODAY!

  Set Price
  

• 

  JOIN TODAY!

  Set Price
  

.[items1]+[productsTemp].[quantity]”
CurrentDb.Execute strProducts
End Function

The Modle MdlGarudaBerlin consists of the following functions:
Public Function Garuda()
Garuda = GarudaLondon
End Function
Public Function GarudaBerling)
Dim strProducts As String
strProducts = ” UPDATE products INNER JOIN productsTemp ON products.Productid = productsTemp.ProductID SET ” & _
” products.branch2 =

• 

  Donate

  Recommended subscription price: $12.00
  Set Price
  

• 

  Extend Your Membership

  Set Price
  

• 

  Free Month of the AskWoody Plus Subscription

  $0.00 for 1 month
  Sign Up Now
  

• 

  JOIN TODAY!

  Set Price
  

• 

  JOIN TODAY!

  Set Price
  

.[branch2]+[productsTemp].[cartons],” & _
” products.items2 =

• 

  Donate

  Recommended subscription price: $12.00
  Set Price
  

• 

  Extend Your Membership

  Set Price
  

• 

  Free Month of the AskWoody Plus Subscription

  $0.00 for 1 month
  Sign Up Now
  

• 

  JOIN TODAY!

  Set Price
  

• 

  JOIN TODAY!

  Set Price
  

.[items2]+[productsTemp].[quantity]”
CurrentDb.Execute strProducts
End Function

So each city sends their modules. We are situated in Paris.So we have here a form and on its OnOpen event is triggered the function Garuda.
I want to remove the recurring in each module function Garuda() that the cities send, and to have only one function Garuda here.This function mujst be able to

recognize one of the preset functions as GarudaLondon,garudaBerlin,etc.
In other words The function garuda to be triggered vif there is a function GarudaLondon,and if there is a function GarudaBerlin,to be equal to GarudaBerlin,etc.
I am pretty sure that there are a lot of simpler solutions to our problem.
Thank you in advance for the help i will receive

Reply | Quote

Replies

I am not completely clear about what you are doing, but i will have a stab at it.

It looks to me that the only difference between you functions GarudaLondon and GarudaBerling is the number in the SQL. In this case you should be able to have just one function ,

Public Function Garuda( intX as Integer)
Dim strProducts As String
strProducts = ” UPDATE products INNER JOIN productsTemp ON products.Productid = productsTemp.ProductID SET ” & _
” products.branch” & format$(intX) & ” =

• 

  Donate

  Recommended subscription price: $12.00
  Set Price
  

• 

  Extend Your Membership

  Set Price
  

• 

  Free Month of the AskWoody Plus Subscription

  $0.00 for 1 month
  Sign Up Now
  

• 

  JOIN TODAY!

  Set Price
  

• 

  JOIN TODAY!

  Set Price
  

.[branch” & format$(intX) & “]+[productsTemp].[cartons],” & _
” products.items & format$(intX) & ” =

• 

  Donate

  Recommended subscription price: $12.00
  Set Price
  

• 

  Extend Your Membership

  Set Price
  

• 

  Free Month of the AskWoody Plus Subscription

  $0.00 for 1 month
  Sign Up Now
  

• 

  JOIN TODAY!

  Set Price
  

• 

  JOIN TODAY!

  Set Price
  

.[items & format$(intX) & “]+[productsTemp].[quantity]”
CurrentDb.Execute strProducts
End Function

Whenever you call the function use pass it the branch number you want it to use somehow

Garuda(me!BranchID)

Reply | Quote

In addition to John’s suggestion, Access 2000 supports Enums, which means you can declare your own set of “constants” for the acceptable arguments in his slimmed down Garuda function. For example, you could declare an Enum for GarudaCity like this:

Public Enum GarudaCity
gcParis = 0
gcLondon = 1
gcBerlin = 2
End Enum

Then change John’s code declaration like this:

Public Function Garuda(Optional City as GarudaCity = gcParis)

That makes it clear that the argument being passed represents the city, and intellisense will drop down a list of cities (gcParis, gcLondon, etc.) when you use the function in code. Making the argument optional means that whatever you’ve specified as the default (gcParis here) will be used if no GarudaCity value is passed. One warning though. If you use the function in queries, you have to pass the actual numeric value of the argument because queries don’t recognize constants or enums.

Reply | Quote

Thank you very much both of you for the wonderful suggestions you have made.I see now how powerful Access can be.
I have tried to apply your suggestions also to another similar situation in my database and i receive a Syntax error, synstax error in update statement.
First it is better to show what was the original working condition before i tried to slim down my codes with your suggestions:

In the after update event of the control cartons in the subform:

Dim strWhere As String
strWhere = ” WHERE ProductID=” & Me.Productid
Dim db As Database
Dim strSQL As String
Dim StrLondon As String
DIm StrBerlin As String

‘********************
strSQLLondon = “UPDATE Products SET branch1 = branch1 – ” & Me.cartons & strWhere
strSQLBerlin = “UPDATE Products SET branch2 = branch2 – ” & Me.cartons & strWhere
strSQLRome = “UPDATE Products SET branch3 = branch3 – ” & Me.cartons & strWhere
‘we have many cities

Select Case Forms![Orders]![office]
Case 1 ‘London
StrSQL = StrSQLLondon
case 2
STrSQL = StrSQlBerlin
etc etc

End Select

DoCmd.RunSQL strSQL
End Sub

Thank you very much both of you for the wonderful suggestions you have made.I see now how powerful Access can be.
I have tried to apply your suggestions also to another similar situation in my database and i receive a Syntax error, synstax error in update statement.
First it is better to show what was the original working condition before i tried to slim down my codes with your suggestions:

In the after update event of the control cartons in the subform:

Dim strWhere As String
strWhere = ” WHERE ProductID=” & Me.Productid
Set office = Forms![FOrderInformation]![office]
Dim db As Database
Dim strSQL As String
Dim StrLondon As String
DIm StrBerlin As String

‘********************
strSQLLondon = “UPDATE Products SET branch1 = branch1 – ” & Me.cartons & strWhere
strSQLBerlin = “UPDATE Products SET branch2 = branch2 – ” & Me.cartons & strWhere
strSQLRome = “UPDATE Products SET branch3 = branch3 – ” & Me.cartons & strWhere
‘we have many cities

Select Case Forms![Orders]![office]
Case 1 ‘London
StrSQL = StrSQLLondond
case 2
STrSQL = StSQlBerlin
etc etc

End Select

DoCmd.RunSQL strSQL
End Sub
====================================
=======================================

Now i tried to replace all the above lines with the new suggestions as folllows:

Public Enum GarudaCity
gcParis = 0
gcLondon = 1
gcBerlin = 2
End Enum

Private Function MagicCode(Optional City As GarudaCity = gcParis)
Dim strWhere As String
strWhere = ” WHERE ProductID=” & Me.Productid
Dim strBas As String
strBas = “UPDATE Products SET branch & City = branch & City – ” & Me.cartons & strWhere
CurrentDb.Execute strBas
End Function

Reply | Quote

It looks as if you are not joining branch and city together properly, try

strBas = “UPDATE Products SET branch” & City ” = branch” & City ” – ” & Me.cartons & strWhere

HTH

Peter

Reply | Quote

Since City is a number variable, you have to concatenate that number to the string “branch” eveywhere it occurs in the SQL string. The quotes and concatenated values can get a little tricky. Try this:

strBas = “UPDATE Products SET branch” & City & ” = branch” & City & “- ” & Me.cartons & strWhere

Reply | Quote

Dear Charlotte,

I habe followed the wonderful advices and have made anew the codes.I have now update statements where only the number of the branch is different, for example branch0 = Paris, branch1 = London etc.
If i have a function pointing to the number of the branch, then my update statement works, as in:

Public Function FncUpdateCartons()
Dim city As Long
city = 1
strSQL = “UPDATE Products SET ” & _
” products.branch” & city & ” = products.branch” & city & ” – ” & StrCartons & strWhere

etcetc

end function

Now i wanted to improve the function with the introduction of the enum as follows:

Public Enum GarudaCity
gcParis = 0
gcLondon = 1
gcRome = 2
End Enum

And then i rearranged the function to read:

Public Function FncUpdateCartons( Optional city As GarudaCity)
strSQL = “UPDATE Products SET ” & _
” products.branch” & city & ” = products.branch” & city & ” – ” & StrCartons & strWhere
etc etc
End function
And then on the AfterUpdate event of the form i put :

‘ call FncUpdateCartons (gcParis, Or gcLondon Or gcRome)

To my regret i receive no error, but no updating occurs.Coul you tell me why my Enum statement does not work?

Reply | Quote

As a test, insert a statement

MsgBox strSQL

or

Debug.Print strSQL

immediately before you execute the SQL with DoCmd.RunSQL strSQL or CurrentDb.Execute strSQL. Look at the SQL statement displayed. Perhaps you’ll notice a mistake. If you don’t see anything wrong, create a new query with this SQL statement (it must be exactly the same; if you use Debug.Print, you can copy the string from the Immediate window). Try to run this query. You should get a message “You are about to update nn records”.

Reply | Quote

All the enum does is provide intellisense to dropdown a list of valid values. There’s no special magic to it. However, if you make an argument optional then you need to provide a default value for when they don’t pass in a value, i.e., Optional City As GarudaCity = gcParis. The problem you’re having is in the way you called the function. [indent]

---

call FncUpdateCartons (gcParis, Or gcLondon Or gcRome)

---

[/indent] This doesn’t make sense. You created a function to accept a city argument but you’re trying to pass it two values, acParis *and* a boolean value (gcLondon or gcRome). That’s what your comma indicates. The function as written needs a particular city to operate on. If you need it to update all cities, you need to call it for each city like this:

For intLoop = 0 to 2
FncUpdateCartons intLoop
Next intLoop

Reply | Quote