Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Function vs sub (office 2000 on windows 2000)

    I am having a great deal of trouble calling a sub from an on click event on a tabbed form. I am trying to reduce the size of my database by creating a module to handle a routine that is called from about 10 different places on a form. I wanted to write the code once in a module rather than have it exist 10 different times in on click events in the form. So I moved the code to a module and tried to call the sub from the on click events on the various places it occurs on the form. The first on click event I tried was on a tree tabbed sub form. The sub makes various controls visible and sets some other properties depending on which tab is clicked. Getting to the issue!!!!!! It doesn't work. When I go to compile the sub I get an "Invalid use of property" error. The steps I used to create the module are as follows: I created a new module and named it. I added a sub to the module which sets the different properties on the form. In the on click event of the tabbed sub form I call the sub by its name exactly. Can someone guide me through this issue and let me know what I am doing wrong?

    Thank You
    Kevin

  2. #2
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function vs sub (office 2000 on windows 2000)

    How are you referring to the objects/controls on the form? If you are using Me at all, that's your problem. You'll need to refer to the form directly, like Forms("frmMyForm").MyControl , etc.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Function vs sub (office 2000 on windows 2000)

    Yes, I did this already, thanks I changed all the reference using "Me!" and fully qualified the text. Any other suggestions?

    thanks
    Kevin

  4. #4
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function vs sub (office 2000 on windows 2000)

    Can you post the code?

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

    Re: Function vs sub (office 2000 on windows 2000)

    If your routine is called in 10 different places on the same form, there is no need to move the code to a standard module; you can keep it in the form module, where you don't have to worry about how to refer to the form.

    If you want to call the routine from different forms, you should indeed put the code in a standard module. In that case, you can refer to the form that calls the code as CodeContextObject instead of Me, e.g. CodeContextObject.NewRecord or CodeContextID.Requery.

  6. #6
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function vs sub (office 2000 on windows 2000)

    Kevin,

    How are you calling the sub?

    Are you doing it in the Code behind the form or are you doing it from the property Sheet?

    If you are doing it in the property sheet, then the procedure HAS to be a function and not a sub. Also you need to have an = sign before the name of the funcion, =MyFunction()

    If you are calling it from code, can you post the line calling the sub and the sub declaration line?
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function vs sub (office 2000 on windows 2000)

    If you have 10 copies of the same code then what you are doing should reduce the DB size a bit. However the code itself probably takes up little room in the DB. What you are doing in reality is a good coding practice by improving the readability/updateability of your DB.
    Concerning the error message I suspect you are not referencing something correctly. ie Refering to a subform as a form or you have declared cntl as Control and then request a property specific to an edit control or even specifying a property that doesn't exist or isn't modifyable.

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Function vs sub (office 2000 on windows 2000)

    Drew, thanks for getting back to me. I have been out sick. The code I am using is listed below:

    This is the line of code used to call the module procedure:

    Private Sub SUPSHIP_Negotiator_Position_Click()
    SUPSHIP_Negotiator_Position
    End Sub

    This event is triggered from the on click event of a tab on a tab control on the form.

    The code for the procedure in in the module is as follows:

    Sub SUPSHIP_Negotiator_Position()



    If IsNull(DLookup("[Pricing_ID]", "Pricing Detail EB", "[Pricing_ID] = Forms![frmPricingTabbed]![Pricing_ID]")) Then
    DoCmd.SetWarnings False
    Beep
    MsgBox "There is no CONTRACTOR Pricing Detail for this pricing action, therefor no data is available to use as your default position. Please start your data input now!!!!"
    End If
    If IsNull(DLookup("[Pricing_ID]", "Pricing Detail TAR", "[Pricing_ID] = Forms![frmPricingTabbed]![Pricing_ID]")) Then
    DoCmd.SetWarnings False
    Beep
    MsgBox "The TAR has not been completed for this Change Proposal. Enter Pricing Information directly ino the Negotiator input now!!!! If you are performing a desk TAR, please input your pricing Data directly into the negotiator position. If you are not performing a desk TAR please uncheck the Desk TAR block on the screen and enter your pricing data into the negotiator position. "
    Forms![frmPricingTabbed]![Desk_TAR].Visible = True
    Forms![frmPricingTabbed]![Desk_TAR].Value = True
    Forms![frmPricingTabbed]![Desk TAR Label].Visible = True

    Exit Sub
    End If

    If IsNull(DLookup("[Pricing_ID]", "Pricing Detail SUPSHIP", "[Pricing_ID] = Forms![frmPricingTabbed]![Pricing_ID]")) Then
    DoCmd.SetWarnings False
    ' Beep
    ' MsgBox "There is no SUPSHIP NEGOTIATOR Pricing Detail for this pricing action. To simplify input, the Technical Advisory Report (TAR)position Data will be used as the SUPSHIP NEGOTIATOR default position. Data may be edited if you decide not to use the TAR position!!!"
    DoCmd.OpenQuery "APPEND SUPSHIP DETAIL"
    Forms![frmPricingTabbed]![SOS_Rate_Name] = Forms![frmPricingTabbed]![TAR_Rate_Name]
    Forms![frmPricingTabbed]![SOS_Esc_Matl] = Forms![frmPricingTabbed]![TAR_Esc_Matl]
    Forms![frmPricingTabbed]![SOS_Matl_Fee] = Forms![frmPricingTabbed]![TAR_Matl_Fee]
    Forms![frmPricingTabbed]![SOS_Labor_Fee] = Forms![frmPricingTabbed]![TAR_Labor_Fee]
    Forms![frmPricingTabbed]![SOS_Des_Matl] = Forms![frmPricingTabbed]![TAR_Des_Matl]
    Forms![frmPricingTabbed]![SOS_Rate_Version] = Forms![frmPricingTabbed]![TAR_Rate_Version]
    Forms![frmPricingTabbed]![vfcm_sos] = DSum("[Deesc_FCM]", "Pricing Detail SUPSHIP", "[Pricing_ID] = '" & Forms![frmPricingTabbed]![Pricing_ID] & "'")
    Forms![frmPricingTabbed]![vdepreciation_sos] = DSum("[Deesc_Depreciation]", "Pricing Detail SUPSHIP", "[Pricing_ID] = '" & Forms![frmPricingTabbed]![Pricing_ID] & "'")
    Forms![frmPricingTabbed]![vlabor_sos] = DSum("[Labor_Total]", "Pricing Detail SUPSHIP", "[Pricing_ID] = '" & Forms![frmPricingTabbed]![Pricing_ID] & "'")
    Forms![frmPricingTabbed]![vLaw_Energy_sos] = Int(DSum("[Law]", "Pricing Detail SUPSHIP", "[Pricing_ID] = '" & Forms![frmPricingTabbed]![Pricing_ID] & "'") + 0.5) + Int(DSum("[Energy]", "Pricing Detail SUPSHIP", "[Pricing_ID] = '" & Forms![frmPricingTabbed]![Pricing_ID] & "'") + 0.5)
    DoCmd.Requery
    Forms![frmPricingTabbed]![Gov_NegID] = Forms![Splash Screen]![VIdCode]
    DoCmd.SetWarnings True
    End If

    Forms![frmPricingTabbed]![Pricing Detail SUPSHIP].Form.Requery


    End Sub


    Thanks for any suggestions that you might have.

    Kevin
    Kevin

  9. #9
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function vs sub (office 2000 on windows 2000)

    Okay, first of all, when your code tells you there is an invalid property, it should be highlighting what it doesn't 'like'.

    Next, just a cursory glance at your code, and two spots stick out. You have two objects that have spaces in their name. Desk TAR Label and Pricing Detail SUPSHIP. Spaces are BAD, when naming an object. In fact, the reason they are bad, is because your code won't like dealing with the spaces sometimes. This may be one of them.

Posting Permissions

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