Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Pass Parameters to procedures

    I have a SQL insert statement that I will perform numerous times from various places in my code, so I am trying to come up with a procedure to do this. The problem is that I don't know how I would call this procedure. Each time I call it I want to pass in different values for the plannercode, param, and val_to_update variables.

    Is there a way to return the number of rows inserted?


    Code:
    Public Sub insert_template(plannercode As String, param As String, val_to_update As String)
                                
        strSQL = "INSERT INTO tbl_StockClasses (PLANNERCODE, PARAMETER, VAL_TO_UPDATE, UPDATE_DATETIME) " & _
                         " VALUES('" & plannercode & "','" & param_name & "','" & val_to_update & "', now());"
                        
        CurrentDb.RunSQL strSQL
        
    End Sub

  2. #2
    Join Date
    Jun 2006
    Location
    Colton, CA
    Posts
    26
    Put the sub into a module, or general declarations.

    Then from within a sub, just do

    insert_template() with the value for each variable seperated by a ,

    For example. Say I wanted to change the backcolor of a form.

    RGB is a function of VB (and VBA). So I could ask my user to enter a value for each of the 3 colours:

    Code:
    Dim myColor, myColorPart1, myColorPart2, myColorPart3 as integer
    
        myColor = inputbox("Please enter a value for Red, Green, and Blue seperated by a ," )
        myColorPart1 = mid(myColor, 1, 3) 'take the first 3 chars
        myColorPart1 = mid(myColor, 5, 3) 'take the second 3 chars
        myColorPart1 = mid(myColor, 9, 3) 'take the last 3 chars
        ChangeColor(myColorPart1, myColorPart2, myColorPart3) 'pass the 3 values to the ChangeColor function
    So say the user enters 100,100,100

    I than pass these parameters to a function called ChangeColor which is a public function in a module

    Code:
    Public Function ChangeColor(Red as integer, Green as Integer, Blue as integer)
    
        me.backcolor = RGB(Red, Green, Blue)
    
    End Function
    Last edited by spikey_richie; 06-19-06 at 20:22.
    Geek it till it mHz

  3. #3
    Join Date
    Sep 2005
    Posts
    220
    OK, I tried that, but I get an error: "Expected: ="

  4. #4
    Join Date
    Jun 2006
    Location
    Colton, CA
    Posts
    26
    You have some conflicting variable names: param in the declare, but you use the value of param_name in the statement

    Try using debug.print to show the values being passed for each of the variables

    Code:
    debug.print "PlannerCode = " & plannercode & " " & ... you get the idea
    Geek it till it mHz

Posting Permissions

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