Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    help with a function (Access 2000)

    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 = [products].[branch1]+[productsTemp].[cartons]," & _
    " products.items1 = [products].[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 = [products].[branch2]+[productsTemp].[cartons]," & _
    " products.items2 = [products].[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

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: help with a function (Access 2000)

    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) & " = [products].[branch" & format$(intX) & "]+[productsTemp].[cartons]," & _
    " products.items & format$(intX) & " = [products].[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)
    Regards
    John



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

    Re: help with a function (Access 2000)

    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.
    Charlotte

  4. #4
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re:help with a function (Access 2000)

    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

  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re:help with a function (Access 2000)

    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

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

    Re:help with a function (Access 2000)

    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
    Charlotte

  7. #7
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: help with a function (Access 2000)

    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?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: help with a function (Access 2000)

    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".

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

    Re: help with a function (Access 2000)

    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.
    <hr> call FncUpdateCartons (gcParis, Or gcLondon Or gcRome)<hr>
    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
    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
  •